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.

​DQL - Retrieving Data with SELECT

​Department

​Employee

​SELECT Statement

​Syntax

​Example 1

​Example 2

​Example 3

​Practice

​Column Aliases

​Syntax

​Example

​Example

​Practice

​DISTINCT

​Syntax

​Example

​Example

​Practice

​WHERE Clause

​Syntax

​Example

​Example

​Example

​Practice

​Comparison Operators

​Example

​Practice

​Logical Operators

​AND

​OR

​NOT

​Practice

​BETWEEN

​Example

​Practice

​IN

​Example

​Practice

​LIKE

​Example

​Example

​Practice

​NULL Values

​Example

​Practice

​ORDER BY

​Ascending Order

​Descending Order

​Multiple Columns

​Practice

​LIMIT and OFFSET

​LIMIT

​OFFSET

​Practice

​Aggregate Functions

​Example

​Practice

​GROUP BY

​Example

​Practice

​HAVING

​Example

​Practice

​SQL Execution Order

​Summary

DQL - Retrieving Data with SELECT

Department

Employee

SELECT Statement

Syntax

Example 1

Example 2

Example 3

Practice

Column Aliases

Syntax

Example

Example

Practice

DISTINCT

Syntax

Example

Example

Practice

WHERE Clause

Syntax

Example

Example

Example

Practice

Comparison Operators

Example

Practice

Logical Operators

AND

OR

NOT

Practice

BETWEEN

Example

Practice

IN

Example

Practice

LIKE

Example

Example

Practice

NULL Values

Example

Practice

ORDER BY

Ascending Order

Descending Order

Multiple Columns

Practice

LIMIT and OFFSET

LIMIT

OFFSET

Practice

Aggregate Functions

Example

Practice

GROUP BY

Example

Practice

HAVING

Example

Practice

SQL Execution Order

Summary