Data Query Language (DQL) - SQL

Data Query Language (DQL) - SQL

SELECT:

SELECT– Retrieves data from a table

SELECT Command is used to retrieve data from one or more tables which are having relation. While retrieving data we can make changes (Manipulate) on the output. So, it’s placed in DML group.

Syntax:

SELECT Col1, Col2, Col3 FROM <tbl_Name>

     Example:

SELECT EmpID, EmpName FROM tbl_Employees.

The above example is to retrieving mentioned columns and all rows from a table.

Output:

EmpID	EmpName
1	Sekhar
2	Raj
3	Naga
4	Ramesh

To retrieve all columns and all rows information

SELECT * FROM tbl_Employees

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
2	Raj	Admin
3	Naga	Accounts
4	Ramesh	MIS

‘*’ represents all column names from the table.

WHERE:

To get few rows information which we required, we need to add WHERE clause to the query.

Syntax:

SELECT Col1, Col2, Col3 FROM <tbl_Name> WHERE < Col_Nme> = <Parameter>

Let us suppose we need the information of the person who is having EmpID = 3

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpID = 3   

Output:

EmpID	EmpName	EmpDept
3	Naga	Accounts

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpID >= 2

To get the List of the employees who are having employees ID >=2

Output:

EmpID	EmpName	EmpDept
2	Raj	Admin
3	Naga	Accounts
4	Ramesh	MIS

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept = 'MIS'

                           (Or)

LIKE:

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept like 'MIS'

To get the list of employees who are working in MIS Dept.

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
4	Ramesh	MIS

Like key word is used to get the list of records which are matching with the mentioned characters.

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees WHERE EmpDept like '%MI%'

Output:

EmpID	EmpName	EmpDept
1	Sekhar	MIS
2	Raj	Admin
4	Ramesh	MIS

 In the above example, I want to get the information of employees who are having characters ‘MI’ in their department column.

IN:

SELECT * FROM tbl_Employees WHERE  EmpDept in ('MIS','Accounts')

Fetch the records from the tbl_Employees which are having 'MIS','Accounts'

as "EmpDept" 

BETWEEN ... AND:

SELECT * FROM tbl_Employees WHERE EmpID BETWEEN 4 AND 8 

Fetch the records from the tbl_Employees whose employee IDS in between 4 AND 8.

Like this we can write different WHERE clauses based on our needs.

ISNULL:

SELECT * FROM tbl_Employees WHERE  EmpDept IS NULL.

returns the list of records which are having column null values in EmpDept .

ORDER BY:

Order by clause is used to sort the data in the table. We can sort the data in Ascending(ASC) and Descending(DESC) order.

Syntax:

SELECT Col1, Col2, Col3 FROM <tbl_Name> ORDER BY Col1,Col2 etc..... ASC/DESC

Example:

SELECT * FROM tbl_Employees ORDER BY EmpName ASC

EmpID	EmpName	EmpDept
11	mno	Trainer
1	Naga	MIS
6	Rahul	kp
7	Rahul	kp
2	RK	Admin
8	Sekhar	Trainer
9	Sekhar	Trainer
10	xyz	Trainer

SELECT * FROM tbl_Employees ORDER BY EmpName DESC 

(Get the data in descending order)

EmpID	EmpName	EmpDept
11	mno	Trainer
1	Naga	MIS
6	Rahul	kp
7	Rahul	kp
2	RK	Admin
8	Sekhar	Trainer
9	Sekhar	Trainer
10	xyz	Trainer

Distinct

Distinct clause is used to remove the duplicate records from a set of rows,

If you observe the above table EmpName is having some names twice.

SELECT EmpName FROM tbl_Employees

If you run this query, you will get output  as below:

EmpName

Naga

RK

Rahul

Rahul

Sekhar

Sekhar

xyz

mno

In this Some employee names like Rahul,Sekhar are repeating.

To remove the repeating records , we have to write query as below:

SELECT DISTINCT EmpName FROM tbl_Employees

EmpName

mno

Naga

Rahul

RK

Sekhar

xyz

Alias:

Alias keyword is used to rename the column names in the selected query.

Employees table Contains "EmpID,EmpName,EmpDept" columns.

SELECT EmpID, EmpName, EmpDept FROM tbl_Employees

EmpID	EmpName	EmpDept
11	mno	Trainer
1	Naga	MIS
6	Rahul	kp
7	Rahul	kp
2	RK	Admin
8	Sekhar	Trainer
9	Sekhar	Trainer
10	xyz	Trainer

In the output if you want to change the columns names need to write the queries as below.

SELECT EmpID AS EID,EmpName AS Ename,EmpDept AS EDept FROM tbl_Employees

EID	Ename	EDept
1	Naga	MIS
2	RK	Admin
6	Rahul	kp
7	Rahul	kp
8	Sekhar	Trainer
9	Sekhar	Trainer
10	xyz	Trainer
11	mno	Trainer