CLAUSES: 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.
WAQ to find the highest salaries
for each department.
Sol: SELECT DEPTNO, MAX(SAL) FROM
EMP GROUP BY DEPTNO
WAQ to find the highest salaries
for each job.
Sol: SELECT JOB, MAX(SAL) FROM
EMP GROUP BY JOB
WAQ 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.
WAQ to find the number of
employees working for each department.
Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO
WAQ 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.
WAQ to find the number of Clerk’s
working for each department.
Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’
GROUP BY DEPTNO
WAQ 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 order_list[ ASC |
DESC ]
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.
SELECT *
FROM EMP ORDER BY SAL
SELECT *
FROM EMP ORDER BY SAL DESC
No comments:
Post a Comment