Clauses In SQL Server

We add clauses in a query to provide the additional facilities like filtering, storing, fetching, and grouping the records in the Table.

SQL Server Provides the following clauses,

Where
Order by
Top n
Group By
Having

Where

This keyword is used to filter the records from the table.

Example

Write a query to display user details whose username is 'Den'.

Select * from user where username ='den'

Important Point to Remember

Where clauses can be used on select, update and delete Commands only.

Order By

Order By is used to arrange or sort the values either in ascending or descending order in the table.

Example

Write a query to display user salary in the ascending order.

Select * from user order by salary

Example

Write a query to display username in the descending order.

Select * from user order by Username Desc

Important Point to Remember

By default, the order by clause will arrange the values in ascending order.

If we want to arrange the values in descending order, then we use Desc Keyword.

Order by clause can be used on both integer and character datatypes.

It will arrange the values in a temporary manner. We cannot arrange permanently

It can be applied on select command only.

Top n

It is used for fetching top most records from the table. Here, n will represent the number of records in the Table.

Example

Write a query to display top 5 records from user Table.

Select top 5 * from user

Group By

This clause is used for dividing the similar data into groups. When we use group by clause we should use an aggregate function like count, sum, max etc.

When we implement group by clause in the query first the data in the table will be divided into separate groups based on the columns and later an aggregate function will execute on each group to get the results.

Create a Table Emp

Create Table Emp(emid int,ename varchar(50),salary int,dname varchar(50),location varchar(50))

Insert some values into Emp Table

insert into Emp values(1,'A',45000,'.Net','Jaipur')
insert into Emp values(2,'B',36000,'Java','Delhi')
insert into Emp values(3,'C',57000,'.Net','Noida')
insert into Emp values(4,'D',31000,'Java','Jaipur')
insert into Emp values(5,'E',51000,'SQL','Pune')
insert into Emp values(6,'F',28000,'Java','Gurgaon')
insert into Emp values(7,'G',48000,'.Net','Jaipur')
insert into Emp values(8,'H',61000,'.Net','Mumbai')
insert into Emp values(9,'I',38000,'Hr','Pune')
insert into Emp values(10,'J',44000,'.Net','Delhi')
insert into Emp values(11,'K',15000,'He','Jaipur')
Select * from Emp

Example

Write a query to find the number of emp working in each deptartment in the company.

Select dname,Total_Emp=count(*) from emp group by dname

Result

Having

Having is also used for filtering the records in the table, just like where clause, but we can use the aggregate function in it.

Example

Write a query to display dname if the department total emp is more than 3

Select dname, count(*) from emp group by dname having count(*)>3

Differences Between Where and Having Clauses

Where Clause	Having Clause
It will filter the records before grouping the data in the Table	It is also used for filtering the records after grouping the data in the Table
If filtering column is associated with an aggregate function in this situation we cannot use where clause keyword	But we can use having clause in this situation
Where clause can be used without group by keyword	It is not possible to use without group by
It will execute on individual rows in the Table	It will execute on the group of records along with the group by clause