The Desi Architect
Notes / Docker Sandboxes / SQL Basics with PostgreSQL: Tables, Queries & Joins
Docker Sandboxes · Tier 0: Start Here · Lab 04

SQL Basics with PostgreSQL: Tables, Queries & Joins

Learn the SQL every later lab assumes. Query a small kirana store: filter, sort, count, group, join, see what keys and constraints stop, and watch an index turn a 200,000 row scan into a single lookup.

Time40 minutes
StackPostgreSQL 16Ports 15428
MemoryAbout 60 MB
Before thisLab 01Docker Compose Basics
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Read and write SELECT with WHERE, ORDER BY and LIMIT.
  • Change data with INSERT, UPDATE and DELETE, and explain why WHERE is not optional.
  • Count and group with COUNT, SUM and GROUP BY.
  • Join tables, and know the difference between JOIN and LEFT JOIN.
  • Explain what a primary key, a foreign key, NOT NULL, UNIQUE and CHECK protect you from.
  • Say what an index is and read the words Seq Scan and Index Scan.

Before you start

You need Docker Desktop running and the basics from Lab 01 (Docker Compose Basics). You do not need any SQL. The lab starts a PostgreSQL 16 database that already has a small kirana store in it.

Lab-only password

The database password in this lab is lab_only_password. It is only for practice on your own laptop. A real database never gets a password like that.