Your SQL practice environment

What you will be able to do

• Run Postgres with Compose on port 15432
• Connect with psql and confirm three seeded tables
• Create the ~/dcubes/sql-lab folder used in later lessons

Why this exists

SQL is how you ask the warehouse questions. A website that ran queries for you would not teach the skill. You type. Your machine runs Postgres.

Do Docker Getting Started first. Python is useful context (First Python data shift) but not required — this page loads the same retail shape from static CSVs.

Install the Postgres client

sudo apt update
sudo apt install --yes postgresql-client
psql --version

brew install libpq
brew link --force libpq
psql --version

Lab folder and samples

mkdir -p ~/dcubes/sql-lab/data ~/dcubes/sql-lab/out
cd ~/dcubes/sql-lab

Download the seed files (right-click save if needed, then copy into data/):

• stores.csv
• products.csv
• orders.csv

# After saving to Downloads — adjust the path for your OS / WSL
cp ~/Downloads/stores.csv ~/Downloads/products.csv ~/Downloads/orders.csv ~/dcubes/sql-lab/data/
ls data
wc -l data/*.csv

You should see three files; orders.csv has a header plus 300 data rows (wc -l → 301).

Start Postgres

cd ~/dcubes/sql-lab
cat > compose.yaml << 'EOF'
services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: dcubes
      POSTGRES_PASSWORD: dcubes
      POSTGRES_DB: retail
    ports:
      - "15432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dcubes -d retail"]
      interval: 3s
      timeout: 3s
      retries: 20

volumes:
  pgdata:
EOF
docker compose up -d
docker compose ps

Wait until healthy. Host port is 15432.

Create tables and load

cd ~/dcubes/sql-lab
export PGPASSWORD=dcubes
psql -h 127.0.0.1 -p 15432 -U dcubes -d retail << 'SQL'
CREATE TABLE IF NOT EXISTS stores (
  store_id TEXT PRIMARY KEY,
  store_name TEXT NOT NULL,
  region TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS products (
  sku TEXT PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL
);
CREATE TABLE IF NOT EXISTS orders (
  order_id TEXT PRIMARY KEY,
  order_ts TIMESTAMPTZ NOT NULL,
  store_id TEXT NOT NULL REFERENCES stores (store_id),
  customer_id TEXT NOT NULL,
  sku TEXT NOT NULL REFERENCES products (sku),
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL,
  line_total NUMERIC(12, 2) NOT NULL,
  payment_method TEXT NOT NULL,
  status TEXT NOT NULL
);
TRUNCATE orders, products, stores CASCADE;
SQL

Load CSVs with \copy (runs on the client, reads host paths):

cd ~/dcubes/sql-lab
export PGPASSWORD=dcubes
psql -h 127.0.0.1 -p 15432 -U dcubes -d retail -c "\copy stores FROM 'data/stores.csv' CSV HEADER"
psql -h 127.0.0.1 -p 15432 -U dcubes -d retail -c "\copy products FROM 'data/products.csv' CSV HEADER"
psql -h 127.0.0.1 -p 15432 -U dcubes -d retail -c "\copy orders FROM 'data/orders.csv' CSV HEADER"
psql -h 127.0.0.1 -p 15432 -U dcubes -d retail -c "SELECT 'stores' AS t, count(*) FROM stores UNION ALL SELECT 'products', count(*) FROM products UNION ALL SELECT 'orders', count(*) FROM orders;"

 t        | count
----------+-------
 stores   |     5
 products |    12
 orders   |   300

Connection shortcut

export PGPASSWORD=dcubes
export PGHOST=127.0.0.1
export PGPORT=15432
export PGUSER=dcubes
export PGDATABASE=retail
psql -c "SELECT count(*) FROM orders;"

Common mistakes

• Port 5432 — this lab uses 15432.
• COPY vs \copy — server COPY cannot see your laptop path; use \copy from psql.
• FK order — load stores and products before orders.
• Docker not running — connection refused.

Next

Why SQL for data work