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
