Memra
programming · beginner

PostgreSQL

Query real data — by writing real SQL

A complete, hands-on PostgreSQL course on real Postgres running in your browser — enough to pass a database exam and do an entry-level data job. SELECT through joins, subqueries, CTEs and recursion, window functions, set operations, the string/number/date/JSON/array toolkit, data modification and upserts, schema design and constraints, normalization, views, transactions and ACID, indexing and EXPLAIN, functions and triggers, roles and security — every concept drilled by writing and running real queries against one cohesive bookshop database, ending in an exam-style capstone.

Start course → Flashcards 108
0 / 57 lessons
We'll stop scheduling reviews for after it.

The Relational Model & First Queries

10 cards
  1. Tables, rows, and your first SELECT 9 min ◈ 2
  2. Choosing columns (projection) 8 min ◈ 2
  3. Filtering rows with WHERE 9 min ◈ 2
  4. Sorting and limiting 8 min ◈ 2
  5. Removing duplicates with DISTINCT 6 min ◈ 2

Filtering Deeper

7 cards
  1. Combining conditions: AND, OR, BETWEEN, IN 9 min ◈ 3
  2. Pattern matching with LIKE 8 min ◈ 2
  3. NULL and the unknown 9 min ◈ 2

Transforming Values: CASE, Strings, Numbers & Dates

8 cards
  1. Conditional logic with CASE 8 min ◈ 2
  2. Working with text 8 min ◈ 2
  3. Numbers, rounding & casting 8 min ◈ 2
  4. Dates & time 9 min ◈ 2

Aggregating Data

10 cards
  1. Counting and summarizing 9 min ◈ 2
  2. GROUP BY: per-category summaries 10 min ◈ 2
  3. HAVING: filtering groups 8 min ◈ 2
  4. string_agg, FILTER & friends 9 min ◈ 2
  5. Subtotals with ROLLUP 8 min ◈ 2

Joining Tables

10 cards
  1. INNER JOIN: combining tables 10 min ◈ 2
  2. LEFT JOIN: keeping unmatched rows 10 min ◈ 2
  3. Joining three tables 10 min ◈ 2
  4. Aggregating across joins 10 min ◈ 2
  5. Self joins, RIGHT, FULL & CROSS 10 min ◈ 2

Set Operations

2 cards
  1. UNION, INTERSECT & EXCEPT 9 min ◈ 2

Subqueries & CTEs

10 cards
  1. Subqueries 10 min ◈ 2
  2. EXISTS and correlated subqueries 9 min ◈ 2
  3. Common Table Expressions (WITH) 10 min ◈ 2
  4. Derived tables, ANY & ALL 9 min ◈ 2
  5. Recursive CTEs 11 min ◈ 2

Window Functions

8 cards
  1. Windows: aggregates that keep the rows 11 min ◈ 2
  2. Ranking rows 10 min ◈ 2
  3. Running totals and LAG 11 min ◈ 2
  4. NTILE, FIRST_VALUE & frames 10 min ◈ 2

Modifying Data

8 cards
  1. INSERT (and RETURNING) 9 min ◈ 2
  2. UPDATE and DELETE 9 min ◈ 2
  3. UPSERT: INSERT … ON CONFLICT 10 min ◈ 2
  4. Bulk inserts & data-modifying CTEs 9 min ◈ 2

Data Types, JSON & Arrays

6 cards
  1. Types & casting 8 min ◈ 2
  2. Arrays 9 min ◈ 2
  3. JSON & JSONB 10 min ◈ 2

Designing Schemas: DDL & Constraints

8 cards
  1. Data types & constraints 10 min ◈ 2
  2. CREATE, ALTER & DROP 9 min ◈ 2
  3. Foreign-key actions 9 min ◈ 2
  4. Generated columns 7 min ◈ 2

Normalization & Data Modeling

1 cards
  1. Normalization, briefly 9 min ◈ 1
  2. Data modeling & the ER mindset 9 min

Views & Transactions

6 cards
  1. Views 8 min ◈ 2
  2. Materialized views 8 min ◈ 2
  3. Transactions & savepoints 10 min ◈ 2
  4. ACID, isolation & MVCC 9 min

Indexing & Performance

3 cards
  1. Indexes & EXPLAIN 10 min ◈ 1
  2. Pagination & reading plans 9 min ◈ 2

Functions, Procedures & Triggers

4 cards
  1. Writing functions 9 min ◈ 2
  2. PL/pgSQL & triggers 11 min ◈ 2

Security & Administration

2 cards
  1. Roles & privileges 9 min ◈ 2
  2. Operating a database 8 min

Capstone & Exam Prep

5 cards
  1. Capstone: a real report 12 min ◈ 2
  2. Exam-style practice 14 min ◈ 3
NORMAL ~/memra/learn/postgresql utf-8 LF