SQL

0. 點樣跑本頁所有例子（Runnable setup）

Slide 2 建議用 db-fiddle.com（online playground）或者 VS Code + SQLite（Python 內置 sqlite3 package；可裝 SQLite Viewer extension 睇內容）；練習可用 LeetCode database problems。以下 script 建立本頁用到的所有 tables（跟 slides 的數據）。貼入 db-fiddle（揀 SQLite）或者用下面 Python 跑：

-- setup.sql
CREATE TABLE Student (            -- slide 8–23
    Student_ID CHAR(4),
    Name       VARCHAR(100),
    Year       INT
);
INSERT INTO Student VALUES ('s123', 'Amy', 1);
INSERT INTO Student VALUES ('s234', 'Bob', 2);
INSERT INTO Student VALUES ('s345', 'Cathy', 1);

-- slide 24–29、35–36、57–63（為免同上面撞名，Student 改叫 Stud）
CREATE TABLE Stud   (StudentID INT, StudName VARCHAR(50), Major VARCHAR(10));
INSERT INTO Stud VALUES (1,'Amy','DS'), (3,'Bob','CS'), (6,'Cathy','CS');
CREATE TABLE Result (StudentID INT, ModuleCode CHAR(6), Grade CHAR(1));
INSERT INTO Result VALUES (1,'CS1002','B'), (1,'CS1001','A'), (3,'CS1001','A'), (6,'CS1001','B');
CREATE TABLE Module (ModuleCode CHAR(6), ModuleName VARCHAR(100));
INSERT INTO Module VALUES ('CS1001','Programming'), ('CS1002','Databases');  -- module 名為示範自訂

-- slide 30–42（Student AS a / Result AS b）
CREATE TABLE S (ID INT, Name VARCHAR(50));
INSERT INTO S VALUES (102,'Mary'), (103,'Alice'), (101,'Alice');
CREATE TABLE R (ID INT, MCode CHAR(6), Grade CHAR(1));
INSERT INTO R VALUES (101,'CS1001','B'), (103,'CS1002','B'), (104,'CS1001','A');

-- slide 45–50（每行係一張 order：Shop ID + Amount）
CREATE TABLE T (Shop INT, Amount INT);
INSERT INTO T VALUES (1,1),(1,2),(1,3),(2,2),(2,4),(3,1),(3,3),(3,4);

# run_sql.py — 用 Python 內置 sqlite3 執行
import sqlite3
con = sqlite3.connect(":memory:")          # 或 "school.db" 存落 file
con.executescript(open("setup.sql").read())

def q(sql):
    cur = con.execute(sql)
    print([d[0] for d in cur.description])  # column names
    for row in cur.fetchall():
        print(row)

q("SELECT * FROM Student")

1. Tools、SQLite 與 SQL 分類

Cloud platforms [Self-study]：Oracle（Always Free Tier，要 credit card）、GCP Cloud SQL、Azure SQL Database、AWS Amazon RDS（Free Tier）。

1.1 SQLite vs standard RDBMS

1.2 SQL 四類語言

2. Basic commands & data types

ExamCommands are case-insensitive!（select = SELECT）Username and password are case-sensitive!

2.1 Data types（commonly used）

2.2 Create & insert（slides 8–9）

CREATE TABLE Student (
    Student_ID char(4),
    Name varchar(100),
    Year int
);
INSERT INTO Student VALUES ('s123', 'Amy', 1);
INSERT INTO Student VALUES ('s234', 'Bob', 2);
INSERT INTO Student VALUES ('s345', 'Cathy', 1);

Notes：string data 用引號包住；VALUES 要 follow the order of attributes in the table。

2.3 NULL

ExamNULL 係 special value，兩個 interpretations：(1) The data value does not exist；(2) The data value is not known。Rule of thumb：database design 應避免有（太多）NULL。Empty string ("") ≠ NULL：""表示有值，而且知道個值係 ""。

-- 搵 NULL 要用 IS NULL，唔可以用 = NULL
SELECT * FROM R WHERE Grade IS NULL;

2.4 Creating index

CREATE INDEX idx  ON Student (Name);
CREATE INDEX idx2 ON Student (Name, Year);   -- composite index（同一 DB 內 index 名要唯一）

3. Basic query：SELECT, DISTINCT, WHERE

SELECT * FROM Student;               -- Option 1：* = all attributes
SELECT Name, Year FROM Student;      -- Option 2：逐個列出 attribute

Potential errors：Syntax error（睇 error message）；Table / Column doesn't exist（檢查串字）。

3.1 DISTINCT

SELECT DISTINCT Year FROM Student;   -- 1, 2      （去除重複）
SELECT Year FROM Student;            -- 1, 2, 1

3.2 WHERE clause

SELECT * FROM S
WHERE (ID > 100 AND ID < 102) OR Name = 'Mary';

Table S：(101, Alice), (102, Mary), (103, Alice)。Result（lecturer notes）：101 Alice（100 < 101 < 102）、102 Mary（Name = Mary）。103 Alice 兩個條件都唔符合。

Parameter format：text 要用引號（Name = 'Mary'）；integer 唔使（ID > 100）。

SELECT * FROM S WHERE ID <> 101;            -- 102 Mary, 103 Alice
SELECT * FROM S WHERE NOT Name = 'Alice';    -- 102 Mary

4. ORDER BY, LIMIT, OFFSET

SELECT * FROM Result ORDER BY Grade;                         -- 預設 ascending
SELECT * FROM Result ORDER BY Grade DESC;                    -- 加 DESC 變 descending
SELECT * FROM Result ORDER BY Grade DESC, StudentID DESC;    -- 先按 Grade，同分再按 StudentID；兩個都 DESC
SELECT * FROM Result LIMIT 2;                                -- 只要頭 2 個
SELECT * FROM Result LIMIT 2 OFFSET 2;                       -- 跳過頭 2 個，攞第 3–4 個

Slide 例子：LIMIT 10 OFFSET 10 = return the 11th to 20th records（常用嚟做分頁）。注意 DESC 只作用喺佢前面嗰一個 attribute——slide 20 兩個都要寫 DESC。

Order of commands：SELECT [DISTINCT] … FROM … WHERE … ORDER BY …——ORDER BY 要放喺 WHERE 之後。

5. JOIN

可以同時由多個 tables 拎資料；There is (usually) a join condition! Types of join：Inner Join；Left / Right (OUTER) JOIN；Full outer join；Cartesian Join；Self Join (example)；Theta join、Natural join 等等。

5.1 INNER JOIN

SELECT * FROM Stud INNER JOIN Result
ON Stud.StudentID = Result.StudentID;

（實際 SELECT * 會出兩個 StudentID 欄，一個來自每個 table。）多個 tables 可能有同名 attributes，用 [table name].[attribute name] 區分。

5.2 Alias

SELECT * FROM Stud AS s INNER JOIN Result AS r ON s.StudentID = r.StudentID;
SELECT * FROM Stud s INNER JOIN Result r ON s.StudentID = r.StudentID;   -- 唔寫 AS 都得
SELECT s.StudentID AS newname FROM Stud AS s;                             -- 改 attribute 名

5.3 LEFT / RIGHT (OUTER) JOIN

Student (S)：102 Mary, 103 Alice, 101 Alice。Result (R)：101 CS1001 B, 103 CS1002 B, 104 CS1001 A。

SELECT a.ID, a.Name, b.MCode, b.Grade
FROM S AS a LEFT JOIN R AS b ON a.ID = b.ID;

SELECT a.ID, a.Name, b.MCode, b.Grade
FROM S AS a RIGHT JOIN R AS b ON a.ID = b.ID;

-- FULL OUTER JOIN（SQLite ≥ 3.39 / PostgreSQL 支援；MySQL 要用 LEFT UNION RIGHT 模擬）
SELECT a.ID, a.Name, b.MCode, b.Grade FROM S AS a FULL OUTER JOIN R AS b ON a.ID = b.ID;

5.4 Cartesian join

SELECT * FROM S AS a, R AS b;          -- 3 × 3 = 9 rows：每個組合都出

Cartesian join（cross product）唔需要 join condition：$|S| \times |R|$ rows。ExamInner join = Cartesian join + WHERE：

SELECT * FROM S AS a, R AS b WHERE a.ID = b.ID;   -- 同 INNER JOIN 結果一樣

（Slides 32–33 的 Result 表第三行係 103 CS1001 A，所以 slide 的結果有 3 行：101/CS1001、103/CS1002、103/CS1001；用本頁 R 表（第三行係 104）就只得 2 行。）

Performance? 先做 Cartesian join 再 WHERE 理論上慢好多，但 existing DBMS 會自動 "re-write" query 去 optimize，所以速度一樣，兩種寫法都可以用。

5.5 Joining multiple tables

SELECT s.StudName, m.ModuleName, r.Grade
FROM Stud s INNER JOIN Result r ON s.StudentID = r.StudentID
            INNER JOIN Module m ON r.ModuleCode = m.ModuleCode;

Just add another join statement in the FROM clause。Each join action is done sequentially：(Student + Result) 先，結果再 + Module。

5.6 Self join example

Query：Who has a better grade on CS1001 than student ID 101?（R 表：101 CS1001 B、103 CS1002 B、104 CS1001 A）

難處：WHERE clause 係逐 row 計算，唔可以跨 rows 比較。Idea：用 同一個 table 兩份 copy（a, b），Cartesian join 將不同 rows 帶入同一個 joined row，就可以比較。（Self join 唔係新的 join 類型，只係示範點解有時同一 SQL 要用同一 table 多次。）

SELECT b.ID
FROM R a, R b
WHERE a.ID = 101 AND a.MCode = 'CS1001'     -- a：搵 101 喺 CS1001 的成績
  AND b.ID <> 101 AND b.MCode = 'CS1001'    -- b：其他學生喺 CS1001 的成績
  AND b.Grade < a.Grade;                    -- 'A' < 'B'（字母序），即 b 成績較好
-- 結果：104

Joined row 係 (a.ID=101, CS1001, B, b.ID=104, CS1001, A)，用 a.xxx / b.xxx 引用各自的 attributes。

Another solution：刪走 b.ID <> 101，因為 the condition b.Grade < a.Grade will also filter the student 101 in table b（B < B 為 false）。兩個 solution 結果一樣；第二個較簡潔，第一個較清楚表達意圖。

6. CASE

SELECT StudentID, ModuleCode, Grade,
    CASE
        WHEN Grade = 'A' THEN 'Excellent'
        WHEN Grade = 'B' THEN 'Good'
        ELSE 'Pass'
    END AS Status
FROM Result;

結果：(1, CS1002, B, Good)、(1, CS1001, A, Excellent)、(3, CS1001, A, Excellent)、(6, CS1001, B, Good)。CASE 由上至下檢查，第一個成立的 WHEN 就用佢的 THEN；全部唔成立就用 ELSE。

7. Aggregate query

For finding statistics：minimum, maximum, sum, average, count；配合 GROUP BY 同 HAVING。Key idea：Notice the intermediate results are also tables!

7.1 Aggregate functions

T 的 B 欄：1, 2, 3, 2, 4, 1, 3, 4（A 欄係 1,1,1,2,2,3,3,3）。

-- 用本頁 T(Shop, Amount)，數據同 slide 的 (A, B) 一樣
SELECT SUM(Amount), COUNT(*), AVG(Amount), MIN(Amount), MAX(Amount) FROM T;   -- 20, 8, 2.5, 1, 4

-- 自己試：
CREATE TABLE TN (A INT, B INT);
INSERT INTO TN VALUES (1,1),(1,2),(1,3),(2,NULL),(2,4),(3,NULL),(3,3),(3,4);
SELECT COUNT(B), COUNT(*), SUM(B), MIN(B), AVG(B) FROM TN;   -- 6, 8, 17, 1, 2.833…

7.2 GROUP BY

每個 record 係一張 order（Shop ID, Amount）。

SELECT Shop, SUM(Amount)
FROM T
GROUP BY Shop;
-- Shop 1: 1+2+3 = 6；Shop 2: 2+4 = 6；Shop 3: 1+3+4 = 8

GROUP BY 將 rows 按 Shop 分組（概念上每組係一個細 table），再對每組計 SUM。

7.3 GROUP BY with WHERE

SELECT Shop, SUM(Amount)
FROM T
WHERE Amount > 2
GROUP BY Shop;
-- 先過濾剩 (1,3),(2,4),(3,3),(3,4) → Shop 1: 3；Shop 2: 4；Shop 3: 7

7.4 HAVING

Post-filtering after you get the aggregate results：

SELECT Shop, SUM(Amount) AS Total
FROM T
WHERE Amount > 2
GROUP BY Shop
HAVING Total > 5;          -- 只剩 Shop 3, Total 7

7.5 GROUP BY 要用乜？

想計每個學生修咗幾多科：

-- Option A：正確
SELECT COUNT(*) FROM Result GROUP BY StudentID;
-- Option B：錯誤 —— 兩個同名學生會被放入同一組
SELECT COUNT(*) FROM Result GROUP BY StudentName;

ExamGROUP BY 要用能唯一識別 entity 的 attribute（key）。Slide 30 的 S 表就有兩個 "Alice"（101、103）——如果按 Name group，佢哋會被當成同一人。

8. Putting it all together：Clause order vs Execution order

SELECT A, B, SUM(C) AS Total
FROM T INNER JOIN W ON T.X = W.Y
WHERE B > 10
GROUP BY A, B
HAVING Total > 100
ORDER BY A DESC

注意寫法次序 唔係 執行次序！

9. Set operations

UNION、INTERSECT、EXCEPT——係 set operations（會去除重複），not multiset。要求兩邊 compatible data types and same number of columns。

SELECT ID FROM S UNION     SELECT ID FROM R;   -- 101, 102, 103, 104（去重）
SELECT ID FROM S UNION ALL SELECT ID FROM R;   -- 102, 103, 101, 101, 103, 104（multiset，保留重複）
SELECT ID FROM S INTERSECT SELECT ID FROM R;   -- 101, 103
SELECT ID FROM S EXCEPT    SELECT ID FROM R;   -- 102（喺 S 但唔喺 R）

10. Sub-query 與 WITH

Observation：SELECT 的結果係一個 table → 可以放喺 FROM clause：

SELECT * FROM (SELECT * FROM Stud) AS X;   -- sub-query 必須俾佢一個 table name，否則 error

10.1 例子：每個 major 平均修幾多科？

Expected：DS → 2，CS → 1。唔可以用一句 SQL 直接做——You cannot average a count in the same SQL。分兩步：

Step 1：每個學生修幾多科

SELECT S.StudentID, S.Major, COUNT(R.ModuleCode) AS NumModule
FROM Stud AS S LEFT JOIN Result AS R
ON S.StudentID = R.StudentID
GROUP BY S.StudentID, S.Major;
-- (1, DS, 2), (3, CS, 1), (6, CS, 1)

Step 2：將 Step 1 結果當 table T，每個 major 計平均：

SELECT Major, AVG(NumModule) AS Avg_Num FROM T GROUP BY Major;

Combining together（sub-query）：

SELECT Major, AVG(NumModule) AS Avg_Num
FROM (
    SELECT S.StudentID, S.Major, COUNT(R.ModuleCode) AS NumModule
    FROM Stud AS S LEFT JOIN Result AS R
    ON S.StudentID = R.StudentID
    GROUP BY S.StudentID, S.Major
) AS T
GROUP BY Major;
-- CS 1.0 ; DS 2.0

WITH statement（CTE，更易讀）：

WITH T AS (
    SELECT S.StudentID, S.Major, COUNT(R.ModuleCode) AS NumModule
    FROM Stud AS S
    LEFT JOIN Result AS R ON S.StudentID = R.StudentID
    GROUP BY S.StudentID, S.Major
)
SELECT Major, AVG(NumModule) AS Avg_Num
FROM T
GROUP BY Major;

import pandas as pd
stud = pd.DataFrame({"StudentID":[1,3,6], "StudName":["Amy","Bob","Cathy"], "Major":["DS","CS","CS"]})
res  = pd.DataFrame({"StudentID":[1,1,3,6], "ModuleCode":["CS1002","CS1001","CS1001","CS1001"],
                     "Grade":["B","A","A","B"]})
t = (stud.merge(res, on="StudentID", how="left")                   # LEFT JOIN
         .groupby(["StudentID","Major"])["ModuleCode"].count()      # COUNT 忽略 NaN
         .reset_index(name="NumModule"))
print(t.groupby("Major")["NumModule"].mean())                     # CS 1.0, DS 2.0

11. Updating your data

UPDATE Student SET Year = 2 WHERE Student_ID = 's345';   -- 格式：UPDATE t SET c1 = v1, c2 = v2, ... WHERE condition
DELETE FROM Student WHERE Year = 2;                      -- 刪走 Bob 同 Cathy

12. More features & query optimization [Self-study]

DBA 相關 features：Primary key、Foreign keys、Database design [Normal forms]、Constraints、Not Null、Stored procedures、Triggers、Views、Backup and recovery、Access control。

-- 示範：Primary key / Foreign key / NOT NULL 約束
CREATE TABLE Student2 (StudentID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL);
CREATE TABLE Result2 (
    StudentID INT REFERENCES Student2(StudentID),
    ModuleCode CHAR(6),
    Grade CHAR(1),
    PRIMARY KEY (StudentID, ModuleCode)
);

Query plan optimizer：

SELECT * FROM S AS a, R AS b WHERE a.ID = b.ID AND a.Name = 'Tom';

最有效率：先喺 Student 表 filter Name = 'Tom'，再計 join——因為 filtered student table 的 records 少好多，join 時要配對的組合大減（filter pushdown）。例：Student 10,000 行、Result 100,000 行，如果 Tom 只有 1 個，先 filter 就只需要用 1 行去 join。

13. In-class exercises & SQL beyond coding

LeetCode basic：Combine Two Tables、Employees Earning More Than Their Managers、Duplicate Emails、Game Play Analysis I、Classes With at Least 5 Students（2 marks，screenshot 提交 Moodle）。More challenging：Delete Duplicate Emails（hint: DELETE query）、Percentage of Users Attended a Contest（hint: ROUND）、Average Time of Process per Machine、The Number of Employees Which Report to Each Employee。

Slide 70：SQL beyond coding——例如喺 Power BI load data from RDBMS、Databricks 的 SQL Editor（截圖，內容擷取不到）。

以下係幾題 basic 題的典型寫法（用題目的 table 名）：

-- Combine Two Tables：每個人都要出，冇地址都要 → LEFT JOIN
SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p LEFT JOIN Address a ON p.personId = a.personId;

-- Employees Earning More Than Their Managers：self join
SELECT e.name AS Employee
FROM Employee e JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;

-- Duplicate Emails：GROUP BY + HAVING
SELECT email AS Email FROM Person GROUP BY email HAVING COUNT(*) > 1;

-- Game Play Analysis I：每個 player 第一次登入日期
SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id;

-- Classes With at Least 5 Students
SELECT class FROM Courses GROUP BY class HAVING COUNT(student) >= 5;

自我測驗 Self-test

一頁總結 Cheat sheet