Build a Bookstore Database with SQL

Design and query a multi-table SQLite database from scratch.

Introduction

30 Second Summary

Every app you use daily stores and retrieves information from a database. From online bookstores tracking orders to streaming services remembering your watchlist, SQL is the language that makes it all work.

In this project, you will build a bookstore database from scratch using SQL. You'll design tables, populate them with data, and write increasingly powerful queries to answer real business questions.

What You'll Build

A multi-table SQLite database representing a bookstore, complete with sample data and analytical queries that extract business insights.

By the end of this project, you'll have:

  • A working SQLite database with four related tables representing authors, books, customers, and orders.
  • A collection of SQL queries that filter, sort, and combine data from multiple tables.
  • Hands-on experience with JOINs and aggregate functions to answer questions like "Which customer spent the most?" and "What genre generates the most revenue?".
  • Secret Mission: Add a reviews table and write queries to find the highest-rated books and most active reviewers.

Are there any prerequisites?

None. This project includes full setup instructions for everything you need. You'll install SQLite as part of the first step.

Before We Start

Before We Start...

Install SQLite

Before you can build a database, you need a tool to talk to it. SQLite is a lightweight database engine that stores everything in a single file on your computer.

The tool you'll use to interact with SQLite is a command-line program called sqlite3. In this step, you'll install it, create your project folder, and open your first database.

In this step, get ready to:
  • Install SQLite and verify it works.
  • Create your project directory and open a new database.
  • Explore the SQLite shell with dot-commands.
Install SQLite on your machine

SQLite is a single binary with no server to configure. On macOS it comes pre-installed. On Windows and Linux, you'll install it with a quick command.

  • Open your terminal. Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Check if SQLite is already installed by running this command:
sqlite3 --version

✔️ I see a version number

SQLite is ready to go. You can move on to the next substep.

ⓧ Command not found

You need to install SQLite. Follow the instructions for your operating system below.

macOS

SQLite comes pre-installed on macOS. If you're seeing this error, your PATH may be misconfigured. Try running the full path directly:

/usr/bin/sqlite3 --version

If that works, you can use /usr/bin/sqlite3 in place of sqlite3 for the rest of this project.

Windows

  • Install SQLite using Windows Package Manager by running this command in PowerShell:
winget install --id SQLite.SQLite
What does this command do?

This downloads the SQLite command-line tools and adds sqlite3 to your system PATH so you can run it from any terminal window.

  • Close and reopen PowerShell after the install finishes.
  • Run sqlite3 --version again to confirm the install worked.
Winget not recognized?

If winget is not recognized, you can download SQLite directly from the official website. Visit sqlite.org/download.html, download the sqlite-tools-win-x64 zip file, extract it, and add the folder to your system PATH.

Help me install SQLite on Windows without winget.

Linux

  • Install SQLite using your package manager by running this command:
sudo apt install sqlite3

On Fedora or RHEL-based distributions, use sudo dnf install sqlite instead.

  • Run sqlite3 --version again to confirm the install worked.

Why SQLite?

SQLite requires zero server setup. You install a single binary and start writing SQL immediately.

The core SQL you learn here (SELECT, WHERE, JOIN, GROUP BY) transfers directly to PostgreSQL, MySQL, and SQL Server.

Create your project directory and open the database

Every project needs a home folder. You'll create one on your Desktop so it's easy to find.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir bookstore
cd bookstore
  • Confirm the folder exists by running:
pwd

You should see a path ending in bookstore, confirming you're in the right place.

  • Create and open your new SQLite database by running this command:
sqlite3 bookstore.db

What does this command do?

Running sqlite3 bookstore.db does two things at once. It creates a new file called bookstore.db if one doesn't exist, and opens an interactive shell connected to that database.

You'll know it worked because your terminal prompt changes to sqlite>, meaning SQLite is ready for commands.

Your terminal prompt should now show sqlite> instead of your normal shell prompt.

Still seeing your normal prompt?

Make sure you typed the command exactly as shown, including the space between sqlite3 and bookstore.db. If you see an error about the command not being found, go back to the install step above.

Help me troubleshoot opening my SQLite database.

Explore the SQLite shell

The SQLite shell has two types of commands. SQL statements end with a semicolon. Dot-commands start with a period and control the shell itself rather than the database.

  • See the full list of available dot-commands by typing:
.help

You'll see a long list of commands scroll by. You don't need to memorize them. The important ones for this project are .tables, .schema, and .quit.

  • Exit the SQLite shell by typing:
.quit

Your terminal prompt returns to normal, meaning you've left the SQLite shell.

  • Re-open the database to confirm it persists by running:
sqlite3 bookstore.db

You're back at the sqlite> prompt. The database file is saved on disk and survives between sessions.

Why does the database persist?

SQLite stores everything in a single file (bookstore.db). When you quit and re-open, you're reconnecting to the same file. This is different from in-memory databases, which are deleted when the session ends.

SQL statements need semicolons

Dot-commands like .help and .quit do not need semicolons. But every SQL statement you write (like SELECT, INSERT, CREATE TABLE) must end with a semicolon.

If you press Enter and see ...> instead of results, SQLite is waiting for you to finish the statement with ;.

Your SQLite environment is ready and your empty database is waiting for tables. Next up, you'll design your bookstore schema and create the tables that hold your data.

Create Your Tables

In the last step, you installed SQLite and created your database file. That's the container for your data, but right now it's completely empty.

Tables define the structure of your data. Think of each table as a spreadsheet with fixed column headers. In this step, you'll design four tables that represent a real bookstore: authors, books, customers, and orders.

In this step, get ready to:
  • Create the authors and books tables.
  • Create the customers and orders tables.
  • Verify your schema with SQLite dot-commands.
Create the authors and books tables

The foundation of any database is its schema. The schema defines what tables exist and what columns each table has. You'll write your schema as a SQL file so you can re-run it anytime.

  • Exit the SQLite shell so you can create a SQL file by running this command:
.quit
  • Create a new file called schema.sql in your bookstore project directory (the same folder where bookstore.db lives).
  • Open schema.sql in your preferred text editor.
  • Add the following SQL to create the authors and books tables:
-- Create the authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

-- Create the books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    genre TEXT,
    price REAL,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

What does this SQL do?

CREATE TABLE defines a new table with specific columns. Each column has a name and a data type.

  • INTEGER PRIMARY KEY makes the id column a unique identifier for each row. SQLite auto-increments this value when you add new rows.
  • TEXT NOT NULL means the column stores text and cannot be left empty.
  • REAL stores decimal numbers (perfect for prices like 12.99).
  • FOREIGN KEY (author_id) REFERENCES authors(id) creates a relationship between tables. Every author_id in the books table must match an id in the authors table.

What is a foreign key?

A foreign key is a column that points to a row in another table. It's how relational databases connect data across tables.

In your bookstore, each book has an author_id that links it to a specific author. This avoids duplicating author information inside every book row.

  • Save schema.sql.

You won't see a change in your database yet. The file just holds the instructions. You'll run it after adding the remaining tables.

Add the customers and orders tables

The customers and orders tables complete the bookstore model. Orders connect customers to books. This is the "relational" heart of your database.

  • Below the books table in schema.sql, add the following SQL:
-- Create the customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT
);

-- Create the orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

What's new in these tables?

  • TEXT UNIQUE on the email column means no two customers can share the same email address. The database rejects duplicate values automatically.
  • The orders table has two foreign keys: customer_id points to customers and book_id points to books. Each order records who bought what.
  • NOT NULL on quantity and order_date means every order must have both values. An order without a quantity or date would be meaningless.
  • Save schema.sql.

Your schema file is complete with all four tables. Next, you'll feed it to SQLite.

✔️ Awesome, I've got everything!

Great. Double check you've saved schema.sql and move on to the next substep.

ⓧ I'd like to double check the full code

Here's the complete schema.sql file with all four tables:

-- Create the authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

-- Create the books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    genre TEXT,
    price REAL,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- Create the customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT
);

-- Create the orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);
Run the schema and verify your tables

Your SQL file contains the instructions. Now you'll tell SQLite to execute them. The .read dot-command runs every SQL statement in a file.

  • Re-open your database by running this command in your terminal:
sqlite3 bookstore.db
  • Run your schema file by entering this inside the SQLite shell:
.read schema.sql

If everything worked, SQLite prints nothing. Silence means success.

  • List all tables in the database by running:
.tables

You should see all four table names:

authors     books       customers   orders
  • View the full schema definition by running:
.schema

You should see all four CREATE TABLE statements printed back, matching exactly what you wrote in schema.sql.

Seeing "table already exists" errors?

This means you ran .read schema.sql more than once. The tables were already created on the first run.

  • If you need to start fresh, exit the shell with .quit, delete bookstore.db, then re-create it with sqlite3 bookstore.db and run .read schema.sql again.
  • Check that your schema.sql file is in the same directory where you ran sqlite3 bookstore.db. If not, SQLite cannot find the file.

help me fix my schema.sql errors in SQLite

Your database structure is in place. Next up, you'll fill these tables with bookstore data so you can start querying.

Populate Your Database

Your bookstore database has four tables, but they're all empty. A database without data is like a library with empty shelves.

In this step, you'll fill your tables with realistic sample data using INSERT INTO statements. You'll add authors, books, customers, and orders that simulate a real bookstore.

In this step, get ready to:
  • Insert authors and books into the database.
  • Insert customers and orders into the database.
  • Verify all data loaded correctly.
Insert authors and books

You'll store all your INSERT statements in a file called seed_data.sql. This keeps your data separate from your schema and makes it easy to re-run if needed.

  • Create a new file called seed_data.sql in the same folder as your schema.sql file. Open it in your text editor.
  • Copy the following SQL into seed_data.sql:
-- Insert authors
INSERT INTO authors (id, name, country) VALUES (1, 'George Orwell', 'United Kingdom');
INSERT INTO authors (id, name, country) VALUES (2, 'Haruki Murakami', 'Japan');
INSERT INTO authors (id, name, country) VALUES (3, 'Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (id, name, country) VALUES (4, 'Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (id, name, country) VALUES (5, 'Toni Morrison', 'United States');

-- Insert books
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (1, '1984', 1, 'Dystopian', 12.99, 1949);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (2, 'Animal Farm', 1, 'Satire', 9.99, 1945);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (3, 'Norwegian Wood', 2, 'Literary Fiction', 14.99, 1987);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (4, 'Kafka on the Shore', 2, 'Magical Realism', 16.99, 2002);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (5, 'Half of a Yellow Sun', 3, 'Historical Fiction', 15.99, 2006);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (6, 'Americanah', 3, 'Literary Fiction', 14.49, 2013);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (7, 'One Hundred Years of Solitude', 4, 'Magical Realism', 13.99, 1967);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (8, 'Love in the Time of Cholera', 4, 'Romance', 12.49, 1985);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (9, 'Beloved', 5, 'Historical Fiction', 14.99, 1987);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (10, 'Song of Solomon', 5, 'Literary Fiction', 13.49, 1977);

What does this code do?

Each INSERT INTO statement adds one row to a table. The syntax is: table name, column list in parentheses, then the values in the same order.

Notice how the author_id column in the books section matches an id from the authors table. This is how foreign keys work. Each book points back to the author who wrote it.

  • Save seed_data.sql.
  • You won't see a change in the database yet. The next substep adds customers and orders before you run the file.

Seeing a syntax error when you save?

Make sure every statement ends with a semicolon. Also check that string values (like author names) are wrapped in single quotes, not double quotes.

help me find the syntax error in my INSERT INTO statements

Insert customers and orders

Now you'll add the people who buy books and their purchase records. Orders link customers to the books they purchased.

  • Add the following SQL to the bottom of seed_data.sql, below the books section:
-- Insert customers
INSERT INTO customers (id, name, email, city) VALUES (1, 'Alice Chen', 'alice@email.com', 'Toronto');
INSERT INTO customers (id, name, email, city) VALUES (2, 'Bob Martinez', 'bob@email.com', 'Mexico City');
INSERT INTO customers (id, name, email, city) VALUES (3, 'Carol Williams', 'carol@email.com', 'London');
INSERT INTO customers (id, name, email, city) VALUES (4, 'David Kim', 'david@email.com', 'Seoul');
INSERT INTO customers (id, name, email, city) VALUES (5, 'Emma Johansson', 'emma@email.com', 'Stockholm');
INSERT INTO customers (id, name, email, city) VALUES (6, 'Frank Obi', 'frank@email.com', 'Lagos');

-- Insert orders
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (1, 1, 1, 2, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (2, 1, 5, 1, '2024-02-20');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (3, 2, 3, 1, '2024-01-22');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (4, 2, 7, 3, '2024-03-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (5, 3, 1, 1, '2024-02-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (6, 3, 9, 2, '2024-03-18');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (7, 4, 4, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (8, 4, 6, 1, '2024-04-02');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (9, 5, 2, 2, '2024-02-14');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (10, 5, 10, 1, '2024-03-25');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (11, 6, 5, 1, '2024-04-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (12, 6, 8, 2, '2024-04-15');

What does this code do?

The customers table stores each person's name, email, and city. The email column has a UNIQUE constraint, so no two customers can share the same email.

Each order row uses customer_id and book_id to connect a customer to the book they purchased. The quantity column tracks how many copies were bought in that order.

  • Save seed_data.sql.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

-- Insert authors
INSERT INTO authors (id, name, country) VALUES (1, 'George Orwell', 'United Kingdom');
INSERT INTO authors (id, name, country) VALUES (2, 'Haruki Murakami', 'Japan');
INSERT INTO authors (id, name, country) VALUES (3, 'Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (id, name, country) VALUES (4, 'Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (id, name, country) VALUES (5, 'Toni Morrison', 'United States');

-- Insert books
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (1, '1984', 1, 'Dystopian', 12.99, 1949);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (2, 'Animal Farm', 1, 'Satire', 9.99, 1945);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (3, 'Norwegian Wood', 2, 'Literary Fiction', 14.99, 1987);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (4, 'Kafka on the Shore', 2, 'Magical Realism', 16.99, 2002);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (5, 'Half of a Yellow Sun', 3, 'Historical Fiction', 15.99, 2006);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (6, 'Americanah', 3, 'Literary Fiction', 14.49, 2013);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (7, 'One Hundred Years of Solitude', 4, 'Magical Realism', 13.99, 1967);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (8, 'Love in the Time of Cholera', 4, 'Romance', 12.49, 1985);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (9, 'Beloved', 5, 'Historical Fiction', 14.99, 1987);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (10, 'Song of Solomon', 5, 'Literary Fiction', 13.49, 1977);

-- Insert customers
INSERT INTO customers (id, name, email, city) VALUES (1, 'Alice Chen', 'alice@email.com', 'Toronto');
INSERT INTO customers (id, name, email, city) VALUES (2, 'Bob Martinez', 'bob@email.com', 'Mexico City');
INSERT INTO customers (id, name, email, city) VALUES (3, 'Carol Williams', 'carol@email.com', 'London');
INSERT INTO customers (id, name, email, city) VALUES (4, 'David Kim', 'david@email.com', 'Seoul');
INSERT INTO customers (id, name, email, city) VALUES (5, 'Emma Johansson', 'emma@email.com', 'Stockholm');
INSERT INTO customers (id, name, email, city) VALUES (6, 'Frank Obi', 'frank@email.com', 'Lagos');

-- Insert orders
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (1, 1, 1, 2, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (2, 1, 5, 1, '2024-02-20');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (3, 2, 3, 1, '2024-01-22');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (4, 2, 7, 3, '2024-03-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (5, 3, 1, 1, '2024-02-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (6, 3, 9, 2, '2024-03-18');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (7, 4, 4, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (8, 4, 6, 1, '2024-04-02');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (9, 5, 2, 2, '2024-02-14');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (10, 5, 10, 1, '2024-03-25');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (11, 6, 5, 1, '2024-04-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (12, 6, 8, 2, '2024-04-15');

Mismatched IDs?

The customer_id and book_id values in the orders must match existing IDs in the customers and books tables. If you changed any IDs in the earlier sections, update your orders to match.

help me check that my foreign key IDs are consistent

Run the seed file and verify your data

Your seed file is complete. Now you'll load it into your database and confirm everything landed correctly.

  • Switch back to your terminal with the SQLite shell open. Run this command to load all the data:
.read seed_data.sql

What does this command do?

The .read dot-command tells SQLite to execute every SQL statement in the file, one after another. It's like typing each INSERT manually, but much faster.

  • Verify your books loaded by running:
SELECT * FROM books;

You should see 10 rows, one for each book with its title, author_id, genre, price, and published year.

  • Verify your orders loaded by running:
SELECT * FROM orders;

You should see 12 rows showing customer purchases across different books and quantities.

  • Confirm the exact row count by running:
SELECT COUNT(*) FROM orders;

You should see 12 as the result.

Seeing "no such table" or "UNIQUE constraint failed"?

If you see a "no such table" error, make sure you ran .read schema.sql in the previous step to create your tables first.

If you see a "UNIQUE constraint failed" error, you may have run .read seed_data.sql more than once. Each INSERT uses a specific ID, so running it twice tries to add duplicate rows. To fix this, delete the data first by running DELETE FROM orders; DELETE FROM customers; DELETE FROM books; DELETE FROM authors; then run .read seed_data.sql again.

help me fix my seed data errors

Your bookstore is stocked and ready for business. Next up, you'll write queries to search, filter, and sort this data.

Query with SELECT, WHERE, and ORDER BY

In the previous step, you populated your database with authors, books, customers, and orders. Now you have a rich dataset sitting in four tables, ready to answer questions.

The real power of a database comes from querying it. In this step, you'll learn how to retrieve exactly the data you need using SELECT, filter it with WHERE, and sort it with ORDER BY.

In this step, get ready to:
  • Filter data using WHERE with comparison and logical operators.
  • Sort and limit results with ORDER BY and LIMIT.
  • Use pattern matching with LIKE, IN, and BETWEEN.
Retrieve and filter data with SELECT and WHERE

SELECT is how you ask a database for data. WHERE lets you narrow the results down to only the rows that match your conditions.

  • In your sqlite3 shell (which should still be open with bookstore.db), retrieve all books by running this query:
-- All books
SELECT * FROM books;

What does this query do?

SELECT * means "give me every column." FROM books tells the database which table to look in. Together, this retrieves every row and every column from the books table.

You should see all 10 books printed in your terminal with their id, title, author_id, genre, price, and published_year.

  • Now filter the results to only show books in a specific genre, books above a certain price, and a combination of both by running these queries:
-- Books in the 'Literary Fiction' genre
SELECT title, price FROM books WHERE genre = 'Literary Fiction';

-- Books over $15
SELECT title, price FROM books WHERE price > 15.00;

-- Books that are Literary Fiction AND over $14
SELECT title, genre, price FROM books WHERE genre = 'Literary Fiction' AND price > 14.00;

What does WHERE do here?

  • genre = 'Literary Fiction' checks for an exact text match. Only rows where the genre column equals that string are returned.
  • price > 15.00 is a comparison operator. You can also use <, >=, <=, and !=.
  • AND combines two conditions so both must be true. You can also use OR when you want rows that match either condition.

The first query should return 3 books (Norwegian Wood, Americanah, Song of Solomon). The second should return 2 books priced above $15. The third narrows Literary Fiction to only those over $14.

Seeing an error or no results?

  • Make sure each query ends with a semicolon. Without it, SQLite waits for more input (you'll see a ...> prompt instead of results).
  • Check that string values are wrapped in single quotes ('Literary Fiction'), not double quotes.
  • If you're stuck at the ...> prompt, type a semicolon and press Enter to finish the statement.

Help me debug my WHERE clause query

Sort and limit results with ORDER BY and LIMIT

Filtering narrows which rows you see. Sorting controls the order those rows appear in.

  • Sort all books by price from most expensive to cheapest by running this query:
-- Books sorted by price (most expensive first)
SELECT title, price FROM books ORDER BY price DESC;

What does ORDER BY do?

ORDER BY price DESC sorts results by the price column in descending order (highest first). Use ASC for ascending (lowest first). If you leave out DESC/ASC, SQL defaults to ascending.

You should see all 10 books listed with Kafka on the Shore ($16.99) at the top and Animal Farm ($9.99) at the bottom.

  • Get only the top 3 most expensive books by adding LIMIT to your query:
-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

What does LIMIT do?

LIMIT 3 caps the results at 3 rows. Combined with ORDER BY price DESC, this gives you the top 3 most expensive books. This is how "top N" queries work in practice.

You should see exactly 3 rows: Kafka on the Shore, Half of a Yellow Sun, and either Norwegian Wood or Beloved (both are $14.99).

Results not sorted correctly?

  • Make sure ORDER BY comes before LIMIT in your query. SQL clauses must follow a specific order: SELECT, FROM, WHERE, ORDER BY, LIMIT.
  • Check you typed DESC (not DSC or DESCENDING).

Help me fix my ORDER BY query

Use LIKE, IN, and BETWEEN for flexible filtering

Sometimes you need more flexible matching than exact equals or greater-than. SQL provides pattern matching operators for these situations.

  • Find books with "the" anywhere in the title by running this query:
-- Books with "the" in the title (case-insensitive)
SELECT title FROM books WHERE title LIKE '%the%';

What does LIKE do?

LIKE performs pattern matching. The % symbol is a wildcard that matches any number of characters. So '%the%' matches any title that contains "the" anywhere in it.

You should see titles like "Kafka on the Shore" and "Love in the Time of Cholera" in the results.

  • Find books that belong to a specific list of genres using IN, and books published within a date range using BETWEEN:
-- Books in specific genres
SELECT title, genre FROM books WHERE genre IN ('Magical Realism', 'Dystopian');

-- Books published between 1980 and 2010
SELECT title, published_year FROM books WHERE published_year BETWEEN 1980 AND 2010;

What do IN and BETWEEN do?

  • IN ('Magical Realism', 'Dystopian') checks if a value matches any item in the list. It is a cleaner alternative to writing multiple OR conditions.
  • BETWEEN 1980 AND 2010 matches values within a range, inclusive of both endpoints. It works with numbers, dates, and text.

The IN query should return 3 books (1984, Kafka on the Shore, One Hundred Years of Solitude). The BETWEEN query should return books published from 1980 to 2010.

Getting no results from LIKE?

  • Make sure the % wildcards are inside the quotes: '%the%' not %'the'%.
  • For IN, each value in the list needs its own set of single quotes: IN ('Magical Realism', 'Dystopian').

Help me troubleshoot my LIKE or IN query

  • Now save all the queries you've written to a file. Exit the SQLite shell by running:
.quit
  • Create a queries.sql file in your project folder. Open it in your text editor and paste the following:
-- ============================================
-- STEP 4: SELECT, WHERE, and ORDER BY
-- ============================================

-- All books
SELECT * FROM books;

-- Books in the 'Literary Fiction' genre
SELECT title, price FROM books WHERE genre = 'Literary Fiction';

-- Books over $15
SELECT title, price FROM books WHERE price > 15.00;

-- Books that are Literary Fiction AND over $14
SELECT title, genre, price FROM books WHERE genre = 'Literary Fiction' AND price > 14.00;

-- Books sorted by price (most expensive first)
SELECT title, price FROM books ORDER BY price DESC;

-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

-- Books with "the" in the title (case-insensitive)
SELECT title FROM books WHERE title LIKE '%the%';

-- Books in specific genres
SELECT title, genre FROM books WHERE genre IN ('Magical Realism', 'Dystopian');

-- Books published between 1980 and 2010
SELECT title, published_year FROM books WHERE published_year BETWEEN 1980 AND 2010;

Why save queries to a file?

Saving your queries in a .sql file gives you a permanent reference you can rerun anytime. You'll keep adding to this file in the next steps as your queries get more complex.

  • Save queries.sql.
  • Re-open your database by running:
sqlite3 bookstore.db
  • Verify your file works by running it from the SQLite shell:
.read queries.sql

You should see all 9 query results printed one after another in your terminal.

✔️ Awesome, I've got everything!

Great. Make sure you've saved queries.sql and that .read queries.sql ran without errors.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file so far. Make sure yours matches exactly:

-- ============================================
-- STEP 4: SELECT, WHERE, and ORDER BY
-- ============================================

-- All books
SELECT * FROM books;

-- Books in the 'Literary Fiction' genre
SELECT title, price FROM books WHERE genre = 'Literary Fiction';

-- Books over $15
SELECT title, price FROM books WHERE price > 15.00;

-- Books that are Literary Fiction AND over $14
SELECT title, genre, price FROM books WHERE genre = 'Literary Fiction' AND price > 14.00;

-- Books sorted by price (most expensive first)
SELECT title, price FROM books ORDER BY price DESC;

-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

-- Books with "the" in the title (case-insensitive)
SELECT title FROM books WHERE title LIKE '%the%';

-- Books in specific genres
SELECT title, genre FROM books WHERE genre IN ('Magical Realism', 'Dystopian');

-- Books published between 1980 and 2010
SELECT title, published_year FROM books WHERE published_year BETWEEN 1980 AND 2010;

You can now retrieve, filter, and sort data from a single table. Next up, you'll combine data from multiple tables using JOINs to answer questions that span across your authors, books, customers, and orders.

Combine Tables with JOIN

Your filtering and sorting skills are solid. You can find any book in the database by genre, price, or publication year.

But here's the problem: your data lives in separate tables. The books table has an author_id number, not the author's name. The orders table has a customer_id, not the customer's name. To answer questions like "who bought what?", you need to combine tables.

That's exactly what a JOIN does. It links rows from two or more tables using a shared column, so you can pull meaningful information across your entire database in a single query.

In this step, get ready to:
  • Write an INNER JOIN to show each book alongside its author's name.
  • Write a multi-table JOIN connecting orders, books, and customers.
  • Use a LEFT JOIN to find authors who might have no books in the database.
Join books to their authors with INNER JOIN

An INNER JOIN returns only rows where the linked columns match in both tables. If a book has an author_id that corresponds to an id in the authors table, that pair shows up in the results.

  • Add the Step 5 section header and your first JOIN query to queries.sql:
-- ============================================
-- STEP 5: JOINs
-- ============================================

-- Books with their author names (INNER JOIN)
SELECT books.title, authors.name AS author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

What does this query do?

The FROM books clause starts with the books table. INNER JOIN authors tells SQL to combine it with the authors table.

The ON books.author_id = authors.id clause is the link. It tells SQL which rows to match: pair every book with the author whose id equals the book's author_id.

The AS author_name part renames the output column so the results are easier to read.

  • Save queries.sql.
  • Switch back to the SQLite shell and run the query by pasting it in:
SELECT books.title, authors.name AS author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

You should see 10 rows listing every book alongside its author's name. For example, 1984|George Orwell and Americanah|Chimamanda Ngozi Adichie.

Seeing an error about no such column?

Make sure you're using the table.column format (e.g., books.title, not just title). When two tables are joined, SQL needs to know which table a column belongs to.

Also check that the ON clause uses a single equals sign (=), not a double (==).

My INNER JOIN query isn't working. The error says no such column.

Connect three tables with a multi-table JOIN

A single JOIN links two tables. But real-world questions often span more. "Who bought what?" requires data from three tables: customers (the who), books (the what), and orders (the link between them).

  • Add this multi-table JOIN query to queries.sql below your first JOIN:
-- Orders with book titles and customer names (multi-table JOIN)
SELECT customers.name AS customer, books.title AS book, orders.quantity, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id;

What does this query do?

This query starts from the orders table and chains two JOINs. The first JOIN links orders.customer_id to customers.id to get the customer's name. The second links orders.book_id to books.id to get the book title.

Each INNER JOIN adds one more table to the result. You can chain as many as you need to answer the question at hand.

  • Save queries.sql.
  • Run the query in your SQLite shell:
SELECT customers.name AS customer, books.title AS book, orders.quantity, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id;

You should see 12 rows showing every order with human-readable names. For example, Alice Chen|1984|2|2024-01-15 tells you Alice bought 2 copies of 1984 on January 15th.

Getting fewer than 12 rows?

Check that both ON clauses reference the correct columns. orders.customer_id = customers.id and orders.book_id = books.id must match exactly. Swapping the column names produces empty or incorrect results.

My multi-table JOIN returns fewer rows than expected or shows wrong data.

Use LEFT JOIN to find unmatched rows

INNER JOIN only shows rows that match in both tables. If an author had no books, they would disappear from the results entirely. A LEFT JOIN keeps every row from the left table (the one after FROM), filling in NULL where there's no match.

  • Add this LEFT JOIN query to queries.sql below your multi-table JOIN:
-- All authors and their books (LEFT JOIN shows authors even with no books)
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id;

How is LEFT JOIN different from INNER JOIN?

With INNER JOIN, an author with zero books would not appear at all. With LEFT JOIN, that author still appears in the results, but the books.title column shows NULL.

Think of it this way: LEFT JOIN preserves the "left" table (authors) completely, regardless of whether matches exist in the "right" table (books).

  • Save queries.sql.
  • Run the query in your SQLite shell:
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id;

You should see 10 rows. In this database, every author has at least two books, so there are no NULL values. If you were to add an author with no books, they would still appear here with a blank title column.

When to use LEFT JOIN vs INNER JOIN

Use INNER JOIN when you only want rows with matches in both tables. Use LEFT JOIN when you want to keep everything from the first table and see where gaps exist. LEFT JOIN is especially useful for finding missing data, like customers who have never placed an order.

Seeing duplicate rows?

Duplicates are expected here. Each author appears once per book they wrote. George Orwell shows up twice because he has two books (1984 and Animal Farm). This is correct JOIN behavior.

My LEFT JOIN shows duplicate author names. Is that correct?

✔️ Awesome, I've got everything!

Great. Make sure you've saved queries.sql with all three JOIN queries.

ⓧ I'd like to double check the full code

Here's the complete queries.sql file including everything from Step 4 and Step 5:

-- ============================================
-- STEP 4: SELECT, WHERE, and ORDER BY
-- ============================================

-- All books
SELECT * FROM books;

-- Books in the 'Literary Fiction' genre
SELECT title, price FROM books WHERE genre = 'Literary Fiction';

-- Books over $15
SELECT title, price FROM books WHERE price > 15.00;

-- Books that are Literary Fiction AND over $14
SELECT title, genre, price FROM books WHERE genre = 'Literary Fiction' AND price > 14.00;

-- Books sorted by price (most expensive first)
SELECT title, price FROM books ORDER BY price DESC;

-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

-- Books with "the" in the title (case-insensitive)
SELECT title FROM books WHERE title LIKE '%the%';

-- Books in specific genres
SELECT title, genre FROM books WHERE genre IN ('Magical Realism', 'Dystopian');

-- Books published between 1980 and 2010
SELECT title, published_year FROM books WHERE published_year BETWEEN 1980 AND 2010;

-- ============================================
-- STEP 5: JOINs
-- ============================================

-- Books with their author names (INNER JOIN)
SELECT books.title, authors.name AS author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- Orders with book titles and customer names (multi-table JOIN)
SELECT customers.name AS customer, books.title AS book, orders.quantity, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id;

-- All authors and their books (LEFT JOIN shows authors even with no books)
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id;

You've unlocked one of the most powerful features in SQL. JOINs let you pull meaningful, connected data from across your entire database. Next up, you'll use aggregate functions and GROUP BY to turn all this raw data into real business insights.

Aggregate and Analyze

You can now pull data from multiple tables at once using JOINs. The next question is: what can you learn from all that connected data?

Aggregate functions let you summarize rows into single values. Instead of seeing every individual order, you can ask "how much total revenue did we make?" or "which customer spent the most?" These are the exact questions data analysts answer every day.

In this step, get ready to:
  • Use COUNT, SUM, and AVG to summarize your data.
  • Group rows with GROUP BY and filter groups with HAVING.
  • Answer real business questions by combining JOINs with aggregates.
Use COUNT, SUM, and AVG aggregate functions

Aggregate functions collapse many rows into a single result. They answer "how many", "how much total", and "what's the average" questions across your entire dataset.

  • In your queries.sql file, add the following section header and three aggregate queries below your Step 5 JOIN queries:
-- ============================================
-- STEP 6: Aggregates and GROUP BY
-- ============================================

-- Total number of orders
SELECT COUNT(*) AS total_orders FROM orders;

-- Total revenue
SELECT SUM(books.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id;

-- Average book price
SELECT AVG(price) AS average_price FROM books;

What do these functions do?

  • COUNT(*) counts the total number of rows in the table.
  • SUM(books.price * orders.quantity) multiplies each book's price by the quantity ordered, then adds all those values together. The JOIN connects orders to their book prices.
  • AVG(price) calculates the mean of all values in the price column.
  • AS gives the result column a readable name (like total_revenue) instead of showing the raw expression.
  • Save queries.sql.
  • Switch back to your SQLite shell and run each query. Try the total revenue query by pasting it in:
SELECT SUM(books.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id;

You should see a single number representing the total revenue from all orders in your bookstore.

Seeing an error or no results?

Make sure your query ends with a semicolon. If you see a continuation prompt (...>), SQLite is waiting for the semicolon to finish the statement.

If you get a "no such table" error, confirm you're in the right database by running .tables to list your tables.

Help me troubleshoot my aggregate query

Group data with GROUP BY and filter with HAVING

Aggregate functions on their own summarize the entire table into one row. GROUP BY splits your data into groups first, then applies the aggregate to each group separately.

  • Add this query to queries.sql to count how many books each author wrote:
-- Number of books per author
SELECT authors.name, COUNT(books.id) AS book_count
FROM authors
INNER JOIN books ON authors.id = books.author_id
GROUP BY authors.name;

What does GROUP BY do here?

GROUP BY authors.name creates one group for each unique author name. Then COUNT(books.id) counts how many books fall into each group.

Without GROUP BY, COUNT would just return one number for all books. With it, you get a count per author.

  • Run this query in your SQLite shell to see the results.
  • You should see each author's name with a count of 2 next to it (since each author has exactly 2 books in our data).
  • Add these two queries below to see total spending per customer and orders per genre:
-- Total spending per customer
SELECT customers.name, SUM(books.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
GROUP BY customers.name
ORDER BY total_spent DESC;

-- Orders per genre
SELECT books.genre, COUNT(orders.id) AS order_count
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY order_count DESC;

What's happening in these queries?

  • The spending query JOINs orders with customers and books, groups by customer name, then sums the cost of each customer's purchases.
  • The genre query JOINs orders with books, groups by genre, then counts how many orders each genre received.
  • ORDER BY total_spent DESC sorts results from highest to lowest so the top spender appears first.
  • Run both queries in your SQLite shell.
  • You should see customers ranked by total spending (Bob Martinez at the top with his 3-copy order) and genres ranked by order count.

Sometimes you only want to see groups that meet a certain condition. HAVING filters groups after aggregation, the same way WHERE filters individual rows before aggregation.

  • Add this query to show only authors who have more than 1 book:
-- Authors with more than 1 book (HAVING)
SELECT authors.name, COUNT(books.id) AS book_count
FROM authors
INNER JOIN books ON authors.id = books.author_id
GROUP BY authors.name
HAVING COUNT(books.id) > 1;

Why HAVING instead of WHERE?

WHERE runs before grouping. It filters individual rows. HAVING runs after grouping. It filters the aggregated results.

You cannot use WHERE COUNT(books.id) > 1 because the count does not exist yet when WHERE runs. That is why HAVING exists.

  • Save queries.sql and run the HAVING query in your SQLite shell.
  • You should see all 5 authors listed, since each author in our data has exactly 2 books.

Getting an error with HAVING?

HAVING must come after GROUP BY, not before it. Check that your query follows the order: SELECT, FROM, JOIN, GROUP BY, HAVING.

Help me fix my GROUP BY or HAVING query

Answer business questions with JOINs and aggregates

Now you have all the building blocks. This final set of queries combines JOINs, aggregates, GROUP BY, ORDER BY, and LIMIT to answer real business questions.

  • Add these three business queries to the bottom of queries.sql:
-- Top 3 best-selling books (by total quantity sold)
SELECT books.title, SUM(orders.quantity) AS total_sold
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.title
ORDER BY total_sold DESC
LIMIT 3;

-- Customer who spent the most
SELECT customers.name, SUM(books.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
GROUP BY customers.name
ORDER BY total_spent DESC
LIMIT 1;

-- Genre that generates the most revenue
SELECT books.genre, SUM(books.price * orders.quantity) AS genre_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY genre_revenue DESC
LIMIT 1;

What does each query answer?

  • Top 3 best-selling books: JOINs orders to books, groups by title, sums the quantity sold, sorts descending, and takes the top 3 with LIMIT.
  • Customer who spent the most: JOINs all three tables, groups by customer, sums their total spend, and takes the single highest with LIMIT 1.
  • Genre generating the most revenue: JOINs orders to books, groups by genre, sums revenue per genre, and returns the top one.

The pattern is the same each time: JOIN the relevant tables, GROUP BY what you want to compare, apply an aggregate to measure it, ORDER BY the aggregate descending, and LIMIT to the top results.

  • Save queries.sql.
  • Run each of the three queries in your SQLite shell.
  • For the top 3 best-selling query, you should see "One Hundred Years of Solitude" at the top (Bob ordered 3 copies).

Results look wrong or unexpected?

If the revenue numbers seem off, double-check that your SUM multiplies books.price * orders.quantity and not just books.price alone. Without the quantity multiplier, you'd count each order as a single-book purchase.

If you see all rows instead of just the top results, confirm that LIMIT appears at the very end of the query, after ORDER BY.

Help me debug my combined JOIN and aggregate query

✔️ Awesome, I've got everything!

Great. Double check you've saved queries.sql with all the aggregate and GROUP BY queries.

ⓧ I'd like to double check the full code

Here is the complete queries.sql file with all queries from Steps 4, 5, and 6:

-- ============================================
-- STEP 4: SELECT, WHERE, and ORDER BY
-- ============================================

-- All books
SELECT * FROM books;

-- Books in the 'Literary Fiction' genre
SELECT title, price FROM books WHERE genre = 'Literary Fiction';

-- Books over $15
SELECT title, price FROM books WHERE price > 15.00;

-- Books that are Literary Fiction AND over $14
SELECT title, genre, price FROM books WHERE genre = 'Literary Fiction' AND price > 14.00;

-- Books sorted by price (most expensive first)
SELECT title, price FROM books ORDER BY price DESC;

-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

-- Books with "the" in the title (case-insensitive)
SELECT title FROM books WHERE title LIKE '%the%';

-- Books in specific genres
SELECT title, genre FROM books WHERE genre IN ('Magical Realism', 'Dystopian');

-- Books published between 1980 and 2010
SELECT title, published_year FROM books WHERE published_year BETWEEN 1980 AND 2010;

-- ============================================
-- STEP 5: JOINs
-- ============================================

-- Books with their author names (INNER JOIN)
SELECT books.title, authors.name AS author_name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- Orders with book titles and customer names (multi-table JOIN)
SELECT customers.name AS customer, books.title AS book, orders.quantity, orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id;

-- All authors and their books (LEFT JOIN shows authors even with no books)
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id;

-- ============================================
-- STEP 6: Aggregates and GROUP BY
-- ============================================

-- Total number of orders
SELECT COUNT(*) AS total_orders FROM orders;

-- Total revenue
SELECT SUM(books.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id;

-- Average book price
SELECT AVG(price) AS average_price FROM books;

-- Number of books per author
SELECT authors.name, COUNT(books.id) AS book_count
FROM authors
INNER JOIN books ON authors.id = books.author_id
GROUP BY authors.name;

-- Total spending per customer
SELECT customers.name, SUM(books.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
GROUP BY customers.name
ORDER BY total_spent DESC;

-- Orders per genre
SELECT books.genre, COUNT(orders.id) AS order_count
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY order_count DESC;

-- Authors with more than 1 book (HAVING)
SELECT authors.name, COUNT(books.id) AS book_count
FROM authors
INNER JOIN books ON authors.id = books.author_id
GROUP BY authors.name
HAVING COUNT(books.id) > 1;

-- Top 3 best-selling books (by total quantity sold)
SELECT books.title, SUM(orders.quantity) AS total_sold
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.title
ORDER BY total_sold DESC
LIMIT 3;

-- Customer who spent the most
SELECT customers.name, SUM(books.price * orders.quantity) AS total_spent
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
GROUP BY customers.name
ORDER BY total_spent DESC
LIMIT 1;

-- Genre that generates the most revenue
SELECT books.genre, SUM(books.price * orders.quantity) AS genre_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY genre_revenue DESC
LIMIT 1;

Secret mission

Add Book Reviews

Your bookstore database tracks orders, but it has no way of knowing whether customers actually liked the books they bought. In this secret mission, you'll add a reviews system and write complex queries to uncover whether high ratings actually translate to more sales.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your project files for reference or delete them entirely.

Resources you used:

  • Project folder on your Desktop containing bookstore.db, schema.sql, seed_data.sql, queries.sql, and reviews.sql.
  • SQLite CLI (sqlite3) installed on your machine.

Keep everything running

No action needed. Choose this if you want to keep practising SQL or plan to extend your bookstore database further.

  • Your bookstore.db file and all SQL scripts stay on your Desktop, ready to reopen any time with sqlite3 bookstore.db.
  • SQLite remains installed on your machine. It takes minimal disk space and has no background processes.

Pause - I'll come back to this later

SQLite has no running processes to shut down. Your database is just a file on disk, so there is nothing to pause.

  • If you still have the SQLite shell open, type .quit to exit.
  • Your project folder stays on your Desktop. When you want to return, open your terminal, navigate to the folder, and run sqlite3 bookstore.db.

Delete - I don't want to use this again

Remove all project files from your machine. This deletes your database and all SQL scripts permanently.

  • If you still have the SQLite shell open, type .quit to exit.
  • Delete your project folder from the Desktop. This is the folder that contains bookstore.db and your SQL scripts.
cd ~/Desktop
rm -rf [[PROJECT_FOLDER="your project folder name"]]

On Windows, use this command instead:

cd ~\Desktop
rmdir /s /q [[PROJECT_FOLDER="your project folder name"]]
  • Confirm the folder is gone by running ls (macOS/Linux) or dir (Windows) on your Desktop. Your project folder should no longer appear.

Nice Work!

Nice Work!

You built a relational database from scratch and proved you can think in SQL. These are the exact skills hiring managers test for in data analyst, backend developer, and business intelligence roles.

You've learned how to:

  • Designed a multi-table relational schema with primary keys, foreign keys, and data constraints to model real-world relationships.
  • Combined data across tables using INNER JOIN and LEFT JOIN to answer questions no single table could.
  • Wrote aggregate queries with GROUP BY and HAVING to extract business insights like top-selling books and highest-spending customers.
  • Extended the database with a reviews table and wrote complex analytical queries correlating ratings with sales.

Ready to quiz yourself?