Querying Data with SQL

01 · OPERATORS IN SQL

Operators

The examples below assume the following table:

NhanVien

Arithmetic Operators

Calculate salary after a 10% raise:

SELECT HoTen, Luong, Luong * 1.1 AS LuongMoi
FROM NhanVien;

Note: Arithmetic operators can be used directly inside SELECT to produce a computed value without saving a new column to the table.

Comparison Operators

Find employees earning over 10 million:

SELECT HoTen, Luong
FROM NhanVien
WHERE Luong > 10000000;

Find employees in department PB01:

SELECT *
FROM NhanVien
WHERE MaPB = 'PB01';

Logical Operators

AND - the employee must satisfy both conditions:

SELECT *
FROM NhanVien
WHERE Luong > 5000000
  AND MaPB = 'PB01';

OR - only one of the two conditions needs to be true:

SELECT *
FROM NhanVien
WHERE Luong > 15000000
   OR MaPB = 'PB01';

NOT - get employees not in PB01:

SELECT *
FROM NhanVien
WHERE NOT MaPB = 'PB01';

Special Operators

LIKE is used to search by pattern, combined with two wildcard characters:

Starts with "Nguyễn" - Use % at the end:

SELECT HoTen
FROM NhanVien
WHERE HoTen LIKE N'Nguyễn%';

All of these would match: Nguyễn An, Nguyễn Bình, Nguyễn Văn Nam.

Ends with "An" - Use % at the start:

SELECT HoTen
FROM NhanVien
WHERE HoTen LIKE N'%An';

Contains "Văn" - Use % on both ends:

SELECT HoTen
FROM NhanVien
WHERE HoTen LIKE N'%Văn%';

_ replaces exactly one character - for example, finding a department code shaped like PB0 followed by exactly one digit:

SELECT *
FROM NhanVien
WHERE MaPB LIKE 'PB0_';

Matches PB01, PB02,... but not PB010, since the pattern PB0_ only accepts exactly 4 characters.

BETWEEN:

SELECT *
FROM NhanVien
WHERE Luong BETWEEN 10000000 AND 15000000;

IN:

SELECT *
FROM NhanVien
WHERE MaPB IN ('PB01', 'PB02');

02 · FUNCTIONS IN SQL

Functions in SQL

Numeric Functions

SELECT
    ABS(-10) AS GiaTriTuyetDoi,
    ROUND(3.14159, 2) AS LamTron,
    SQRT(16) AS CanBacHai;

String Functions

SELECT
    HoTen,
    UPPER(HoTen) AS HoTenInHoa
FROM NhanVien;

Concatenate employee ID and name:

SELECT
    CONCAT(MaNV, ' - ', HoTen) AS ThongTinNhanVien
FROM NhanVien;

Date and Time Functions

Get the year an employee was hired:

SELECT
    HoTen,
    YEAR(NgVL) AS NamVaoLam
FROM NhanVien;

Find how many days an employee has worked (T-SQL's DATEDIFF requires the unit DAY to be specified):

SELECT
    HoTen,
    DATEDIFF(DAY, NgVL, GETDATE()) AS SoNgayLamViec
FROM NhanVien;

Aggregate Functions

Unlike the 3 function groups above (which compute per row), aggregate functions compute over a set of many rows and return a single value - usually paired with GROUP BY in section 04.

Count employees and compute the sum/average salary:

SELECT
    COUNT(*) AS SoNhanVien,
    SUM(Luong) AS TongLuong,
    AVG(Luong) AS LuongTrungBinh,
    MAX(Luong) AS LuongCaoNhat,
    MIN(Luong) AS LuongThapNhat
FROM NhanVien;

03 · THE SELECT CLAUSE

SELECT

General Syntax

SELECT [DISTINCT] <ColumnList>
FROM <TableName>
[WHERE <Condition>]
[GROUP BY <ColumnList>]
[HAVING <GroupCondition>]
[ORDER BY <ColumnList> [ASC | DESC]];

Basic SELECT

Get all data:

SELECT *
FROM NhanVien;

Get only specific columns:

SELECT MaNV, HoTen, Luong
FROM NhanVien;

DISTINCT

Removes duplicate rows:

SELECT DISTINCT MaPB
FROM NhanVien;

Data PB01, PB01, PB02, PB02, PB03 ➜ result becomes just PB01, PB02, PB03.

WHERE

Used to filter rows based on a condition:

SELECT *
FROM NhanVien
WHERE Luong > 10000000
  AND MaPB = 'PB01';

ORDER BY

SELECT HoTen, Luong
FROM NhanVien
ORDER BY Luong DESC;

ASC - ascending, DESC - descending.

TOP

Example - get the most recently hired employee:

SELECT TOP 1 HoTen, NgVL
FROM NhanVien
ORDER BY NgVL DESC;

GROUP BY

Used to group rows sharing the same value. Example - number of employees in each department:

SELECT
    MaPB,
    COUNT(*) AS SoLuong
FROM NhanVien
GROUP BY MaPB;

HAVING

WHERE filters individual rows, while HAVING filters groups after GROUP BY. Example - only show salary levels shared by at least 3 employees:

SELECT
    Luong,
    COUNT(*) AS SoLuong
FROM NhanVien
GROUP BY Luong
HAVING COUNT(*) >= 3;

04 · JOIN OPERATIONS

JOIN

NhanVien.MaPB = PhongBan.MaPB

INNER JOIN

Only returns rows that have matching data in both tables:

SELECT
    nv.HoTen,
    pb.TenPB
FROM NhanVien nv
INNER JOIN PhongBan pb
    ON nv.MaPB = pb.MaPB;

LEFT JOIN

Returns all data from the left table, even when there's no matching data in the right table:

SELECT
    nv.HoTen,
    pb.TenPB
FROM NhanVien nv
LEFT JOIN PhongBan pb
    ON nv.MaPB = pb.MaPB;

RIGHT JOIN

The opposite of LEFT JOIN: keeps all data from the right table:

SELECT
    nv.HoTen,
    pb.TenPB
FROM NhanVien nv
RIGHT JOIN PhongBan pb
    ON nv.MaPB = pb.MaPB;

FULL OUTER JOIN

Keeps data from both tables, whether or not there's a match:

SELECT
    nv.HoTen,
    pb.TenPB
FROM NhanVien nv
FULL OUTER JOIN PhongBan pb
    ON nv.MaPB = pb.MaPB;

CROSS JOIN

Produces a Cartesian product - if NhanVien has 5 rows and PhongBan has 3 rows, the result has 5 × 3 = 15 rows:

SELECT
    nv.HoTen,
    pb.TenPB
FROM NhanVien nv
CROSS JOIN PhongBan pb;

SELF JOIN

A table is used twice to express a relationship between rows within the same table - for example, employees and their managers:

SELECT
    nv.HoTen AS NhanVien,
    ql.HoTen AS QuanLy
FROM NhanVien nv
JOIN NhanVien ql
    ON nv.MaQL = ql.MaNV;

EQUI JOIN & Recommended Syntax

An EQUI JOIN compares related columns with =. Prefer the JOIN ... ON syntax:

FROM NhanVien nv
INNER JOIN PhongBan pb
    ON nv.MaPB = pb.MaPB

instead of putting the join condition in WHERE (still works, but not recommended):

FROM NhanVien nv, PhongBan pb
WHERE nv.MaPB = pb.MaPB;

05 · SET OPERATIONS

Set Operations

There are 3 main operations used to combine the results of multiple queries:

UNION

Takes all results and removes duplicate rows. Example - employees assigned to DA01 or DA02:

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA01'

UNION

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA02';

INTERSECT

Takes the intersection. Example - employees assigned to both DA01 and DA02:

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA01'

INTERSECT

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA02';

EXCEPT

Takes results from the first query that are not present in the second. Example - employees assigned to DA01 but not DA02:

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA01'

EXCEPT

SELECT MaNV
FROM PhanCong
WHERE MaDA = 'DA02';

06 · NESTED QUERIES - SUBQUERY

Subquery

SELECT TenSP
FROM SanPham
WHERE MaSP IN
(
    SELECT MaSP
    FROM HoaDon
);

Non-Correlated Subquery

An inner query that does not depend on the outer query. Example - find products sold in January 2024:

SELECT TenSP
FROM SanPham
WHERE MaSP IN
(
    SELECT MaSP
    FROM HoaDon
    WHERE MONTH(NgayBan) = 1
      AND YEAR(NgayBan) = 2024
);

IN / NOT IN

IN - checks whether a value is present in the subquery's result set:

SELECT TenSP
FROM SanPham
WHERE MaSP IN
(
    SELECT MaSP
    FROM HoaDon
);

NOT IN - find products that have never appeared on an invoice:

SELECT TenSP
FROM SanPham
WHERE MaSP NOT IN
(
    SELECT MaSP
    FROM HoaDon
);

ANY / ALL

ANY compares against at least one value in the subquery's result. Example - salary greater than any employee in PB01, PB02, or PB03:

SELECT HoTen, Luong
FROM NhanVien
WHERE Luong > ANY
(
    SELECT Luong
    FROM NhanVien
    WHERE MaPB IN ('PB01', 'PB02', 'PB03')
);

ALL requires the condition to hold against every value in the subquery. Example - products priced higher than every product in the Laptop category:

SELECT MaSP, TenSP, GiaBan
FROM SanPham
WHERE DanhMuc = 'Phu Kien'
  AND GiaBan > ALL
(
    SELECT GiaBan
    FROM SanPham
    WHERE DanhMuc = 'Laptop'
);

EXISTS / NOT EXISTS

EXISTS checks whether a subquery returns at least one row. Example - employees with at least one order:

SELECT HoTen
FROM NhanVien nv
WHERE EXISTS
(
    SELECT *
    FROM DonHang dh
    WHERE dh.MaNV = nv.MaNV
);

Here, the subquery references the current row of the outer query (nv.MaNV) - this is called a Correlated Subquery.

NOT EXISTS - find employees with no orders at all:

SELECT HoTen
FROM NhanVien nv
WHERE NOT EXISTS
(
    SELECT *
    FROM DonHang dh
    WHERE dh.MaNV = nv.MaNV
);

07 · SUMMARY - WHICH TOOL TO USE?

Which Tool to Use?

A quick-reference table: knowing the syntax is one thing, but knowing when to use what is what matters.

08 · PRACTICE EXERCISES

Comprehensive Practice