In Focus

Different Types Of Clauses In SQL Server

In this blog, we are going to learn about clauses in SQL Server. Clauses are used with table expressions and help in finding the result.

Introduction

In this blog, we will see the most popular concept in SQL Server, i.e., Clauses. Clauses are used with table expressions to help find the exact results. A clause can be used with joins. Let's see this with an example.

Step 1

Open SQL Server 2014 and create three tables as below.

Employee Table

  1. CREATE TABLE [dbo].[Employee](  
  2.     [EmployeeId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Gender] [char](10) NULL,  
  5.     [Position] [nvarchar](50) NULL,  
  6.     [Salary] [int] NULL,  
  7.     [Department_Id] [int] NULL,  
  8.     [Incentive_Id] [int] NULL,  
  9. PRIMARY KEY CLUSTERED   
  10. (  
  11.     [EmployeeId] 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]  
  14.   
  15. GO  

Department Table

  1. CREATE TABLE [dbo].[Department](  
  2.     [DepartmentId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [DepartmentName] [nvarchar](50) NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [DepartmentId] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9.   
  10. GO  

Incentive Table

  1. CREATE TABLE [dbo].[Incentive](  
  2.     [IncentiveId] [int] IDENTITY(1,1) NOT NULL,  
  3.     [IncentiveAmount] [int] NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [IncentiveId] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9.   
  10. GO  

Step 2

Insert the values in tables, like I have done using the following queries.
  1. /*INESRT VALUES IN EMPLOYEE TABLE*/  
  2. insert into Employee values('Anisha Agarwal','Female','Sales Excutive',30000,6,3)  
  3. insert into Employee values('Manish Agarwal','Male','Accountant',40000,1,6)  
  4. insert into Employee values('Fayaz Ansari','Male','UI Developer',50000,3,8)  
  5. insert into Employee values('Rahul Sharma','Male','Software Engineer',45000,3,8)  
  6. insert into Employee values('Abdul Rahim','Male','HR',30000,3,5)  
  7. insert into Employee values('Arvind Kumar','Male','HR',32000,3,5)  
  8. insert into Employee values('Priya Jain','Female','Marketing',25000,4,4)  
  9. insert into Employee values('Zoya','Female','Sales Excutive',30000,6,3)  
  10. insert into Employee values('Monika Agarwal','Female','Marketing',25000,4,4)  
  11. insert into Employee values('Suresh Kumar','Male','Assistant',20000,null,4)  
  12.   
  13. /*INESRT VALUES IN DEPARTMENT TABLE*/  
  14. insert into Department values('Accountant')  
  15. insert into Department values('HR')  
  16. insert into Department values('IT')  
  17. insert into Department values('Markeing')  
  18. insert into Department values('Payroll')  
  19. insert into Department values('Sales')  
  20.   
  21. /*INESRT VALUES IN INCENTIVE TABLE*/  
  22. insert into Incentive values(1000)  
  23. insert into Incentive values(2000)  
  24. insert into Incentive values(3000)  
  25. insert into Incentive values(4000)  
  26. insert into Incentive values(5000)  
  27. insert into Incentive values(6000)  
  28. insert into Incentive values(7000)  
  29. insert into Incentive values(8000)  
  30. insert into Incentive values(9000)  
  31. insert into Incentive values(10000)  

Different types of clauses used in SQL

WHERE Clause

This clause is used to define the condition to extract and display only those records which fulfill the given condition.

Syntax

SELECT column_name(s) FROM table_name WHERE condition
  1. select Name,Salary from Employee where salary=(select MAX(Salary) as 'Highest Salary' from Employee)  
  1. select Name,Salary from Employee where salary between 20000 and 50000  
  1. select EmployeeId,Name,Salary,DepartmentName from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId  
  4. where DepartmentName='IT'   

GROUP BY Clause

It is used with a SELECT statement to group the results of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name

Total salary of the department

  1. select SUM(Salary) as 'Department Total Salary',DepartmentName from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId   
  4. group by DepartmentName  

Total number of employees in teh department

  1. select COUNT(EmployeeId) as 'Total Employee',DepartmentName from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId   
  4. group by DepartmentName  
Different Types Of Clause In SQL Server

HAVING clause

This clause is used in association with the GROUP BY clause. It is applied to each group of results or the entire result as a single group and it is much similar to a WHERE clause. The only difference is that you cannot use it without GROUP BY clause.

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name HAVING condition
  1. select COUNT(EmployeeId) as 'Total Employee',DepartmentName from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId   
  4. group by DepartmentName   
  5. having COUNT(EmployeeId)>=1   

 

Different Types Of Clause In SQL Server

 

ORDER BY clause

This clause is used to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.

Syntax

SELECT column_name(s) FROM table_name WHERE condition ORDER BY column_name ASC|DESC

Order by Ascending

  1. select EmployeeId,Name,Salary,DepartmentName,IncentiveAmount from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId  
  4. join Incentive  
  5. on Employee.Incentive_Id=Incentive.IncentiveId  
  6. order by Name ASC  

 

Different Types Of Clause In SQL Server

 

Order by descending

  1. select EmployeeId,Name,Salary,DepartmentName,IncentiveAmount from Employee  
  2. join Department  
  3. on Employee.Department_Id=Department.DepartmentId  
  4. join Incentive  
  5. on Employee.Incentive_Id=Incentive.IncentiveId  
  6. order by Name DESC  

 

Different Types Of Clause In SQL Server