Build a Bookstore Database with SQL

Design and query a relational bookstore database using SQL.

Introduction

30 Second Summary

Every time you search for a book online, filter by genre, or see a "customers also bought" recommendation, a database is answering your question behind the scenes. Learning how to ask those questions yourself is one of the most in-demand skills in tech.

In this project, you will build a bookstore database from scratch, fill it with sample data, and write SQL queries that answer real business questions like "Which genre earns the most revenue?" and "Who are our top customers?"

What You'll Build

A fully functional relational database representing a bookstore, with linked tables for authors, books, customers, and orders.

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

  • A working SQLite database file with 4 related tables connected by primary and foreign keys.
  • A collection of 10+ SQL queries that filter, sort, join, and aggregate data to answer real business questions.
  • Hands-on experience with the core SQL operations that appear in every data job posting: SELECT, JOIN, WHERE, GROUP BY, and ORDER BY.
  • Secret Mission: build a book recommendation query that suggests titles based on what similar customers have purchased.

Are there any prerequisites?

None. This project starts from zero and includes full setup instructions for every tool you need. No prior database or programming experience is required.

Install DB Browser for SQLite

Before you can build a database, you need a tool that lets you create tables and run SQL queries.

In this step, you will install DB Browser for SQLite, a free visual tool that makes working with databases feel like using a spreadsheet. You will also create an empty database file that you will fill with bookstore data in the next steps.

In this step, get ready to:
  • Download and install DB Browser for SQLite.
  • Explore the main interface tabs.
  • Create your empty bookstore database file.
Download and install DB Browser for SQLite

DB Browser for SQLite is a free, open-source application that gives you a visual interface for creating and querying SQLite databases. Instead of typing commands into a terminal, you get a point-and-click interface with a built-in SQL editor.

  • Open your web browser and navigate to the DB Browser for SQLite download page:
https://sqlitebrowser.org/dl/

Windows

  • Scroll to the Windows section.
  • Click DB Browser for SQLite - Standard installer for 64-bit Windows to download the installer.
  • Once the download finishes, double-click the .msi file to run the installer.
  • Follow the installer prompts, accepting the default options, and click Finish when done.

macOS

  • Scroll to the macOS section.
  • Click DB Browser for SQLite (Universal) to download the .dmg file.
  • Once the download finishes, double-click the .dmg file to open it.
  • Drag the DB Browser for SQLite icon into your Applications folder.

What is SQLite?

SQLite is a lightweight database engine that stores your entire database in a single file. There is no server to install or configure.

The SQL you learn here transfers directly to larger databases like PostgreSQL and MySQL. The core commands (SELECT, JOIN, GROUP BY) work the same way everywhere.

Launch DB Browser and explore the interface
  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type DB Browser and press Enter to open it.

You should see the DB Browser for SQLite window open with a toolbar at the top and several tabs below it.

Take a moment to locate these three tabs, which you will use throughout this project:

  • Database Structure shows your tables and their columns (like a blueprint of your database).
  • Browse Data displays your table contents in a familiar spreadsheet-like grid.
  • Execute SQL is where you will write and run all your SQL queries.

Why these three tabs matter

Think of these tabs as three different views of the same database. Database Structure is the architect's view (what tables exist and how they connect). Browse Data is the data entry view (what is actually stored). Execute SQL is the analyst's view (asking questions about the data).

Don't see DB Browser in your search results?

On Windows, try searching for DB Browser for SQLite or look in your Start menu under recently added programs.

On macOS, open Finder, navigate to Applications, and double-click DB Browser for SQLite.

Help me open DB Browser for SQLite on my computer.

Create your bookstore database file

Every SQLite database lives in a single file. You will now create the empty file that will hold all your bookstore tables and data.

  • Click New Database in the toolbar at the top of the DB Browser window.
  • A save dialog appears. Navigate to your Desktop (or another folder you can find easily).
  • Type bookstore.db as the file name and click Save.

What happens when you click New Database?

DB Browser creates an empty .db file at the location you chose. This file will eventually hold all your tables, data, and relationships. Right now it is empty, ready for you to define your schema in the next step.

After saving, DB Browser may prompt you with a Create Table dialog. Click Cancel to close it. You will create tables using SQL in the next step.

  • Verify the title bar of DB Browser now shows bookstore.db (or the full path to your file).
  • Click Write Changes in the toolbar to save the empty database to disk.

Always click Write Changes

DB Browser does not auto-save. Every time you make a change (creating tables, inserting data), click Write Changes in the toolbar to persist your work to the .db file. Forgetting this step is the most common mistake new users make.

Don't see Write Changes in the toolbar?

If the button is greyed out, it means there are no unsaved changes. This is expected for a brand-new empty database.

If you do not see the button at all, look for a floppy-disk icon or check the File menu for Write Changes.

Help me find the Write Changes button in DB Browser for SQLite.

Your tool is installed and your empty database is ready. Next up, you will design the schema for your bookstore by creating tables with SQL.

Design Your Database Schema

Your database file is ready, but it's completely empty. Before you can store any books, customers, or orders, you need to define the structure that holds this data.

This structure is called a schema. In this step, you'll write SQL statements that create 4 tables representing the core entities of a bookstore.

In this step, get ready to:
  • Create the authors and books tables with proper column types.
  • Create the customers and orders tables with foreign key relationships.
  • Verify all 4 tables in the Database Structure tab and save your changes.
Create the authors and books tables

A table in a relational database is like a spreadsheet. Each column has a name and a data type that controls what kind of values it can store.

  • Switch to the Execute SQL tab in DB Browser.
  • Create the authors table by typing this SQL into the editor and pressing F5 (or Ctrl+Return) to run it:
-- Create the authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT
);

What does this code do?

  • CREATE TABLE authors creates a new table called authors in your database.
  • id INTEGER PRIMARY KEY makes id the unique identifier for each row. This is called a primary key.
  • TEXT NOT NULL means the name column must always have a value. You cannot add an author without a name.
  • country TEXT is optional. NULL values are allowed here since not every record needs a country.
  • Check the message area below the SQL editor. You should see a message confirming the query executed successfully.

Seeing an error message?

  • Make sure you typed the SQL exactly as shown, including the semicolon at the end.
  • Check for mismatched parentheses. Every opening ( needs a closing ).
  • If the error message has a red background, it highlights exactly where the problem is in your SQL.

Help me fix a CREATE TABLE error in DB Browser for SQLite.

  • Select all the text in the SQL editor and delete it.
  • Create the books table by typing this SQL and pressing F5 to run it:
-- 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 code do?

  • author_id INTEGER stores the id of the author who wrote the book. This is how you link books to authors.
  • price REAL stores decimal numbers like 9.99. REAL is SQLite's type for floating-point numbers.
  • FOREIGN KEY (author_id) REFERENCES authors(id) tells the database that author_id must match an existing id in the authors table. This is called a foreign key relationship.
  • Check the message area for a success confirmation.

Getting a syntax error?

  • Double-check the FOREIGN KEY line. Make sure you have REFERENCES authors(id) with no space between authors and (id).
  • Ensure the authors table was created successfully in the previous step. The books table references it.

Help me fix my books CREATE TABLE statement with a foreign key.

Create the customers and orders tables

Your bookstore also needs to track who is buying books and what they ordered. These two tables complete your schema.

  • Select all the text in the SQL editor and delete it.
  • Create the customers table by typing this SQL and pressing F5 to run it:
-- Create the customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    city TEXT
);

What does this code do?

  • This table follows the same pattern as authors. The id column uniquely identifies each customer.
  • email TEXT and city TEXT are both optional columns with no NOT NULL constraint.
  • Check the message area for a success confirmation.

Table not created?

  • Make sure you cleared the previous SQL before running. DB Browser may try to re-run old statements that already exist.
  • If you see a "table already exists" error, the table was already created. You can move on to the next one.

Help me debug my customers CREATE TABLE statement.

  • Select all the text in the SQL editor and delete it.
  • Create the orders table by typing this SQL and pressing F5 to run it:
-- Create the orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

What does this code do?

  • quantity INTEGER tracks how many copies of a book were ordered.
  • order_date TEXT stores dates as text in YYYY-MM-DD format. SQLite does not have a dedicated date type.
  • This table has TWO foreign keys. customer_id links to customers and book_id links to books. This is how you connect all three tables through orders.
  • Check the message area for a success confirmation.

Seeing a foreign key error?

  • Make sure both the customers and books tables were created successfully before creating orders. Foreign keys reference existing tables.
  • Check that you have a comma between the two FOREIGN KEY lines.

Help me fix my orders table with two foreign keys.

✔️ Awesome, I've got everything!

Great. All 4 tables are created. Time to verify them visually.

ⓧ I'd like to double check the full code

Here is the complete schema SQL you should have run across all 4 statements:

-- 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,
    city TEXT
);

-- Create the orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    book_id INTEGER,
    quantity INTEGER,
    order_date TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);
Verify your tables and save

Before moving on, confirm that all 4 tables were created correctly and save your work to the database file.

  • Click the Database Structure tab at the top of DB Browser.
  • Verify you can see all 4 tables listed: authors, books, customers, and orders.
  • Click each table name to expand it and confirm the columns match what you defined.
  • Click Write Changes in the toolbar to save your database.

Why do I need to click Write Changes?

DB Browser keeps changes in memory until you explicitly save them. If you close the application without clicking Write Changes, your tables could be lost.

Your schema is set. In the next step, you'll fill these tables with sample data so you can start writing queries.

Populate Your Database with Sample Data

In the previous step, you built the structure of your bookstore database. Four empty tables are waiting for records. A database without data is like a library without books.

In this step, you'll fill those tables with authors, books, customers, and orders. This sample data gives you realistic material to query in the next steps.

In this step, get ready to:
  • Insert authors and books into the database.
  • Insert customers and orders into the database.
  • Verify your data in the Browse Data tab and save changes.
Insert authors and books

The INSERT INTO statement adds new rows to a table. You specify the table name, the columns you're filling, and the values for each row.

  • In DB Browser for SQLite, switch to the Execute SQL tab.
  • Clear any existing text in the SQL editor, then paste the following statements to add 6 authors:
-- Insert authors
INSERT INTO authors (id, name, country) VALUES (1, 'George Orwell', 'United Kingdom');
INSERT INTO authors (id, name, country) VALUES (2, 'Ursula K. Le Guin', 'United States');
INSERT INTO authors (id, name, country) VALUES (3, 'Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (id, name, country) VALUES (4, 'Frank Herbert', 'United States');
INSERT INTO authors (id, name, country) VALUES (5, 'Yuval Noah Harari', 'Israel');
INSERT INTO authors (id, name, country) VALUES (6, 'Octavia Butler', 'United States');

What does this code do?

Each INSERT INTO line follows the same pattern: table name, column list in parentheses, then VALUES with the data for one row.

The id column is the primary key for each author. Text values like names and countries are wrapped in single quotes.

  • Click the Execute SQL button (the play icon) or press F5 to run the statements.
  • The output panel below the editor should show no error messages.

Seeing an error after running?

Check that every text value is wrapped in single quotes, not double quotes. SQL uses single quotes for string values.

If you see a "UNIQUE constraint failed" error, it means you already inserted these authors. Select all text in the editor, delete it, type DELETE FROM authors; and run it, then try the INSERT statements again.

Help me fix my INSERT INTO statement for the authors table.

  • Now clear the SQL editor and paste these statements to add 12 books across multiple genres:
-- Insert books
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (1, '1984', 1, 'Fiction', 9.99, 1949);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (2, 'Animal Farm', 1, 'Fiction', 7.99, 1945);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (3, 'The Left Hand of Darkness', 2, 'Science Fiction', 12.99, 1969);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (4, 'A Wizard of Earthsea', 2, 'Fantasy', 10.99, 1968);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (5, 'Americanah', 3, 'Fiction', 14.99, 2013);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (6, 'Half of a Yellow Sun', 3, 'Fiction', 13.99, 2006);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (7, 'Dune', 4, 'Science Fiction', 11.99, 1965);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (8, 'Dune Messiah', 4, 'Science Fiction', 10.99, 1969);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (9, 'Sapiens', 5, 'Non-Fiction', 15.99, 2011);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (10, 'Homo Deus', 5, 'Non-Fiction', 14.99, 2015);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (11, 'Kindred', 6, 'Science Fiction', 11.99, 1979);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (12, 'Parable of the Sower', 6, 'Science Fiction', 12.99, 1993);

What does this code do?

Each book row includes an author_id that matches a specific author's id in the authors table. This is the foreign key relationship you defined in the schema.

For example, books with author_id = 1 belong to George Orwell (id 1 in the authors table). This link lets you combine data from both tables later using JOINs.

  • Click the Execute SQL button or press F5 to run the statements.
  • Confirm the books were inserted by clearing the editor and running this quick check:
SELECT COUNT(*) FROM books;

You should see 12 as the result.

Seeing fewer than 12 rows?

Make sure you pasted all 12 INSERT statements. If some ran and some failed, you may have a partial insert. Check the output panel for specific error messages pointing to the failing line.

If you see a foreign key error, make sure you ran the authors INSERT statements first. Books reference authors by author_id, so the authors must exist before you insert books.

Help me debug my books INSERT statements.

Insert customers and orders

Your bookstore now has authors and books. Next you need people to buy them. Customers and orders complete the picture so you can answer business questions later.

  • Clear the SQL editor and paste these statements to add 5 customers:
-- Insert customers
INSERT INTO customers (id, name, email, city) VALUES (1, 'Alice Chen', 'alice@email.com', 'Seattle');
INSERT INTO customers (id, name, email, city) VALUES (2, 'Bob Martinez', 'bob@email.com', 'Austin');
INSERT INTO customers (id, name, email, city) VALUES (3, 'Carol Johnson', 'carol@email.com', 'Chicago');
INSERT INTO customers (id, name, email, city) VALUES (4, 'David Kim', 'david@email.com', 'New York');
INSERT INTO customers (id, name, email, city) VALUES (5, 'Emma Wilson', 'emma@email.com', 'Portland');

What does this code do?

Each customer gets a unique id, a name, an email address, and a city. The orders table will reference these customer IDs to track who bought what.

  • Click Execute SQL or press F5 to run.
  • The output panel should show no errors.

Getting a constraint error?

If you see a "UNIQUE constraint failed" message, it means customers with those IDs already exist. Clear the editor, run DELETE FROM customers; first, then try the INSERT statements again.

Help me fix my customers INSERT statements.

  • Clear the SQL editor and paste these statements to add 15 orders:
-- Insert orders
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (1, 1, 1, 1, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (2, 1, 7, 1, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (3, 1, 9, 2, '2024-02-20');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (4, 2, 5, 1, '2024-01-22');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (5, 2, 7, 1, '2024-02-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (6, 2, 11, 1, '2024-03-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (7, 3, 3, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (8, 3, 4, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (9, 3, 12, 2, '2024-02-14');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (10, 4, 1, 1, '2024-02-01');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (11, 4, 9, 1, '2024-02-01');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (12, 4, 10, 1, '2024-03-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (13, 5, 2, 3, '2024-01-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (14, 5, 6, 1, '2024-02-28');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (15, 5, 8, 1, '2024-03-15');

What does this code do?

Each order links a customer_id to a book_id, with a quantity and date. These two foreign keys connect orders to both the customers and books tables.

Notice that some customers have multiple orders (Alice has 3 orders), and some orders have quantities greater than 1. This variety makes your aggregation queries more interesting later.

  • Click Execute SQL or press F5 to run.
  • Confirm all orders were inserted by clearing the editor and running:
SELECT COUNT(*) FROM orders;

You should see 15 as the result.

Seeing a foreign key error?

Orders reference both customer_id and book_id. Make sure you inserted all customers and books before running the orders INSERT statements.

Also check that dates are in the format YYYY-MM-DD with single quotes around them.

Help me fix my orders INSERT statements.

✔️ Awesome, I've got everything!

Great. Double check you've run all four groups of INSERT statements (authors, books, customers, orders) without errors.

ⓧ I'd like to double check the full code

Here are all the INSERT statements you should have run in this step:

-- Insert authors
INSERT INTO authors (id, name, country) VALUES (1, 'George Orwell', 'United Kingdom');
INSERT INTO authors (id, name, country) VALUES (2, 'Ursula K. Le Guin', 'United States');
INSERT INTO authors (id, name, country) VALUES (3, 'Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (id, name, country) VALUES (4, 'Frank Herbert', 'United States');
INSERT INTO authors (id, name, country) VALUES (5, 'Yuval Noah Harari', 'Israel');
INSERT INTO authors (id, name, country) VALUES (6, 'Octavia Butler', 'United States');

-- Insert books
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (1, '1984', 1, 'Fiction', 9.99, 1949);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (2, 'Animal Farm', 1, 'Fiction', 7.99, 1945);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (3, 'The Left Hand of Darkness', 2, 'Science Fiction', 12.99, 1969);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (4, 'A Wizard of Earthsea', 2, 'Fantasy', 10.99, 1968);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (5, 'Americanah', 3, 'Fiction', 14.99, 2013);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (6, 'Half of a Yellow Sun', 3, 'Fiction', 13.99, 2006);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (7, 'Dune', 4, 'Science Fiction', 11.99, 1965);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (8, 'Dune Messiah', 4, 'Science Fiction', 10.99, 1969);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (9, 'Sapiens', 5, 'Non-Fiction', 15.99, 2011);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (10, 'Homo Deus', 5, 'Non-Fiction', 14.99, 2015);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (11, 'Kindred', 6, 'Science Fiction', 11.99, 1979);
INSERT INTO books (id, title, author_id, genre, price, published_year) VALUES (12, 'Parable of the Sower', 6, 'Science Fiction', 12.99, 1993);

-- Insert customers
INSERT INTO customers (id, name, email, city) VALUES (1, 'Alice Chen', 'alice@email.com', 'Seattle');
INSERT INTO customers (id, name, email, city) VALUES (2, 'Bob Martinez', 'bob@email.com', 'Austin');
INSERT INTO customers (id, name, email, city) VALUES (3, 'Carol Johnson', 'carol@email.com', 'Chicago');
INSERT INTO customers (id, name, email, city) VALUES (4, 'David Kim', 'david@email.com', 'New York');
INSERT INTO customers (id, name, email, city) VALUES (5, 'Emma Wilson', 'emma@email.com', 'Portland');

-- Insert orders
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (1, 1, 1, 1, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (2, 1, 7, 1, '2024-01-15');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (3, 1, 9, 2, '2024-02-20');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (4, 2, 5, 1, '2024-01-22');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (5, 2, 7, 1, '2024-02-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (6, 2, 11, 1, '2024-03-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (7, 3, 3, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (8, 3, 4, 1, '2024-01-30');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (9, 3, 12, 2, '2024-02-14');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (10, 4, 1, 1, '2024-02-01');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (11, 4, 9, 1, '2024-02-01');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (12, 4, 10, 1, '2024-03-10');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (13, 5, 2, 3, '2024-01-05');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (14, 5, 6, 1, '2024-02-28');
INSERT INTO orders (id, customer_id, book_id, quantity, order_date) VALUES (15, 5, 8, 1, '2024-03-15');
Verify your data in the Browse Data tab

Now that all 4 tables have data, you can visually confirm everything looks correct before saving.

  • Click the Browse Data tab at the top of DB Browser.
  • Use the Table dropdown to select authors. You should see 6 rows with names and countries.
  • Switch the dropdown to books. You should see 12 rows spanning Fiction, Science Fiction, Fantasy, and Non-Fiction genres.
  • Switch to customers. You should see 5 rows.
  • Switch to orders. You should see 15 rows with customer IDs, book IDs, quantities, and dates.
  • Click Write Changes in the toolbar to save all your data to the bookstore.db file.

Why do I need to click Write Changes?

DB Browser for SQLite holds your changes in memory until you explicitly save them. Clicking Write Changes persists everything to the .db file on disk. If you close DB Browser without writing changes, your data will be lost.

Browse Data tab showing empty tables?

Make sure you selected the correct table from the dropdown at the top of the Browse Data tab. If a table is truly empty, return to the Execute SQL tab and re-run the INSERT statements for that table.

Help me figure out why my tables are empty in Browse Data.

Your bookstore is fully stocked. You have 6 authors, 12 books, 5 customers, and 15 orders ready to query. Next up, you'll write your first SELECT queries to search and filter this data.

Query Your Data with SELECT, WHERE, and ORDER BY

Your bookstore database is fully stocked with authors, books, customers, and orders. Now it is time to start asking questions of that data.

In this step, you will write SQL queries that retrieve, filter, and sort your book catalog. These are the same operations that power every search bar, product listing, and report dashboard in the real world.

In this step, get ready to:
  • Retrieve data with SELECT and filter results with WHERE.
  • Sort results with ORDER BY and combine it with WHERE.
  • Use LIKE for pattern matching and BETWEEN for range filtering.
Retrieve all books and filter by genre

The SELECT statement is how you pull data out of a table. Adding a WHERE clause lets you narrow those results to only the rows that match a condition.

  • Switch to the Execute SQL tab in DB Browser for SQLite.
  • Clear any SQL left over from previous steps, then type and run the following queries:
-- Get all books
SELECT * FROM books;

-- Filter by genre
SELECT title, price, published_year
FROM books
WHERE genre = 'Science Fiction';

What do these queries do?

  • SELECT * FROM books retrieves every column and every row from the books table. The * is shorthand for "all columns."
  • The second query asks for only three specific columns (title, price, published_year) and uses WHERE genre = 'Science Fiction' to keep only science fiction books.
  • To run just one query at a time, highlight the query text you want to execute, then press Ctrl+Return (Windows) or Cmd+Return (macOS).
  • Run the first query. You should see all 12 books listed in the results grid below the editor.
  • Run the second query. You should see 5 rows, all with the genre Science Fiction.

Not seeing any results?

  • Make sure you highlighted only one query before pressing Ctrl+Return. If nothing is highlighted, DB Browser runs all statements and only shows the last result.
  • Check that each query ends with a semicolon (;). Missing semicolons can cause execution to fail silently.
  • help me debug my SELECT query in DB Browser for SQLite
Sort results with ORDER BY

Filtering tells the database which rows to keep. Sorting with ORDER BY controls the order those rows appear in your results.

  • Type and run these two queries to explore sorting:
-- Sort by price descending
SELECT title, genre, price
FROM books
ORDER BY price DESC;

-- Cheapest fiction books
SELECT title, price
FROM books
WHERE genre = 'Fiction'
ORDER BY price ASC;

What do these queries do?

  • ORDER BY price DESC sorts from the most expensive book to the cheapest. DESC means descending.
  • The second query combines WHERE and ORDER BY to answer a real question: "What are the cheapest fiction books in our store?" ASC means ascending (lowest to highest).
  • Highlight and run the first query. The most expensive book (Sapiens at $15.99) should appear at the top.
  • Highlight and run the second query. You should see 4 fiction books listed from cheapest (Animal Farm at $7.99) to most expensive (Americanah at $14.99).

Results not sorted correctly?

  • Double-check that you typed DESC or ASC after the column name. Without either keyword, SQL defaults to ascending order.
  • Make sure ORDER BY comes after WHERE in your query. Swapping their positions causes a syntax error.
  • help me fix my ORDER BY query
Use LIKE and BETWEEN for advanced filtering

Sometimes you do not know the exact value you are looking for. LIKE lets you search for patterns inside text, and BETWEEN lets you filter within a numeric or date range.

  • Type and run this query to find books with "the" anywhere in the title:
-- Pattern matching with LIKE
SELECT title, genre
FROM books
WHERE title LIKE '%the%';

What does this query do?

The % symbol is a wildcard that matches any number of characters. So '%the%' finds any title containing "the" anywhere in the string.

SQLite's LIKE is case-insensitive for ASCII letters, so it matches both "The" and "the".

  • Highlight and run the LIKE query. You should see 2 results: "The Left Hand of Darkness" and "Parable of the Sower."
  • Now type and run this query to find books published within a date range:
-- Range filtering with BETWEEN
SELECT title, published_year
FROM books
WHERE published_year BETWEEN 2000 AND 2023;

What does this query do?

BETWEEN 2000 AND 2023 is inclusive on both ends. It returns books where published_year is 2000 or later and 2023 or earlier.

  • Highlight and run the BETWEEN query. You should see 4 books: Half of a Yellow Sun (2006), Sapiens (2011), Americanah (2013), and Homo Deus (2015).

Getting fewer results than expected?

  • Make sure you typed BETWEEN 2000 AND 2023, not a narrower range. BETWEEN is inclusive, so both boundary values count.
  • For LIKE queries, remember that % goes on both sides of the search term to match anywhere in the string. Using 'the%' only matches titles that start with "the."
  • help me understand why my LIKE or BETWEEN query returns unexpected results

✔️ Awesome, I've got everything!

Great. Make sure you have run all 6 queries and reviewed the results for each one.

ⓧ I'd like to double check the full code

Here is the complete set of queries from this step:

-- Get all books
SELECT * FROM books;

-- Filter by genre
SELECT title, price, published_year
FROM books
WHERE genre = 'Science Fiction';

-- Sort by price descending
SELECT title, genre, price
FROM books
ORDER BY price DESC;

-- Cheapest fiction books
SELECT title, price
FROM books
WHERE genre = 'Fiction'
ORDER BY price ASC;

-- Pattern matching with LIKE
SELECT title, genre
FROM books
WHERE title LIKE '%the%';

-- Range filtering with BETWEEN
SELECT title, published_year
FROM books
WHERE published_year BETWEEN 2000 AND 2023;

You can now pull, filter, and sort data from a single table. Next up, you will use JOINs to combine data across multiple tables and answer questions that span books, authors, and customers.

Join Tables to Answer Business Questions

You can now filter and sort data within any single table in your bookstore database. The next challenge is combining data that lives in different tables.

Right now, the books table only stores an author_id number. To see the actual author name alongside a book title, you need to connect the two tables. This is exactly what JOIN queries do.

In this step, get ready to:
  • Write an INNER JOIN to display books alongside their author names.
  • Build a multi-table JOIN that creates a complete order summary.
  • Use a LEFT JOIN to find authors who have no books in the store.
Connect books to their authors with INNER JOIN

When two tables share a relationship through a foreign key (like books.author_id pointing to authors.id), an INNER JOIN lets you pull columns from both tables into a single result.

  • In the Execute SQL tab, clear any previous queries and run this JOIN query by pressing F5 (or Ctrl+Return on Windows, Cmd+Return on macOS):
-- Books with author names (INNER JOIN)
SELECT books.title, authors.name AS author, authors.country
FROM books
INNER JOIN authors ON books.author_id = authors.id;

What does this query do?

  • FROM books INNER JOIN authors tells SQLite to combine rows from both tables.
  • ON books.author_id = authors.id is the join condition. It matches each book to the author whose id equals the book's author_id.
  • AS author renames the authors.name column to author in the results, making the output clearer.
  • Only books that have a matching author appear in the results. That is what "INNER" means. Both sides must have a match.

You should see 12 rows in the results grid below. Each row shows a book title alongside its author's name and country.

Not seeing 12 rows?

  • Make sure you are in the Execute SQL tab, not the Browse Data tab.
  • Check for typos in table and column names. author_id and authors.id must match your schema exactly.
  • Make sure the query ends with a semicolon.
  • Help me debug my INNER JOIN query
Build a complete order summary with multiple JOINs

A single JOIN connects two tables. You can chain multiple JOINs to pull data from three or more tables at once. This is how you answer a question like "Which customer ordered which book, and when?"

  • Clear the editor and run this multi-table JOIN query:
-- Complete order summary (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
ORDER BY orders.order_date;

What does this query do?

  • The query starts from the orders table, then joins customers and books using their foreign key relationships.
  • Each INNER JOIN ... ON line connects one additional table. The first connects orders to customers. The second connects orders to books.
  • ORDER BY orders.order_date sorts the results chronologically so you can see the purchase timeline.
  • The result is a readable order history. No more looking up customer IDs or book IDs manually.

You should see 15 rows (one for each order), sorted by date. Each row shows a customer name, book title, quantity, and order date.

Seeing fewer than 15 rows?

  • Double-check that you have INNER JOIN on both join lines. A missing JOIN means one table is not connected.
  • Verify your ON conditions match the correct columns: orders.customer_id = customers.id and orders.book_id = books.id.
  • Help me debug my multi-table JOIN
Use LEFT JOIN to find authors with no books

INNER JOIN only returns rows where both tables have a match. Sometimes you want to find rows that don't have a match. A LEFT JOIN returns every row from the left table, filling in NULL where the right table has no match.

  • To see LEFT JOIN in action, you first need an author who has no books. Clear the editor and run this INSERT followed by the LEFT JOIN query:
-- Add an author with no books to demonstrate LEFT JOIN
INSERT INTO authors (id, name, country) VALUES (7, 'Toni Morrison', 'United States');

-- LEFT JOIN: find authors with no books
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id
WHERE books.id IS NULL;

What does this query do?

The INSERT adds Toni Morrison as author id 7 with no books referencing her. She exists in the authors table but nothing in books points to her.

LEFT JOIN keeps every author, even if no book matches. Authors without books get NULL in the books.title column.

WHERE books.id IS NULL filters to show only authors that have no matching books. This is a common pattern for finding "orphan" or "missing" records in a database.

  • You should see 1 row in the results: Toni Morrison with NULL in the title column.
  • Click Write Changes in the toolbar to save the new author to your database file.

Seeing 0 rows in the LEFT JOIN result?

  • If the INSERT failed because Toni Morrison was already added, that is fine. Run SELECT * FROM authors; to check she exists with id 7.
  • Make sure you used LEFT JOIN and not INNER JOIN. An INNER JOIN hides Toni Morrison because she has no matching books.
  • Check that WHERE books.id IS NULL appears at the end. Without it, you see all authors (with and without books) instead of just the unmatched ones.
  • Help me debug my LEFT JOIN query

✔️ Awesome, I've got everything!

Great. Make sure you clicked Write Changes to save Toni Morrison to your database.

ⓧ I'd like to double check the full code

Here are all the queries you ran in this step. Compare them with what you have in your Execute SQL tab.

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

-- Complete order summary (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
ORDER BY orders.order_date;

-- Add an author with no books to demonstrate LEFT JOIN
INSERT INTO authors (id, name, country) VALUES (7, 'Toni Morrison', 'United States');

-- LEFT JOIN: find authors with no books
SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id
WHERE books.id IS NULL;

You have now connected data across multiple tables using JOINs. Next up, you will use GROUP BY and aggregate functions to answer big-picture business questions like "Which genre generates the most revenue?"

Aggregate Data with GROUP BY

Your JOIN queries can now pull data across multiple tables. The next question is: how do you summarize that data into meaningful numbers?

Business questions like "how many books do we have in each genre?" or "which genre makes the most money?" need you to group rows together and run calculations on each group. That is exactly what GROUP BY and aggregate functions do.

In this step, get ready to:
  • Count records per group with COUNT and GROUP BY.
  • Calculate totals and averages with SUM and AVG.
  • Filter grouped results with HAVING.
Count books per genre and orders per customer

COUNT tells you how many rows fall into each group. Combined with GROUP BY, it answers questions like "how many books exist in each genre?"

  • In the Execute SQL tab, clear any previous queries and type the following to count books per genre:
-- Count books per genre
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;
  • Highlight the query you just typed, then press Ctrl+Return (macOS) or F5 (Windows) to run it.

What does this query do?

GROUP BY genre takes every row in the books table and sorts them into buckets by genre. COUNT(*) then counts the rows in each bucket.

The AS book_count part gives the count column a readable name in the results.

You should see 4 rows: Fiction (4), Fantasy (1), Non-Fiction (2), and Science Fiction (5).

Seeing an error or no results?

  • Make sure you highlighted only this one query before pressing Run. If multiple queries are in the editor, DB Browser runs all of them and only shows the last result.
  • Check that your query ends with a semicolon. Missing semicolons can prevent the shortcut from executing the statement.

Help me fix my GROUP BY query.

Now find out how many orders each customer has placed. This requires a JOIN because customer names live in the customers table but orders live in the orders table.

  • Type this query below your previous one in the Execute SQL tab:
-- Count orders per customer
SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;
  • Highlight this query and press Ctrl+Return (macOS) or F5 (Windows) to run it.

What does this query do?

The INNER JOIN connects the customers and orders tables so you can group by customer name. COUNT(orders.id) counts how many order rows belong to each customer.

You should see 5 rows, each showing a customer name and the number 3 (every customer placed 3 orders in our sample data).

Getting a "no such column" error?

  • Check that you wrote customers.name and orders.id with the correct table prefix. Without the prefix, SQL does not know which table you mean.
  • Verify your JOIN condition uses customers.id = orders.customer_id (not customer_id = id without table names).

Help me fix my JOIN and GROUP BY query.

Calculate revenue with SUM and average price with AVG

COUNT tells you how many. SUM and AVG tell you how much. These aggregate functions work the same way inside a GROUP BY query.

  • Calculate total revenue by genre by typing this query:
-- Total revenue by genre
SELECT books.genre,
       SUM(books.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY total_revenue DESC;
  • Highlight this query and run it.

What does this query do?

SUM(books.price * orders.quantity) multiplies each book's price by the quantity ordered, then adds up all those amounts within each genre. This gives you the total money earned per genre.

ORDER BY total_revenue DESC sorts the results so the highest-earning genre appears first.

You should see Science Fiction at the top (highest revenue), followed by Fiction, Non-Fiction, and Fantasy.

Results look wrong or query fails?

  • Make sure you join orders to books (not the other way around). The FROM clause starts with orders because you want every order row multiplied by its book price.
  • Check that you typed books.price * orders.quantity with the asterisk between them. A missing * causes a syntax error.

Help me debug my SUM revenue query.

Now find the average book price per genre. This helps you understand pricing across your catalog.

  • Type and run this query:
-- Average book price per genre
SELECT genre, AVG(price) AS avg_price
FROM books
GROUP BY genre;

What does this query do?

AVG(price) adds up all book prices in a genre and divides by the count. You get one average price per genre without needing to calculate it manually.

You should see Non-Fiction with the highest average price and Fantasy with the lowest.

Filter grouped results with HAVING

WHERE filters individual rows before grouping. HAVING filters groups after the aggregation is done. Use HAVING when your condition depends on an aggregate value like a count or sum.

  • Find only genres with more than 2 books by typing this query:
-- HAVING: genres with more than 2 books
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING book_count > 2;
  • Highlight and run the query.

Why HAVING instead of WHERE?

You cannot write WHERE book_count > 2 because book_count does not exist yet when WHERE runs. WHERE happens before GROUP BY. HAVING happens after, so it can see the aggregated values.

You should see only 2 rows: Fiction (4 books) and Science Fiction (5 books). Fantasy and Non-Fiction are filtered out because they have 2 or fewer books.

Still seeing all 4 genres?

  • Check that you wrote HAVING (not WHERE) after the GROUP BY line.
  • Make sure you highlighted only this query before running. If the previous COUNT query is also selected, you will see its unfiltered results instead.

Help me understand HAVING vs WHERE in my query.

Finally, combine HAVING with ORDER BY to rank customers by total spend. This answers the question: who are our top customers?

  • Type and run this query to find customers who spent more than $25 total:
-- HAVING: customers who spent more than $25 total
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
HAVING total_spent > 25
ORDER BY total_spent DESC;

What does this query do?

This query joins three tables (orders, customers, books), groups by customer name, calculates each customer's total spending, then uses HAVING to keep only those who spent more than $25. The ORDER BY at the end ranks them from highest to lowest spender.

This is a real-world pattern. Businesses use queries like this to identify their most valuable customers.

You should see all 5 customers listed, ranked from highest to lowest total spend. Alice Chen appears at the top because her orders include a quantity-2 purchase of Sapiens.

Seeing fewer customers than expected?

  • Double-check that your HAVING threshold is 25 (not a higher number). With our sample data, every customer spent more than $25.
  • Verify you have both INNER JOIN lines connecting orders to customers and orders to books. Missing one JOIN means the price or name data is unavailable.

Help me fix my HAVING query with multiple JOINs.

✔️ Awesome, I've got everything!

Great. Make sure all 6 queries are saved in your Execute SQL tab for reference.

ⓧ I'd like to double check the full code

Here are all the queries from this step combined:

-- Count books per genre
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre;

-- Count orders per customer
SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

-- Total revenue by genre
SELECT books.genre,
       SUM(books.price * orders.quantity) AS total_revenue
FROM orders
INNER JOIN books ON orders.book_id = books.id
GROUP BY books.genre
ORDER BY total_revenue DESC;

-- Average book price per genre
SELECT genre, AVG(price) AS avg_price
FROM books
GROUP BY genre;

-- HAVING: genres with more than 2 books
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING book_count > 2;

-- HAVING: customers who spent more than $25 total
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
HAVING total_spent > 25
ORDER BY total_spent DESC;

Secret mission

Build a Book Recommendation Query

Ever wondered how online stores suggest books you might like? In this secret mission, you'll build a collaborative filtering recommendation engine in pure SQL. Using nested subqueries and NOT IN, you'll write a query that finds customers with similar tastes and suggests books they loved that you haven't read yet.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • bookstore.db (your SQLite database file).
  • SQL query files (queries_step4.sql, queries_step5.sql, queries_step6.sql, queries_secret_mission.sql, schema.sql).
  • DB Browser for SQLite (installed application).

Keep everything running

No action needed. Choose this if you want to keep practicing SQL or add more data to your bookstore database.

  • Your bookstore.db file stays where you saved it, ready to open in DB Browser for SQLite any time.
  • DB Browser for SQLite stays installed for future database projects.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your database file and queries are safely saved on disk.

  • Close DB Browser for SQLite if it is still open.
  • Your bookstore.db and .sql files remain on your computer until you decide to delete them.
  • To resume, open DB Browser for SQLite and use File > Open Database to load bookstore.db.

Delete - I don't want to use this again

Remove all project files and uninstall DB Browser for SQLite to start completely fresh.

  • Delete your bookstore.db file and all .sql query files from the folder where you saved them.
  • On Windows: open Settings, select Apps, find DB Browser for SQLite in the list, and click Uninstall.
  • On macOS: open Finder, go to Applications, drag DB Browser for SQLite to the Trash, then empty the Trash.
  • If you installed via Homebrew on macOS, run the following command instead:
brew uninstall db-browser-for-sqlite

Nice Work!

Nice Work!

You just built a full relational database from scratch and queried it to answer real business questions. These are the same SQL skills that data analysts, backend developers, and product managers use every single day.

You've learned how to:

  • Built a 4-table relational database with proper primary keys and foreign keys linking authors, books, customers, and orders.
  • Written SELECT, WHERE, ORDER BY, INNER JOIN, and LEFT JOIN queries to filter, sort, and combine data across multiple tables.
  • Used GROUP BY with aggregate functions (COUNT, SUM, AVG) and HAVING to generate and filter business insights like revenue by genre and top customers.
  • Built a collaborative filtering recommendation engine using subqueries and self-joins to suggest books based on similar customer purchases.

Ready to quiz yourself?