Diving Into The Basics of SQL Server

Let’s start our journey practically with SQL. I’ve attached the Chinook files; you can just run the script and make your environment ready for work. To learn the database, most of the time, we need to build the schema and put some data inside. But to speed up the learning and make our hands dirty with code as soon as possible, large organizations provide us the pre-built database and to make some experiments on it. So here, we’re using Chinook database and I’m working on SQL Server 2014.

The examples are so important to know the implementation of the things. It makes it extremely easy for a developer or DBA to understand what is happening under the hood. So, in our complete series, we’ll mostly work with examples and discuss a little bit about the results of those example queries.

Order By

The Order By clause is used to arrange the results in Ascending or Descending order.

Example 1

SELECT InvoiceId
, InvoiceDate
, Total
FROM Invoice
WHERE Total > 10
ORDER BY Total

Here, we’re displaying the invoice information and applying the check with the help of Where clause if Total is greater than 10. And then, we’re ordering the results by Total in an ascending order.

As we’ve not explicitly mentioned any order type, it is by default in ascending order.

Example 2

Now, we want to display the records in descending order. So,

SELECT InvoiceId
, InvoiceDate
, Total
FROM Invoice
WHERE Total > 10
ORDER BY Total DESC

BETWEEN & AND

Now, we want to display the items within a specific range. So for that, we’ll use BETWEEN & AND. These are the reserved keywords in SQL actually. Let’s see how we use them in SQL.

SELECT InvoiceId
, InvoiceDate
, Total
FROM Invoice
WHERE Total
BETWEEN 10.39
AND 15.9
ORDER BY Total

Now, you might wonder if it is necessary to use both the keywords at the same time. Well, the answer will be "No, we can write the same query without BETWEEN as well".

SELECT InvoiceId
, InvoiceDate
, Total
FROM Invoice
WHERE Total >= 10.39
AND Total <= 15.9
ORDER BY Total

And now, you’ll get the same number of rows in the result.

Example 2

Let’s filter the records on the basis of date.

SELECT InvoiceId
, InvoiceDate
, Total
FROM Invoice
WHERE InvoiceDate
BETWEEN '2012-01-01'
AND '2012-12-31'
ORDER BY InvoiceDate

Actually, if you didn’t specify the time, SQL Server stores the time of midnight (00:00:00:000) with the data given by the user automatically. And if you run this script, as you’ve not any demand of time range, obviously you’ll see all the records between these 2 dates. And if you’ve input the time in the table for data, then you’ll see the results where the time will be (00:00:00:000).

Alias or Assign

Alias is the thing which we use to rename our attribute with the help of as keyword.

SELECT InvoiceId
, UnitPrice
, Quantity
, UnitPrice * Quantity AS Total
FROM InvoiceLine

If you see the results of the above code in the Output window, you’ll see 4 attributes containing data - InvoiceId, UnitPrice, Quantity, and Total.

Now, let’s suppose we don’t want to use the "as" keyword. So, we’ll use the increment operator.

SELECT InvoiceId
, UnitPrice
, Quantity
, Total = UnitPrice * Quantity
FROM InvoiceLine

Now again, you’ll see the same result.

LEFT FUNCTION

With the help of Left() function, we get the letters from the left side of how much we specified.

SELECT EmployeeId
, LEFT(FirstName, 1) + ' ' + LastName AS FullName
, City
FROM Employee
ORDER BY FirstName

Let us run the query and watch the results in the Output window.

Look, we get the first letter of FirstName with the help of LEFT() function.

How to find Age from Date of Birth?

Here, we’ll use DateDiff() sql function to find out the difference from Date of Birth to today’s date and we’ll get the current date dynamically. Let’s see what happens,

SELECT EmployeeId
, FirstName
, LastName
, BirthDate
, Convert(varchar(10), BirthDate, 10) AS FormattedBirthDay
, DateDiff(Year, BirthDate, GetDate()) AS Age
FROM Employee

SQL Server is basically using Visual Studio Shell under the hood. So you might see that when your Visual Studio Environment is not ready, your SQL Server Management Studio also doesn’t work. The purpose of this talk is to tell you that in SSMS, we also have the feature of IntelliSense. If we write the function, then with the help of IntelliSense, we know what arguments we can input inside the function.

Here is the result of our query.

Now you might be thinking of how Convert() works, here is the detailed link. But still, you might be confused about what is 10 inside Convert(). Actually, it is the DateTime style. On MSDN, all the Date and Time styles are mentioned.

How to Show Different Records?

Here, we are accessing all the Billing Cities from each record in the table.

SELECT BillingCity
FROM Invoice
ORDER BY BillingCity

But now, we want to show the distinct Billing Cities. However, a single city name is available in multiple records but we want distinct. So, let's write this query.

SELECT DISTINCT BillingCity
FROM Invoice
ORDER BY BillingCity

And now, you’ll see all the distinct records.

Top 5 Records

SQL Server has a nice feature to select top records.

SELECT TOP 5 InvoiceId, Total FROM Invoice
ORDER BY Total

If we want to show the 5% records of the table, we write like this.

SELECT TOP 5 PERCENT InvoiceId, Total FROM Invoice ORDER BY Total

Total Number of Records in a table

SELECT COUNT(*) FROM Invoice

So, it will show the all number of records in a table as an Output.

Not Equal To

There are two syntaxes of not equal to in SQL Server (<> & !=)

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState <> NULL
ORDER BY BillingState

And similarly, we can write the same statement in this way.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState != NULL
ORDER BY BillingState

Yes, we have another variation to write the query as well.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND BillingState > 'B'
ORDER BY BillingState

In this query, it will check if the BillingState contains the letter greater than B.

Little Confusion

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND BillingState = 'AZ'
OR BillingState = 'CA'
AND Total > 4
ORDER BY BillingState

Look, this query is quite confusing to understand. If you just up and down your conditional expression with AND, OR; you’ll see the variations in results. So, always write the simple code.

Clean Script

Here, the script code is very clear.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND (BillingState = 'AZ'
OR BillingState = 'CA')
AND Total > 4
ORDER BY BillingState

Include Some Specific Data Records with IN keyword

So here, we’re using IN keyword in the SQL Server.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND BillingState IN ('AZ', 'NV', 'MA', 'CA')
ORDER BY BillingState

And similarly, if we don’t want to show the records which don’t contain the BillingState ('AZ', 'NV', 'MA', 'CA'), we’ll just use NOT with IN.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND BillingState NOT IN ('AZ', 'NV', 'MA', 'CA')
ORDER BY BillingState

Like Phrase

In SQL Server, we have Like keyword. Let’s suppose we want to display the records where a specific attribute containing data starts with a specific letter.

And here, we have many symbols to work for wildcards.

Symbol	Purpose
%	Wildcard
_	Single Character
[]	A single character from the list
[-]	Character ranges
[^]	Not in List

Now, let’s use some symbols in the query.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
WHERE BillingState IS NOT NULL
AND BillingState LIKE 'N%'
ORDER BY BillingState

It will show the records where BillingState starts with N.

Now, instead of discussing all the Like phrase symbols, let me tell you how can we use it.

LIKE ‘N_’ i.e. NT, NS, NS, NA etc.
LIKE ‘N[TV]’ i.e. NT, NV etc.
LIKE ‘N[A-M]’ i.e. it will show all the BillingStates of the Range NA-NM
LIKE ‘N[^N-V]’ i.e. which is not in range of N-V

You can apply these examples and watch the results on your own.

FETCH & OFFSET

This is helpful when we are working on pagination and we have to show the data into the Grid.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
ORDER BY BillingState, BillingCity
OFFSET 0 ROWS
FETCH FIRST 10 ROWS ONLY

And now, we want to show the next 10 rows. So,

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
ORDER BY BillingState, BillingCity
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY

And now we want to show the next 10 rows.

SELECT InvoiceId
, InvoiceDate
, BillingState
, BillingCity
, Total
FROM Invoice
ORDER BY BillingState, BillingCity
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

OFFSET basically is the point from where you start getting your results.

How to Comment SQL Queries?

If we want to make the script comment, then we’ll use double dash. If you know Visual Studio shortcuts, here we can also use (Ctrl + K, C) for commenting and for uncommenting (Ctrl + K, U).

--SELECT InvoiceId
-- , InvoiceDate
-- , BillingState
-- , BillingCity
-- , Total
--FROM Invoice
--ORDER BY BillingState, BillingCity
--OFFSET 20 ROWS
--FETCH NEXT 10 ROWS ONLY

Look now the complete query is commented. And obviously, we can manually comment any line of the script by putting double dashes in the start of a line.

Cast & Convert

There is no difference between these two functions. Both work for the same purpose.

SELECT CAST(1 AS VARCHAR) + 'ABC'
SELECT CONVERT(INT, 14.87)
SELECT CONVERT(DATETIME, '2018-05-02')
-- 3rd parameter is for different style of date and it prints it in string form.
SELECT CONVERT(VARCHAR, GETDATE())
SELECT CONVERT(VARCHAR, GETDATE(), 101)
SELECT CONVERT(VARCHAR, GETDATE(), 102)
SELECT CONVERT(VARCHAR, GETDATE(), 103)
SELECT CONVERT(VARCHAR, GETDATE(), 104)

The only difference is that in CAST(), we got data first and datatype later in which we want to cast our data. But in the CONVERT() function, datatype comes first and data comes at the 2^nd argument and the 3^rd parameter is optional. 3^rd parameter represents the different styles for data and it is mostly used for datetime data values.

Default Values

Actually when we create the table then we specify if it is nullable or not and if we specify it is not nullable then it is sure then some value this attribute will always contain. Now we can define our own the default value for any attribute if the user does not enter its value. So,

CREATE TABLE ModifyData(
Id int PRIMARY KEY IDENTITY(1,1) NOT NULL,
FirstName nvarchar(50) NOT NULL,
LastName nvarchar(50) NOT NULL,
Department nvarchar(10) NOT NULL DEFAULT 'R&D',
HireDate DATE NOT NULL DEFAULT GETDATE()
)

So this is how we create the default values. We just need the DEFAULT keyword and then we specify the default value for that attribute.

And in this same query, we can get the idea of how to create the table.

SELECT INTO

It is used to copy the data from one table to another. With the help of this statement, we create the table with the same attribute names with the same types as we defined in the old table. But here only column definition and data is copied into the new table. You don’t get PK/FK, indexes, Defaults etc in this new table.

Now let’s see it in action but first of all, insert some data in it.

INSERT INTO ModifyData(FirstName, LastName, Department) VALUES ('Usama', 'Shahid', 'SE')

Now let’s make a copy of ModifyData

SELECT * INTO ModifyDataCopy
FROM ModifyData

And now, if you open ModifyDataCopy Design tab, you’ll see there is no PK.

Now let’s do more insertion in ModifyDataCopy,

INSERT INTO ModifyDataCopy(FirstName, LastName, Department) VALUES ('Safwan', 'Shahid', 'SE')

And now you’ll see the error because Id attribute is not the primary key here and HireDate doesn’t contain Default Date value so it is null. And it fails because it is defined as not null.

So we have proved what we say.

MERGE

Now you might be wondering about MERGE statement. In SQL Server, MERGE statement is a little bit different because here, we can perform CRUD operation inside the MERGE statement.

For Merging we at least needs two tables.

Source Table
It has the data that we need to apply on the Target table.
Target Table
It is the place where we’ll perform MERGE operation and change the things.

MERGE statement actually joins the tables on the basis of some common attribute. And obviously, it is compulsory to have at least one common attribute to join the tables anywhere. And on the basis of this matchup of attributes, we can perform CRUD operations.

Let’s see the syntax of the MERGE statement.

MERGE [TARGET] AS T
USING [SOURCE] AS S
ON [JOIN_CONDITIONS]
WHEN MATCHED THEN
[UPDATE STATEMENT]
WHEN NOT MATCHED BY TARGET THEN
[INSERT STATEMENT]
WHEN NOT MATCHED BY SOURCE THEN
[DELETE STATEMENT]

So let’s create new tables containing some data inside.

Create table SourceTbl
(
ID int primary key,
Name nvarchar(20)
)
GO
Insert into SourceTbl values (1, 'USAMA')
Insert into SourceTbl values (2, 'SAFWAN')
GO
Create table TargetTbl
(
ID int primary key,
Name nvarchar(20)
)
GO
Insert into TargetTbl values (1, 'HASSAN')
Insert into TargetTbl values (3, 'GULRAIZ')
GO

Now both tables have ID common attribute containing one common ID ‘1’. Now let’s apply the MERGE statement according to the given syntax.

MERGE TargetTbl AS T
USING SourceTbl AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name) VALUES (S.ID, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Now let me tell you what it does.

In our TargetTbl has the same key record as we have SourceTbl ‘1’. So ON expression matches and it updates T.Name as we have Name in Name.
The rows which are present in our Source table but not present in Target table, we’re inserting those rows in Target table from Source table. In this statmenet,
1. INSERT INTO TblName VALUES ()
INTO is actually optional. So it is working in our MERGE query.

The rows which are present in Target table but not present in Source table -- we are deleting them.

So after running above MERGE statement when you run this statement.

SELECT * FROM TargetTbl

We’ll get this result,

And this is how our tables are merged.

One more thing, the semi-colon is compulsory after MERGE statement otherwise, you’ll see the error in Messages Window ‘A MERGE Statement must be terminated by a semi-colon(;)’

If we talk about real life examples, we never take the decision of deleting the records from the table very easily. We always care about the data. So, just remove the 3^rd WHEN statement and our query will become like this.

MERGE TargetTbl AS T
USING SourceTbl AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.Name = S.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Name) VALUES (S.ID, S.Name);