SQL and PostgreSQL

What you'll learn

• Querying One TableTables and SELECT, filtering with WHERE, sorting and limiting, NULL, expressions and CASE.
• Aggregating DataCOUNT, SUM and AVG, GROUP BY, HAVING, and conditional aggregates for pivot-style reports.
• Joining TablesKeys and INNER JOIN, LEFT JOIN and finding what is missing, joining many tables, self joins.
• Subqueries and CTEsSubqueries, EXISTS and correlated queries, CTEs and recursive queries, UNION, INTERSECT and EXCEPT.
• Designing SchemasCREATE TABLE and constraints, primary and foreign keys with cascades, normalization, migrations.
• Changing DataINSERT and RETURNING, UPDATE and DELETE, upserts with ON CONFLICT, and transactions.
• Analytics and PerformanceWindow functions, top-N per group and LAG, indexes and query plans, keyset pagination.
• SQL from Node and PostgreSQLnode:sqlite, parameters and SQL injection, transactions in code, moving to PostgreSQL, then the checkout boss.

Course outline

8 units and 34 lessons. Each lesson has a short read with examples you run, a quiz, and coding problems tested in your browser; most have a step-through visualizer.

1. 1

   Unit 1:Querying One Table

   Tables and SELECT, filtering with WHERE, sorting and limiting, NULL, expressions and CASE.

   Free
   1. SQL
      Tables, Rows and SELECT Relational databases store data in tables; SQL asks for the rows you want. Meet the practice shop database and your first SELECT.
      1 problem
   2. WHR
      Filtering with WHERE Keep only the rows you want: comparisons, AND, OR and NOT, IN, BETWEEN and LIKE patterns, and operator precedence.
      1 problem
   3. ORD
      Sorting, Limiting and DISTINCT ORDER BY one or more columns, ASC and DESC, LIMIT and OFFSET for top-N and pages, and DISTINCT to remove duplicate rows.
      1 problem
   4. NULL
      NULL: the Missing Value NULL means unknown: why = NULL never matches, IS NULL, COALESCE for defaults, and how NULL spreads through expressions.
      1 problem
   5. f(x)
      Expressions, Functions and CASE Computed columns: arithmetic, ROUND, string functions and ||, date functions, and CASE for if-then-else inside a query.
      1 problem
2. 2

   Unit 2:Aggregating Data

   COUNT, SUM and AVG, GROUP BY, HAVING, and conditional aggregates for pivot-style reports.

   Free
   1. SUM
      COUNT, SUM, AVG, MIN and MAX Collapse many rows into one answer: counting rows and values, totals and averages, and how aggregates treat NULL.
      1 problem
   2. GRP
      GROUP BY One result row per group: grouping by one or more columns, the rule for what may appear in SELECT, and grouping by expressions.
      1 problem
   3. HAV
      HAVING: Filtering Groups WHERE filters rows before grouping; HAVING filters groups after. Combine both to answer "which groups match?" questions.
      1 problem
   4. FLT
      Conditional Aggregates Count or sum only some rows inside a group: SUM(CASE …), the FILTER clause, and turning rows into a pivot table.
      1 problem
3. 3

   Unit 3:Joining Tables

   Keys and INNER JOIN, LEFT JOIN and finding what is missing, joining many tables, self joins.

   Pro
   1. JOIN
      Keys and INNER JOIN Primary and foreign keys connect tables; INNER JOIN combines rows whose keys match, with table aliases to keep it readable.
      1 problem
   2. LEFT
      LEFT JOIN and What Is Missing Keep every row of the left table even without a match, find rows with no partner (anti-joins), and the ON versus WHERE trap.
      1 problem
   3. JN+
      Joining Many Tables Follow the keys through several tables, aggregate across joins, and avoid double counting when a join fans out.
      1 problem
   4. SELF
      Self Joins Join a table to itself with two aliases: employees and their managers, and comparing rows of the same table.
      1 problem
4. 4

   Unit 4:Subqueries and CTEs

   Subqueries, EXISTS and correlated queries, CTEs and recursive queries, UNION, INTERSECT and EXCEPT.

   Pro
   1. ( )
      Subqueries A query inside a query: scalar subqueries that return one value, IN with a list of values, and subqueries in FROM.
      1 problem
   2. EX?
      EXISTS and Correlated Subqueries Subqueries that refer to the outer row: EXISTS and NOT EXISTS, the latest row per group, and why NOT EXISTS beats NOT IN.
      1 problem
   3. WITH
      CTEs and Recursive Queries WITH names intermediate results so long queries read top to bottom; WITH RECURSIVE walks hierarchies of any depth.
      1 problem
   4. UNI
      UNION, INTERSECT and EXCEPT Combine the rows of two queries: UNION and UNION ALL, INTERSECT for rows in both, EXCEPT for rows in one but not the other.
      1 problem
5. 5

   Unit 5:Designing Schemas

   CREATE TABLE and constraints, primary and foreign keys with cascades, normalization, migrations.

   Pro
   1. DDL
      CREATE TABLE and Constraints Define tables with types and constraints (NOT NULL, UNIQUE, CHECK, DEFAULT), so the database itself rejects bad data.
      1 problem
   2. FK
      Primary Keys, Foreign Keys and Cascades Identity with primary keys (including composite ones), relationships with foreign keys, and what happens on delete: RESTRICT, CASCADE, SET NULL.
      1 problem
   3. 1NF
      Normalization Store each fact once: the update, insert and delete anomalies of duplicated data, the normal forms in plain words, and when to denormalize on purpose.
      1 problem
   4. ALT
      Changing Schemas: Migrations ALTER TABLE to add, rename and drop columns, backfilling data, versioned migration files, and changing a live database safely.
      1 problem
6. 6

   Unit 6:Changing Data

   INSERT and RETURNING, UPDATE and DELETE, upserts with ON CONFLICT, and transactions.

   Pro
   1. INS
      INSERT and RETURNING Add rows with a column list, several rows at once, rows copied from a query with INSERT … SELECT, and RETURNING to get generated ids.
      1 problem
   2. UPD
      UPDATE and DELETE Change and remove rows safely: WHERE on every statement, updates computed from other tables, deleting in the right order, and checking first with SELECT.
      1 problem
   3. UPS
      Upserts with ON CONFLICT Insert or update in one statement: ON CONFLICT DO UPDATE with the excluded row, DO NOTHING for idempotent inserts, and why read-then-write is racy.
      1 problem
   4. TXN
      Transactions BEGIN, COMMIT and ROLLBACK: all-or-nothing groups of statements, what ACID means, and isolation in a busy database.
      1 problem
7. 7

   Unit 7:Analytics and Performance

   Window functions, top-N per group and LAG, indexes and query plans, keyset pagination.

   Pro
   1. OVER
      Window Functions Aggregates that keep every row: OVER, PARTITION BY and ORDER BY in the window, ROW_NUMBER, RANK and DENSE_RANK, running totals.
      1 problem
   2. TOP
      Top-N per Group and LAG The most common window-function patterns: the best N rows per group with ROW_NUMBER in a CTE, and comparing a row with the previous one using LAG.
      1 problem
   3. IDX
      Indexes and Query Plans How an index turns a full scan into a lookup, reading EXPLAIN QUERY PLAN, composite index column order, and what indexes cost on writes.
      1 problem
   4. KEY
      Keyset Pagination and Query Habits Why OFFSET gets slow and unstable, paging by the last row seen with row-value comparisons, and everyday habits for fast queries.
      1 problem
8. 8

   Unit 8:SQL from Node and PostgreSQL

   node:sqlite, parameters and SQL injection, transactions in code, moving to PostgreSQL, then the checkout boss.

   Pro
   1. JS
      SQL from Node: node:sqlite Open a database from JavaScript, run statements with exec, and read rows with prepared statements: run, get and all.
      1 problem
   2. ?
      Parameters and SQL Injection Never build SQL by concatenating input: what SQL injection is, placeholders that keep data separate from code, and building dynamic filters safely.
      1 problem
   3. TRY
      Transactions in Application Code BEGIN, COMMIT and ROLLBACK around JavaScript: the try/catch pattern, validating inside the transaction, and returning results only after commit.
      1 problem
   4. PG
      From SQLite to PostgreSQL What changes when the same app moves to PostgreSQL: types, identity columns, strict GROUP BY, ILIKE, dates, placeholders, connection pools and migrations.
      1 problem
   5. BOSS
      Boss: Place Orders Safely Everything together: validate, insert an order and its lines with prices copied, decrease stock, and roll it all back if any line fails.
      1 problem