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.
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 |