SQL Server Clauses

Sql Server clauses used in SELECT statements are mainly:

WHERE
GROUP BY
HAVING
ORDER BY

Syntax

SELECT columns_list
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

Table used in the following examples:
SQL-Server-Insert1

WHERE: Where clause is used to filter the data based on condition. Only rows that meets conditions contribute data to the result set.
Examples:
1) Get employees whose designation is manager
SELECT * FROM emp where job=‘manager’

2) Select employees whose salary is more than 5000 dollars
SELECT * FROM emp where salary>5000

GROUP BY: Group by clause is used to group records based on one or more columns to calculate aggregates like max, min, avg, sum,…
Using group by we can get summarized data.

Examples:
1. Display the maximum salaries of each department.

SELECT deptno, MAX(salary) maxsal from emp group by deptno

Output:
SQL Server Clauses

2) How to find out number of employees working in each department.
SELECT deptno, count(*) FROM emp group by deptno

3) Display number of employees joined in each calendar year.
SELECT YEAR(joindate) as year, COUNT(*) from emp group by YEAR(joindate)

4) Find the highest salaries for each job.
SELECT job max(salary) from emp group by job

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

Examples:
Display departments information which are paying total more than 5000 dollars.
SELECT deptno, sum(salary) from emp group by deptno
having sum(salary)>5000

WHERE vs Having clauses

  • To filter data before Group by use the where clause
  • To filter data after group by use having clause
  • In condition, if there is no aggregate function then use where clause
  • In condition, if there aggregate functio, then use Having clause

ORDER BY: 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 rows are sorted in an ascending or descending sequence.

Examples:

1) Display employees information sorted by salary
select * from emp order by salary

2) Display employees information sorted by salary in descending order.
select * from emp order by salary desc

Powered by k2schools