Skip to content

README.md

Latest commit

 

History

History
327 lines (256 loc) · 13.8 KB

README.md

File metadata and controls

327 lines (256 loc) · 13.8 KB
Raw

SQL Story PostgreSQL

sql-story-postgresql is a hands-on PostgreSQL learning repository for studying SQL from novice to advanced. It uses Docker to start PostgreSQL and pgAdmin, then loads two datasets for real query practice:

  • periode: a calendar table loaded from data/period.csv
  • superstore: a retail sales table loaded from data/superstore.csv

This repo is designed as a learning journey, not just a Docker setup. Start with simple SELECT queries, move into grouped analysis, and then work with joins, CTEs, window functions, PostgreSQL-specific features, schema design, transactions, and query tuning.

Project Structure

sql-story-postgresql/
|-- docker-compose.yml
|-- README.md
|-- data/
|   |-- period.csv
|   `-- superstore.csv
|-- init-db/
|   `-- 01-load-data.sql
|-- exercises/
|   |-- 01-novice-exercises.sql
|   |-- 02-intermediate-exercises.sql
|   |-- 03-advanced-exercises.sql
|   |-- 04-database-objects-exercises.sql
|   |-- 05-ddl-dml-exercises.sql
|   |-- 06-performance-exercises.sql
|   |-- 07-data-types-exercises.sql
|   |-- 08-postgresql-features-exercises.sql
|   |-- 09-joins-exercises.sql
|   |-- 10-subqueries-exercises.sql
|   |-- 11-schema-design-exercises.sql
|   `-- 12-transactions-exercises.sql
|-- lessons/
|   |-- 01-novice.sql
|   |-- 02-intermediate.sql
|   |-- 03-advanced.sql
|   |-- 04-database-objects.sql
|   |-- 05-ddl-dml.sql
|   |-- 06-performance.sql
|   |-- 07-data-types.sql
|   |-- 08-postgresql-features.sql
|   |-- 09-joins.sql
|   |-- 10-subqueries.sql
|   |-- 11-schema-design.sql
|   `-- 12-transactions.sql
|-- scripts/
|   |-- backup-db.ps1
|   |-- build.ps1
|   |-- reload-data.ps1
|   |-- reset-db.ps1
|   `-- sql/
|       `-- reload-data.sql
|-- backups/              # local database backups, ignored by Git
`-- solutions/
    |-- 01-novice-solutions.sql
    |-- 02-intermediate-solutions.sql
    |-- 03-advanced-solutions.sql
    |-- 04-database-objects-solutions.sql
    |-- 05-ddl-dml-solutions.sql
    |-- 06-performance-solutions.sql
    |-- 07-data-types-solutions.sql
    |-- 08-postgresql-features-solutions.sql
    |-- 09-joins-solutions.sql
    |-- 10-subqueries-solutions.sql
    |-- 11-schema-design-solutions.sql
    `-- 12-transactions-solutions.sql

What You Will Learn

  • Novice: SELECT, WHERE, ORDER BY, LIMIT, basic aggregates
  • Intermediate: GROUP BY, HAVING, CASE, joins, date-based summaries
  • Advanced: CTEs, window functions, ranking, running totals, business analysis
  • Database objects: functions, procedures, indexes, views, materialized views, triggers
  • Database design: CREATE, ALTER, INSERT, UPDATE, DELETE, UPSERT, constraints
  • Data types: TEXT, VARCHAR, NUMERIC, BOOLEAN, UUID, JSONB, arrays, NULL
  • PostgreSQL features: RETURNING, ON CONFLICT, generate_series(), FILTER, DISTINCT ON, ILIKE
  • Relational querying: join types, self joins, anti joins, and subqueries with EXISTS
  • Schema design: primary keys, foreign keys, unique rules, defaults, and check constraints
  • Transactions: BEGIN, COMMIT, ROLLBACK, and savepoints
  • Performance basics: EXPLAIN, EXPLAIN ANALYZE, and query plan inspection

Learning Workspace

This repo has three learning layers:

  • lessons/: guided examples you can run directly
  • exercises/: practice prompts without answers
  • solutions/: one possible answer set for the exercises

Recommended flow:

  1. Run a lesson file and study the results.
  2. Try the matching exercise file on your own.
  3. Compare your answer with the matching solution file.

Learning Map

Lessons

Exercises

Solutions

Prerequisites

  • Docker Desktop
  • Docker Compose

Setup

1. Create .env

Create a .env file in the project root:

POSTGRES_HOST=postgres
POSTGRES_PORT=5432
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DB=mydb

PGADMIN_EMAIL=admin@example.com
PGADMIN_PASSWORD=admin

POSTGRES_HOST_PORT=5432
PGADMIN_HOST_PORT=5050

2. Start the services

docker compose up -d

This starts PostgreSQL and pgAdmin. When PostgreSQL initializes a brand new postgres_data volume, it runs 01-load-data.sql once to create and load the learning tables.

View logs:

docker compose logs -f postgres

Stop the services:

docker compose down

Reset the database and rebuild everything from scratch:

docker compose down -v
docker compose up -d

Or use the helper script:

./scripts/reset-db.ps1

Reload CSV data manually without restarting the stack:

./scripts/reload-data.ps1

Create a local database backup:

./scripts/backup-db.ps1

Backups are written to backups/ as timestamped .backup files. The backups/ directory is ignored by Git.

3. Open PostgreSQL

docker compose exec postgres psql -U ${POSTGRES_USER} -d ${POSTGRES_DB}

Or use pgAdmin:

  • URL: http://localhost:5050
  • Email: value from PGADMIN_EMAIL
  • Password: value from PGADMIN_PASSWORD

For pgAdmin server registration:

  • Host: postgres
  • Port: 5432
  • Username: value from POSTGRES_USER
  • Password: value from POSTGRES_PASSWORD
  • Database: value from POSTGRES_DB

Loaded Tables

The startup flow works like this:

Loaded tables:

  • periode
  • superstore

You can verify the tables:

\dt

And inspect row counts:

SELECT COUNT(*) FROM periode;
SELECT COUNT(*) FROM superstore;

Dataset Overview

periode

This is a date dimension table with one row per day and columns such as:

  • date_actual
  • day_name
  • week_of_year
  • month_actual
  • month_name
  • quarter_actual
  • year_actual
  • weekend_flag

superstore

This is a sales dataset with order, customer, geography, product, and financial fields such as:

  • order_id
  • order_date
  • ship_date
  • ship_mode
  • customer_name
  • segment
  • country
  • city
  • state
  • region
  • category
  • sub_category
  • product_name
  • sales
  • quantity
  • discount
  • profit

Suggested Learning Order

  1. Start with 01-novice.sql, then attempt 01-novice-exercises.sql, and compare with 01-novice-solutions.sql.
  2. Move to 02-intermediate.sql, then complete 02-intermediate-exercises.sql, and review 02-intermediate-solutions.sql.
  3. Continue with 03-advanced.sql, then solve 03-advanced-exercises.sql, and check 03-advanced-solutions.sql.
  4. Study 04-database-objects.sql, then attempt 04-database-objects-exercises.sql, and compare with 04-database-objects-solutions.sql.
  5. Practice schema changes and data modification in 05-ddl-dml.sql, then complete 05-ddl-dml-exercises.sql, and review 05-ddl-dml-solutions.sql.
  6. Learn plan reading and index-aware tuning with 06-performance.sql, then work through 06-performance-exercises.sql and check 06-performance-solutions.sql.
  7. Learn how PostgreSQL stores and interprets values with 07-data-types.sql, then complete 07-data-types-exercises.sql and review 07-data-types-solutions.sql.
  8. Explore PostgreSQL-only query features in 08-postgresql-features.sql, then try 08-postgresql-features-exercises.sql and compare with 08-postgresql-features-solutions.sql.
  9. Strengthen relational thinking with 09-joins.sql, then solve 09-joins-exercises.sql and check 09-joins-solutions.sql.
  10. Practice query decomposition in 10-subqueries.sql, then work through 10-subqueries-exercises.sql and compare with 10-subqueries-solutions.sql.
  11. Study constraints and table relationships in 11-schema-design.sql, then attempt 11-schema-design-exercises.sql and review 11-schema-design-solutions.sql.
  12. Learn transaction safety with 12-transactions.sql, then complete 12-transactions-exercises.sql and compare with 12-transactions-solutions.sql.

Notes

  • Initialization scripts in init-db/ only run when PostgreSQL creates a fresh database volume.
  • If you want to rerun the CSV load manually without restarting the stack, use reload-data.ps1.
  • If you want a completely fresh database volume and startup flow, use reset-db.ps1.
  • If you change 01-load-data.sql, use docker compose down -v before starting again.
  • The table name is periode, while the source CSV file is period.csv.
  • 07-data-types.sql enables the pgcrypto extension so gen_random_uuid() works on a fresh database.

Additional Resources