Multi-column GROUP BY · JOINs revisited · Every testing type in one place
Multi-column GROUP BY · JOINs · Reading complex queries
You already know that GROUP BY category gives you one row per category. But what if you need counts for every combination of two columns — like "pending software", "pending hardware", "escalated software", and "escalated hardware" all in one result?
You just group by both columns. SQL creates a bucket for each unique pair.
This is the table from your pre-work. It's a job tracker for a tech support company.
| JobID | Issue | FixTime | Category | Status |
|---|---|---|---|---|
| 0001 | Cannot connect to internet | 22 | Software | Pending |
| 0002 | Installation failure | 76 | Hardware | Pending |
| 0004 | Forgot password | 97 | Software | Resolved |
| 0005 | Installation failure | 110 | Hardware | Escalated |
| 0009 | Blue screen error | 120 | Software | Pending |
| 0011 | Installation failure | 45 | Software | Escalated |
Compare these two queries side by side:
-- One column: one row per Category
SELECT Category, COUNT(*) AS job_count
FROM Issues
GROUP BY Category;Result: 2 rows — Software (4 jobs), Hardware (2 jobs). You lose all information about status.
-- Two columns: one row per Category+Status combination
SELECT Category, Status, COUNT(*) AS job_count
FROM Issues
GROUP BY Category, Status
ORDER BY job_count DESC;Result: one row for every unique pair that exists in the data:
| Category | Status | job_count |
|---|---|---|
| Software | Pending | 2 |
| Software | Escalated | 1 |
| Software | Resolved | 1 |
| Hardware | Pending | 1 |
| Hardware | Escalated | 1 |
The question only wanted Pending and Escalated rows, so you add a WHERE to remove Resolved before grouping:
SELECT Category, Status, COUNT(*) AS job_count
FROM Issues
WHERE Status IN ('Pending', 'Escalated')
GROUP BY Category, Status
ORDER BY job_count DESC;GROUP BY col1, col2 — think of it as drawing a grid. Each row of the grid is a unique combination. SQL counts (or sums, or averages) the rows that fall into each cell of that grid.
Look at the query you wrote for Q21. Does it use GROUP BY Category, Status? Does it have a WHERE to exclude Resolved? Does it end with ORDER BY job_count DESC?
SELECT Category, Status, COUNT(*) AS job_count
FROM Issues
WHERE Status IN ('Pending', 'Escalated')
GROUP BY Category, Status
ORDER BY job_count DESC;This gives exactly four rows — pending software, pending hardware, escalated software, escalated hardware — in descending count order.
A JOIN is how you answer questions that span two tables. The idea: find rows in both tables that share the same key value, and stitch them together into one wider row.
We'll use a new schema — a school's assignment tracker.
| students | ||
|---|---|---|
| student_id (PK) | name | year |
| 1 | Blake | 12 |
| 2 | Jordan | 11 |
| 3 | Alex | 12 |
| submissions | |||
|---|---|---|---|
| sub_id (PK) | student_id (FK) | subject | score |
| 101 | 1 | Software Engineering | 87 |
| 102 | 1 | Maths | 72 |
| 103 | 2 | Software Engineering | 91 |
| 104 | 3 | Maths | 65 |
Without a JOIN, you can only ask questions about one table. To ask "what score did Blake get in Software Engineering?" you need both tables.
-- Show student name alongside their subject and score
SELECT students.name, submissions.subject, submissions.score
FROM students
INNER JOIN submissions ON students.student_id = submissions.student_id;SQL walks every row of students, finds matching rows in submissions where student_id is equal, and glues them together:
| name | subject | score |
|---|---|---|
| Blake | Software Engineering | 87 |
| Blake | Maths | 72 |
| Jordan | Software Engineering | 91 |
| Alex | Maths | 65 |
Notice Jordan has no row and Alex has one — that's fine, INNER JOIN only returns rows that have a match on both sides.
Syntax pattern: FROM table_a INNER JOIN table_b ON table_a.shared_key = table_b.shared_key. The ON clause is the glue — it tells SQL which columns must match.
This is the pattern exams love — it combines everything.
SELECT students.name, AVG(submissions.score) AS avg_score
FROM students
INNER JOIN submissions ON students.student_id = submissions.student_id
GROUP BY students.name;Step by step: JOIN gives you name + score on each row → GROUP BY name buckets them per student → AVG averages each bucket.
SELECT students.name, AVG(submissions.score) AS avg_score
FROM students
INNER JOIN submissions ON students.student_id = submissions.student_id
GROUP BY students.name
HAVING AVG(submissions.score) >= 80;The condition uses an aggregate so it goes in HAVING, not WHERE.
SELECT submissions.subject, MAX(submissions.score) AS top_score
FROM submissions
GROUP BY submissions.subject
ORDER BY top_score DESC;No JOIN needed here — everything you need is in submissions. Only reach for a JOIN when you genuinely need columns from two different tables.
HSC exams often give you a query and ask you to explain what it returns in plain English, or to spot a mistake. Practice reading a query clause by clause from top to bottom.
SELECT students.name, COUNT(submissions.sub_id) AS total_submissions
FROM students
INNER JOIN submissions ON students.student_id = submissions.student_id
WHERE submissions.score >= 70
GROUP BY students.name
HAVING COUNT(submissions.sub_id) > 1
ORDER BY total_submissions DESC;"From the joined student/submission data, looking only at submissions where the score is at least 70 (WHERE), count how many such submissions each student has (GROUP BY + COUNT). Keep only students with more than one qualifying submission (HAVING). List them highest count first (ORDER BY)."
With our sample data: Blake has scores 87 and 72 — both ≥ 70, count = 2. Jordan has 91 — one submission ≥ 70. Alex has 65 — filtered out by WHERE. HAVING then drops Jordan (only 1). Result: just Blake (2).
It would be a syntax error — you can't use an aggregate function like COUNT inside a WHERE clause. WHERE runs before groups are formed, so COUNT doesn't exist yet at that point. HAVING is the right tool whenever your condition involves an aggregate.
Every testing concept in the HSC syllabus — unified in one place
Download the six exercise files below into a new folder called testing_exercises. Click each link — your browser will save the .py file.
⬇ 01_testing_levels.py
⬇ 02_test_data_strategies.py
⬇ 03_box_testing.py
⬇ 04_debugging_toolkit.py
⬇ 05_interface_debugging.py
⬇ 06_sast_static_analysis.py
Put all six files in one folder, then open that folder in VSCode so the debugger is ready for exercises 4 and 5:
code testing_exercises
To run an exercise, open its file and press the ▶ Run button, or use the terminal:
python3 01_testing_levels.py
You're set. Each section below will tell you which file to open when it's time to code.
Test a single function or method in complete isolation. Nothing else runs. You control every input and check every output.
Test a group of related units working together — e.g. the entire login flow (form → validation → database check → session). You're checking that the parts integrate correctly.
Test the entire application end-to-end, as a real user would. Does every feature work? Does the app behave correctly as a whole?
| Level | What you'd test |
|---|---|
| Unit | Does Pet.feed() increase hunger by exactly 10? Test it alone with a known starting state. |
| Subsystem | Do feed(), play(), and sleep() interact correctly? Does feeding after sleep still work? |
| System | Run the whole game from start to end. Can a user create a pet, keep it alive through a full session, and save their progress? |
Order matters: you always run unit tests first. Finding a bug at the unit level is cheap — you know exactly which function is wrong. Finding it at the system level means digging through the entire app.
calculate_discount() function with a dozen different price inputs.Unit test — single function, tested in isolation.
System test — the whole application, end-to-end, as a real user.
Subsystem test — two components (coupon engine + display) working together.
Open testing_exercises/01_testing_levels.py. You'll write three test functions — one at each scope level — against the same Pet class.
Choosing what data to test with is its own skill. Exams will give you a function and ask you to pick appropriate test data — or spot which strategy a given test case represents.
Test at the exact edges of valid input — and one step either side. Systems most often fail right at boundaries.
Design tests so that every possible branch in the code is executed at least once. An untested branch is a hidden bug waiting to happen.
Feed the system inputs it wasn't designed for — wrong types, empty strings, negative numbers, absurdly large values. Does it crash or handle it gracefully?
Say a function accepts an age between 0 and 120 inclusive. The boundary test cases are:
| Input | Why | Expected |
|---|---|---|
| -1 | Just below lower boundary | Reject / error |
| 0 | Exactly at lower boundary | Accept |
| 1 | Just inside lower boundary | Accept |
| 60 | Normal mid-range value | Accept |
| 119 | Just inside upper boundary | Accept |
| 120 | Exactly at upper boundary | Accept |
| 121 | Just above upper boundary | Reject / error |
Look at this function:
def classify_score(score):
if score >= 90:
return "Outstanding"
elif score >= 75:
return "Merit"
elif score >= 50:
return "Pass"
else:
return "Fail"There are 4 branches (paths). Full path coverage requires at least one test case that hits each branch:
| Test input | Branch hit |
|---|---|
| 95 | "Outstanding" branch |
| 80 | "Merit" branch |
| 60 | "Pass" branch |
| 40 | "Fail" branch |
Testing only score = 95 gives you 25% path coverage. If the "Fail" branch had a typo, you'd never catch it.
Boundary value testing. "ab" = below minimum, "abcdef" = exactly at minimum, "abcdefghijklmnopqrst" = exactly at maximum (20 chars), "abcdefghijklmnopqrstu" = one above maximum. The mid-range value is also included for coverage.
Faulty / abnormal data. None of these are valid inputs. The goal is to confirm the system handles bad data without crashing.
Path coverage testing. The explicit goal is to ensure every branch is exercised.
Open testing_exercises/02_test_data_strategies.py. Three small functions, three strategies — fill in the test cases for each.
The "box" metaphor refers to how much of the code's internals you can see while you're testing.
You can only see the outside. You know what inputs to give and what output is expected, but you have no knowledge of the code inside. Tests are driven entirely by the specification.
Full visibility of the source code. You design tests specifically to exercise every line, branch, and path. Path coverage testing is a white box strategy.
Partial knowledge. You know some internals (e.g. the database schema or API structure) but not all. Common in integration and security testing.
| Approach | Typical user | Typical scenario |
|---|---|---|
| Black box | QA tester, client | "Does the login page accept valid credentials and reject invalid ones?" |
| White box | Developer (themselves) | "I'll trace through my function and write a test for each branch." |
| Grey box | Pen tester, integration tester | "I know the API uses JWTs — let me test what happens with a forged token." |
Black/white/grey box describes how much you know, not what level you're testing at. You can do a black box unit test. You can do a white box system test. The two dimensions are independent.
Open testing_exercises/03_box_testing.py. First test a "mystery function" from its spec only — then re-test it knowing the implementation. Feel the difference.
You've been using print() to debug since day one. That's one tool in a full toolkit. Each tool is suited to a different kind of problem.
print() to trace values. Fast to add, zero setup. Clutters code if forgotten.The workflow is: set a breakpoint → run the program → it pauses → use single-line stepping to walk forward one line at a time → watch the variables panel to see every change.
This is far more powerful than print statements for complex bugs, because you see the entire program state, not just the variables you thought to print.
This is a specific skill the syllabus calls out. When a function calls another function, the data it passes (the interface) can be wrong even if both functions individually work correctly. Classic symptoms:
None (forgot a return statement) and the caller crashesTo debug an interface, set a breakpoint just after the function call and inspect what was returned — before the caller does anything with it.
Breakpoint + single-line stepping (or a watch). Set a breakpoint inside the loop body and step through iterations, watching the variable change each time.
get_discount() is returning the wrong type — but apply_discount() that calls it is where the crash happens.Interface debugging. Set a breakpoint in apply_discount() at the line that calls get_discount() and inspect what it returns before applying it. The bug is in the return value, not in apply_discount() itself.
Print statement — it's the simplest and fastest tool when you know exactly what you want to check and where.
Open testing_exercises/04_debugging_toolkit.py. There's a real bug in calculate_average_grade(). Find it three different ways — printing, breakpoints + stepping, and a watch expression. You'll need VSCode for the last two.
Open testing_exercises/05_interface_debugging.py. Both functions look correct individually. The bug only shows when one calls the other. Use a breakpoint to inspect the value at the boundary.
Security testing asks a different question: not "does the software work correctly?" but "can it be broken by someone who's trying?" These tools live in the Secure Software module but they're still testing — they belong on the same map.
Scans source code without running it. Finds issues like hardcoded secrets, SQL injection patterns, or unsafe function calls. Runs in CI pipelines — catches bugs before they ship.
Runs the application and probes it from the outside — sending malformed requests, fuzzing inputs, checking responses. Finds vulnerabilities that only appear at runtime.
A structured survey of known weaknesses — checks patch levels, configurations, open ports, dependency versions. Produces a risk-ranked list. Doesn't actively exploit anything.
A tester (with permission) actively tries to break in — exploiting vulnerabilities, chaining attacks, escalating privileges. Proves what an attacker could actually achieve.
Tests how the system behaves under attack or unexpected load — DoS attempts, database floods, service disruptions. Does it degrade gracefully or collapse entirely?
| Tool | Needs running app? | Actively attacks? |
|---|---|---|
| SAST | No — reads source code | No |
| DAST | Yes — probes live app | No (automated) |
| Vulnerability assessment | Yes (usually) | No — only identifies |
| Penetration testing | Yes | Yes — exploits findings |
| Resilience testing | Yes | Yes — stresses the system |
Open testing_exercises/06_sast_static_analysis.py. Five code snippets, each with a vulnerability. Identify it, name the category, and write the fix — without ever executing the file. This is exactly what a SAST tool does for you automatically.
HSC exams love asking you to identify the type of testing from a scenario. Read each one and name the correct term — some scenarios might have more than one correct answer.
user_id variable update after each database call.Breakpoints + single-line stepping + watches. She's using the full IDE debugging toolkit on a single function — also white box (she's inside the code).
System testing + black box. End-to-end (full system), with no knowledge of internals (black box).
SAST (Static Application Security Testing). Analysing source code without running the application.
Boundary value testing. Inputs at, just below, and just above both the lower (0) and upper (100) boundaries.
Penetration testing. Active, authorised attack simulation — exploiting real vulnerabilities to prove what an attacker could achieve.
calculate_tax(), apply_discount(), and get_total() produce the correct final price when called in sequence — but does not test the entire checkout process.Subsystem testing. Three related functions working together — larger than unit, smaller than system.
Path coverage testing + white box. Explicit goal is full branch coverage (path coverage); requires knowledge of the code structure (white box).
Resilience testing. Testing system behaviour under extreme/attack-like stress to see if it degrades gracefully or fails completely.
| Question | Terms |
|---|---|
| What scope? | Unit → Subsystem → System |
| What data? | Boundary · Path coverage · Faulty/abnormal |
| What knowledge? | Black box · White box · Grey box |
| What debugging tool? | Print · Breakpoint · Stepping · Watch · IDE · Interface check |
| Security-specific? | SAST · DAST · Vulnerability assessment · Pen testing · Resilience |
When a question describes a testing scenario, run through the three questions: what scope? what data strategy? what knowledge level? Then check if it's security-specific. One scenario can correctly answer multiple questions — that's fine.