SQL and Relational Databases
SQL and Relational Databases
This domain tests relational thinking and query construction: filtering, joins, aggregation, subqueries, keys, normalization, and the difference between DDL and DML. Expect to read a query and predict its rows or explain why it returns too many.
Core rules
- Logical evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- WHERE filters rows before grouping; HAVING filters groups after aggregation.
- INNER JOIN keeps matches; LEFT JOIN keeps all left rows and fills unmatched right columns with NULL.
- NULL is not equal to anything; use IS NULL. NOT IN with a NULL in the list can return no rows.
- A primary key uniquely identifies a row; a foreign key references another table's key.
- Normalization reduces redundancy; DDL defines structures, DML changes rows.
Worked example
Find departments with more than three employees and an average salary above 60000:
SELECT d.name, AVG(e.salary) AS avg_salary, COUNT(*) AS n
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) > 60000 AND COUNT(*) >= 3
ORDER BY avg_salary DESC;
Reasoning: the join pairs employees with departments; GROUP BY creates one row per department; HAVING is required because the conditions use aggregates; ORDER BY can use the alias. If departments with zero employees must appear, use LEFT JOIN and count employee keys rather than COUNT(*).
Traps
- Putting aggregate conditions in WHERE instead of HAVING.
- Using COUNT(column) and forgetting NULLs are excluded.
- Joining on non-unique keys and multiplying rows.
- Selecting non-grouped columns without aggregation.
- Assuming subquery ORDER BY affects the outer result.