CLAUSES in SQL Server

SQL Server provides with the following clauses that can be used in the SELECT statements:

WHERE
GROUP BY
HAVING
ORDER BY

The complete syntax of the SELECT statement looks as following:

SELECT <select_list> FROM <tname>

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

WHERE Clause:

The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions is not used.

SELECT * FROM EMP WHERE JOB='MANAGER'

SELECT * FROM EMP WHERE DEPTNO=20

GROUP BY Clause:

The GROUP BY clause partitions the result set into groups based on the values in the columns of the group_by_list. For example, the Emp table has 3 values in Deptno column. A GROUP BY Deptno clause partitions the result set into 3 groups, one for each value of Deptno.

Ex-1:How to find the highest salaries for each department.

Sol: SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

Ex-2:How to find the highest salaries for each job.

Sol: SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB

Ex-3:How to find the highest salaries for each department in it for each job.

Sol: SELECT DEPTNO, JOB, MAX(SAL) FROM EMP GROUP BY DEPTNO, JOB

Note: While using the GROUP By clause the select_list of the query should contain only the following:

Group Functions or Aggregate Functions
Columns used in the Group By Clause
Constants.

Ex-4:How to find the number of employees working for each department.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO

Ex-5:How to find the number of employees working for each department only if the number is greater than 3.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3

HAVING Clause:

The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are typically used with a GROUP BY clause.

Ex-6:How to find the number of Clerkâ€™s working for each department.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO

Ex-7:How to find the number of Clerk's working for each department only if the count is greater than 1.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*)>1

ORDER BY clause:

The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.