Clauses in SQL Server

In this article, we will show you the list of Clauses in SQL Server with practical examples. It is one of the basic Interview Questions that you might face in the interviews.

Clauses in SQL Server

There are five types of clauses in SQL Server. They are

Order By Clause
Top Clause
Where Clause
Group By Clause and
Having Clause

Order By Clauses in SQL Server

The SQL Server Order By is used to sort the data in either Ascending or Descending order. I suggest you refer to Order By article to understand the SQL Order By in detail. In this example, we will sort the Employee table in Descending Order using Sales

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY Sales DESC

Top Clauses in SQL Server

The SQL Server Top is used to select top N records. Refer to Top Clause. This example returns the first six records from the Employee table order by yearly income in descending order.

SELECT TOP 6 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY YearlyIncome DESC

Where Clauses in SQLServer

The Where Clause is used to restrict the number of records returned by the SELECT Statement. Please refer to Where Clause. This example returns all the records from the Employee table whose sales are greater than 2000

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Sales > 2000

Group By Clauses in SQL Server

The Group By clause returns the aggregated data by grouping one or more columns and applying aggregated functions to the remaining columns.

This Group By Clause query Groups the Employee table by Occupation and returns the Sum of Yearly Income, Sum of Sales, Average of Sales, and Average of Yearly Income

SELECT Occupation
      ,SUM([YearlyIncome]) AS TotalIncome
      ,SUM([Sales]) AS TotalSale
      ,AVG([YearlyIncome]) AS AverageIncome
      ,AVG([Sales]) AS AverageSale
  FROM [Employee]
GROUP BY Occupation

Having Clause

The Having Clause is used to limit the number of records returned by the Group By Clause.

This query first Groups the Employee table by Occupation and Education and returns the Sum of Income, Sales, Average Yearly Income, and Average Sales. Next, the Having Clause will restrict the records whose Sum of sales is greater than 2000

SELECT Occupation,Education
 	  ,SUM([YearlyIncome]) AS TotalIncome
      ,SUM([Sales]) AS TotalSale
      ,AVG([YearlyIncome]) AS AverageIncome
      ,AVG([Sales]) AS AverageSale
  FROM [Employee]
GROUP BY Occupation, Education
HAVING SUM(Sales) > 2000