19 topics
Databases and SQL help, including why your LEFT JOIN lost its rows
A LEFT JOIN that quietly behaves like an INNER JOIN is the classic SQL bug, and the cause is almost always a WHERE clause that filters on a column from the right-hand table. The join dutifully keeps the unmatched rows and fills them with NULL, then the WHERE clause tests those NULLs, they fail, and the rows disappear. The condition belonged in the ON clause.
Where students get stuck
My LEFT JOIN drops the rows it was supposed to keep
Filtering happens after the join. Unmatched left rows survive the join with NULL in every right-hand column, and any WHERE test on those columns is neither true nor false, so the row is discarded. Move the condition into the ON clause and it is applied while matching, which preserves the outer rows. The exception is a deliberate test for absence: WHERE right.id IS NULL after a LEFT JOIN is the standard way to find rows with no match, and it works precisely because IS NULL is the one test NULL passes.
GROUP BY says my column is not in the list
Once you group, each output row stands for many input rows, so every selected column has to be either something you grouped by — the same for the whole group — or wrapped in an aggregate that collapses many values into one. A bare column has no single value to show. Either add it to the GROUP BY, which makes the grouping finer, or aggregate it with MAX or COUNT. Some databases let you get away with it and pick an arbitrary value, which is worse, because the query looks fine and the number is wrong.
WHERE and HAVING look like the same thing
They run at different times. WHERE filters individual rows before grouping, so it cannot see a COUNT, because no count exists yet. HAVING filters whole groups after aggregation, so it can. Practically: WHERE order_date >= '2026-01-01' cuts the rows going in, and HAVING COUNT(*) > 5 cuts the groups coming out. Use both in one query when you need both. Putting a condition in HAVING that could have gone in WHERE still gives the right answer but makes the database aggregate rows it was going to throw away.
Comparing to NULL never matches anything
NULL means unknown, not empty, so any comparison with it returns unknown rather than true — including = NULL and even NULL = NULL. Use IS NULL and IS NOT NULL. The same rule catches people out with NOT IN: if the subquery returns a single NULL, the whole NOT IN is unknown for every row and you get an empty result. Aggregates behave differently again: COUNT(column) skips NULLs while COUNT(*) counts every row, which is why the two disagree.
Normalisation makes no sense to me as a set of rules
Each normal form removes one kind of duplication that lets the data disagree with itself. First normal form: no repeating groups in a column, so no comma-separated list of phone numbers. Second: no column depending on only part of a composite key, which is why product_name does not belong in an order_line table keyed by order and product. Third: no column depending on another non-key column, so storing city and postcode together lets you change one and not the other. If you can describe an update that would leave two rows contradicting each other, the design is not normalised yet.
What's covered
Databases & SQL topics you can work through with a tutor, generate practice on, or turn into flashcards and a study plan.
Querying with SQL
- SELECT, WHERE, ORDER BY, and LIMIT
- Aggregate functions and GROUP BY with HAVING
- INNER, LEFT, RIGHT, and FULL OUTER joins
- Self joins and joining a table more than once
- Subqueries, correlated subqueries, and CTEs
- UNION, INTERSECT, and EXCEPT
Database design
- Entity-relationship diagrams and cardinality
- Primary keys, foreign keys, and referential integrity
- Normalisation to first, second, and third normal form
- Resolving many-to-many with a junction table
- Choosing data types and using constraints
Changing and protecting data
- INSERT, UPDATE, and DELETE with joins
- Transactions, COMMIT, and ROLLBACK
- ACID properties and isolation levels
- Views and stored procedures
Performance and beyond relational
- Indexes, when they help, and when they are ignored
- Reading a query plan
- Relational algebra notation for exam questions
- Document stores and when a relational schema is the wrong fit
Databases & SQL questions
My query runs but returns the wrong numbers. Can that be diagnosed?
Yes, and it is the most useful thing to bring. A query with no error is a logic problem, so the method is to cut it back to the join alone, count the rows, and see where duplication or loss appears. Row counts at each stage tell you which clause is lying long before staring at the finished query does.
Which SQL dialect does it use?
Standard SQL unless you say otherwise, then it follows your course's system — MySQL, PostgreSQL, SQL Server, SQLite or Oracle. That matters more than students expect, since string concatenation, date arithmetic and LIMIT versus TOP all differ, and an exam marked against one dialect will penalise another's syntax.
Can it help me design a schema for a project rather than write queries?
Yes. Schema design is a conversation, not a lookup, so talk through your entities out loud and it will push on the relationships — whether that is really one-to-many, what happens when the same customer has two addresses. The whiteboard is used for the ER diagram as it develops.
Will it do my database assignment for me?
No. Database courses assess design justification as much as working SQL, and a schema you did not reason about cannot be defended in a viva or a write-up. It will explain a join type, check your normalisation against a rule, or spot why a query duplicates rows, and you write the answer.
Stuck on databases & sql right now?
Talk it through out loud, share your screen, and watch it worked out step by step on a whiteboard.
Start free — no card