SQL Notes

SQL Interview: Normalisation, NULL and Query Optimisation

Design and correctness questions: the normal forms, why NULL breaks intuition, and how to answer "this query is slow, what do you do?"

Interview Preparation

SQL Interview: Indexes, Transactions and ACID

The performance and concurrency half of a SQL interview: how indexes work, what they cost, ACID, isolation levels and deadlocks.

Interview Preparation

SQL Interview: Aggregation, GROUP BY and Subqueries

GROUP BY rules, HAVING versus WHERE, the NULL behaviour of aggregates, and the subquery questions that separate confident answers from vague ones.

Interview Preparation

SQL Interview: Joins, Keys and Constraints

The questions asked in almost every SQL interview, with short answers you can actually say out loud and the follow up the interviewer will ask next.

SQL

Realistic Scenarios: Analysis and Optimisation

Six end to end problems of the kind real work produces - a sales report, a retention table, a data quality audit, a duplicate cleanup and two optimisa...

SQL

Advanced Practice: Subqueries, CTEs and Window Functions

Twelve harder exercises - correlated subqueries, recursive CTEs, ranking, running totals and top N per group - with solutions and the reasoning behind...

SQL

Intermediate Practice: Joins and Aggregation

Fifteen exercises combining joins, grouping and having, each with the solution and a note on the trap it was designed to expose.

SQL

Beginner Practice Queries

Twenty graded exercises on SELECT, filtering, sorting and simple functions, each with a worked solution and the reason it is written that way.

SQL

Writing Portable SQL

How much portability is worth paying for, which constructs are safe everywhere, and how to isolate the parts that can never be portable.

SQL

PostgreSQL, MySQL, SQL Server, Oracle and SQLite Compared

One reference table for the differences that actually break code: paging, auto increment, strings, dates, upserts and the features each product lacks.

SQL

Users, Roles, GRANT, REVOKE and Least Privilege

Controlling who can do what: creating users, granting the minimum needed, grouping privileges into roles, and the accounts a typical application actua...

SQL

SQL Injection and Parameterised Queries

Why string concatenation lets user input rewrite your query, and why prepared statements make it structurally impossible - with the cases parameters c...