SQL Practice Sandbox

SQL Migrations and Queries

Explore a small SQLite database, run queries, and study how the schema evolves across migrations.

SQL Playground

Run queries against the in memory database built from the migrations below.

Loading SQLite engine

Query editor

Results

Run a query to see results.

Practice prompts

Basics

List all books with their authors and review counts

Filtering

Find books published after 2010 that have at least one review with rating 4 or higher

Modeling

Add a migration that introduces a read_status column with a default of unread

Data move

Mark every book with rating 4 or higher as done

Joins

List each category plus the titles in it ordered by category then title

Introspection

Check foreign keys and indexes using PRAGMA

Migrations

These files build the database in order. Each one is shown in full so you can read and edit them.

001_create_books.sql

Create books table and seed starter rows

BEGIN;

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_name TEXT NOT NULL,
  year_published INTEGER
);

INSERT INTO books (title, author_name, year_published) VALUES
  ('Practical SQL', 'Anthony DeBarros', 2018),
  ('SQL Cookbook', 'Anthony Molinaro', 2005),
  ('Database Design for Mere Mortals', 'Michael Hernandez', 1996),
  ('Seven Databases in Seven Weeks', 'Luc Perkins', 2017);

COMMIT;

002_normalize_authors.sql

Normalize authors into a separate table

BEGIN;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO authors (name)
SELECT DISTINCT author_name FROM books;

CREATE TABLE books_new (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors(id),
  year_published INTEGER
);

INSERT INTO books_new (id, title, author_id, year_published)
SELECT b.id, b.title, a.id, b.year_published
FROM books b
JOIN authors a ON a.name = b.author_name;

DROP TABLE books;
ALTER TABLE books_new RENAME TO books;

CREATE INDEX idx_books_author_id ON books(author_id);

COMMIT;

003_add_reviews.sql

Add reviews table with constraints and seed data

BEGIN;

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books(id),
  reviewer TEXT NOT NULL,
  rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
  note TEXT,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO reviews (book_id, reviewer, rating, note) VALUES
  (1, 'Jordan', 5, 'Great starter for analytics work'),
  (2, 'Alex', 4, 'Covers many patterns in short form'),
  (3, 'Riley', 4, 'Good for design fundamentals');

CREATE INDEX idx_reviews_book_id ON reviews(book_id);

COMMIT;

004_add_categories.sql

Add categories and join table for many to many

BEGIN;

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE book_categories (
  book_id INTEGER NOT NULL REFERENCES books(id),
  category_id INTEGER NOT NULL REFERENCES categories(id),
  PRIMARY KEY (book_id, category_id)
);

INSERT INTO categories (name) VALUES
  ('SQL'),
  ('Data Design'),
  ('Reference'),
  ('Hands on');

INSERT INTO book_categories (book_id, category_id)
SELECT b.id, c.id
FROM books b
JOIN categories c
  ON (b.title LIKE '%SQL%' AND c.name = 'SQL')
  OR (b.title LIKE '%Database%' AND c.name = 'Data Design')
  OR (b.title LIKE '%Cookbook%' AND c.name = 'Reference')
  OR (b.title LIKE '%Seven Databases%' AND c.name = 'Hands on');

COMMIT;

How to use this lab

Start with the starter query, then change it and run again.

Use the practice prompts to guide your next steps.

Reset the database at any time to replay the migrations.