Back to catalog
DatabasesBeginner

Practical SQL for Beginners

One small online shop, eleven lessons, and every query you will actually write at work

Practical SQL for Beginners

About this course

Eleven lessons built on a single database: four tables of an online shop — customers, products, orders and order items. You write the same queries a backend developer, an analyst or a QA engineer writes on a normal working day: pull the rows you need, count revenue per month, join tables without duplicating money, change data without wiping a table, and find out why a query that used to take three milliseconds now takes three seconds.

The dialect is PostgreSQL, with the places where MySQL behaves differently called out as they come up. Every lesson has runnable SQL and a note on the mistake people usually make there — NULL that silently drops rows from a filter, a LEFT JOIN turned back into an INNER JOIN by a WHERE clause, a SUM inflated by a join, an index the planner refuses to use. After each module there is a short test, and a final test at the end.

Course curriculum

  1. Tables and the first queries

    The shop schema the whole course runs on, and the three clauses you will type in almost every query: SELECT, WHERE, ORDER BY.

    • 1.The shop schema: tables, primary keys, foreign keys12 min
    • 2.SELECT: columns, expressions and aliases10 min
    • 3.WHERE, ORDER BY and LIMIT: exactly the rows you need12 min

  2. Aggregates and grouping

    Turning thousands of rows into a number and a table of numbers: count, sum, avg, GROUP BY, HAVING, and the order in which the database actually evaluates a query.

    • 1.Aggregate functions and what they do with NULL12 min
    • 2.GROUP BY: one row per group14 min
    • 3.HAVING and the order the database really works in12 min

  3. Joining tables and changing data

    Four tables become one result: INNER, LEFT and the rest of the joins, the double-counting trap, and INSERT, UPDATE, DELETE done without regrets.

    • 1.INNER JOIN: rows from two tables side by side14 min
    • 2.LEFT JOIN, the rest of the family, and the double-counting trap16 min
    • 3.INSERT, UPDATE, DELETE without regrets14 min

  4. Speed and safety

    Why the same query takes three milliseconds or three seconds, and how to change data without half-finished orders or a hole an attacker can walk through.

    • 1.Indexes and EXPLAIN: why the query got slow18 min
    • 2.Transactions and parameterized queries18 min