Post
Ask Question

Basic SQL Queries For Beginners

Table of Contents

  1. Create and Insert Query
  2. Simple SQL Select Query
  3. Aggregate and Scalar Function
  4. Date Function
  5. Other Select Function
  6. Where
  7. Group BY and Having Clause
  8. Sub Query
  9. Joins
  10. UNION and UNION ALL.
  11. Common Table Expressions(CTE) - With
  12. View
  13. Pivot
  14. Stored Procedure (SP)
  15. Functions
  16. SQL Server Coding Standards
  17. Few Worked Examples

1. Create and Insert Query

Before we start we will first create a table and insert some sample data into it so we can use these tables in our select class. I want to explain the table design with actual data since that will help the reader to understand this in detail.

In database design the important phase is to create a table with proper normalization with primary and foreign key relationships.

Now in this example we will assume we need to create a restaurant Order Management tables with relationships.

For this we need an Order Master, an Order Detail and an Item Master as the major tables. We will use these 3 tables in this article. First let's start by creating the tables. As I have said, we will use the 3 tables here in this article so we start by creating the Item Master as the first table. As the name suggests this table will be used to store the items.

Create Table

Item Master: Here we have created an ItemMaster with the necessary fields. As I already said, we need to make a plan about our project. List all the necessary tables we need to create for the project and describe and list all the fields to be created for each table. Here I used Item_Code as a primary key field that will be used in another table for the reference to this main table.

  1. CREATE TABLE [dbo].[ItemMasters](  
  2.     [Item_Code] [varchar](20) NOT NULL,  
  3.     [Item_Name] [varchar](100) NOT NULL,  
  4.     [Price]  Int NOT NULL,  
  5.     [TAX1]  Int NOT NULL,  
  6.     [Discount]  Int NOT NULL,  
  7.     [Description] [varchar](200) NOT NULL,  
  8.     [IN_DATE] [datetime] NOT NULL,  
  9.     [IN_USR_ID] [varchar](20) NOT NULL,  
  10.     [UP_DATE] [datetime] NOT NULL,  
  11.     [UP_USR_ID] [varchar](20) NOT NULL,  
  12.  CONSTRAINT [PK_ItemMasters] PRIMARY KEY CLUSTERED   
  13. (  
  14.     [Item_Code] ASC  
  15. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  16. ) ON [PRIMARY]  
Insert Item Master
  1. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  2.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  3.      VALUES  
  4.            ('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE(),'SHANU'  
  5.            ,GETDATE(),'SHANU')  
  6.              
  7. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  8.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  9.      VALUES  
  10.            ('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold user choice',GETDATE(),'SHANU'  
  11.            ,GETDATE(),'SHANU')  
  12.              
  13. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  14.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  15.      VALUES  
  16.            ('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE(),'SHANU'  
  17.            ,GETDATE(),'SHANU')  
  18.              
  19. INSERT INTO [ItemMasters]   ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  20.            ,[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  21.      VALUES  
  22.            ('Item004','Potato Fry',15,0,0,'No Comments',GETDATE(),'SHANU'  
  23.            ,GETDATE(),'SHANU')  
Order Master: Since this is a master table we will have one main record and all the subsequent related records will be stored in the Order Detail Table.

Note: First please understand what Master and Detail means. If you don't understand what a Master and a Detail are then I will explain that first. Master means there is one main record and in the details we have all the details of the main record.

Say for example we have a restaurant and an order for one Coke, one Burger and one Potato Fries. Which means I have make an order from the waiter with 3 Items. So the Order_No for the example can be “Ord0001” and this Order_No will have the 3 items so first we create the Order Master.
  1. CREATE TABLE [dbo].[OrderMasters](  
  2.     [Order_No] [varchar](20) NOT NULL,  
  3.     [Table_ID] [varchar](20) NOT NULL,  
  4.     [Description] [varchar](200) NOT NULL,  
  5.     [IN_DATE] [datetime] NOT NULL,  
  6.     [IN_USR_ID] [varchar](20) NOT NULL,  
  7.     [UP_DATE] [datetime] NOT NULL,  
  8.     [UP_USR_ID] [varchar](20) NOT NULL,  
  9.  CONSTRAINT [PK_OrderMasters] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [Order_No] ASC  
  12. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  
Insert Order Master
  1. INSERT INTO [OrderMasters]  
  2.            ([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  3.      VALUES  
  4.            ('Ord_001','T1','',GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
  5.         
  6. INSERT INTO [OrderMasters]  
  7.            ([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  8.      VALUES  
  9.            ('Ord_002','T2','',GETDATE(),'Mak' ,GETDATE(),'MAK')  
  10.              
  11. INSERT INTO [OrderMasters]  
  12.            ([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  13.      VALUES  
  14.            ('Ord_003','T3','',GETDATE(),'RAJ' ,GETDATE(),'RAJ')  
Order Detail: As table name suggests this will have the details of an order so for example we consider my preceding example order with the 3 items. First we create an Order Detail table.
  1. CREATE TABLE [dbo].[OrderDetails](  
  2.     [Order_Detail_No] [varchar](20) NOT NULL,  
  3.     [Order_No] [varchar](20) CONSTRAINT  fk_OrderMasters FOREIGN KEY REFERENCES OrderMasters(Order_No),  
  4.     [Item_Code] [varchar](20) CONSTRAINT  fk_ItemMasters FOREIGN KEY REFERENCES ItemMasters(Item_Code),  
  5.     [Notes] [varchar](200) NOT NULL,  
  6.     [QTY]  INT NOT NULL,  
  7.     [IN_DATE] [datetime] NOT NULL,  
  8.     [IN_USR_ID] [varchar](20) NOT NULL,  
  9.     [UP_DATE] [datetime] NOT NULL,  
  10.     [UP_USR_ID] [varchar](20) NOT NULL,  
  11.  CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED   
  12. (  
  13.     [Order_Detail_No] ASC  
  14. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  15. ) ON [PRIMARY]  
  16.   
  17.   
  18. --Now let’s insert the 3 items for the above Order No 'Ord_001'.  
  19. INSERT INTO [OrderDetails]  
  20.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  21.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  22.      VALUES  
  23.            ('OR_Dt_001','Ord_001','Item001','Need very Cold',3  
  24.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
  25.   
  26. INSERT INTO [OrderDetails]  
  27.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  28.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  29.      VALUES  
  30.            ('OR_Dt_002','Ord_001','Item004','very Hot ',2  
  31.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
  32.   
  33. INSERT INTO [OrderDetails]  
  34.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  35.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  36.      VALUES  
  37.            ('OR_Dt_003','Ord_001','Item003','Very Spicy',4  
  38.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
Order Detail

Here we can see the same ordre_No for 3 different details with different items. Refer to the Item Master for the Item Name details. We will see in detail the select query uses the following in this article. Now we insert another Order Master detail into the Detail tables.
  1. INSERT INTO [OrderDetails]  
  2.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  3.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  4.      VALUES  
  5.            ('OR_Dt_004','Ord_002','Item002','Need very Hot',2  
  6.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
  7.   
  8. INSERT INTO [OrderDetails]  
  9.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  10.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  11.      VALUES  
  12.            ('OR_Dt_005','Ord_002','Item003','very Hot ',2  
  13.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
  14.   
  15. INSERT INTO [OrderDetails]  
  16.            ([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]  
  17.            ,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  18.      VALUES  
  19.            ('OR_Dt_006','Ord_003','Item003','Very Spicy',4  
  20.            ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')  
2. Simple SQL Select Query

Can someone tell me what a Select Query in SQL is? A Select is the one basic and most important DML statement of SQL. So what is DML in SQL? DML stands for Data Manipulation Language. That means these statements are used to manipulate the data that already exists. As the name suggests the SELECT statement selects one or more columns with one or more data to display from our DB with our optional filters.

For example now I want to display my Name in the SQL Server. So as a result I will use the select statement here as an example.
  1. SELECT 'My Name Is SYED SHANU'  
  2. -- With Column Name using 'AS'  
  3. SELECT 'My Name Is SYED SHANU' as 'MY NAME'  
  4. -- With more then the one Column   
  5. SELECT 'My Name' as 'Column1', 'Is' as 'Column2', 'SYED SHANU' as 'Column3'  
Select Query

Select Statement from Table
  1. -- To Display all the columns from the table we use * operator in select Statement.  
  2. Select * from ItemMasters  
  3. -- If we need to select only few fields from a table we can use the Column Name in Select Statement.  
  4. Select    Item_Code  
  5.         ,Item_name as Item  
  6.         ,Price  
  7.         ,Description  
  8.         ,In_DATE  
  9.         FROM  
  10.         ItemMasters  
Select Statement from Table

3. Simple Aggregate and Scalar Function

Aggregate and Scalar Functions are builtin functions of SQL Server that we can be use in our select statements. For example a few Aggregate and Scalar functions are Count, Max, Sum, Upper, lower, Round and and so on. Here in comments I have explained each of its uses.

We can use our tblItemMaster with all these builtin functions.
  1. select * from ItemMasters  
  2. -- Aggregate  
  3. -- COUNT() -> returns the Total no of records from table , AVG() returns the Average Value from Colum,MAX() Returns MaX Value from Column  
  4. -- ,MIN() returns Min Value from Column,SUM()  sum of total from Column  
  5. Select Count(*)  TotalRows,AVG(Price) AVGPrice  
  6.         ,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal   
  7.         FROM ItemMasters  
  8.           
  9. -- Scalar   
  10. -- UCASE() -> Convert to  Upper Case  ,LCASE() -> Convert to Lower Case,  
  11. -- SUBSTRING() ->Display selected char from column ->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay)  
  12. --,LEN() -> lenth of column date,  
  13. -- ROUND()  -> Which will round the value  
  14. SELECT  UPPER(Item_NAME) Uppers,LOWER(Item_NAME) Lowers,  
  15.         SUBSTRING(Item_NAME,2,3) MidValue,LEN(Item_NAME) Lenths   
  16.        ,SUBSTRING(Item_NAME,2,LEN(Item_NAME)) MidValuewithLenFunction,   
  17.         ROUND(Price,0) as Rounded  
  18.         FROM ItemMasters  
Simple Aggregate & Scalar Function

4. Date Function

In all our projects and tables we use the date column. The Date function plays a very important role in all our projects. So we should be very careful with date functions since sometimes these functions will be trickier. We need to select appropriate date functions and formats in our projects. Let's see a few examples of those here.
  1. -- GETDATE() -> to Display the Current Date and Time  
  2. -- Format() -> used to display our date in our requested format  
  3. Select GETDATE() CurrentDateTime, FORMAT(GETDATE(),'yyyy-MM-dd') AS DateFormats,  
  4.        FORMAT(GETDATE(),'HH-mm-ss')TimeFormats,  
  5.        CONVERT(VARCHAR(10),GETDATE(),10) Converts1,  
  6.        CONVERT(VARCHAR(24),GETDATE(),113),  
  7.        CONVERT(NVARCHAR, getdate(), 106) Converts2 ,-- here we used Convert Function   
  8.       REPLACE(convert(NVARCHAR, getdate(), 106), ' ', '/') Formats-- Here we used replace and --convert functions.  
  9.       --first we convert the date to nvarchar and then we replace the '' with '/'   
  10.   
  11. select * from Itemmasters  
  12.   
  13. Select  ITEM_NAME,IN_DATE CurrentDateTime, FORMAT(IN_DATE,'yyyy-MM-dd') AS DateFormats,  
  14.         FORMAT(IN_DATE,'HH-mm-ss')TimeFormats,  
  15.         CONVERT(VARCHAR(10),IN_DATE,10) Converts1,  
  16.         CONVERT(VARCHAR(24),IN_DATE,113),  
  17.         convert(NVARCHAR, IN_DATE, 106) Converts2 ,-- here we used Convert Function   
  18.         REPLACE(convert(NVARCHAR,IN_DATE, 106), ' ', '/') Formats  
  19.         FROM Itemmasters  
Date Function  
  • DatePart: We use the datepart to display the selected Year, Month and Day.

  • DateADD: We use dateadd to display or add or subrtract days from the selected date.

  • DateDIff: We use dateDiff to calculate the difference between 2 dates. 
  1. --Datepart DATEPART(dateparttype,yourDate)  
  2. SELECT DATEPART(yyyy,getdate()) AS YEARs ,  
  3. DATEPART(mm,getdate()) AS MONTHS,  
  4. DATEPART(dd,getdate()) AS Days,  
  5. DATEPART(week,getdate()) AS weeks,  
  6. DATEPART(hour,getdate()) AS hours  
  7.   
  8. --Days Add to add or subdtract date from a selected date.  
  9. SELECT GetDate()CurrentDate,DATEADD(day,12,getdate()) AS AddDays ,  
  10.  DATEADD(day,-4,getdate()) AS FourDaysBeforeDate   
  11.    
  12.  -- DATEDIFF() -> to display the Days between 2 dates  
  13.  select DATEDIFF(year,'2003-08-05',getdate())  yearDifferance ,   
  14.   DATEDIFF(day,DATEADD(day,-24,getdate()),getdate()) daysDifferent,  
  15.  DATEDIFF(month,getdate(),DATEADD(Month,6,getdate())) MonthDifferance  
DateDIff

5. Other Select Function

Top: To display the Top First or Last selected records first we see how to select the first or Top records and last to the Top records from the Select statement.

Order By: This is used in a SQL Select statement to display the records in ascending or descending order by the columns.
  1. <a name="5">  
  2. --Top to Select Top first and last records using Select Statement.  
  3. Select * FROM ItemMasters  
  4. --> First Display top 2 Records  
  5. Select TOP 2 Item_Code  
  6.              ,Item_name as Item  
  7.              ,Price  
  8.              ,Description  
  9.              ,In_DATE  
  10. FROM ItemMasters  
  11. --> to Display the Last to Records we need to use the Order By Clause  
  12. -- order By to display Records in assending or desending order by the columns  
  13. Select TOP 2  Item_Code  
  14.              ,Item_name as Item  
  15.              ,Price  
  16.              ,Description  
  17.              ,In_DATE  
  18. FROM ItemMasters  
  19. ORDER BY Item_Code DESC</a>  
Other Select Function

Distinct: The distinct keyword is used in select statements to avoid the duplicate records.
  1. <a name="5">  
  2. Select * FROM ItemMasters  
  3. --Distinct -> To avoid the Duplicate records we use the distinct in select statement  
  4. -- for example in this table we can see here we have the duplicate record 'Chiken Burger'  
  5. -- but with different Item_Code when i use the below select statement see what happen  
  6.   
  7. Select   Item_name as Item  
  8.         ,Price  
  9.         ,Description      
  10.         ,IN_USR_ID    
  11.         FROM ItemMasters  
  12. -- here we can see the Row No 3 and 5 have the duplicate record to avoid this we use the distinct Keyword in select statement.  
  13.   
  14. select Distinct Item_name as Item  
  15.                 ,Price  
  16.                 ,Description      
  17.                 ,IN_USR_ID    
  18.                  FROM ItemMasters</a>  
Distinct

6. Where Clause

The where clause is very important in SQL Select statements. Why we use a where clause and what is use of where clause. Where clause is nothing but a filter of the records using some condition.

Now for example we consider a table has 10,000 records. If we use a select query to display the records then the load time might be long to display all the data. In that case we can use a condition and display some specific data.

For example we can use the Where Clause to display the records for the past 1 week or 1 month.

We can consider our restaurant Order Detail table, for example we consider an Order Detail table to have 10,000 records but for sure for one Order_No there will not be more than 100 records in the Order Detail. Now let's see a few where clause uses.
  1. Select * from ItemMasters  
  2. Select * from OrderDetails  
  3. --Where -> To display the data with certain conditions  
  4. -- Now below example which will display all the records which has Item_Name='Coke'  
  5. select * FROM ItemMasters WHERE ITEM_NAME='COKE'  
  6. -- If we want display all the records Iten_Name which Starts with 'C' then we use Like in where clause.  
  7. SELECT * FROM ItemMasters WHERE ITEM_NAME Like 'C%'  
  8.   
  9. --> here we display the ItemMasters where the price will be greater then or equal to 40.  
  10. --> to use more then one condition we can Use And or Or operator.  
  11. --If we want to check the data between to date range then we can use Between Operator in Where Clause.  
  12. select Item_name as Item  
  13.         ,Price  
  14.         ,Description      
  15.         ,IN_USR_ID    
  16.          FROM ItemMasters  
  17.          WHERE  
  18.             ITEM_NAME Like 'C%'   
  19.             AND   
  20.             price >=40  
  21. --> here we display the OrderDetails where the Qty will be greater 3  
  22.   
  23. Select * FROM OrderDetails WHERE qty>3  
Where Clause

Where In Clause
  1. -- In clause -> used to display the data which is in the condition  
  2. select *  
  3.         FROM ItemMasters  
  4.         WHERE  
  5.         Item_name IN ('Coffee','Chiken Burger')  
  6.   
  7. -- In clause with Order By - Here we display the in descending order.  
  8. select *  
  9.         FROM ItemMasters  
  10.         WHERE  
  11.         Item_name IN ('Coffee','Chiken Burger')  
  12.         ORDER BY Item_Code Desc  
In Clause

Where Between keyword
  1. -- between  -> Now if we want to display the data between to date range then we use betweeen keyword  
  2. select * FROM ItemMasters  
  3.   
  4. select * FROM ItemMasters  
  5.          WHERE  
  6.          In_Date BETWEEN '2014-09-22 15:59:02.853' AND '2014-09-22 15:59:02.853'  
  7.   
  8. select * FROM ItemMasters  
  9.          WHERE  
  10.          ITEM_NAME Like 'C%'   
  11.          AND  
  12.          In_Date BETWEEN '2014-09-22 15:59:02.853' AND '2014-09-22 15:59:02.853'  
Between keyword

To display the records within a range we use the between keyword in the where clause.

7. Group BY Clause

A Group by Clause will display records as a result of a combined set or result of the same related records.
  1. --Group By -> To display the data with group result.Here we can see we display all the AQggregate result by Item Name  
  2. Select ITEM_NAME,Count(*)  TotalRows,AVG(Price) AVGPrice  
  3.         ,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal   
  4.         FROM  
  5.         ItemMasters  
  6.         GROUP BY ITEM_NAME  
  7.           
  8. -- Here this group by will combine all the same Order_No result and make the total or each order_NO  
  9. Select Order_NO,Sum(QTy) as TotalQTY   
  10.     FROM OrderDetails  
  11.     where qty>=2  
  12.     GROUP BY Order_NO  
  13.       
  14. -- Here the Total will be created by order_No and Item_Code  
  15. Select Order_NO,Item_Code,Sum(QTy) as TotalQTY   
  16.     FROM OrderDetails  
  17.     where qty>=2  
  18.     GROUP BY Order_NO,Item_Code  
  19.     Order By Order_NO Desc,Item_Code  
Group BY Clause

Group By and Having Clause

The Having and Group By clauses don't support aggregate functions. To use aggregate functions we use a having clause in a Select statement.
  1. --Group By Clause -- here this will display all the Order_no   
  2. Select Order_NO,Sum(QTy) as TotalQTY   
  3.     FROM OrderDetails  
  4.     GROUP BY Order_NO  
  5.       
  6. -- Having Clause-- This will avoid the the sum(qty) less then 4   
  7. Select Order_NO,Sum(QTy) as TotalQTY   
  8.     FROM OrderDetails  
  9.     GROUP BY Order_NO  
  10.     HAVING Sum(QTy) >4  
Having Clause

8. Sub Query

A Sub Query can be called an Inner Query or nested query that can be used in a Where clause of a select, insert, update or delete statement.
  1. --Sub Query -- Here we used the Sub query in where clause to get all the Item_Code where the price>40 now this sub   
  2. --query reslut we used in our main query to filter all the records which Item_code from Subquery result  
  3. SELECT * FROM ItemMasters   
  4.          WHERE Item_Code IN   
  5.         (SELECT Item_Code FROM ItemMasters WHERE price > 40)   
  6.           
  7.           
  8. -- Sub Query with Insert Statement  
  9. INSERT INTO ItemMasters           ([Item_Code] ,[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]  
  10.            ,[IN_USR_ID],[UP_DATE] ,[UP_USR_ID])  
  11.     Select 'Item006'  
  12.            ,Item_Name,Price+4,TAX1,Discount,Description  
  13.            ,GetDate(),'SHANU',GetDate(),'SHANU'  
  14.            from ItemMasters  
  15.            where Item_code='Item002'         
  16.                   
  17. --After insert we can see the result as   
  18.         Select * from ItemMasters  
Sub Query

9. Joins

So far we have seen all the somethings related to one table. Now we see how to join more than one table and display the results. Select statements will not be effective until we use a Join. The main purpose of using SQL Server is to use the normalization and increase the performance by displaying the records. With normalization we split large tables into small related tables. Now we need to display the related table data as a result of one single select statement. To accomplish this we use a Join in our SQL Select statement and combine all the data and display it.

Simple Join
  1. --Now we have used the simple join with out any condition this will display all the  
  2. -- records with duplicate data to avaoid this we see our next example with condition  
  3. SELECT * FROM Ordermasters,OrderDetails  
  4. -- Simple Join with Condition  now here we can see the duplicate records now has been avoided by using the where checing with both table primaryKey field  
  5. SELECT *   
  6.         FROM  
  7.         Ordermasters as M, OrderDetails as D  
  8.         where M.Order_NO=D.Order_NO  
  9.         and M.Order_NO='Ord_001'  
  10.   
  11. -- Now to make more better understanding we need to select the need fields from both   
  12. --table insted of displaying all column.  
  13. SELECT M.order_NO,M.Table_ID,D.Order_detail_no,Item_code,Notes,Qty  
  14.                 FROM   
  15.                 Ordermasters as M, OrderDetails as D   
  16.                 where M.Order_NO=D.Order_NO                    
  17.  -- Now lets Join 3 table  
  18.  SELECT  M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,  
  19.                 I.Price*D.Qty as TotalPrice  
  20.                 FROM   
  21.                 Ordermasters as M, OrderDetails as D,ItemMasters as I   
  22.                 where   
  23.                 M.Order_NO=D.Order_NO AND D.Item_Code=I.Item_Code  
Joins

Inner Join, Left Outer Join, Right Outer Join and Full outer Join

We can see each join example as in the following with comments.
  1. --INNER JOIN   
  2. --This will display the records which in both table Satisfy here i have used Like in where class which display the   
  3. SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  4.         FROM   
  5.         Ordermasters as M Inner JOIN OrderDetails as D   
  6.         ON M.Order_NO=D.Order_NO  
  7.         INNER JOIN  ItemMasters as I   
  8.         ON   D.Item_Code=I.Item_Code  
  9.         WHERE  
  10.         M.Table_ID like 'T%'  
  11. --LEFT OUTER JOIN   
  12. --This will display the records which Left side table Satisfy  
  13.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  14.         FROM   
  15.         Ordermasters as M LEFT OUTER JOIN OrderDetails as D   
  16.         ON M.Order_NO=D.Order_NO  
  17.         LEFT OUTER JOIN     ItemMasters as I   
  18.         ON   D.Item_Code=I.Item_Code  
  19.         WHERE  
  20.         M.Table_ID like 'T%'  
  21. --RIGHT OUTER JOIN   
  22. --This will display the records which Left side table Satisfy  
  23.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  24.         FROM   
  25.         Ordermasters as M RIGHT OUTER JOIN OrderDetails as D   
  26.         ON M.Order_NO=D.Order_NO  
  27.         RIGHT OUTER JOIN    ItemMasters as I   
  28.         ON   D.Item_Code=I.Item_Code  
  29.         WHERE  
  30.         M.Table_ID like 'T%'  
  31.           
  32. --FULL OUTER JOIN   
  33. --This will display the records which Left side table Satisfy  
  34.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  35.         FROM   
  36.         Ordermasters as M FULL OUTER JOIN OrderDetails as D   
  37.         ON M.Order_NO=D.Order_NO  
  38.         FULL OUTER JOIN     ItemMasters as I   
  39.         ON   D.Item_Code=I.Item_Code  
  40.         WHERE  
  41.         M.Table_ID like 'T%'  
full outer Join

10. UNION and UNION ALL

Before we see joins in SQL. In a join we join 2 or more tables and display all the common related result. If we need to display 2 or more tables to be combined and return all the data then we use a UNION or UNION ALL.

Here we can see UNION and UNION ALL. So what do you think about the difference between these two?

A Union will display only the unique results but a Union ALL will display the data with duplicate results also.

If we only need the distinct results of combining tables then we can use a UNION. If we need the results with duplicate data then we use the UNION ALL. That means it will display all the data.

Note: Both or All Select Column count, Name and data type should be the same to use a Union in SQL.

The syntax for using a union is like:
  1. Select column1,Colum2 from Table1  
  2. Union  
  3. Select Column1,Column2 from Table2  
  4.   
  5. Select column1,Colum2 from Table1  
  6. Union All  
  7. Select Column1,Column2 from Table2  
Example
  1. --Select with different where condition which display the result as 2 Table result  
  2. select Item_Code,Item_Name,Price,Description FROM ItemMasters where price <=44  
  3. select Item_Code,Item_Name,Price,Description FROM ItemMasters where price >44  
  4.   
  5. -- Union with same table but with different where condition now which result as one table which combine both the result.  
  6. select Item_Code,Item_Name,Price,Description FROM ItemMasters where price <=44  
  7. UNION  
  8. select Item_Code,Item_Name,Price,Description FROM ItemMasters where price >44  
  9.   
  10. -- Union ALL with Join sample  
  11.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  12.     FROM   
  13.         Ordermasters as M (NOLOCK)   Inner JOIN OrderDetails as D    
  14.         ON M.Order_NO=D.Order_NO INNER JOIN     ItemMasters as I   
  15.         ON   D.Item_Code=I.Item_Code WHERE      I.Price <=44       
  16. Union ALL  
  17.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice  
  18.     FROM   
  19.         Ordermasters as M (NOLOCK)   Inner JOIN OrderDetails as D  
  20.         ON M.Order_NO=D.Order_NO    INNER JOIN  ItemMasters as I  
  21.         ON   D.Item_Code=I.Item_Code    WHERE   I.Price>44  
11. Common Table Expressions (CTE) - With

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

We need to use the With clause in CTE. CTE will be very useful for displaying the records in some certain range. Here now for a simple example I have 2 dates with certain ranges, for example I have a Start_Date and an End_Date. Both have a 20 days difference. Now if I want to display all the dates with weekNo and WeekStart Day then as a result here we can see this query created using CTE.
  1. declare @sDate datetime,  
  2.         @eDate datetime;  
  3.   
  4. select  @sDate = getdate()-5,  
  5.         @eDate = getdate()+16;  
  6. --select @sDate StartDate,@eDate EndDate  
  7. ;with cte as  
  8.    (  
  9.       select @sDate StartDate,'W'+convert(varchar(2),  
  10.             DATEPART( wk, @sDate))+'('+convert(varchar(2),@sDate,106)+')' as 'SDT'       
  11.   union all  
  12.        select  dateadd(DAY, 1, StartDate) ,  
  13.               'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),  
  14.                dateadd(DAY, 1, StartDate),106)+')' as 'SDT'     
  15.   FROM  cte  
  16.   WHERE dateadd(DAY, 1, StartDate)<=  @eDate   
  17.     )  
  18. select * from cte  
  19. option (maxrecursion 0)  
CTE

In the following Multiple CTE sample I have used more than one CTE. Now let's see how to use the Multiple CTE. Here you can see in the following sample as in the preceding that will display all the days but with one more CTE added.

In this example I have used a UNION ALL.
  1. declare @sDate datetime,@eDate datetime;  
  2.         declare @sDate1 datetime,@eDate1 datetime;  
  3. select  @sDate = '2014-09-10', @eDate = '2014-09-15';  
  4. select  @sDate1 = '2014-09-10', @eDate1 = '2014-09-18';          
  5. WITH    cte   
  6.           AS (  
  7.               select '1' valuetype ,@sDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @sDate))+'('+convert(varchar(2),@sDate,106)+')' as 'SDT'    
  8.      
  9.   union all  
  10.   select valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'SDT'      
  11.      
  12.   from cte  
  13.   where dateadd(DAY, 1, StartDate)<=  @eDate  
  14.              ),  
  15.              cte2   
  16.           AS (  
  17.               select '2' valuetype, @sDate StartDate,'W'+convert(varchar(2),DATEPART( wk, @sDate1))+'('+convert(varchar(2),@sDate1,106)+')' as 'SDT'    
  18.      
  19.   union all  
  20.   select '2' valuetype, dateadd(DAY, 1, StartDate) ,'W'+convert(varchar(2),DATEPART( wk, StartDate))+'('+convert(varchar(2),dateadd(DAY, 1, StartDate),106)+')' as 'SDT'         
  21.   from cte  
  22.   where dateadd(DAY, 1, StartDate)<=  @eDate1  
  23.              )  
  24.     SELECT  *    FROM    cte  
  25.     union all   
  26.       SELECT  *    FROM    cte2      
  27.     option (maxrecursion 0)  
UNION ALL

12. View

Many people will be confused whether a view is the same as a select. In a view we use the same select query but we need a view. So what is the use of a view?

I will say a view will be very useful in actual projects.

For example we have a long select query with more than 20 columns and more than 4 table joins. We write one big query and for that suppose we need to use that query in other places we need to call that query. Let's see the uses of views in SQL.  
  1. Speed: Views can be used to increase the performance.

  2. Security: If suppose we have an Order Detail Table and it has a total of items sold, a Total price and so on. Since those fields can be viewed only by the administrator or manager and for the user they just need to only see the itemname and ItemPrice. Then in that case we can create a view for the user to display only the itemName and Price.

  3. If we need to join more than one table and need to display that in several places then in that case we can use a View.
  1. CREATE   
  2. VIEW viewname  
  3. AS  
  4. Select ColumNames from yourTable  
  5.   
  6. Example :   
  7. -- Here we create view for our Union ALL example  
  8. Create   
  9. VIEW myUnionVIEW  
  10. AS  
  11.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,  
  12.         I.Price*D.Qty as TotalPrice  
  13.         FROM   
  14.           Ordermasters as M  Inner JOIN OrderDetails as D   
  15.           ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I   
  16.           ON   D.Item_Code=I.Item_Code WHERE    I.Price <=44       
  17. Union ALL  
  18.  SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,  
  19.         I.Price*D.Qty as TotalPrice  
  20.         FROM   
  21.           Ordermasters as M  Inner JOIN OrderDetails as D   
  22.           ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I   
  23.           ON   D.Item_Code=I.Item_Code  WHERE   I.Price>44  
  24.           
  25. -- View Select query  
  26. Select * from myUnionVIEW  
  27. -- We can also use the View to display with where condition and with selected fields   
  28. Select order_Detail_NO,Table_ID,Item_Name,Price from myUnionVIEW where price >40  
View

13. Pivot

Why do we use a pivot in our SQL? So many people ask how to display the data from a row to a column. Yes we can do that using a Pivot. Using a Pivot we can display the rows as columns and display the average or sum of items of that. For example I want to display all the items with the price as a column and for that we can use a pivot. Now let's see an sample.

Here we can see an example that displays all the records of tblItemMaster with ItemName as a column and display the price in each row of items.
  1. -- Simple Pivot Example   
  2. SELECT *  FROM ItemMasters   
  3. PIVOT(SUM(Price)         
  4.  FOR ITEM_NAME IN ([Chiken Burger], Coffee,Coke)) AS PVTTable  
  5.   
  6. -- Pivot with detail example  
  7. SELECT *  
  8. FROM (  
  9.     SELECT   
  10.        ITEM_NAME,   
  11.         price as TotAmount   
  12.     FROM ItemMasters  
  13.      
  14. ) as s  
  15. PIVOT  
  16. (  
  17.     SUM(TotAmount)  
  18.     FOR [ITEM_NAME] IN ([Chiken Burger], [Coffee],[Coke])  
  19. )AS MyPivot  
Pivot

Here we can see one more important example that will display all the Item Names as columns. To use this first we need to add all the item names to a variable. Then we can use this variable to display as a pivot. See the following example.
  1. DECLARE @MyColumns AS NVARCHAR(MAX),  
  2.     @SQLquery  AS NVARCHAR(MAX)  
  3. -- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query  
  4. select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Item_NAME)   
  5.                     FROM ItemMasters  
  6.                     GROUP BY Item_NAME  
  7.                     ORDER BY Item_NAME  
  8.             FOR XML PATH(''), TYPE  
  9.             ).value('.', 'NVARCHAR(MAX)')   
  10.         ,1,1,'')  
  11. -- here we use the above all Item name to disoplay its price as column and row display  
  12. set @SQLquery = N'SELECT ' + @MyColumns + N' from   
  13.              (  
  14.                  SELECT   
  15.        ITEM_NAME,   
  16.         price as TotAmount   
  17.     FROM ItemMasters  
  18.             ) x  
  19.             pivot   
  20.             (  
  21.                  SUM(TotAmount)  
  22.                 for ITEM_NAME in (' + @MyColumns + N')  
  23.             ) p '  
  24.   
  25. exec sp_executesql @SQLquery;  
display as pivot

14. Stored Procedure (SP)

I saw many times in Code Project many people asking questions about how to write more than one query in SQL Server and use them in our C# program. For example many people ask how to run a Select, Insert and Update at the same time in SQL. Well we actually cannot run all the queries at the same time but we can use Store Procedures to execute them all one by one. A SP is nothing but a function we can write once and use many times and also perform various tasks with different parameter values.

In our example now I want to insert a new item into a table Item Master but before inserting I want to generate my itemCode. To do that for example as a simple method I will use the count of records and add one value to it and generate my item Code.

Syntax
  1. CREATE PROCEDURE [ProcedureName]                                                
  2.                                                   
  3. AS                                                                  
  4. BEGIN  
  5. -- Select or Update or Insert query.  
  6. END  
  7.   
  8. To execute SP we use  
  9. exec ProcedureName  
Example Select Sp with no parameter
  1. -- =============================================                                                                  
  2. -- Author      : Shanu                                                                  
  3. -- Create date : 2014-09-15                                                                  
  4. -- Description : To Display Pivot Data                                                          
  5. -- Latest                                                                  
  6. -- Modifier    : Shanu                                                                  
  7. -- Modify date : 2014-09-15                                                                   
  8. -- =============================================                                                                  
  9. -- exec USP_SelectPivot                                   
  10. -- =============================================                                                             
  11. Create PROCEDURE [dbo].[USP_SelectPivot]        
  12. AS                                                                  
  13. BEGIN                                                   
  14.    DECLARE @MyColumns AS NVARCHAR(MAX),  
  15.     @SQLquery  AS NVARCHAR(MAX)  
  16. -- here first we get all the ItemName which should be display in Columns we use this in our necxt pivot query  
  17. select @MyColumns = STUFF((SELECT ',' + QUOTENAME(Item_NAME)   
  18.                     FROM ItemMasters  
  19.                     GROUP BY Item_NAME  
  20.                     ORDER BY Item_NAME  
  21.             FOR XML PATH(''), TYPE  
  22.             ).value('.', 'NVARCHAR(MAX)')   
  23.         ,1,1,'')  
  24. -- here we use the above all Item name to disoplay its price as column and row display  
  25. set @SQLquery = N'SELECT ' + @MyColumns + N' from   
  26.              (  
  27.                  SELECT   
  28.        ITEM_NAME,   
  29.         price as TotAmount   
  30.     FROM ItemMasters  
  31.             ) x  
  32.             pivot   
  33.             (  
  34.                  SUM(TotAmount)  
  35.                 for ITEM_NAME in (' + @MyColumns + N')  
  36.             ) p '  
  37.   
  38. exec sp_executesql @SQLquery;             
  39.                    
  40.    RETURN                                                
  41.     END  
Stored procedure

Note to alter the SP we use an Alter procedure procedureName.

Select and insert SP
  1. -- Author      : Shanu                                                                  
  2. -- Create date : 2014-09-15                                                                  
  3. -- Description : To Display Pivot Data                                                          
  4. -- Latest                                                                  
  5. -- Modifier    : Shanu                                                                  
  6. -- Modify date : 2014-09-15                                                                   
  7. -- =============================================                                                                  
  8. -- exec USP_InsertItemMaster   'IceCream',120,0,0,'it should be in Cold','SHANU'                               
  9. -- =============================================                                                             
  10. Create PROCEDURE [dbo].[USP_InsertItemMaster]                                                
  11.    (                                  
  12.            @ItemNAME          VARCHAR(100)     = '',                                
  13.             @Price            INT    = 0,                                
  14.             @TAX              INT    = 0,                             
  15.             @Discount         INT    = 0,           
  16.             @Description      VARCHAR(100)     = '',                                
  17.             @UserID           VARCHAR(20)     = ''                
  18.       )                                                          
  19. AS                                                                  
  20. BEGIN                                                   
  21.    DECLARE @RowsCount AS int;  
  22.   
  23. Select @RowsCount= count(*)+1 from [ItemMasters]  
  24.   
  25.   
  26. INSERT INTO [ItemMasters]  
  27.            ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])  
  28.      VALUES  
  29.            ('Item00'+ CAST(@RowsCount AS VARCHAR(10))   
  30.            ,@ItemNAME  
  31.            ,@Price  
  32.            ,@TAX  
  33.            ,@Discount  
  34.            ,@Description  
  35.            ,getdate()  
  36.            ,@UserID  
  37.            ,getdate()  
  38.            ,@UserID)  
  39.     END  
insert SP

15. Functions

In this article we have already seen a few pre-defined system functions like MAX(), SUM(), GetDate() and and so on. Now let’s see how to create a user defined function.

If someone were to ask me what a function is and what is the use of a function then in a simple way I will say that if I want to execute or perform some action several times with a different meaning then I will create a function and call it whenever I need it. The following is the syntax to create a function.
  1. <a style="font-size: 14px; color: #111111">  
  2.   
  3. Create Function functionName  
  4. As  
  5. Begin  
  6. END  
  7.   
  8. </a>  
Here we will see a simple function that will return the max row count of tblItemMaster.
  1. <a style="font-size: 14px; color: #111111">  
  2. -- =============================================                                                                  
  3. -- Author      : Shanu                                                                  
  4. -- Create date : 2014-09-15                                                                  
  5. -- Description : To Display Pivot Data                                                          
  6. -- Latest                                                                  
  7. -- Modifier    : Shanu                                                                  
  8. -- Modify date : 2014-09-15                                                                   
  9.   
  10. Alter FUNCTION [dbo].[ufnSelectitemMaster]()  
  11. RETURNS int   
  12. AS   
  13. -- Returns total Row count of Item Master.  
  14.   
  15. BEGIN  
  16.   DECLARE @RowsCount AS int;  
  17.   
  18. Select @RowsCount= count(*)+1 from ItemMasters  
  19.  RETURN @RowsCount;  
  20.   
  21. END  
  22.   
  23.   
  24. -- to View Function we use select and fucntion Name  
  25. select [dbo].[ufnSelectitemMaster]()  
  26.   
  27. </a>  
Functions

Here we can see another function that will return the last date of a month from a given date.
  1. -- =============================================                                                                  
  2. -- Author      : Shanu                                                                  
  3. -- Create date : 2014-09-15                                                                  
  4. -- Description : To Display Pivot Data                                                          
  5. -- Latest                                                                  
  6. -- Modifier    : Shanu                                                                  
  7. -- Modify date : 2014-09-15      
  8.   
  9. ALTER FUNCTION [dbo].[ufn_LastDayOfMonth]  
  10. (  
  11.    @DATE NVARCHAR(10)   
  12. )  
  13. RETURNS NVARCHAR(10)  
  14. AS  
  15. BEGIN  
  16.    RETURN CONVERT(NVARCHAR(10), DATEADD(D, -1, DATEADD(M, 1, CAST(SUBSTRING(@DATE,1,7) + '-01' AS DATETIME))), 120)  
  17. END  
  18. SELECT dbo.ufn_LastDayOfMonth('2014-09-01')AS LastDay   
  19.   
  20. </a>  
16. SQL Server Coding Standards

Here are a few SQL Coding Standards sites. Kindly refer to these links and follow the SQL Standards in your queries.
17. Few Working Examples

Example 1: To Display Every Week Sunday.

In some case we need to display all the days that should be Sunday, Monday and so on. For example to list all the days of work that start on Sunday.

Here now let's see our following example. I have the From Date and the To Date. Now I want to display all the Sundays alone between this range.

For this we can use our preceding CTE, the same example. Now using the CTE we get all the dates between 2 dates. Now what we need to do is to select only the Sunday dates. I have used the temp table to store all the Sundays and display the result.
  1.  declare @FromDate datetime,  
  2.         @ToDate datetime;                          
  3.                                                             
  4. IF OBJECT_ID('tempdb..#TEMP_EveryWk_Snday') IS NOT NULL                                                                            
  5.     DROP TABLE #TEMP_EveryWk_Snday                                                                         
  6.                                                                             
  7.  DECLARE @TOTALCount INT                                                                        
  8.          SET @FromDate = getdate();    
  9.          SET @ToDate = DATEADD(Month, 3,getdate());                                                                  
  10.  Select  @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);                
  11.    WITH d AS                                                                         
  12.             (                                                                        
  13.               SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()                                                                         
  14.                 OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))                                                                        
  15.               FROM sys.all_objects                                               
  16.             )                                                                
  17.          SELECT Distinct DATEADD(DAY, 1 - DATEPART(WEEKDAY, AllDays),   
  18.                         CAST(AllDays AS DATE))WkStartSundays                    
  19.             INTO #TEMP_EveryWk_Snday                                                                      
  20.             FROM d                               
  21.             WHERE  
  22.             AllDays  <= @ToDate       
  23.         
  24.     Select  WkStartSundays WEEKSTART_Sunday,  
  25.             DATENAME(dw,WkStartSundays) Day_Name   
  26.             FROM #TEMP_EveryWk_Snday  
Points of Interest

If I have missed any SQL query that you want to understand then kindly leave me a comment, I will be happy to add those with examples to this article. I have attached a Zip file that has this article as a Word file for your easy access.


Similar Articles