StackMonsters
Switch to light mode

PostgreSQL Course · 04

SQL Fundamentals

Query a real PostgreSQL database that runs in your browser. Filter, sort and summarize rows, join tables, write subqueries, translate relational algebra, design and normalize a schema, then add views, procedures, indexes, transactions and access control. Every table is on screen while you work, and every lab checks your query the moment you run it. Nothing to install.

Start free See pricing
16
chapters
232
lessons & labs
$19.99/mo
with Pro, Chapters 1-3 free
Nothing to install
PostgreSQL runs in your browser

Who it's for

Students in a database course, and engineers who query every day.

  • Computer science students taking a database course (relational model, SQL, ER design, normalization)
  • Developers and SREs who use a database every day but learned SQL by copying queries
  • Anyone preparing for a technical interview with SQL questions

What you'll be able to do

Query, design and run a relational database with confidence.

  • Write SELECT queries that filter, sort, group and summarize data
  • Combine tables with inner, outer and self joins, subqueries and set operations
  • Translate relational algebra expressions to SQL and back
  • Design a schema from an ER model and normalize it to 3NF and BCNF
  • Create tables with keys and constraints, and change data safely
  • Use views, functions, procedures, indexes, transactions and roles

The path

16 chapters, from your first SELECT to a database you can design, tune and secure.

Each lesson is short and ends in a quiz, then a lab on a real PostgreSQL database in your browser, with every table on screen. Chapters 1-3 are free.

  1. 01Free

    First Queries

    Read a table with SELECT

    • tables
    • rows
    • columns

    3 lessons · 5 labs

  2. 02Free

    Filter Rows

    Keep only the rows you need with WHERE

    • WHERE
    • comparison
    • AND

    6 lessons · 7 labs

  3. 03Free

    Sort and Shape Results

    Order, limit and compute the columns you return

    • ORDER BY
    • LIMIT
    • DISTINCT

    6 lessons · 7 labs

  4. 04

    Create Tables

    Define tables, add rows and change a schema

    • CREATE TABLE
    • data types
    • INSERT

    8 lessons · 8 labs

  5. 05

    Keys and Constraints

    Let the database refuse bad data

    • PRIMARY KEY
    • candidate keys
    • UNIQUE

    7 lessons · 7 labs

  6. 06

    Change Data

    Insert, update and delete rows without surprises

    • INSERT … SELECT
    • UPDATE
    • DELETE

    6 lessons · 6 labs

  7. 07

    Summarize Data

    Count, total and average rows in groups

    • COUNT
    • SUM
    • AVG

    7 lessons · 7 labs

  8. 08

    Join Tables

    Combine rows from related tables

    • INNER JOIN
    • aliases
    • LEFT JOIN

    10 lessons · 10 labs

  9. 09

    Subqueries and Set Operations

    Queries inside queries, and UNION, INTERSECT, EXCEPT

    • scalar subquery
    • IN (subquery)
    • EXISTS

    9 lessons · 9 labs

  10. 10

    Relational Algebra

    The operators under every SQL query

    • σ selection
    • π projection
    • ρ rename

    10 lessons · 10 labs

  11. 11

    Design a Database

    ER models, relationships and normal forms

    • entities
    • relationships
    • cardinality

    10 lessons · 11 labs

  12. 12

    Views, Functions and Procedures

    Keep queries and logic inside the database

    • CREATE VIEW
    • functions
    • stored procedures

    6 lessons · 6 labs

  13. 13

    Indexes and Performance

    Read query plans and make slow queries fast

    • EXPLAIN
    • CREATE INDEX
    • multi-column indexes

    6 lessons · 6 labs

  14. 14

    Transactions

    All or nothing, even when things fail

    • BEGIN
    • COMMIT
    • ROLLBACK

    8 lessons · 8 labs

  15. 15

    Database Security

    Roles, privileges and least privilege

    • CREATE ROLE
    • GRANT
    • REVOKE

    6 lessons · 7 labs

  16. 16

    Beyond Relational

    JSON documents and when NoSQL fits

    • jsonb
    • ->>
    • @>

    5 lessons · 5 labs

Start free See every course