SQL Survival Guide for HKDSE ICT Paper 2A

SQL takes a major share of Paper 2A Databases. Unlike descriptive questions where you might scatter marks, SQL is binary: your query either works perfectly (full marks) or fails (zero). The good news? SQL is pattern-based. If you memorize these patterns and avoid the common traps, you can reliably secure your SQL marks.

This guide covers every SQL pattern tested in HKDSE ICT Paper 2A — clause order, filtering, joins, data definition, and exam question archetypes. Master these, practice the drill plan, and SQL becomes your scoring subject.

The golden rule: clause order matters

SQL clauses must appear in a strict order. Getting this wrong costs you the entire question’s marks, even if your logic is sound.

Memorize this order:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY

Worked example — Students and marks schema:

-- Find students with average mark >= 80, ordered by average descending
SELECT StudentName, AVG(Mark) as AvgMark
FROM Students
INNER JOIN Marks ON Students.StudentID = Marks.StudentID
WHERE Mark IS NOT NULL
GROUP BY StudentID, StudentName
HAVING AVG(Mark) >= 80
ORDER BY AvgMark DESC;

Why this order:

1. SELECT — What you want to output
2. FROM — Which tables you’re querying
3. WHERE — Filter rows before grouping (individual records)
4. GROUP BY — Group rows for aggregate functions (COUNT, AVG, SUM, MAX, MIN)
5. HAVING — Filter groups after grouping (group-level conditions)
6. ORDER BY — Sort final results

Exam trap: Don’t put WHERE after GROUP BY. Don’t put HAVING before GROUP BY. Don’t use aggregate functions (like AVG(Mark)) in WHERE — that belongs in HAVING. One clause out of place = zero marks.

WHERE vs HAVING: the filtering divide

This is the most common SQL mistake in Paper 2A. The difference is simple but critical:

WHERE filters individual rows before grouping. HAVING filters groups after grouping.

Contrast these two queries:

-- WHERE: Filter rows before grouping
SELECT StudentID, COUNT(*) as PaperCount
FROM Marks
WHERE Mark >= 50  -- Only papers with >= 50 are counted
GROUP BY StudentID;

-- HAVING: Filter groups after grouping
SELECT StudentID, AVG(Mark) as AvgMark
FROM Marks
GROUP BY StudentID
HAVING AVG(Mark) >= 50;  -- Only students with average >= 50 shown

First query: Counts papers >= 50 per student. A student with marks [40, 60, 80] counts 2 papers (60 and 80).

Second query: Calculates full average, then filters. Same student with marks [40, 60, 80] has average 60, so they’re included.

Exam pattern: If the condition involves individual records (like Mark >= 50), use WHERE. If the condition involves aggregates (like AVG(Mark) >= 50 or COUNT(*) > 3), use HAVING.

JOINs via keys: INNER vs LEFT

JOINs combine data from multiple tables using primary/foreign key relationships. Paper 2A tests two join types:

Key reminder:

• Primary Key (PK): Unique identifier for each record (e.g., StudentID in Students table)
• Foreign Key (FK): References PK in another table (e.g., StudentID in Marks table)

INNER JOIN — Only matching records from both tables:

SELECT Students.StudentName, Marks.Mark
FROM Students
INNER JOIN Marks ON Students.StudentID = Marks.StudentID;

Result: Only students who have marks. If a student has no mark record, they don’t appear.

LEFT JOIN — All records from left table + matching from right table (NULL if no match):

SELECT Students.StudentName, Marks.Mark
FROM Students
LEFT JOIN Marks ON Students.StudentID = Marks.StudentID;

Result: All students, even those without marks. Students with no marks show Mark as NULL.

When to use which:

• Need only records with matches (e.g., students who took exams)? → INNER JOIN
• Need all records from one table regardless of matches (e.g., all students, including those who haven’t taken exams)? → LEFT JOIN

Exam tip: In Paper 2A, most queries use INNER JOIN. LEFT JOIN appears when the question explicitly asks for “all students including those without…” or “list all X even if no Y exists.”

DDL vs DML: defining vs manipulating data

SQL commands fall into two categories:

DDL (Data Definition Language) — Defines database structure:

• CREATE TABLE — Create a new table
• ALTER TABLE — Modify table structure (add/drop columns)
• DROP TABLE — Delete entire table (structure + data)

DML (Data Manipulation Language) — Manipulates data within tables:

• INSERT INTO — Add new records
• UPDATE — Modify existing records
• DELETE FROM — Remove records

DROP vs DELETE vs TRUNCATE (frequently compared):

Command	What It Removes	Speed	Can Rollback
DROP TABLE Marks	Table structure + all data	Fast	No (DDL)
DELETE FROM Marks	All rows (table remains)	Slow (row-by-row)	Yes (DML)
DELETE FROM Marks WHERE Mark < 50	Specific rows	Slow	Yes
TRUNCATE TABLE Marks	All rows (table remains)	Fast	No (DDL)

Exam pattern: You might be asked to “write a SQL command to remove all marks below 50” → DELETE FROM Marks WHERE Mark < 50. Or “remove the Marks table entirely” → DROP TABLE Marks.

Keys recap: the foundation

Before writing joins, you must understand keys:

Primary Key (PK):

• Uniquely identifies each record
• Cannot be NULL, cannot repeat
• Never changes (stable identifier)

Foreign Key (FK):

• References PK in another table
• Enforces referential integrity
• Can be NULL (optional relationship)

Candidate Key:

• Could serve as primary key
• Minimal superkey

Composite Key:

• Two or more fields together form unique identifier
• Example: (StudentID, CourseID) as primary key in an enrollment table

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,  -- PK
    StudentName VARCHAR(50),
    ClassID INT,  -- FK to Classes table
    FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

Exam question patterns

Paper 2A SQL questions follow three archetypes. Recognize the pattern, apply the template:

1. Write-a-query-from-English-spec (most common)

Example paraphrase: “Write a SQL query to find the names of students who scored above 80 in at least one paper, ordered by name.”

Pattern recognition:

• “Find names” → SELECT StudentName
• “Students who scored above 80” → WHERE Mark > 80
• “At least one paper” → Just WHERE, no grouping needed
• “Ordered by name” → ORDER BY StudentName

Solution:

SELECT DISTINCT s.StudentName
FROM Students s
INNER JOIN Marks m ON s.StudentID = m.StudentID
WHERE m.Mark > 80
ORDER BY s.StudentName;

2. Spot-the-error-in-query

Example paraphrase: “The following SQL query is supposed to find students with average mark above 70, but it contains an error. Identify and correct it.”

-- Broken query
SELECT StudentName, AVG(Mark) as AvgMark
FROM Students
INNER JOIN Marks ON Students.StudentID = Marks.StudentID
WHERE AVG(Mark) > 70  -- Error: aggregate in WHERE
GROUP BY StudentID;

Error: Cannot use aggregate function AVG(Mark) in WHERE clause. Correction: Move aggregate condition to HAVING clause.

3. Output-of-given-query

Example paraphrase: “Given the Students and Marks tables below, what is the output of the following query?”

For this pattern, you trace the query step-by-step: apply filters, execute joins, compute aggregates, sort results. Practice tracing on small schemas (3-4 records per table).

20-query drill plan

Build muscle memory with this progression. Start from single-table SELECTs, advance to grouped joins.

Week 1: Foundation (Queries 1-8)

1. SELECT * FROM Students
2. SELECT StudentName FROM Students WHERE Class = '6A'
3. SELECT StudentName FROM Students WHERE StudentName LIKE 'Chan%'
4. SELECT * FROM Marks WHERE Mark BETWEEN 60 AND 80
5. SELECT * FROM Marks WHERE Mark IS NULL
6. SELECT COUNT(*) FROM Marks
7. SELECT AVG(Mark) FROM Marks WHERE Subject = 'ICT'
8. SELECT StudentName, Mark FROM Students INNER JOIN Marks ON Students.StudentID = Marks.StudentID

Week 2: Grouping & Aggregates (Queries 9-14) 9. SELECT StudentID, AVG(Mark) FROM Marks GROUP BY StudentID 10. SELECT StudentID, COUNT(*) FROM Marks GROUP BY StudentID HAVING COUNT(*) > 2 11. SELECT Subject, AVG(Mark) FROM Marks GROUP BY Subject ORDER BY AVG(Mark) DESC 12. SELECT Class, COUNT(*) FROM Students GROUP BY Class 13. SELECT StudentID, SUM(Mark) FROM Marks WHERE Mark >= 50 GROUP BY StudentID 14. SELECT Subject, MAX(Mark), MIN(Mark) FROM Marks GROUP BY Subject

Week 3: Joins & Filtering (Queries 15-20) 15. SELECT s.StudentName, m.Mark FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID WHERE m.Mark > 70 16. SELECT s.StudentName, AVG(m.Mark) FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID GROUP BY s.StudentID HAVING AVG(m.Mark) >= 60 17. SELECT s.StudentName, m.Mark FROM Students s LEFT JOIN Marks m ON s.StudentID = m.StudentID 18. SELECT c.ClassName, COUNT(s.StudentID) FROM Classes c LEFT JOIN Students s ON c.ClassID = s.ClassID GROUP BY c.ClassName 19. SELECT s.StudentName, m.Subject, m.Mark FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID WHERE m.Subject = 'ICT' AND m.Mark >= 50 ORDER BY m.Mark DESC 20. SELECT s.ClassName, AVG(m.Mark) FROM Students s INNER JOIN Marks m ON s.StudentID = m.StudentID GROUP BY s.ClassName HAVING AVG(m.Mark) > 70 ORDER BY AVG(m.Mark) DESC

Practice routine: Write each query from memory without looking at notes. Check against sample solutions. Re-write queries that fail until you can write them correctly three times in a row.

Final exam tactics

When you open Paper 2A:

1. Read the schema first — Identify PKs, FKs, and table relationships before writing any query.
2. Parse the English — Underline what you’re selecting, filtering, grouping, and ordering.
3. Write clause order skeleton — SELECT ___ FROM ___ WHERE ___ GROUP BY ___ HAVING ___ ORDER BY ___ (fill in blanks as you go).
4. Double-check aggregate placement — Any COUNT, AVG, SUM, MAX, MIN in WHERE? Move to HAVING.
5. Verify JOIN logic — Do you need all records (LEFT) or only matches (INNER)?
6. Trace mentally — For “output-of-query” questions, manually trace 2-3 records to confirm your answer.

SQL rewards precision. Memorize the patterns, avoid the traps, and practice until clause order feels automatic. You can secure full SQL marks in Paper 2A — this guide gives you the roadmap.

---

Want feedback on your SQL queries? Book a lesson — we’ll review your practice queries, fix your mistakes, and build your confidence before the exam. For a complete subject roadmap including all electives, see our HKDSE ICT complete guide.

← All articles