How to answer SQL questions

What it looks like in the real exam

From NESA material 2025 HSC 2025 HSC exam, Q21 (4 marks): count jobs by status and category, filter and sort. 2025 sample: 2025 NESA sample exam, Q17 (3 marks: join, filter, order), 2025 NESA sample exam, Q22A (2 marks: complete the query). Familiarisation: 2025 NESA familiarisation exam, Q12A (2 marks) and 2025 NESA familiarisation exam, Q12B (3 marks, three tables, AS renames). The online exam has a SQL query tool.

The clause order (memorise)

The tables used in the examples below (original, I created and ran them in a real database):

CREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name TEXT, Year INTEGER, House TEXT);
INSERT INTO Students VALUES (1,'Ava',12,'Red'),(2,'Ben',11,'Blue'),(3,'Cleo',12,'Blue'),(4,'Dan',12,'Red'),(5,'Eli',11,'Red');
CREATE TABLE Scores (StudentID INTEGER, Subject TEXT, Mark INTEGER);
INSERT INTO Scores VALUES (1,'SE',80),(1,'Maths',70),(2,'SE',60),(3,'SE',90),(4,'SE',50),(5,'Maths',75);

Method

1. Read the question and look at the table(s) (names of the tables and fields). Copy the exact names. From course notes
2. Underline the parts and name the clause for each: “show” → SELECT; “from” → FROM; “only those where” → WHERE; “how many / total / average for each” → aggregate + GROUP BY; “highest first / A to Z” → ORDER BY. General advice
3. Write FROM first (the table(s) you need, with a join if the data is in two tables), then SELECT, WHERE, GROUP BY, ORDER BY. General advice
4. Quote text, not numbers: Status = 'Pending', Year = 12. Join conditions with AND / OR; use brackets when mixing them. From NESA material NESA 2025: quotation marks around searched data; correct AND/OR.
5. If you use COUNT, SUM, AVG, MAX or MIN with another field in the SELECT, that other field must be in GROUP BY. From course notes
6. Check: would it run? semicolon at the end; commas between fields; every table named; the order of rows as asked; any rename done with AS. From NESA material NESA: the query must run.

Worked example 1: count, filter, group and sort (2025 HSC Q21)

SELECT Category, Status, COUNT(JobID) AS Jobs
FROM Issues
WHERE (Status = 'Pending' OR Status = 'Escalated')
GROUP BY Status, Category
ORDER BY Jobs DESC

1. “count the jobs” → COUNT(JobID) AS Jobs.
2. “for each status in each category” → GROUP BY Status, Category.
3. “only Pending and Escalated, Software and Hardware” → a WHERE with quoted text and OR/AND brackets.
4. “descending order” → ORDER BY Jobs DESC.

From NESA material The bank notes that the official sample doesn’t filter the category to Software or Hardware (the table is assumed to hold only those two). Adding the extra condition is safe: AND (Category = 'Software' OR Category = 'Hardware').

Original practice I ran this pattern on an invented table and it returned counts sorted high to low.

Worked example 2: a join (2025 sample Q17)

SELECT Products.Name AS 'Product', Suppliers.Name AS 'Supplier'
FROM Products, Suppliers
WHERE Products.SupplierID = Suppliers.SupplierID
    AND Products.Producer = 'Yumtreats'
    AND Products.Mass >= 50
ORDER BY Products.Name DESC

From NESA material The bank notes: an INNER JOIN … ON version is equally valid; quote text values; both tables have a column called Name, so write Table.Name.

Original practice Both forms below return the same rows (checked on a test database):

SELECT Students.Name, Scores.Subject, Scores.Mark
FROM Students, Scores
WHERE Students.StudentID = Scores.StudentID AND Scores.Mark > 75
ORDER BY Scores.Mark DESC;

SELECT Students.Name, Scores.Subject, Scores.Mark
FROM Students INNER JOIN Scores ON Students.StudentID = Scores.StudentID
WHERE Scores.Mark > 75
ORDER BY Scores.Mark DESC;

Result: Cleo SE 90; Ava SE 80.

Worked example 3: three tables and renaming (2025 familiarisation Q12b)

SELECT Customers.CustName as 'Customer', Products.ProductName as 'Product',
Orders.OrderQuantity as 'Quantity'
FROM Products, Customers, Orders
WHERE Orders.ProductID = Products.ProductID
AND Orders.CustID = Customers.CustID
ORDER BY Orders.OrderID ASC

Reasoned Method: join two links (A to B and B to C), so you need two join conditions joined by AND. AS 'Customer' renames a column for display. The query is not published in full text, so I checked the pattern on my own three-table database: result Ana/Book/2, Raj/Pen/5, Ana/Pen/1.

Aggregates, GROUP BY, ALIAS and CRUD, with outputs

Original practice Run on the table above:

SELECT Name, House FROM Students WHERE Year = 12 ORDER BY House ASC, Name DESC;

Cleo Blue; Dan Red; Ava Red.

SELECT Subject, COUNT(StudentID) AS Entries, AVG(Mark) AS Average
FROM Scores GROUP BY Subject ORDER BY Entries DESC;

SE 4 entries, average 70.0; Maths 2 entries, average 72.5.

SELECT MAX(Mark), MIN(Mark) FROM Scores;

90 and 50.

SELECT Students.House, SUM(Scores.Mark) AS Total
FROM Students, Scores WHERE Students.StudentID = Scores.StudentID
GROUP BY Students.House ORDER BY Total DESC;

Red 275; Blue 150.

From course notes CRUD: INSERT INTO Students VALUES (6,'Fay',11,'Blue'); · SELECT … · UPDATE Scores SET Mark = 85 WHERE StudentID = 1 AND Subject = 'SE'; · DELETE FROM Scores WHERE StudentID = 5;. Never run UPDATE or DELETE without WHERE unless you mean every row.

Common mistakes

• From NESA material NESA 2025 Q21: the query must run; no quotation marks around the data being searched; AND/OR misused in the WHERE; wrong or missing GROUP BY/ORDER BY fields.
• General advice WHERE Status = 'Pending' AND Status = 'Escalated' never matches (one row has one status). Use OR.
• General advice Selecting a plain field with COUNT without GROUP BY.
• General advice Forgetting the join condition: you get every row paired with every row.
• General advice Ambiguous names: when two tables have a Name column, write Table.Name.
• General advice Putting ORDER BY before WHERE.

Time per mark

Derived 4 marks ≈ 7 minutes; 3 marks ≈ 5 minutes; 2 marks ≈ 3.5 minutes. General advice Spend a minute naming each clause before you type.

Practice (5 items, with answers)

SELECT Name FROM Students WHERE Year = 12 AND House = 'Red';

SELECT Name FROM Students WHERE Year = 12 ORDER BY Name DESC;

SELECT House, COUNT(*) FROM Students GROUP BY House;

SELECT Students.Name, Scores.Mark
FROM Students, Scores
WHERE Students.StudentID = Scores.StudentID
  AND Scores.Subject = 'SE' AND Scores.Mark >= 80
ORDER BY Students.Name;

SELECT AVG(Mark) FROM Scores WHERE Subject = 'SE';

More: multiple-choice bank (SQL items), flashcards.

Terms, flashcards and past questions

Key terms for this page: SQL questions · SQL SELECT · SQL WHERE · SQL ORDER BY · SQL GROUP BY · SQL aggregate functions (COUNT, SUM, AVG) · SQL JOIN · SQL alias (AS) · SQL statement structure · SQL relational and logical operators · SQL calculated aggregate with GROUP BY · CRUD · Primary key and foreign key · SQL injection

Flashcards · All key terms · Learn step by step · All past questions · Multiple-choice bank · Mock exam

Past questions used on this page:

• 2025 HSC exam, Q21
• 2025 NESA sample exam, Q17
• 2025 NESA sample exam, Q22A
• 2025 NESA familiarisation exam, Q12A
• 2025 NESA familiarisation exam, Q12B
• Open this question page
• Open this question page
• Open this question page