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
