Build a Bookstore Database with SQL

Design a relational bookstore database and write SQL queries from scratch.

Introduction

30 Second Summary

Think about how a bookstore keeps track of thousands of books, which authors wrote them, and which customers bought what. Without a structured system, answering simple questions like "What's our best-selling genre?" becomes impossible.

In this project, you will build a bookstore database from scratch using SQL. You'll design tables, fill them with sample data, and write queries that answer real business questions about your store.

What You'll Build

A fully functional SQLite database with four related tables tracking books, authors, customers, and orders.

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

  • A working relational database with multiple linked tables storing bookstore data.
  • A collection of SQL queries that filter, sort, join, and aggregate data to answer real business questions.
  • Hands-on experience with the full CRUD cycle (Create, Read, Update, Delete) in SQL.
  • Secret Mission: Build a recommendation query that suggests books to customers based on their purchase history and favourite genres.

Are there any prerequisites?

None. This project includes full setup instructions for every tool you need. No prior SQL or database experience is required.

Install DB Browser for SQLite

Every database needs a tool to interact with it. You need somewhere to write SQL commands, run them, and see the results.

In this step, you'll install DB Browser for SQLite. It's a free, visual tool that lets you create databases, write queries, and browse your data without touching the command line.

In this step, get ready to:
  • Download and install DB Browser for SQLite.
  • Launch the app and verify it opens.
  • Create a new database file for your bookstore.
Download and install DB Browser for SQLite

DB Browser for SQLite is a desktop application. You'll download it from the official website and install it using a standard Windows installer.

  • Open your web browser and go to the DB Browser for SQLite downloads page:
  • https://sqlitebrowser.org/dl/
  • Under the Windows section, click DB Browser for SQLite - Standard installer for 64-bit Windows to download the installer.
  • Once the download finishes, open the file DB.Browser.for.SQLite-v3.13.1-win64.msi from your Downloads folder.
  • Follow the installer prompts. Accept the defaults and click Next through each screen until the installation completes.

Why SQLite?

SQLite stores your entire database in a single file on your computer. There's no server to install, no passwords to configure, and no ports to manage.

The SQL you learn here transfers directly to larger databases like PostgreSQL and MySQL. The fundamentals are identical across all of them.

Installer not running?

If Windows shows a security warning, click More info, then Run anyway. This is normal for open-source software downloaded from the internet.

help me troubleshoot the DB Browser installer on Windows

Launch DB Browser for SQLite

Now that the installer has finished, you can open DB Browser from the Windows Start menu.

  • Press the Windows key on your keyboard to open the Start menu.
  • Type DB Browser and press Enter to open the app.

✔️ I see the DB Browser window

You should see a window with a toolbar at the top (New Database, Open Database, etc.) and four tabs below it: Database Structure, Browse Data, Edit Pragmas, and Execute SQL.

You're all set. Move on to the next substep.

ⓧ I don't see DB Browser

If the app doesn't appear in your Start menu search, the installer may not have completed successfully.

  • Go back to your Downloads folder and double-click DB.Browser.for.SQLite-v3.13.1-win64.msi again.
  • If the installer offers a Repair option, select it and let it complete.
  • After repair finishes, try searching for DB Browser in the Start menu again.

help me get DB Browser for SQLite to appear in my Start menu

Create your bookstore database

With DB Browser open, you'll create the database file that will hold all your bookstore data. In SQLite, a database is just a single file on your computer.

  • Click the New Database button in the toolbar at the top of the window.
  • A save dialog appears. Navigate to your Desktop folder.
  • In the File name field, type bookstore and click Save.

What just happened?

DB Browser created a file called bookstore.db on your Desktop. This single file is your entire database. Everything you build in this project lives inside it.

You might also see a Create Table dialog pop up automatically. Click Cancel to close it. You'll create tables using SQL in the next step.

  • If the Create Table dialog appeared, click Cancel to dismiss it.
  • Verify that the title bar of DB Browser now shows bookstore.db in the window title.
  • Click the Database Structure tab. It should be empty because you haven't created any tables yet.

Don't see bookstore.db in the title bar?

Make sure you clicked Save in the file dialog. If you accidentally closed it, click New Database again and repeat the steps.

help me create a new database in DB Browser for SQLite

Your tool is installed and your database file is ready. Next up, you'll design your first tables and write SQL to bring the bookstore to life.

Design Your Authors and Books Tables

You have DB Browser for SQLite open and an empty bookstore.db ready to go. Now it is time to design the structure your data will live in.

Every database starts with tables. A table defines what kind of information you want to store and how each piece relates to others. In this step, you will create two tables that work together: one for authors and one for their books.

In this step, get ready to:
  • Design an authors table with columns for identification, name, and nationality.
  • Design a books table linked to the authors table with a foreign key.
  • Execute your SQL and confirm both tables appear in the database.
Write the CREATE TABLE statement for authors

A CREATE TABLE statement defines a table's structure. It names each column, sets its data type, and adds constraints like NOT NULL to ensure required fields always have values.

  • Switch to the Execute SQL tab in DB Browser.
  • Type the following SQL into the editor pane:
-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

What does this code do?

  • The comment block at the top organizes your SQL file with a header and step label.
  • CREATE TABLE authors tells SQLite to create a new table called authors.
  • id INTEGER PRIMARY KEY AUTOINCREMENT creates a unique numeric ID that increases automatically for each new row.
  • name TEXT NOT NULL stores the author's name as text and requires a value (cannot be left blank).
  • country TEXT NOT NULL stores the author's country of origin and also requires a value.

You will not see a change yet. The next chunk adds the books table, then you will run everything together.

Write the CREATE TABLE statement for books

The books table needs to track which author wrote each book. In relational databases, this connection is called a foreign key. It is a column in one table that points to the primary key of another table.

  • Below the authors statement in the Execute SQL editor, add the following SQL:
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    genre TEXT NOT NULL,
    price REAL NOT NULL,
    published_year INTEGER NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

What does this code do?

  • price REAL NOT NULL stores the book price as a decimal number. SQLite uses REAL for floating-point values.
  • published_year INTEGER NOT NULL stores the publication year as a whole number.
  • author_id INTEGER NOT NULL stores the ID of the author who wrote this book.
  • FOREIGN KEY (author_id) REFERENCES authors(id) declares the relationship. It tells SQLite that author_id must match an existing id in the authors table.

What is a foreign key?

A foreign key is a column that points to a row in another table. Here, each book's author_id points to an id in the authors table. This creates a relationship: you can always look up which author wrote a given book.

This is the foundation of relational database design. Instead of repeating an author's full name and country on every book row, you store that information once in authors and reference it by ID.

Run both statements and verify the tables
  • Click the Execute SQL button in the Execute SQL tab toolbar to run all the statements in the editor.
  • Click the Database Structure tab.

You should see both authors and books listed as tables, each with their columns displayed underneath.

  • Return to the Execute SQL tab.
  • Click the Save SQL file button in the Execute SQL toolbar to save your SQL to the local file system.
  • Name the file bookstore_queries.sql and save it to your Desktop.
  • Click Write Changes in the main toolbar to save the database.

Why click Write Changes?

DB Browser does not auto-save your database. Clicking Write Changes commits your new table structures to the bookstore.db file on disk. Without it, your changes only exist in memory and would be lost if the app closed.

Don't see your tables?

  • Check for a red error message in the results pane below the editor. A missing comma, semicolon, or parenthesis will stop execution.
  • Make sure the authors table comes before the books table in your SQL. The books table references authors, so authors must exist first.
  • If you see "table already exists", the statements already ran successfully. Click the Database Structure tab to confirm your tables are there.

Still stuck? Help me debug my CREATE TABLE error in DB Browser for SQLite.

✔️ Awesome, I've got everything!

Great. Make sure you have clicked Write Changes and saved your SQL file.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have in your Execute SQL editor and saved in bookstore_queries.sql:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

Your database now has a proper schema with two linked tables. Next up, you will fill these tables with sample data so you have something to query.

Add Sample Data

Your tables are built, but they're empty. A database without data is like a bookstore with empty shelves.

In this step, you'll populate your tables with sample authors and books using INSERT INTO statements. This gives you real data to query against in later steps.

In this step, get ready to:
  • Add 6 authors to the authors table.
  • Add 12 books across different genres to the books table.
  • Verify your data loaded correctly using the Browse Data tab.
Insert authors into your database

Each author needs a name and country. The id column fills itself automatically thanks to AUTOINCREMENT.

  • Switch back to the Execute SQL tab in DB Browser for SQLite.
  • Add the following INSERT statements below your existing CREATE TABLE code:
-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

What does this code do?

INSERT INTO adds a new row to a table. You specify which columns to fill and what values to put in them.

Notice you don't include id in the column list. SQLite assigns the next available number automatically because you defined it with AUTOINCREMENT.

  • Select only the 6 INSERT statements you just added (highlight them with your cursor).
  • Click the Execute button (the play icon) to run them.
  • You should see a message in the results pane confirming the statements executed successfully.

Seeing a "table authors already has data" or duplicate error?

If you accidentally ran the INSERT statements twice, the table will have duplicate rows. To fix this, run DELETE FROM authors; to clear the table, then run your 6 INSERT statements again.

Make sure you only highlight and execute the INSERT statements, not the CREATE TABLE statements above them (those tables already exist).

Help me fix a duplicate data error in DB Browser for SQLite.

Insert books into your database

Each book references an author through the author_id column. This is how the two tables connect. Jane Austen was the first author you inserted, so her id is 1.

  • Add the following INSERT statements below your authors data:
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

What does author_id do here?

The author_id value in each book row matches the id of an author in the authors table. This is a foreign key relationship.

For example, author_id = 2 means the book belongs to Gabriel Garcia Marquez (the second author inserted). This link is what lets you later JOIN the tables together to see book titles alongside author names.

  • Select only the 12 book INSERT statements you just added.
  • Click Execute to run them.
  • You should see a success message in the results pane below.

Seeing a foreign key or constraint error?

Make sure you ran the authors INSERT statements first. The books table references author IDs that must already exist in the authors table.

Also check that your author_id values are between 1 and 6 (one for each author you inserted).

Help me fix a foreign key error when inserting books.

Verify your data in the Browse Data tab

The Browse Data tab gives you a spreadsheet-style view of your table contents. This is the quickest way to confirm your data loaded correctly.

  • Click the Browse Data tab at the top of DB Browser.
  • Select authors from the table dropdown.
  • You should see 6 rows with authors from Jane Austen through Isabel Allende.
  • Switch to the books table from the same dropdown.
  • You should see 12 rows covering genres like Romance, Fiction, Dystopian, Magical Realism, and Historical Fiction.
  • Click Write Changes in the toolbar to save everything to your bookstore.db file.

Save your SQL script too

Copy all the SQL from the Execute SQL tab (both the CREATE TABLE and INSERT statements) into your bookstore_queries.sql file and save it. This keeps a record of every statement you've run.

✔️ Awesome, I've got everything!

Great. Double check you've clicked Write Changes to save your database.

ⓧ I'd like to double check the full code

Here is the complete bookstore_queries.sql file up to this point:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

Your bookstore now has real inventory. Next up, you'll write queries to search, filter, and sort through this data.

Query Your Data with SELECT, WHERE, and ORDER BY

Your bookstore now has a catalog of 12 books across 6 authors. The data is stored, but just having data sitting in a table isn't useful on its own.

In this step, you'll learn to ask questions of your data using SQL queries. You'll retrieve rows with SELECT, narrow results with WHERE, and control the order with ORDER BY.

In this step, get ready to:
  • Write SELECT queries to retrieve all books and filter by genre.
  • Combine conditions with AND to answer more specific questions.
  • Sort and limit results with ORDER BY and LIMIT.
Retrieve all books and filter by genre with WHERE

The SELECT statement is how you read data from a table. On its own, it returns everything. Adding a WHERE clause lets you filter rows based on a condition.

  • In DB Browser, switch to the Execute SQL tab.
  • Clear any SQL from previous steps, then type the following queries:
-- Get all books
SELECT * FROM books;

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

What do these queries do?

The first query uses the asterisk (*) to select every column from the books table. This returns all 12 rows with all their details.

The second query is more targeted. It only asks for the title and price columns, and the WHERE clause filters to only books where the genre is exactly 'Fiction'.

  • Click the Execute button (the play icon) or press Ctrl+Enter to run the queries.
  • In the results pane below, you should see two result sets. The first shows all 12 books. The second shows only the Fiction books: Norwegian Wood, Kafka on the Shore, Americanah, and Eva Luna.

Not seeing any results?

  • Make sure you're in the Execute SQL tab, not the Browse Data tab.
  • Check that 'Fiction' in your WHERE clause uses straight single quotes, not curly quotes.
  • Confirm your database has data by clicking the Browse Data tab and selecting the books table from the dropdown.

Why aren't my SELECT queries returning results?

Combine conditions with AND

Real questions about your data usually involve more than one condition. For example, "Which books were published recently AND are affordable?" The AND keyword lets you require multiple conditions to be true at once.

  • Add this query below your previous ones in the Execute SQL tab:
-- Combine conditions
SELECT title, price, published_year FROM books
WHERE published_year > 2000 AND price < 15.00;

What does this query do?

This query asks for books that meet BOTH conditions: published after the year 2000, and priced under $15.00. A row must satisfy both sides of the AND to appear in the results.

Only books that pass both filters show up. If a book was published in 2013 but costs $15.99, it gets excluded because it fails the price condition.

  • Highlight just this new query and click Execute.
  • You should see two results: Kafka on the Shore (2002, $13.99) and Half of a Yellow Sun (2006, $14.99).

Run just one query at a time

If you have multiple queries in the SQL editor, highlight the specific query you want to run before clicking Execute. DB Browser runs only the selected text when you have a selection active.

Sort and limit results with ORDER BY and LIMIT

Databases don't guarantee any particular order for returned rows. ORDER BY lets you control the sort order. LIMIT caps how many rows come back.

  • Add these two queries below your previous ones in the Execute SQL tab:
-- Sort by price descending
SELECT title, price FROM books ORDER BY price DESC;

-- Sort by year and limit to 5
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 5;

What do these queries do?

  • The first query sorts all books by price from highest to lowest. DESC means descending order. Without it, SQL defaults to ascending (ASC).
  • The second query sorts books by year (newest first) then chops the results to only the 5 most recent. LIMIT 5 is perfect for "top N" questions.
  • Highlight the first ORDER BY query and click Execute. Americanah ($15.99) should appear at the top.
  • Highlight the second query (with LIMIT) and click Execute. You should see exactly 5 books, with Americanah (2013) first and Norwegian Wood (1987) last.

Results not sorted correctly?

  • Make sure you typed DESC after the column name in ORDER BY. Without it, results sort in ascending order.
  • Check that you highlighted only the query you want to run. If multiple queries are selected, you may see unexpected results.

Why isn't my ORDER BY sorting the results the way I expect?

  • Make sure all five queries from this step are together in your Execute SQL tab. Click Write Changes in the toolbar to save your database.

What about my .sql file?

Your Execute SQL tab content is saved within the database file. If you're also maintaining a separate bookstore_queries.sql file, copy these five queries into it below your INSERT statements from the previous step. This gives you a single reference file of everything you've written.

✔️ Awesome, I've got everything!

Great. Make sure you've saved your work with Write Changes before moving on.

ⓧ I'd like to double check the full code

Here's everything your bookstore_queries.sql file should contain so far:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

-- Step 4: Query data with SELECT, WHERE, ORDER BY

-- Get all books
SELECT * FROM books;

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

-- Combine conditions
SELECT title, price, published_year FROM books
WHERE published_year > 2000 AND price < 15.00;

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

-- Sort by year and limit to 5
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 5;

You can now ask questions of your data and get precise answers. Next up, you'll add customers and orders to the database so you can start tracking who is buying which books.

Create Customers and Orders Tables

You now have a database full of books and authors, plus queries that filter and sort them. A bookstore is more than just inventory, though.

To answer business questions like "Who are our best customers?" and "What's selling the most?", you need to track who is buying what. In this step, you'll create two new tables that model the relationship between customers and the books they order.

In this step, get ready to:
  • Create a customers table to store buyer information.
  • Create an orders table with foreign keys linking customers to books.
  • Populate both tables with sample data to prepare for JOIN queries.
Create the customers table

Every order in your bookstore belongs to a customer. Before you can record orders, you need a table that stores customer information like their name and email address.

  • Switch back to the Execute SQL tab in DB Browser for SQLite.
  • Clear any previous SQL from the editor pane.
  • Create the customers table by typing the following SQL and clicking the Execute button (or pressing F5):
-- Create a table to store customer information
CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

What does this code do?

This creates a customers table with three columns.

  • id is an auto-incrementing primary key, just like in your other tables.
  • name stores the customer's full name as text.
  • email has the UNIQUE constraint, which means no two customers can share the same email address. This prevents duplicate accounts.
  • Click the Database Structure tab.
  • You should see customers listed alongside your existing authors and books tables.

Don't see the customers table?

  • Make sure you clicked Execute (or pressed F5) after typing the SQL. The code won't run until you execute it.
  • Check the results pane at the bottom for error messages. A typo in AUTOINCREMENT or a missing comma between column definitions is the most common cause.
  • If you see an error saying the table already exists, you may have run it twice. That's fine. The table is already created.

Help me fix my CREATE TABLE customers statement.

Create the orders table

The orders table is the most connected table in your database. Each order links a customer to a book using foreign keys. This is how relational databases model real-world relationships.

  • Return to the Execute SQL tab and clear the editor pane.
  • Create the orders table by typing the following SQL and clicking Execute:
-- Create a table to track customer orders
-- Each order links a customer to a book they purchased
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

What does this code do?

This table has two foreign keys that create relationships between tables.

  • customer_id points to the id column in the customers table. This tells you which customer placed the order.
  • book_id points to the id column in the books table. This tells you which book was purchased.
  • quantity has DEFAULT 1, meaning if you don't specify a quantity, SQLite automatically uses 1.
  • order_date stores dates as text in YYYY-MM-DD format. SQLite does not have a dedicated date type, so text is the standard approach.
  • Click the Database Structure tab.
  • You should now see four tables listed: authors, books, customers, and orders.

Seeing an error about a missing table?

  • The orders table references customers and books in its foreign keys. Make sure both of those tables exist before you create orders.
  • Check that FOREIGN KEY (customer_id) REFERENCES customers(id) is spelled exactly as shown. A typo in the referenced table name causes the error.

Help me fix my CREATE TABLE orders statement.

Add sample customers and orders

Empty tables aren't useful for practicing queries. You'll add 4 customers and 10 orders so you have enough data to write meaningful JOINs in the next step.

  • Clear the editor pane in the Execute SQL tab.
  • Add 4 customers to the customers table by typing the following SQL and clicking Execute:
-- Add sample customers
INSERT INTO customers (name, email) VALUES ('Alice Chen', 'alice@example.com');
INSERT INTO customers (name, email) VALUES ('Bob Martinez', 'bob@example.com');
INSERT INTO customers (name, email) VALUES ('Carol Johnson', 'carol@example.com');
INSERT INTO customers (name, email) VALUES ('David Kim', 'david@example.com');

What does this code do?

Each INSERT INTO adds one row to the customers table. You don't need to specify the id column because AUTOINCREMENT handles that automatically. Alice gets id 1, Bob gets id 2, and so on.

  • Click the Browse Data tab and select customers from the table dropdown.
  • You should see 4 rows: Alice Chen, Bob Martinez, Carol Johnson, and David Kim.

Seeing a UNIQUE constraint error?

  • This means a customer with that email already exists. You may have run the INSERT statements more than once.
  • Check the Browse Data tab. If the 4 customers are already there, you're good to continue.

Help me fix my customer INSERT statements.

Now you'll add orders that link customers to the books they purchased.

  • Return to the Execute SQL tab and clear the editor pane.
  • Add 10 orders by typing the following SQL and clicking Execute:
-- Add sample orders linking customers to books
-- customer_id and book_id reference the id columns in their respective tables
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (1, 1, 1, '2024-01-15');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (1, 5, 2, '2024-02-20');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (2, 3, 1, '2024-01-22');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (2, 9, 1, '2024-03-10');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (3, 7, 1, '2024-02-05');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (3, 8, 1, '2024-02-05');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (3, 4, 2, '2024-03-18');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (4, 10, 1, '2024-01-30');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (4, 11, 1, '2024-02-14');
INSERT INTO orders (customer_id, book_id, quantity, order_date) VALUES (4, 6, 2, '2024-03-25');

What do these numbers mean?

Each row uses IDs to reference other tables. For example, (1, 1, 1, '2024-01-15') means customer 1 (Alice Chen) bought book 1 (Pride and Prejudice), quantity 1, on January 15, 2024.

The value (1, 5, 2, '2024-02-20') means Alice bought 2 copies of book 5 (Norwegian Wood). These ID-based links are what make JOIN queries possible in the next step.

  • Click the Browse Data tab and select orders from the table dropdown.
  • You should see 10 rows, each with a customer_id, book_id, quantity, and order_date.
  • Click Write Changes in the toolbar to save all your changes to the database file.

Seeing fewer than 10 rows?

  • Make sure you copied all 10 INSERT statements. Count the lines in your editor pane before executing.
  • Check the results pane for errors. If a customer_id or book_id doesn't match an existing row in its referenced table, SQLite may skip that row silently.

Help me fix my orders INSERT statements.

Finally, add the new SQL from this step to your bookstore_queries.sql file so you have a complete record of everything you've built.

  • Open bookstore_queries.sql in a text editor.
  • Add the following comment, CREATE TABLE statements, and INSERT statements at the bottom of the file (after the Step 4 queries):
-- Step 5: Create Customers and Orders tables

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO customers (name, email) VALUES ('Alice Chen', 'alice@example.com');
INSERT INTO customers (name, email) VALUES ('Bob Martinez', 'bob@example.com');
INSERT INTO customers (name, email) VALUES ('Carol Johnson', 'carol@example.com');
INSERT INTO customers (name, email) VALUES ('David Kim', 'david@example.com');

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

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and that your database shows 4 tables in the Database Structure tab.

ⓧ I'd like to double check the full code

Here is the complete bookstore_queries.sql file up to this point:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

-- Step 4: Query data with SELECT, WHERE, ORDER BY

-- Get all books
SELECT * FROM books;

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

-- Combine conditions
SELECT title, price, published_year FROM books
WHERE published_year > 2000 AND price < 15.00;

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

-- Sort by year and limit to 5
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 5;

-- Step 5: Create Customers and Orders tables

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO customers (name, email) VALUES ('Alice Chen', 'alice@example.com');
INSERT INTO customers (name, email) VALUES ('Bob Martinez', 'bob@example.com');
INSERT INTO customers (name, email) VALUES ('Carol Johnson', 'carol@example.com');
INSERT INTO customers (name, email) VALUES ('David Kim', 'david@example.com');

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

Your database now has four interconnected tables. Next up, you'll write JOIN queries that combine data across these tables to answer real business questions like "Who bought what?" and "Which authors sell the most in the UK?"

Join Tables to Answer Business Questions

Your bookstore database now has four separate tables holding different pieces of information. But real business questions rarely live in just one table.

To answer "Who ordered what?" you need data from orders, customers, and books all at once. That's where JOIN queries come in.

In this step, get ready to:
  • Write an INNER JOIN query to show order details with customer names and book titles.
  • Join books to authors and filter by a specific country.
  • Build a multi-table JOIN that creates a full order report.
Show order details with customer and book names

Right now, the orders table only stores IDs like customer_id and book_id. An INNER JOIN lets you combine rows from multiple tables by matching those IDs to their actual names.

  • Switch to the Execute SQL tab in DB Browser for SQLite.
  • Clear any previous SQL from the editor pane.
  • Write your first JOIN query by pasting the following code:
-- Step 6: Join tables

-- Order details with customer and book names
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 with the orders table and reaches into two other tables to replace raw IDs with readable names.

  • INNER JOIN customers ON orders.customer_id = customers.id matches each order to the customer who placed it.
  • INNER JOIN books ON orders.book_id = books.id matches each order to the book that was purchased.
  • AS customer and AS book create friendly column names in your results.
  • Click the Execute button (the play icon) to run the query.
  • You should see 10 rows in the results pane, each showing a customer name, book title, quantity, and order date instead of raw IDs.

Seeing an error or empty results?

  • Check that you typed INNER JOIN (two words) not INNERJOIN.
  • Make sure the ON clause uses the correct column names: orders.customer_id = customers.id and orders.book_id = books.id.
  • Verify that your customers and orders tables have data by switching to the Browse Data tab and selecting each table from the dropdown.

Why isn't my INNER JOIN returning results?

Find all books by authors from a specific country

You can also use JOIN with a WHERE clause to filter across tables. This lets you ask questions that span relationships, like "Show me every book written by a UK author."

  • Clear the editor pane and paste this query:
-- All books by authors from the United Kingdom
SELECT books.title, authors.name AS author
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE authors.country = 'United Kingdom';

What does this query do?

  • The JOIN connects each book to its author using the author_id foreign key.
  • The WHERE clause filters to only authors whose country is 'United Kingdom'.
  • The result shows book titles alongside author names, but only for UK authors.
  • Click Execute to run the query.
  • You should see 4 rows: the two Jane Austen books and the two George Orwell books.

Seeing 0 rows?

  • Make sure you typed 'United Kingdom' with that exact capitalization and spelling. SQL string comparisons are case-sensitive.
  • Confirm the WHERE clause references authors.country (with the table prefix), not just country.

Why is my JOIN with WHERE returning no rows?

Build a full order report with a multi-table JOIN

Real business reports often pull from many tables at once. This final query joins all four tables together and calculates a total price for each order line.

  • Clear the editor pane and paste this query:
-- Full order report
SELECT
    customers.name AS customer,
    books.title AS book,
    authors.name AS author,
    books.price,
    orders.quantity,
    (books.price * orders.quantity) AS total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
INNER JOIN authors ON books.author_id = authors.id
ORDER BY customers.name;

What does this query do?

  • Three INNER JOINs chain together: orders to customers, orders to books, and books to authors. Each ON clause specifies how the tables connect.
  • (books.price * orders.quantity) AS total creates a calculated column that multiplies the book price by how many copies were ordered.
  • ORDER BY customers.name sorts the full report alphabetically by customer, making it easy to scan.
  • Click Execute to run the query.
  • You should see 10 rows sorted alphabetically by customer name, with a total column showing the calculated cost for each order line.

Seeing a 'no such column' error?

  • When joining multiple tables, always prefix column names with the table name (e.g. books.price, not just price). This avoids ambiguity.
  • Check that the third INNER JOIN connects authors to books (ON books.author_id = authors.id), not authors to orders.

Help me fix my multi-table JOIN query.

  • Add all three JOIN queries to your bookstore_queries.sql file under a -- Step 6: Join tables comment.
  • Click Write Changes in the DB Browser toolbar to save your database.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file with all three JOIN queries.

ⓧ I'd like to double check the full code

Here's everything your bookstore_queries.sql file should contain up to this point:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

-- Step 4: Query data with SELECT, WHERE, ORDER BY

-- Get all books
SELECT * FROM books;

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

-- Combine conditions
SELECT title, price, published_year FROM books
WHERE published_year > 2000 AND price < 15.00;

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

-- Sort by year and limit to 5
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 5;

-- Step 5: Create Customers and Orders tables

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO customers (name, email) VALUES ('Alice Chen', 'alice@example.com');
INSERT INTO customers (name, email) VALUES ('Bob Martinez', 'bob@example.com');
INSERT INTO customers (name, email) VALUES ('Carol Johnson', 'carol@example.com');
INSERT INTO customers (name, email) VALUES ('David Kim', 'david@example.com');

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

-- Step 6: Join tables

-- Order details with customer and book names
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 books by authors from the United Kingdom
SELECT books.title, authors.name AS author
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE authors.country = 'United Kingdom';

-- Full order report
SELECT
    customers.name AS customer,
    books.title AS book,
    authors.name AS author,
    books.price,
    orders.quantity,
    (books.price * orders.quantity) AS total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
INNER JOIN authors ON books.author_id = authors.id
ORDER BY customers.name;

You can now pull data from any combination of your tables in a single query. Next up, you'll use aggregate functions to crunch numbers across groups of rows and answer questions like "How many books has each author written?" and "Which customer spent the most?"

Aggregate Data with GROUP BY and Functions

Your JOIN queries from the previous step pulled data across multiple tables to create detailed reports. Now you can answer a bigger category of question: summaries.

Questions like "which author wrote the most books?" or "how much did each customer spend?" require the database to group rows together and calculate a single result per group. SQL handles this with aggregate functions and the GROUP BY clause.

In this step, get ready to:
  • Use COUNT() to find how many books each author has written.
  • Use SUM() to calculate total revenue per customer.
  • Use AVG(), MAX(), and MIN() to find price statistics per genre.
Count books per author with COUNT() and GROUP BY

COUNT() tallies the number of rows in each group. Paired with GROUP BY, it lets you answer "how many per category?" questions in a single query.

  • Switch back to the Execute SQL tab in DB Browser for SQLite.
  • Clear the editor pane, then paste the following query to count books per author:
-- 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
ORDER BY book_count DESC;

What does this query do?

GROUP BY authors.name creates one group for each unique author name. Within each group, COUNT(books.id) counts how many book rows belong to that author.

ORDER BY book_count DESC sorts the result so the most prolific author appears first. The AS book_count alias gives the counted column a readable name you can reference in ORDER BY.

  • Click the Execute button (or press F5) to run the query.
  • You should see 6 rows in the results pane, each showing an author name and a book_count of 2.

Seeing an error or no results?

Make sure you have a semicolon at the end of the query. DB Browser needs it to know where the statement ends.

If you see 0 rows, confirm your authors and books tables still have data by switching to the Browse Data tab and selecting each table.

Help me debug my COUNT() GROUP BY query

Calculate total revenue per customer with SUM()

SUM() adds up numeric values within each group. Combined with a JOIN across the orders, customers, and books tables, it can calculate how much money each customer has spent.

  • Clear the editor pane and paste this query to calculate total spending per customer:
-- Total revenue 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;

What does this query do?

books.price * orders.quantity calculates the line total for each order. SUM() then adds up all line totals within each customer's group.

The query joins three tables because the order amount lives across two of them: the price is in books and the quantity is in orders. The customer name comes from customers.

  • Click Execute to run the query.
  • You should see 4 rows showing each customer's name and their total_spent value, with the highest spender at the top.

Seeing NULL or unexpected totals?

Check that your JOIN conditions match the correct columns: orders.customer_id = customers.id and orders.book_id = books.id. A mismatch here produces incorrect totals.

Help me debug my SUM() revenue query

Find price statistics per genre with AVG(), MAX(), and MIN()

You can use multiple aggregate functions in the same SELECT to get several statistics at once. This query answers three questions in one go: the average price, the most expensive book, and the cheapest book in each genre.

  • Clear the editor pane and paste this query to find price statistics grouped by genre:
-- Average, max, min price per genre
SELECT
    genre,
    AVG(price) AS avg_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM books
GROUP BY genre;

What does this query do?

GROUP BY genre splits all 12 books into groups by their genre value. Within each group, three functions run independently: AVG(price) calculates the mean, MAX(price) finds the highest price, and MIN(price) finds the lowest.

This query only needs the books table because genre and price both live there. No JOIN needed.

  • Click Execute to run the query.
  • You should see 5 rows (one per genre: Dystopian, Fiction, Historical Fiction, Magical Realism, Romance) with avg_price, max_price, and min_price columns.

Seeing only one row?

If you see a single row with one average across all books, the GROUP BY genre clause is missing. Without it, SQL treats the entire table as one group.

Help me fix my AVG/MAX/MIN query

  • Add all three aggregate queries to your bookstore_queries.sql file under a new comment -- Step 7: Aggregate functions.
  • Click Write Changes in the toolbar to save your database.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file and database changes.

ⓧ I'd like to double check the full code

Here is the complete bookstore_queries.sql file including everything from all steps so far:

-- =============================================
-- BOOKSTORE DATABASE - Complete SQL Script
-- =============================================

-- Step 2: Create Authors and Books tables

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

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

-- Step 3: Insert sample data

INSERT INTO authors (name, country) VALUES ('Jane Austen', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Gabriel Garcia Marquez', 'Colombia');
INSERT INTO authors (name, country) VALUES ('Haruki Murakami', 'Japan');
INSERT INTO authors (name, country) VALUES ('Chimamanda Ngozi Adichie', 'Nigeria');
INSERT INTO authors (name, country) VALUES ('George Orwell', 'United Kingdom');
INSERT INTO authors (name, country) VALUES ('Isabel Allende', 'Chile');

INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Pride and Prejudice', 'Romance', 9.99, 1813, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Sense and Sensibility', 'Romance', 8.99, 1811, 1);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('One Hundred Years of Solitude', 'Magical Realism', 14.99, 1967, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Love in the Time of Cholera', 'Romance', 12.99, 1985, 2);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Norwegian Wood', 'Fiction', 11.99, 1987, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Kafka on the Shore', 'Fiction', 13.99, 2002, 3);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Half of a Yellow Sun', 'Historical Fiction', 14.99, 2006, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Americanah', 'Fiction', 15.99, 2013, 4);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('1984', 'Dystopian', 10.99, 1949, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Animal Farm', 'Dystopian', 7.99, 1945, 5);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('The House of the Spirits', 'Magical Realism', 13.99, 1982, 6);
INSERT INTO books (title, genre, price, published_year, author_id) VALUES ('Eva Luna', 'Fiction', 11.99, 1987, 6);

-- Step 4: Query data with SELECT, WHERE, ORDER BY

-- Get all books
SELECT * FROM books;

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

-- Combine conditions
SELECT title, price, published_year FROM books
WHERE published_year > 2000 AND price < 15.00;

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

-- Sort by year and limit to 5
SELECT title, published_year FROM books ORDER BY published_year DESC LIMIT 5;

-- Step 5: Create Customers and Orders tables

CREATE TABLE customers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO customers (name, email) VALUES ('Alice Chen', 'alice@example.com');
INSERT INTO customers (name, email) VALUES ('Bob Martinez', 'bob@example.com');
INSERT INTO customers (name, email) VALUES ('Carol Johnson', 'carol@example.com');
INSERT INTO customers (name, email) VALUES ('David Kim', 'david@example.com');

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

-- Step 6: Join tables

-- Order details with customer and book names
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 books by authors from the United Kingdom
SELECT books.title, authors.name AS author
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE authors.country = 'United Kingdom';

-- Full order report
SELECT
    customers.name AS customer,
    books.title AS book,
    authors.name AS author,
    books.price,
    orders.quantity,
    (books.price * orders.quantity) AS total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN books ON orders.book_id = books.id
INNER JOIN authors ON books.author_id = authors.id
ORDER BY customers.name;

-- Step 7: Aggregate functions

-- 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
ORDER BY book_count DESC;

-- Total revenue 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;

-- Average, max, min price per genre
SELECT
    genre,
    AVG(price) AS avg_price,
    MAX(price) AS max_price,
    MIN(price) AS min_price
FROM books
GROUP BY genre;

Secret mission

Build a Book Recommendation Query

Ever wonder how Amazon knows what books you might like? Build a recommendation query that suggests books to customers based on their purchase history and favourite genres.

Clean Up Your Resources

Clean Up Your Resources

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

Resources you used:

  • DB Browser for SQLite (installed application).
  • bookstore.db database file.
  • bookstore_queries.sql script file.

Keep everything running

No action needed. Choose this if you want to keep practicing SQL queries or extending your bookstore database with new tables and data.

  • Your bookstore.db file stays on your computer ready to open any time.
  • Your bookstore_queries.sql file serves as a reference for future SQL projects.
  • DB Browser for SQLite remains installed for whenever you want to explore databases.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your files stay on disk and are ready to reopen whenever you come back.

  • Close DB Browser for SQLite by clicking the X in the top-right corner of the window.
  • Your bookstore.db and bookstore_queries.sql files remain saved on your computer.
  • To resume later, open DB Browser for SQLite and click Open Database to reload your bookstore.db file.

Delete - I don't want to use this again

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

  • Close DB Browser for SQLite if it is still open.
  • Delete your bookstore.db file. Navigate to wherever you saved it and delete it (right-click the file, then select Delete).
  • Delete your bookstore_queries.sql file the same way.
  • To uninstall DB Browser for SQLite, press the Windows key and search for Add or remove programs.
  • Find DB Browser for SQLite in the list, click it, then click Uninstall.

Nice Work!

Nice Work!

You just built a fully functional relational bookstore database from scratch using SQL. From designing tables and inserting data to writing complex queries that answer real business questions, you now have hands-on experience with the skills that power every data-driven application.

You've learned how to:

  • Designed a 4-table relational schema with proper foreign keys linking authors, books, customers, and orders.
  • Wrote JOIN queries across multiple tables to combine related data into full business reports.
  • Used aggregate functions (COUNT, SUM, AVG, MAX, MIN) with GROUP BY to extract insights like revenue per customer and books per author.
  • Built a book recommendation engine using subqueries and NOT IN to suggest books based on purchase history.

Ready to quiz yourself?