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
Clauses in SQL Server 1

Order By Clause in SQL Server

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

-- SQL Order By Clause Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY Sales DESC
Clauses in SQL Server 2

Top Clause in SQL Server

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

-- SQL Top Clause Example
SELECT TOP 6 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY YearlyIncome DESC
Clauses in SQL Server 3

SQL Server Where

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

-- SQL Where Clause Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Sales > 2000
Clauses in SQL Server 4

Sql Server Group By

The Group By clause returns the aggregated data by grouping one or more columns and apply aggregated functions on 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

-- SQL Group By Clause Example
SELECT Occupation
      ,SUM([YearlyIncome]) AS TotalIncome
      ,SUM([Sales]) AS TotalSale
      ,AVG([YearlyIncome]) AS AverageIncome
      ,AVG([Sales]) AS AverageSale
  FROM [Employee]
GROUP BY Occupation
Clauses in SQL Server 5

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, Education, and returns the Sum of Income, Sales, Average of Yearly Income,  and Average of Sales. Next, Having Clause will restrict the records whose Sum of sales is greater than 2000

-- SQL Having Clause Example
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
Clauses in SQL Server 6