Extended Functions, CASE & Subquery

01 · WEEK 4 OVERVIEW

From Querying Data to Processing Data

This is the shift from querying data to processing data and turning it into information. By the end of this week, students will be able to convert data types, extract substrings, build conditional logic with CASE, write subqueries in FROM/SELECT combined with aliases, and check for the existence of related data with EXISTS/NOT EXISTS.

02 · THE CAST() FUNCTION

CAST()

Syntax

CAST(expression AS target_data_type)

expression: the value or expression to convert. target_data_type: the data type to convert to.

Basic Example

Convert a string into a datetime:

SELECT CAST('2025-04-10' AS DATETIME) AS Ngay;

Result: 2025-04-10 00:00:00.000

Convert a float into an integer:

SELECT CAST(123.456 AS INT) AS SoNguyen;

Result: 123

Common Data Types

CAST() in a Real Scenario

Table NhanVien(MaNV, HoTen, Luong) where Luong is stored as DECIMAL(12,2), and we want to display it as a whole number:

SELECT
    MaNV,
    HoTen,
    CAST(Luong AS INT) AS Luong
FROM NhanVien;

Watch Out for Rounding

SELECT CAST(123.99 AS INT);

Result: 123

03 · THE GETDATE() FUNCTION

GETDATE()

SELECT GETDATE() AS NgayGioHienTai;

Combining GETDATE() and CAST()

If you only need today's date, without the time:

SELECT CAST(GETDATE() AS DATE) AS NgayHienTai;

Example: Calculating Age

Suppose NgaySinh = '2000-08-15'. Get today's date with SELECT GETDATE() AS HomNay;

DATEADD() - Precise Age Calculation Down to the Day

SELECT DATEADD(YEAR, -18, GETDATE()) AS Ngay18NamTruoc;

To check "the student must be at least 18 years old", instead of subtracting year from year (which drifts around the birthday), compare NgaySinh against "the date exactly 18 years before today":

SELECT MaSV, HoTen, NgaySinh
FROM SinhVien
WHERE NgaySinh <= DATEADD(YEAR, -18, GETDATE());

This is exact down to the day since it compares 2 date values directly, unlike rounding to calendar years with YEAR(GETDATE()) - YEAR(NgaySinh) - the calendar-year rounding still works fine when the question only needs a rough estimate, but it will be off by a few months around a birthday.

04 · THE LEFT() AND RIGHT() FUNCTIONS

LEFT() and RIGHT()

LEFT() takes a number of characters from the left of a string, RIGHT() takes them from the right.

LEFT(string, num_chars)
RIGHT(string, num_chars)

SELECT LEFT('abcdef', 3);   -- abc
SELECT RIGHT('abcdef', 3);  -- def

Example with a Student ID

Suppose MaSV = '22520001', table SinhVien(MaSV, HoTen). Get the first 3 characters (cohort code):

SELECT
    MaSV,
    HoTen,
    LEFT(MaSV, 3) AS Khoa
FROM SinhVien;

Get the last 3 characters (sequence number):

SELECT
    MaSV,
    RIGHT(MaSV, 3) AS SoThuTu
FROM SinhVien;

For MaSV = 22520001: Khoa = 225, SoThuTu = 001.

Quick Comparison

05 · CASE...END

CASE...END

Condition-Based Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_other
END

SELECT
    HoTen,
    Luong,
    CASE
        WHEN Luong >= 20000000 THEN N'Cao'
        WHEN Luong >= 10000000 THEN N'Trung bình'
        ELSE N'Thấp'
    END AS MucLuong
FROM NhanVien;

Example: Classifying Customers

Classify by TongChiTieu (total spend): ≥ 10,000,000 ➜ VIP, ≥ 5,000,000 ➜ Loyal, otherwise ➜ Regular.

SELECT
    MaKH,
    HoTen,
    CASE
        WHEN TongChiTieu >= 10000000 THEN N'VIP'
        WHEN TongChiTieu >= 5000000 THEN N'Thân thiết'
        ELSE N'Thường'
    END AS PhanLoai
FROM KhachHang;

Condition Order Matters - A Lot

The following is logically wrong:

CASE
    WHEN TongChiTieu >= 5000000 THEN N'Thân thiết'
    WHEN TongChiTieu >= 10000000 THEN N'VIP'
    ELSE N'Thường'
END

CASE with Value Comparison

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result_other
END

Example: classifying gender:

SELECT
    MaNV,
    HoTen,
    CASE GioiTinh
        WHEN 1 THEN N'Nam'
        WHEN 0 THEN N'Nữ'
        ELSE N'Không xác định'
    END AS PhanLoaiGioiTinh
FROM NhanVien;

The Two Forms of CASE

CASE Isn't Just for SELECT

CASE can also be used in ORDER BY, and even WHERE/GROUP BY when needed:

ORDER BY
    CASE
        WHEN TrangThai = N'Ưu tiên' THEN 1
        ELSE 2
    END;

06 · ALIAS AND SUBQUERY

Alias and Basic Subquery

SELECT SV.MaSV, SV.HoTen
FROM SinhVien SV;

Alias for a column:

SELECT
    HoTen AS TenSinhVien
FROM SinhVien;

Alias for a table (AS can be omitted):

SELECT
    SV.MaSV,
    SV.HoTen
FROM SinhVien AS SV;

What Is a Subquery?

SELECT *
FROM SinhVien
WHERE Diem > (
    SELECT AVG(Diem)
    FROM SinhVien
);

Reads as: "Find students whose grade is higher than the average grade of all students." Logic: compute AVG(Diem) first, then take the rows where Diem > AVG.

07 · SUBQUERY IN FROM AND SELECT

Advanced Subquery

This is the new core topic for Week 4.

Subquery in FROM + Alias

SELECT Alias1.Cot1, Alias1.Cot2
FROM
(
    SELECT ...
    FROM ...
    WHERE ...
) AS Alias1
WHERE ...;

SQL builds a temporary result table from the subquery, and the outer query then uses that table:

SELECT *
FROM
(
    SELECT
        MaSV,
        HoTen,
        Diem
    FROM SinhVien
) AS SV
WHERE SV.Diem >= 8;

Subquery in SELECT + Alias

This is an especially important part of Week 4.

SELECT
    <main column>,
    (
        SELECT <expression>
        FROM <related table>
        WHERE <linking condition>
    ) AS <alias>
FROM <main table>;

Example - Counting Sales per Product

Tables SanPham(MaSP, TenSP), CTHD(MaHD, MaSP, SoLuong). Problem: "for each product, show how many times it appears in invoice details."

SELECT
    SP.TenSP,
    (
        SELECT COUNT(*)
        FROM CTHD
        WHERE CTHD.MaSP = SP.MaSP
    ) AS SoLanBan
FROM SanPham SP;

Correlated Subquery

In the example above, WHERE CTHD.MaSP = SP.MaSP references SP.MaSP from the outer query - this is a correlated subquery: the subquery is recomputed separately for each row of the outer query.

Comparison with a Regular Subquery

A regular subquery runs independently, with no dependency on the outer row:

SELECT *
FROM SanPham
WHERE Gia >
(
    SELECT AVG(Gia)
    FROM SanPham
);

A correlated subquery depends on each outer row (SP.MaSP in the example above).

When Should You Use a Correlated Subquery?

It's a great fit for questions of the form "for each A, compute a value related to A" - for example: how many times has each product been sold? how many courses has each student registered for? how many projects has each employee participated in? how many invoices does each customer have? how many employees does each department have?

Example - Academic Affairs

Tables SINHVIEN(MaSV, HoTen), DANGKY(MaSV, MaHP). Problem: "show each student along with the number of courses they've registered for."

SELECT
    SV.MaSV,
    SV.HoTen,
    (
        SELECT COUNT(*)
        FROM DangKy DK
        WHERE DK.MaSV = SV.MaSV
    ) AS SoHocPhan
FROM SinhVien SV;

Combining CASE + Subquery

Problem: "classify students based on the number of courses they've registered for."

SELECT
    SV.MaSV,
    SV.HoTen,
    CASE
        WHEN
        (
            SELECT COUNT(*)
            FROM DangKy DK
            WHERE DK.MaSV = SV.MaSV
        ) >= 20
        THEN N'Đăng ký nhiều'

        WHEN
        (
            SELECT COUNT(*)
            FROM DangKy DK
            WHERE DK.MaSV = SV.MaSV
        ) >= 15
        THEN N'Bình thường'

        ELSE N'Đăng ký ít'
    END AS PhanLoai
FROM SinhVien SV;

EXISTS and NOT EXISTS

SELECT ...
FROM BangA a
WHERE EXISTS (
    SELECT * FROM BangB b WHERE b.KhoaNgoai = a.KhoaChinh
);

Example - "Find students who registered for at least 1 course" (reusing SinhVien, DangKy from above):

SELECT SV.MaSV, SV.HoTen
FROM SinhVien SV
WHERE EXISTS (
    SELECT * FROM DangKy DK WHERE DK.MaSV = SV.MaSV
);

Flip it to NOT EXISTS to find students who have not registered for any course:

SELECT SV.MaSV, SV.HoTen
FROM SinhVien SV
WHERE NOT EXISTS (
    SELECT * FROM DangKy DK WHERE DK.MaSV = SV.MaSV
);

EXISTS/NOT EXISTS is always a correlated subquery (just like the Correlated Subquery above) - the WHERE DK.MaSV = SV.MaSV clause references the outer row, so the subquery is re-evaluated separately for each student. This is exactly the mechanism behind the "2 nested NOT EXISTS" structure from Week 3 used to solve "for all" problems - a single EXISTS/NOT EXISTS is for a simpler "does this exist or not" question, while 2 nested layers are needed to express "for every".

08 · PUTTING IT ALL TOGETHER

Keyword Recognition & Cheat Sheet

The First Four Weeks of IT004

Week 1: creating & manipulating data. Week 2: querying & combining data. Week 3: advanced querying ("all" / grouping / TOP). Week 4: transforming & computing over data with CAST, CASE, Subquery, and EXISTS/NOT EXISTS.

09 · PRACTICE EXERCISES

Comprehensive Practice

After Week 4, here are some extra problems to sharpen your thinking - not part of the BTTH4 submission. Each has a hidden hint - click to reveal.