Your report is correct. The next question is whether it is fast enough and readable enough to ship. A query that takes a moment on fifteen rows can grind for a minute on fifteen million, and a column called c2 means nothing to the executive reading the slide. Optimisation and presentation are the last mile of a capstone - they turn a query that works into a finding people can act on.
This lesson serves two audiences at once. The database wants efficient SQL: filter early, read only the columns you need, and make the engine do less work. The human reader wants clear SQL: rounded numbers, no blank cells, and columns whose names explain themselves. Happily, the changes that help one often help the other - a query that reads less data is faster, and a result set of a few well-named columns is far easier to read.
flowchart LR
M["Measure the query"] --> F["Find the cost"]
F --> R["Rewrite it"]
R --> M2["Re-measure"]
M2 --> Q{"Faster?"}
Q -->|yes| D["Good enough - ship"]
Q -->|no| F
style D fill:#0e7490,color:#fff
style Q fill:#1e293b,color:#fff
Make it fast: measure, then cut work
Optimisation begins with measurement, not guessing. You find the slow part, understand why it is slow, change one thing, and measure again. On a small dataset every query feels instant, so build the habits now: a query that scans a table once is almost always preferable to one that re-scans it over and over.
Three principles do most of the work. Filter early: put conditions in WHERE so rows are discarded before they are aggregated, not afterwards in HAVING. Select only what you need: SELECT * drags every column through the engine and leaves the reader guessing which one matters. Prefer a join over a correlated subquery: a subquery that refers to the outer row runs once per row, repeating its work, while a joined derived table computes each value a single time and reuses it.
SELECT d.name AS department,
ROUND(AVG(e.salary)) AS avg_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name
ORDER BY avg_salary DESC;
flowchart TD A["All rows"] --> W["WHERE filters first"] W --> F["Fewer rows survive"] F --> G["GROUP BY aggregates the survivors"] G --> R["Result"] style W fill:#0e7490,color:#fff style F fill:#0e7490,color:#fff
Make it readable: round, fill, and name
A stakeholder does not read result sets the way you do. A raw AVG(salary) of 561666.6666666666 implies a precision the data does not have, a NULL cell reads as a blank or an error, and a column called expr1 says nothing. Presentation is the step that turns machine output into a number a person can trust at a glance.
Three functions clean almost everything up. ROUND(AVG(salary)) drops the spurious decimals and shows a defensible whole number. COALESCE(city, 'Unknown') replaces a NULL with a label that explains the gap instead of hiding it. And an AS alias gives every column a name a non-technical reader understands - avg_salary, not a bare expression. None of these change the underlying data; they change only how it is shown.
SELECT d.name || ' (' || COALESCE(d.city, 'Unknown') || ')' AS location
FROM departments d
ORDER BY d.name;
flowchart LR R["AVG returns 561666.6666"] --> RO["ROUND to 561667"] RO --> CO["COALESCE blanks to a label"] CO --> AL["Alias as avg_salary"] AL --> S["Stakeholder-ready column"] style S fill:#0e7490,color:#fff
The canonical optimisation: join instead of re-computing
The most common slow query in reporting is a correlated subquery - a subquery that mentions the outer row, so the database must re-run it once for every row of the outer table. To find each department's top earner, a correlated subquery re-scans employees for every department. On a large table that is rows-times-departments of repeated effort.
The fix is to compute the per-department maximum a single time, in a derived table, and join back to it. SELECT department_id, MAX(salary) ... GROUP BY department_id runs one pass over employees; the join then matches each employee to their department's maximum. The answer is identical, but the work is proportional to the rows, not to the rows times the departments. Same result, far less effort - and the query reads as two clear stages instead of a knot.
SELECT d.name AS department, e.name AS top_earner, e.salary
FROM departments d
JOIN employees e ON e.department_id = d.id
JOIN (
SELECT department_id, MAX(salary) AS max_sal
FROM employees
GROUP BY department_id
) AS m ON m.department_id = e.department_id AND m.max_sal = e.salary
ORDER BY d.name;
Materialise the deliverable
When a report is final, you often want it as a stable artifact - a table you can query again, feed to a chart, or share without re-running the whole pipeline. CREATE TABLE ... AS SELECT builds that table and fills it from a query in one step, inheriting its column names from the SELECT.
This is where the lessons of the whole module come together. A presentation table should hold rounded, COALESCEd, well-aliased columns - the clean output, not the raw query - and it must be free of the fan-out trap, joining each fact table only once. Build it once from validated queries, and every consumer downstream sees the same trustworthy numbers.
Present average salary per department cleanly: return each department's name and its rounded average salary as avg_salary, highest first. Drop the spurious decimals a stakeholder would not trust.
SELECT d.name,
-- average salary, rounded
FROM departments d
JOIN employees e ON d.id = e.department_id
-- group and sort here
;
ROUND(AVG(e.salary))removes the stray decimals.Sort with
ORDER BY avg_salary DESCand alias the column withAS avg_salary.
SELECT d.name, ROUND(AVG(e.salary)) AS avg_salary FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY avg_salary DESC;
Build a readable location label for the slide deck: return one column called location shaped like Engineering (Oslo). Where the city is NULL, show Unknown instead of leaving a blank. Order by department name A to Z.
SELECT
-- build 'Name (City)', using Unknown when city is NULL
FROM departments d
ORDER BY d.name;
Glue strings with
||, and wrap the city inCOALESCE(d.city, 'Unknown').Alias the whole expression
AS location.
SELECT d.name || ' (' || COALESCE(d.city, 'Unknown') || ')' AS location FROM departments d ORDER BY d.name;
This query is unreadable: it uses SELECT *, dumps every column, and reports the average salary as a long unrounded decimal. Rewrite it to return each department's name and its rounded average salary as avg_salary, highest first.
SELECT *, AVG(salary) FROM employees GROUP BY department_id;
Drop the
*and name only the columns the report needs.Join to
departmentsfor a readable name, thenROUND(AVG(e.salary))and sort descending.
SELECT d.name AS department, ROUND(AVG(e.salary)) AS avg_salary FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.name ORDER BY avg_salary DESC;
Turn the validated payroll summary into a shareable artifact. Create a table called slide_summary with three clean columns: department (name), headcount, and avg_salary (rounded). Build it from a single join to employees so the counts are not inflated.
-- create slide_summary with department, headcount, rounded avg_salary
Use
CREATE TABLE slide_summary AS SELECT ...to build and fill the table in one step.Select
d.name AS department,COUNT(e.id) AS headcount, andROUND(AVG(e.salary)) AS avg_salary, grouped byd.name.
CREATE TABLE slide_summary AS SELECT d.name AS department, COUNT(e.id) AS headcount, ROUND(AVG(e.salary)) AS avg_salary FROM departments d JOIN employees e ON e.department_id = d.id GROUP BY d.name;
This report uses a correlated subquery that re-runs for every department - and it is missing the salary the stakeholder asked for. Rewrite it as a JOIN to a derived table that computes each department's MAX(salary) once. Return each department's name, the top earner's name as top_earner, AND their salary, ordered A to Z.
SELECT d.name AS department,
(SELECT e.name FROM employees e
WHERE e.department_id = d.id
ORDER BY e.salary DESC LIMIT 1) AS top_earner
FROM departments d
ORDER BY d.name;
Compute the per-department max once:
(SELECT department_id, MAX(salary) AS max_sal FROM employees GROUP BY department_id) AS m.Join
employees eto that derived table on bothdepartment_idandmax_sal = e.salaryso only the top earner in each department survives.
SELECT d.name AS department, e.name AS top_earner, e.salary FROM departments d JOIN employees e ON e.department_id = d.id JOIN (SELECT department_id, MAX(salary) AS max_sal FROM employees GROUP BY department_id) AS m ON m.department_id = e.department_id AND m.max_sal = e.salary ORDER BY d.name;
Recap
- Optimisation starts with measurement: find the slow part, change one thing, then re-measure. Use
EXPLAIN QUERY PLANto see the shape of the work. - Filter early in
WHERE, not late inHAVING, so fewer rows reach the aggregate. - Avoid
SELECT *- name only the columns the report actually needs. - Replace a correlated subquery with a joined derived table that computes each value a single time.
- For humans:
ROUNDspurious decimals,COALESCENULLs into labels, andASaliases rename columns to plain English. CREATE TABLE ... AS SELECTturns a validated query into a stable, shareable artifact.
That closes the capstone: you planned, modelled, built, transformed, validated, and now present. The same workflow scales from fifteen rows to fifteen million.