Skip to content

SQL, from beginner to expert

SQL is the language for asking questions of data kept in tables. It is fifty years old and still the first thing a data engineer is tested on. This course goes from the first SELECT to window functions, query plans and data modelling, with a sample shop database used in every chapter. Every example has been run on PostgreSQL and on SQLite, and the table under it is what the database really returned.

Why PostgreSQL and SQLite

They are the two SQL choices Codility offers in its practice SQL tasks (seen on Codility's own practice page, 11 Nov 2026; a real employer's test lists its languages on its first page, so check there). They are also free and easy to install. Almost everything here is standard SQL and works on MySQL, SQL Server and Oracle too; where a database does it differently, the chapter says so, and chapter 28 collects the differences. Results on MySQL, SQL Server and Oracle have not been tested here.

How to use it

  • Preparing for an interview soon? Follow the fast track. It picks the chapters that matter most and puts them in the order to read them.
  • Coming back after a while? Read the cheat sheet, then the chapters where it felt rusty.
  • Starting out? Read in order from chapter 1.
  • Try everything. Load the sample database on your own computer (a few minutes, free), or paste it into an online SQL playground, and run the examples yourself. Change them and see what happens.

How a chapter works

  • The opening box says what the chapter covers and what you need first.
  • Every query is followed by its result. The small line above the result says where it ran: "Ran on PostgreSQL 16 and SQLite 3.53: same rows on both." Where the two differ, both versions are shown in tabs.
  • Interview tips name the usual wrong answer, run it, and show it failing on the sample data.
  • Practice ends each chapter: questions from easy to hard. The answer, its result and the explanation are folded under each one. Try first, then open it.

Contents

Part Chapters
Start Fast track · Cheat sheet · The sample database
I. Foundations 1. Tables, rows and keys · 2. SELECT, WHERE, ORDER BY, LIMIT · 3. The order a query really runs in · 4. Expressions: CASE, numbers, dates, CAST · 5. Text functions and pattern matching · 6. NULL and three-valued logic
II. Interview core 7. Aggregation: GROUP BY and HAVING · 8. Joins: inner, left, right, full, cross · 9. Self, semi and anti joins · 10. Subqueries · 11. CTEs and recursive CTEs · 12. UNION, INTERSECT, EXCEPT · 13. Window functions: ranking · 14. Window functions: LAG, LEAD and running totals · 15. Classic interview patterns · 16. Dates and time series
III. Changing data and designing tables 17. INSERT, UPDATE, DELETE, MERGE · 18. CREATE TABLE, types, constraints · 19. Data modelling · 20. Transactions and isolation · 21. Views and materialized views · 22. Duplicates and incremental loads · 23. Data quality checks · 24. JSON and arrays
IV. Expert 25. Indexes and EXPLAIN · 26. Making slow queries fast · 27. GROUPING SETS, ROLLUP, CUBE, LATERAL · 28. Dialect differences · 29. Safe SQL
V. Practice 30. Practice set A · 31. Practice set B · 32. Mock tests · Glossary

Codility-style tasks on other small databases (employees, sensor readings, logins, payments, flights) are in the Codility course under Interview prep.