Use these exercises as a progression rather than a race. The beginner task filters individual test results, the intermediate task includes departments with no results, and the advanced task chooses the best known score within each department. Each step adds one reasoning requirement while retaining the same small dataset.
The fixture contains a missing score and an empty department. Those cases make the answers more informative than a table in which every record is complete. Write a query, predict its rows, and compare it with the worked answer. If the result differs, identify which rule was misunderstood before trying another unrelated syntax pattern.
Beginner: select and filter individual rows
The first exercise returns named learners with known scores of at least eighty. SELECT identifies the requested columns, WHERE applies the threshold, and ORDER BY makes the output predictable. A NULL score cannot establish score>=80 as true, so it does not appear. This is a row-level task; it needs neither grouping nor a window calculation.
The worked query is SELECT name,score FROM exercise_results WHERE score>=80 ORDER BY score DESC,id. It returns Ben with ninety and Cara with eighty. Add the unique id after score to resolve a future tie. Do not replace a missing score with zero unless the exercise explicitly adopts that policy; absence is not the same as a failed test.
Beginner extension: inspect missing measurements
A separate data-quality task asks which learner lacks a score. Use IS NULL, not score=NULL. The answer is Dev. That row should remain a real result record needing a measurement, while an empty department has no result record at all. Those states become important in the next exercise.
Predict the effect of a known zero score: it would be excluded from the eighty threshold but would not match IS NULL. A known score of exactly eighty is included because the comparison is inclusive. Boundary and missing-value checks are basic reasoning skills, even when the SQL statement contains only a few keywords.
Intermediate: preserve the complete department list
The intermediate report needs every department, including Optional with no result records. Start from exercise_departments and LEFT JOIN exercise_results through dept_id. Count the right-side result id so the outer-join placeholder does not count as a fictional result. Group by the department key and selected label to obtain one summary per department.
AVG(score) uses known scores only. Foundation has seventy and ninety, producing eighty. Advanced has eighty and NULL, also producing eighty but based on only one known observation. Optional has no observations and its mean is NULL. Equal means do not imply equally complete evidence, so include both total result count and known-score count.
Intermediate extension: apply a group threshold
A report of departments with at least two result records uses HAVING COUNT(r.id)>=2 after grouping. Both Foundation and Advanced qualify. If the threshold instead concerns known scores, HAVING COUNT(r.score)>=2 retains only Foundation. The aggregate expression is part of the requirement; changing it changes the population.
A score threshold in WHERE after a left join can remove the placeholder for an empty department and lose coverage. Put conditions defining eligible matches in ON when all departments must remain, or state explicitly that the report is only about qualifying result rows. Filter placement is a substantive exercise, not a cosmetic choice of where to write the same words.
Advanced: choose a source row per department
The advanced exercise ranks known scores within each department. ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY score DESC,id) chooses the highest score and resolves any ties using result identity. Put the window value in a CTE and filter rn=1 in the outer query. The selected result still has its name and id, unlike a bare MAX that returns only the score.
If the contract says every tied winner, use a peer rank based on score alone or compare against the grouped maximum. Adding id to a rank changes the tie definition. Do not choose ROW_NUMBER, RANK or DENSE_RANK from their names alone; explain whether the answer should be one deterministic row or all peers at the top known value.
Review readiness before raising difficulty
You are ready to progress when you can explain result grain, NULL handling and ordering without reading the solution. For the intermediate task, justify Optional’s zero count and NULL mean. For the advanced task, explain why unknown scores are removed before ranking and why the outer WHERE can see rn. These explanations matter more than typing speed.
Extend one test at a time. Add a tied high score, a known zero or a new empty department and predict only the affected outputs. The queries work in modern PostgreSQL, MySQL 8+, SQL Server and SQLite with windows, but window availability and date or identity syntax should be checked if you expand the model. Keep the shared core while labelling local extensions.
Run the complete practice setup
Three departments and four result records support all difficulty levels. Advanced contains one known score and one missing score. Optional has no child rows at all. These deliberate cases distinguish data completeness from relationship coverage.
CREATE TABLE exercise_departments (id INTEGER PRIMARY KEY,name VARCHAR(30) NOT NULL);
CREATE TABLE exercise_results (id INTEGER PRIMARY KEY,dept_id INTEGER NOT NULL,name VARCHAR(30) NOT NULL,score INTEGER);
INSERT INTO exercise_departments (id,name) VALUES (1,'Foundation'),(2,'Advanced'),(3,'Optional');
INSERT INTO exercise_results (id,dept_id,name,score) VALUES
(1,1,'Ana',70),(2,1,'Ben',90),(3,2,'Cara',80),(4,2,'Dev',NULL);Practical exercise and worked solution
Complete the advanced task: return one highest-known-score learner per department that has a known score. Resolve ties by the lowest result id.
WITH ranked AS (
SELECT id,dept_id,name,score,
ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY score DESC,id) AS rn
FROM exercise_results WHERE score IS NOT NULL
)
SELECT dept_id,name,score FROM ranked WHERE rn=1 ORDER BY dept_id;dept_id | name | score
1 | Ben | 90
2 | Cara | 80Ben ranks ahead of Ana in Foundation. Cara is the only known-score candidate in Advanced because Dev is removed by the input filter. Optional has no candidate and therefore no winner row. This result is deliberately different from the complete department summary. To preserve empty departments beside a missing winner, join the chosen winners back to the department dimension with a left join.
Official documentation
The PostgreSQL table-expression guide, PostgreSQL window tutorial and SQLite window reference explain the progression’s underlying operations.
SQL Exercises by Difficulty with Complete Worked Answers Examples
Each example shows the code, the output it produces and what each part does.
Intermediate answer: counts and known averages
The left input defines the complete department population. The child-key count distinguishes actual results from placeholders, while the score count reveals measurement completeness. Multiplying by 1.0 expresses fractional average intent across engines whose AVG of integer input can return an integer-type result.
SELECT d.id,d.name,COUNT(r.id) AS result_count,
COUNT(r.score) AS known_scores,AVG(r.score*1.0) AS mean_score
FROM exercise_departments AS d
LEFT JOIN exercise_results AS r ON r.dept_id=d.id
GROUP BY d.id,d.name ORDER BY d.id;id | name | result_count | known_scores | mean_score
1 | Foundation | 2 | 2 | 80
2 | Advanced | 2 | 1 | 80
3 | Optional | 0 | 0 | NULLCommon SQL Exercises by Difficulty with Complete Worked Answers Mistakes
Counting the outer-join placeholder as a result
COUNT(*) produces one for Optional after the LEFT JOIN, because it counts the preserved combination. COUNT(r.id) produces zero because no real result key exists. COUNT(r.score) is also zero there, but it is not a general result count: it skips Dev’s genuine record with an unknown score. Name the two counts according to what each measures.
SQL Exercises by Difficulty with Complete Worked Answers FAQ
Should I skip directly to advanced exercises?
Only if you can already explain filtering, grouping and join preservation, such as why a LEFT JOIN keeps customers without orders. Advanced exercises build on those ideas, so gaps there show up as confusing wrong answers later.
Is a missing winner always a missing department?
No. It can mean the department has no known-score candidates: every score is NULL, so ranking or MAX finds nothing. Check whether the department exists and whether its rows have usable values before deciding what to report.
How do I keep all tied winners?
Use a rank or maximum-comparison policy that defines peers by score, such as RANK() = 1 or WHERE score = (SELECT MAX(score) ...), instead of ROW_NUMBER(), which assigns one unique row position and drops the other ties.
How many exercises should I do at each level?
Move on when you can solve new exercises at a level without looking up syntax and can explain why your answer is correct. For most learners that's five to ten varied problems per level, revisited a week later to check they've stuck.
What should I do when my answer has the wrong number of rows?
Too many rows usually means a join multiplied them; count rows after each join. Too few usually means an inner join or WHERE condition dropped unmatched or NULL rows. Rebuild the query step by step and check the row count at every step.
SQL Exercises by Difficulty with Complete Worked Answers Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which department has two result records but only one known score?
SQL Exercises by Difficulty with Complete Worked Answers Quiz
2 questions to check what you learned.
Which clause tests a grouped result-count threshold?
- A same-level WHERE COUNT(*) condition
- HAVING
- AS before the table alias
Which function selects one deterministic highest-score row with an id tie-breaker?
- COUNT(*) without grouping
- MAX(score) with an arbitrary ungrouped name
- ROW_NUMBER over the department partition