Memra

programming · beginner · 8 h 33 m · Athabasca University

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.

0 / 57 lessons
Reviews stop being scheduled for after it.

up next

Tables, rows, and your first SELECT

9 m

Start course

The Relational Model & First Queries

0 / 5 lessons
  1. Tables, rows, and your first SELECT 9 m
  2. Choosing columns (projection) 8 m
  3. Filtering rows with WHERE 9 m
  4. Sorting and limiting 8 m
  5. Removing duplicates with DISTINCT 6 m

Filtering Deeper

0 / 3 lessons
  1. Combining conditions: AND, OR, BETWEEN, IN 9 m
  2. Pattern matching with LIKE 8 m
  3. NULL and the unknown 9 m

Transforming Values: CASE, Strings, Numbers & Dates

0 / 4 lessons
  1. Conditional logic with CASE 8 m
  2. Working with text 8 m
  3. Numbers, rounding & casting 8 m
  4. Dates & time 9 m

Aggregating Data

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

Joining Tables

0 / 5 lessons
  1. INNER JOIN: combining tables 10 m
  2. LEFT JOIN: keeping unmatched rows 10 m
  3. Joining three tables 10 m
  4. Aggregating across joins 10 m
  5. Self joins, RIGHT, FULL & CROSS 10 m

Set Operations

0 / 1 lesson
  1. UNION, INTERSECT & EXCEPT 9 m

Subqueries & CTEs

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

Window Functions

0 / 4 lessons
  1. Windows: aggregates that keep the rows 11 m
  2. Ranking rows 10 m
  3. Running totals and LAG 11 m
  4. NTILE, FIRST_VALUE & frames 10 m

Modifying Data

0 / 4 lessons
  1. INSERT (and RETURNING) 9 m
  2. UPDATE and DELETE 9 m
  3. UPSERT: INSERT … ON CONFLICT 10 m
  4. Bulk inserts & data-modifying CTEs 9 m

Data Types, JSON & Arrays

0 / 3 lessons
  1. Types & casting 8 m
  2. Arrays 9 m
  3. JSON & JSONB 10 m

Designing Schemas: DDL & Constraints

0 / 4 lessons
  1. Data types & constraints 10 m
  2. CREATE, ALTER & DROP 9 m
  3. Foreign-key actions 9 m
  4. Generated columns 7 m

Normalization & Data Modeling

0 / 2 lessons
  1. Normalization, briefly 9 m
  2. Data modeling & the ER mindset 9 m

Views & Transactions

0 / 4 lessons
  1. Views 8 m
  2. Materialized views 8 m
  3. Transactions & savepoints 10 m
  4. ACID, isolation & MVCC 9 m

Indexing & Performance

0 / 2 lessons
  1. Indexes & EXPLAIN 10 m
  2. Pagination & reading plans 9 m

Functions, Procedures & Triggers

0 / 2 lessons
  1. Writing functions 9 m
  2. PL/pgSQL & triggers 11 m

Security & Administration

0 / 2 lessons
  1. Roles & privileges 9 m
  2. Operating a database 8 m

Capstone & Exam Prep

0 / 2 lessons
  1. Capstone: a real report 12 m
  2. Exam-style practice 14 m
~/memra/learn/postgresql