PostgreSQL Practice Lab

中文说明

I put this repository together while practising SQL for backend interviews. Short syntax exercises were useful at first, but they did not help much with reading an unfamiliar schema or turning a business question into a query.

This lab runs PostgreSQL locally with Docker and loads two sample businesses:

• Pagila, a film rental database with customers, stores, inventory, rentals, and payments.
• Northwind, an order database with products, suppliers, employees, shipping, and sales.

There are 12 warm-up exercises and 100 longer questions covering joins, aggregation, subqueries, window functions, and common reporting tasks. Your SQL files stay in a local answers/ directory that Git ignores.

Start the database

You need Docker Desktop or Docker Engine with Compose, plus a Bash-compatible shell.

git clone https://github.com/pllsparrow/postgresql-practice-lab.git
cd postgresql-practice-lab
docker compose up -d
bin/check

The first startup imports both datasets and can take a little longer.

To open psql:

bin/psql-pagila
bin/psql-northwind

For DBeaver, DataGrip, or another database client, use:

Host: localhost
Port: 55432
User: postgres
Password: postgres
Database: pagila or northwind

These credentials are only for the local Docker container. Do not reuse them for a database exposed to the Internet.

Working through the questions

I recommend starting with questions/sql-warmup.md, then moving to questions/sql-100.md.

Create your own answer file and run it against the database named by the question:

mkdir -p answers
touch answers/W01.sql

bin/run-sql pagila answers/W01.sql
bin/run-sql northwind answers/051.sql

Some questions have a reference query that can be checked locally:

bin/check-answer W01
bin/check-answer 001

I use the reference query after attempting the problem, mainly to compare the result and review a different approach.

If you need a quick reminder of the common tables and relationships, see notes/schema-map.md. The helper commands are in bin/, questions are in questions/, and available reference queries are in refs/.

To rebuild the databases from scratch:

docker compose down -v
docker compose up -d

This removes the Docker database volume, but it does not delete SQL files under answers/.

Data sources and licenses

Pagila and Northwind keep their original licenses. The root MIT license covers only the material written for this training repository. Details and source links are in THIRD_PARTY_NOTICES.md and docs/SOURCES.md.