T-SQL Clauses

Most commonly used T-SQL command is SELECT statement. Below are the basic T-SQL clauses every SQL beginner must know:

SELECT <Column_List>
FROM <Table_Name>
WHERE <Conditional_Statement>
GROUP BY <Column_List>
HAVING <Conditional_Statement>
ORDER BY <Column_List>


Lets take an example of Employee table to understand these clauses:

EmployeeID	FirstName	MiddleName	LastName	DOB	Basic	Department
1	Hari	N	Sharma	10/10/1980	2500	Sales
2	John	D	Howell	11/11/1981	1900	Sales
3	Vijay	NULL	Sharma	10/20/1982	1500	Engineering
4	Paul	NULL	Catlin	7/25/1976	2600	Engineering
5	Romil	F	Driscoll	1/13/1979	2400	Sales
6	Karthik	P	Nagarajan	8/10/1969	2800	Engineering
7	Hany	H	Rodriguez	4/10/1977	2200	Management
8	William	NULL	Green	1/21/1981	1800	Management
9	Michelle	NULL	Jones	5/19/1974	2300	Management
10	Thomas	D	Lee	3/31/1984	1600	Sales

SELECT statement is used to pull the data from a table, view, or table valued function.
FROM clause is used to specify the name of the source table which we pull the data from.

For instance, we want to pull FirstName. LastName of all the employees along with Department name then we will use following query:

SELECT  FirstName,LastName,Department
FROM    dbo.Employee

Here is output:

WHERE clause is used to filter the data coming from a table. For instance, you want to pull FirstName, MiddleName, LastName, and Basic of all employees who belong to Sales department:

SELECT  FirstName,MiddleName,LastName,Basic
FROM    dbo.Employee
WHERE   Department = 'Sales'

　
Here is output:

 

GROUP BY clause is used to aggregate the data based on certain group of columns. For instance, find out the TOTAL of basic amount for each department.

SELECT  Department, SUM(Basic) BasicTotal
FROM    dbo.Employee
GROUP BY Department

Here is output:

 

HAVING clause is used to filter out the data based on aggregated values. For instance, find out the list of Departments for which Total of basic is greater than 6500:

SELECT  Department, SUM(Basic) BasicTotal
FROM    dbo.Employee
GROUP BY Department
HAVING   SUM(Basic) > 6500

I will explain the difference between WHERE and HAVING in my next post.


ORDER BY clause is used to arrange the result set in desired order, We can order the result in DESC or ASC order. For instance, get the list of all the employees in the order of First Name and then Last Name:

SELECT  FirstName,MiddleName,LastName,Basic
FROM    dbo.Employee
ORDER BY FirstName,LastName

You can also write this query in following way (1 means first column in SELECT statement and 2 means second column the SELECT statement):

SELECT FirstName,MiddleName,LastName,Basic
FROM dbo.Employee
ORDER BY 1,2