Phase Project: Explore a Bookstore Database — SQL Course

What you'll learn

Combine foundational SELECT skills to answer a structured set of bookstore questions and present reproducible findings. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Requirements to queries to a realistic data question
Apply Data exploration to a realistic data question
Apply Filters and expressions to a realistic data question
Apply NULL handling to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
Question inventory	A prioritized list of stakeholder questions	Translate each question into result grain and required evidence
Exploration query	A query testing data shape or quality	Keep discovery separate from final report SQL
Reproducibility	Another analyst can rerun the same work	Save setup, queries, assumptions, and expected checks together

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the bookstore exploration report question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Create an auditable report query

A comment states the grain and deterministic ordering makes the top list reproducible.

report_top_books.sql

-- Grain: one active book; purpose: five newest database titles.
SELECT id, title, price, published_at
FROM course.books
WHERE category = 'Database' AND discontinued_at IS NULL
ORDER BY published_at DESC, id DESC
FETCH FIRST 5 ROWS ONLY;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

A collection of correct-looking queries is not a project until assumptions, result grain, edge cases, and conclusions are documented.

Independent workshop

Deliver a ten-question bookstore exploration workbook and a one-page findings summary.

Your finished workshop must include:

Requirements to queries
Data exploration
Filters and expressions
NULL handling
Top-N results
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

Question inventory: Translate each question into result grain and required evidence
Exploration query: Keep discovery separate from final report SQL
Reproducibility: Save setup, queries, assumptions, and expected checks together

Quick check

1. Which rule best applies to Question inventory?

2. Which rule best applies to Exploration query?

3. Which rule best applies to Reproducibility?

Next: Schemas, CREATE TABLE & Type Design