Post
Ask Question

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , Total  
  4. FROM Invoice  
  5. WHERE Total > 10  
  6. 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,

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , Total  
  4. FROM Invoice  
  5. WHERE Total > 10  
  6. 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.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , Total  
  4. FROM Invoice  
  5. WHERE Total  
  6. BETWEEN 10.39  
  7. AND 15.9  
  8. 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".

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , Total  
  4. FROM Invoice  
  5. WHERE Total >= 10.39  
  6. AND Total <= 15.9  
  7. 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.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , Total  
  4. FROM Invoice  
  5. WHERE InvoiceDate   
  6. BETWEEN '2012-01-01'  
  7. AND '2012-12-31'  
  8. 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.

  1. SELECT InvoiceId  
  2.         , UnitPrice  
  3.         , Quantity  
  4.         , UnitPrice * Quantity AS Total  
  5. 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.

  1. SELECT InvoiceId  
  2.         , UnitPrice  
  3.         , Quantity  
  4.         , Total = UnitPrice * Quantity  
  5. 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.

  1. SELECT EmployeeId  
  2.         , LEFT(FirstName, 1) + ' ' + LastName AS FullName  
  3.         , City  
  4. FROM Employee  
  5. ORDER BY FirstName  

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

Basics of SQL Server 

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,

  1. SELECT EmployeeId  
  2.         , FirstName  
  3.         , LastName  
  4.         , BirthDate  
  5.         , Convert(varchar(10), BirthDate, 10) AS FormattedBirthDay  
  6.         , DateDiff(Year, BirthDate, GetDate()) AS Age  
  7. 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.

Basics of SQL Server 

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.

  1. SELECT BillingCity  
  2. FROM Invoice  
  3. 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.

  1. SELECT DISTINCT BillingCity  
  2. FROM Invoice  
  3. 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.

  1. SELECT TOP 5 InvoiceId, Total FROM Invoice  
  2. ORDER BY Total  

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

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

Total Number of Records in a table

  1. 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 (<> & !=)

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState <> NULL  
  8. ORDER BY BillingState  

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState != NULL  
  8. ORDER BY BillingState  

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND BillingState > 'B'  
  9. ORDER BY BillingState  

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

Little Confusion

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND BillingState = 'AZ'  
  9. OR BillingState = 'CA'  
  10. AND Total > 4  
  11. 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.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND (BillingState = 'AZ'  
  9. OR BillingState = 'CA')  
  10. AND Total > 4  
  11. ORDER BY BillingState  

Include Some Specific Data Records with IN keyword

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND BillingState IN ('AZ', 'NV', 'MA', 'CA')  
  9. 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.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND BillingState NOT IN ('AZ', 'NV', 'MA', 'CA')  
  9. 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.

SymbolPurpose
%Wildcard
_Single Character
[]A single character from the list
[-]Character ranges
[^]Not in List

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. WHERE BillingState IS NOT NULL  
  8. AND BillingState LIKE 'N%'  
  9. 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.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. ORDER BY BillingState, BillingCity  
  8. OFFSET 0 ROWS  
  9. FETCH FIRST 10 ROWS ONLY  

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

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. ORDER BY BillingState, BillingCity  
  8. OFFSET 10 ROWS  
  9. FETCH NEXT 10 ROWS ONLY  

And now we want to show the next 10 rows.

  1. SELECT InvoiceId  
  2.         , InvoiceDate  
  3.         , BillingState  
  4.         , BillingCity  
  5.         , Total  
  6. FROM Invoice  
  7. ORDER BY BillingState, BillingCity  
  8. OFFSET 20 ROWS  
  9. 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).

  1. --SELECT InvoiceId  
  2. --      , InvoiceDate  
  3. --      , BillingState  
  4. --      , BillingCity  
  5. --      , Total  
  6. --FROM Invoice  
  7. --ORDER BY BillingState, BillingCity  
  8. --OFFSET 20 ROWS  
  9. --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.

  1. SELECT CAST(1 AS VARCHAR) + 'ABC'  
  2. SELECT CONVERT(INT, 14.87)  
  3. SELECT CONVERT(DATETIME, '2018-05-02')  
  4.   
  5. -- 3rd parameter is for different style of date and it prints it in string form.  
  6. SELECT CONVERT(VARCHAR, GETDATE())  
  7. SELECT CONVERT(VARCHAR, GETDATE(), 101)  
  8. SELECT CONVERT(VARCHAR, GETDATE(), 102)  
  9. SELECT CONVERT(VARCHAR, GETDATE(), 103)  
  10. 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 2nd argument and the 3rd parameter is optional. 3rd 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,

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

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.

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

Now let’s make a copy of ModifyData

  1. SELECT * INTO ModifyDataCopy  
  2. FROM ModifyData  

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

Basics of SQL Server 

Now let’s do more insertion in ModifyDataCopy,

  1. 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.

  1. MERGE [TARGET] AS T  
  2. USING [SOURCE] AS S  
  3.    ON [JOIN_CONDITIONS]  
  4.  WHEN MATCHED THEN   
  5.       [UPDATE STATEMENT]  
  6.  WHEN NOT MATCHED BY TARGET THEN  
  7.       [INSERT STATEMENT]   
  8.  WHEN NOT MATCHED BY SOURCE THEN  
  9.       [DELETE STATEMENT]  

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

  1. Create table SourceTbl  
  2. (  
  3.      ID int primary key,  
  4.      Name nvarchar(20)  
  5. )  
  6. GO  
  7. Insert into SourceTbl values (1, 'USAMA')  
  8. Insert into SourceTbl values (2, 'SAFWAN')  
  9. GO  
  10. Create table TargetTbl  
  11. (  
  12.      ID int primary key,  
  13.      Name nvarchar(20)  
  14. )  
  15. GO  
  16. Insert into TargetTbl values (1, 'HASSAN')  
  17. Insert into TargetTbl values (3, 'GULRAIZ')  
  18. 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.

  1. MERGE TargetTbl AS T  
  2. USING SourceTbl AS S  
  3. ON T.ID = S.ID  
  4. WHEN MATCHED THEN  
  5.     UPDATE SET T.Name = S.Name  
  6. WHEN NOT MATCHED BY TARGET THEN  
  7.     INSERT (ID, Name) VALUES (S.ID, S.Name)  
  8. WHEN NOT MATCHED BY SOURCE THEN  
  9.     DELETE;  

Now let me tell you what it does.

  1. 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.

  2. 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.
  1. 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.

  1. SELECT * FROM TargetTbl  

We’ll get this result,

Basics of SQL Server 

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 3rd WHEN statement and our query will become like this.

  1. MERGE TargetTbl AS T  
  2. USING SourceTbl AS S  
  3. ON T.ID = S.ID  
  4. WHEN MATCHED THEN  
  5.     UPDATE SET T.Name = S.Name  
  6. WHEN NOT MATCHED BY TARGET THEN  
  7.     INSERT (ID, Name) VALUES (S.ID, S.Name);  


Similar Articles