Follow in Twitter & Facebook



Like in Facebook

Powered By Blogger Widgets

Free Download

FREE Tools



Friday, April 20, 2012

Different Clauses in Sql Server 2008

Introduction:
 Here we will discuss about different clauses in sqlserver like WHERE, GROUP BY, HAVING, ORDER BY etc. Also you can check my last article on Constraints in SQL Server 2008.

Description:
SQL Server provides with the following clauses that can be used in the Select statements like WHERE, GROUP BY, HAVING, ORDER BY.
The order while using inside a sql query is like
WHERE
GROUP BY
HAVING
ORDER BY

The complete syntax of the select statement looks as the 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 condition shows as the result.

 For EX:
 SELECT * FROM EMP WHERE JOB='CLERK'

GROUP BY Clause:

This clause partitions the results 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:
SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO
SELECT DEPTNO,DEPTNAME,MAX(SAL) FROM EMP GROUP BY DEPTNO,DEPTNAME
*Note that while using GROUP BY, you can't include the aggregate functions for example
its a wrong to write SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO,MAX(SAL)

HAVING Clause:
The HAVING Clause is an additional filter that is applied to the result set.
Logically you can tell like the HAVING Clause filters the 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 GROUP BY Clause.

For Example
SELECT DEPTNO,COUNT(*) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING COUNT(*)>1
It will find the number of clerks working in each department if the count is greater than 1.

ORDER BY Clause:
The ORDER BY Clause defines the order in which the rows of the resultset are sorted.
The ASC and DESC keywords are used to specify if the rows are sorted in ascending or descending order.
ASC is the default one. For DESC, you need to provide the DESC keyword.

For Example
SELECT * FROM EMP ORDER BY SAL
SELECT * FROM EMP ORDER BY SAL DESC
The first statement is for ascending and the second statement is for descending. Also you can see some SQL Server Articles.




0 on: "Different Clauses in Sql Server 2008"