DQL

Learn how to retrieve, filter, sort, group, and analyze data using SQL SELECT queries.

DQL - Retrieving Data with SELECT

Data Query Language (DQL) is used to retrieve data from a database. The primary command in DQL is SELECT.

Throughout this chapter, we’ll use the following tables.

Department

Department ID	Department Name
1	Engineering
2	HR
3	Sales

Employee

Employee ID	Employee Name	Salary	City	Department ID
101	Rahul	65000	Hyderabad	1
102	Anitha	55000	Bengaluru	2
103	Kiran	72000	Hyderabad	1
104	Sneha	50000	Chennai	3
105	Ajay	80000	Hyderabad	1

SELECT Statement

The SELECT statement retrieves data from one or more tables.

Syntax

SELECT column_name
FROM table_name;

Example 1

Retrieve all columns.

SELECT *
FROM employees;

Example 2

Retrieve only employee names.

SELECT employee_name
FROM employees;

Example 3

Retrieve employee names and salaries.

SELECT employee_name, salary
FROM employees;

Practice

Display only the employee names and cities.

Solution

SELECT employee_name, city
FROM employees;

Column Aliases

Aliases provide temporary names to columns, making the output easier to read.

Syntax

SELECT column_name AS alias_name
FROM table_name;

Example

SELECT
    employee_name AS Name,
    salary AS MonthlySalary
FROM employees;

Example

SELECT
    employee_name AS Name,
    salary * 12 AS AnnualSalary
FROM employees;

Practice

Display employee names as Employee and salary as Salary.

Solution

SELECT
    employee_name AS Employee,
    salary AS Salary
FROM employees;

DISTINCT

DISTINCT removes duplicate values.

Syntax

SELECT DISTINCT column_name
FROM table_name;

Example

SELECT DISTINCT city
FROM employees;

Example

SELECT DISTINCT department_id
FROM employees;

Practice

Display all unique department IDs.

Solution

SELECT DISTINCT department_id
FROM employees;

WHERE Clause

The WHERE clause filters rows based on a condition.

Syntax

SELECT *
FROM employees
WHERE condition;

Example

SELECT *
FROM employees
WHERE salary > 60000;

Example

SELECT *
FROM employees
WHERE city = 'Hyderabad';

Example

SELECT *
FROM employees
WHERE department_id = 1;

Practice

Display employees earning more than ₹70,000.

Solution

SELECT *
FROM employees
WHERE salary > 70000;

Comparison Operators

Operator	Meaning
=	Equal
!=	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than or Equal
<=	Less Than or Equal

Example

SELECT *
FROM employees
WHERE salary >= 60000;

Practice

Display employees whose salary is less than ₹60,000.

Solution

SELECT *
FROM employees
WHERE salary < 60000;

Logical Operators

Logical operators combine multiple conditions.

AND

Both conditions must be true.

SELECT *
FROM employees
WHERE city = 'Hyderabad'
AND salary > 60000;

OR

At least one condition must be true.

SELECT *
FROM employees
WHERE city = 'Hyderabad'
OR city = 'Chennai';

NOT

Negates a condition.

SELECT *
FROM employees
WHERE NOT city = 'Hyderabad';

Practice

Display employees from Hyderabad earning more than ₹65,000.

Solution

SELECT *
FROM employees
WHERE city = 'Hyderabad'
AND salary > 65000;

BETWEEN

BETWEEN checks whether a value falls within a range.

Example

SELECT *
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Practice

Display employees with salaries between ₹55,000 and ₹80,000.

Solution

SELECT *
FROM employees
WHERE salary BETWEEN 55000 AND 80000;

IN

IN checks whether a value exists in a list.

Example

SELECT *
FROM employees
WHERE department_id IN (1, 3);

Practice

Display employees belonging to departments 2 and 3.

Solution

SELECT *
FROM employees
WHERE department_id IN (2, 3);

LIKE

LIKE searches for text patterns.

Pattern	Meaning
A%	Starts with A
%a	Ends with a
%an%	Contains “an”
_	Single character

Example

SELECT *
FROM employees
WHERE employee_name LIKE 'A%';

Example

SELECT *
FROM employees
WHERE employee_name LIKE '%a';

Practice

Display employees whose names start with S.

Solution

SELECT *
FROM employees
WHERE employee_name LIKE 'S%';

NULL Values

Use IS NULL or IS NOT NULL to work with missing values.

Example

SELECT *
FROM employees
WHERE department_id IS NULL;

Practice

Display employees whose department has been assigned.

Solution

SELECT *
FROM employees
WHERE department_id IS NOT NULL;

ORDER BY

ORDER BY sorts the result set.

Ascending Order

SELECT *
FROM employees
ORDER BY salary;

Descending Order

SELECT *
FROM employees
ORDER BY salary DESC;

Multiple Columns

SELECT *
FROM employees
ORDER BY department_id, salary DESC;

Practice

Display employees sorted by employee name.

Solution

SELECT *
FROM employees
ORDER BY employee_name;

LIMIT and OFFSET

LIMIT

Returns only the specified number of rows.

SELECT *
FROM employees
LIMIT 3;

OFFSET

Skips a specified number of rows.

SELECT *
FROM employees
LIMIT 2 OFFSET 3;

Practice

Display the first four employees.

Solution

SELECT *
FROM employees
LIMIT 4;

Aggregate Functions

Aggregate functions perform calculations on multiple rows.

Function	Purpose
COUNT()	Counts rows
SUM()	Total
AVG()	Average
MIN()	Smallest value
MAX()	Largest value

Example

SELECT COUNT(*)
FROM employees;

SELECT AVG(salary)
FROM employees;

SELECT MAX(salary)
FROM employees;

Practice

Find the minimum salary.

Solution

SELECT MIN(salary)
FROM employees;

GROUP BY

GROUP BY groups rows before performing aggregate calculations.

Example

SELECT
    department_id,
    AVG(salary)
FROM employees
GROUP BY department_id;

Practice

Count employees in each city.

Solution

SELECT
    city,
    COUNT(*)
FROM employees
GROUP BY city;

HAVING

HAVING filters groups after aggregation.

Example

SELECT
    department_id,
    AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Practice

Display cities having more than one employees.

Solution

SELECT
    city,
    COUNT(*)
FROM employees
GROUP BY city
HAVING COUNT(*) > 1;

SQL Execution Order

Although we write queries in this order:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

SQL actually executes them in the following order:

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT

Understanding this order helps explain why some clauses (like WHERE) cannot use column aliases created in SELECT.

Summary

In this chapter, you learned how to:

• Retrieve data using SELECT
• Filter rows with WHERE
• Remove duplicates using DISTINCT
• Sort results using ORDER BY
• Limit returned rows using LIMIT and OFFSET
• Perform calculations using aggregate functions
• Group data using GROUP BY
• Filter grouped results using HAVING

The next chapter introduces SQL JOINs, allowing you to retrieve related data from multiple tables.