Build a Bookstore Database with SQL

Design a multi-table relational database and query it like a data analyst.

Introduction

30 Second Summary

Every bookstore needs a way to track its inventory, know who wrote each book, and figure out which titles are selling best. Behind the scenes, all of that information lives in a database.

In this project, you will build a bookstore database from scratch using SQL, the universal language for working with data. You will design tables, fill them with realistic data, and write queries that answer real business questions.

What You'll Build

A fully functional bookstore database with three related tables that you can query for business insights, just like a data analyst would in the real world.

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

  • A relational schema with proper primary keys, foreign keys, and constraints connecting authors, books, and sales.
  • Realistic sample data representing a small bookstore's inventory and sales history.
  • A collection of SQL queries that answer questions like "Which author generated the most revenue?" and "What are the top 3 best-selling books?"
  • Secret Mission: Add a Customers table, link it to Sales, and write queries to find your most loyal customers.

Are there any prerequisites?

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

Design and Create Your Tables

Your empty database is ready. Now you need to give it structure by creating tables that define what data your bookstore can store and how that data relates to other data.

A relational database organizes information into tables. Each table has columns (the type of data it holds) and rows (the actual records). In this step, you will create three tables that work together: one for authors, one for books, and one for sales.

In this step, get ready to:
  • Create the authors table with columns for id, name, and country.
  • Create the books table with a foreign key linking each book to its author.
  • Create the sales table to track how many copies of each book are sold.
Create the authors table

Every database starts with a schema. The schema defines the structure of your tables, including what columns they have and what type of data each column accepts.

  • Click the Execute SQL tab in the main pane of DB Browser for SQLite.
  • You will see a blank editor pane at the top (labeled SQL 1), a Results pane in the middle, and a Messages pane at the bottom.
  • Type the following SQL into the editor pane to create your first table:
-- Create the authors table
CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    country TEXT
);
  • Click the Execute SQL button in the toolbar above the editor pane (or press Ctrl+Enter) to run your statement.

What does this code do?

This creates a table called authors with three columns:

  • id is the unique identifier for each author. INTEGER PRIMARY KEY AUTOINCREMENT means the database assigns a new number automatically every time you add a row.
  • name stores the author's full name. TEXT NOT NULL means this column must always have a value.
  • country stores where the author is from. It is optional (no NOT NULL constraint), so you can leave it blank.
  • Check the Messages pane at the bottom of the Execute SQL tab. You should see a confirmation that the query executed successfully.
  • Click the Database Structure tab. You should see authors listed as a table.

Don't see the authors table?

  • Make sure your SQL ends with a semicolon (;). Without it, the database does not know your statement is complete.
  • Check the Messages pane for a red error message. A common mistake is misspelling AUTOINCREMENT or INTEGER.
  • Help me fix my CREATE TABLE error.
Create the books table

Books belong to authors. To represent this relationship, the books table needs a column that points to a specific author in the authors table. This pointer is called a foreign key.

  • Click back on the Execute SQL tab.
  • Clear the editor pane (select all text and delete it).
  • Type the following SQL to create the books table:
-- Create the books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    genre TEXT,
    price REAL NOT NULL,
    published_year INTEGER,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);
  • Click the Execute SQL button (or press Ctrl+Enter) to run the statement.

What does this code do?

This creates a books table with six columns plus a foreign key constraint:

  • price REAL NOT NULL stores the book's price as a decimal number. REAL is SQLite's type for floating-point numbers.
  • author_id INTEGER NOT NULL stores the id of the book's author. Every book must have an author.
  • FOREIGN KEY (author_id) REFERENCES authors(id) tells the database that author_id must match an existing id in the authors table. This is what connects the two tables together.
  • Check the Messages pane for a success confirmation.
  • Click the Database Structure tab. You should now see both authors and books listed.

Seeing an error about the authors table?

  • The FOREIGN KEY line references the authors table. If you did not successfully create that table in the previous substep, this statement will fail. Go back and create the authors table first.
  • Make sure authors(id) is spelled exactly as shown. The table name is case-sensitive in the reference.
  • Help me debug my books table creation error.
Create the sales table

The sales table tracks every time a book is purchased. Each sale links back to a specific book using another foreign key.

  • Switch back to the Execute SQL tab.
  • Clear the editor pane.
  • Type the following SQL to create the sales table:
-- Create the sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    sale_date TEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id)
);
  • Click the Execute SQL button (or press Ctrl+Enter) to run the statement.

What does this code do?

This creates the sales table with four columns:

  • book_id links each sale to a specific book in the books table via a foreign key.
  • quantity records how many copies were sold in this transaction.
  • sale_date stores the date as text in ISO-8601 format (like 2024-01-15). SQLite stores dates as text strings.
  • Check the Messages pane for a success confirmation.
  • Click the Database Structure tab. You should now see all three tables listed: authors, books, and sales.
  • Click the Write Changes button in the main toolbar to save your tables to the database file.

Why click Write Changes?

DB Browser for SQLite does not automatically save structural changes (like creating tables) to the database file on disk. Clicking Write Changes persists your work so it is still there if you close and reopen the application.

Don't see all three tables?

  • If only one or two tables appear in the Database Structure tab, go back to the Execute SQL tab and re-run any failed statements. Fix any typos shown in the Messages pane.
  • Remember that tables must be created in order. The books table references authors, and sales references books. If a referenced table does not exist yet, the statement will fail.
  • Help me figure out why my tables are not showing up in Database Structure.

✔️ Awesome, I've got everything!

Great. Make sure you have clicked Write Changes to save your database before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL for all three tables. You can run them all at once in the Execute SQL editor if you need to start fresh:

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

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

-- Create the sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    sale_date TEXT NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

Your database schema is complete. You have three tables connected by foreign keys, ready to hold bookstore data. Next up, you will fill these tables with sample authors, books, and sales records.

Populate Your Database with Sample Data

You have three empty tables ready and waiting. A database without data is like a library with empty shelves.

In this step, you will fill those tables with realistic bookstore data using SQL's INSERT INTO statement. The order matters here. Because books references authors and sales references books, you need to populate them in sequence: authors first, then books, then sales.

In this step, get ready to:
  • Insert 5 authors into the authors table.
  • Insert 10 books with varying genres, prices, and publication years.
  • Insert 16 sales records spanning different dates and quantities.
Insert authors into the authors table

Authors need to go in first because the books table's author_id column references author IDs. If you tried to insert a book before its author exists, the foreign key relationship would point to nothing.

  • Switch to the Execute SQL tab in DB Browser.
  • Clear any existing SQL from the previous step.
  • Insert your first batch of data by pasting the following SQL into the editor pane:
-- Insert authors
INSERT INTO authors (name, country) VALUES
('George Orwell', 'United Kingdom'),
('Haruki Murakami', 'Japan'),
('Chimamanda Ngozi Adichie', 'Nigeria'),
('Gabriel Garcia Marquez', 'Colombia'),
('Toni Morrison', 'United States');

What does this code do?

INSERT INTO authors (name, country) tells SQLite which table and which columns to fill.

The VALUES keyword is followed by a comma-separated list of rows. Each row is a pair of parentheses containing one value per named column, in the same order. You do not supply an id value because AUTOINCREMENT handles that automatically.

  • Click the Execute SQL button in the toolbar (or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
  • Check the bottom message pane. You should see a message confirming that the query executed successfully.
  • Verify the data by running this quick query:
SELECT * FROM authors;

You should see 5 rows in the results pane, listing each author with their assigned ID, name, and country.

Not seeing 5 rows?

  • Make sure each SQL statement ends with a semicolon. Without it, DB Browser does not know the statement is complete.
  • Check for typos in column names. The columns must be name and country (lowercase), matching what you defined in the CREATE TABLE statement.
  • If you see a "table authors already has data" style error, you may have run the INSERT twice. Run DELETE FROM authors; to clear it out, then re-run the INSERT.

Help me troubleshoot my INSERT INTO authors statement.

Insert books into the books table

Now that authors exist in the database, you can add books that reference them. Each book's author_id must match an existing author's id.

  • Clear the SQL editor pane.
  • Insert 10 books by pasting the following SQL:
-- Insert books
INSERT INTO books (title, genre, price, published_year, author_id) VALUES
('1984', 'Dystopian', 9.99, 1949, 1),
('Animal Farm', 'Satire', 7.99, 1945, 1),
('Norwegian Wood', 'Fiction', 12.99, 1987, 2),
('Kafka on the Shore', 'Fiction', 14.99, 2002, 2),
('Half of a Yellow Sun', 'Historical Fiction', 13.99, 2006, 3),
('Americanah', 'Fiction', 11.99, 2013, 3),
('One Hundred Years of Solitude', 'Magical Realism', 10.99, 1967, 4),
('Love in the Time of Cholera', 'Romance', 9.99, 1985, 4),
('Beloved', 'Historical Fiction', 12.99, 1987, 5),
('Song of Solomon', 'Fiction', 11.99, 1977, 5);

What does this code do?

This inserts 10 books across 5 authors, giving each author exactly 2 books. Notice that each row ends with a number like 1, 2, or 3. That is the author_id matching the auto-generated ID from the authors table.

The price column uses REAL (a floating-point number), so values like 9.99 work. The published_year is a plain INTEGER.

  • Click Execute SQL (or press Cmd+Enter / Ctrl+Enter).
  • Verify by running:
SELECT * FROM books;

You should see 10 rows in the results pane, each with an auto-assigned id, title, genre, price, year, and the author_id linking back to the authors table.

Seeing a foreign key error?

  • Make sure you ran the authors INSERT first. Books reference author IDs, so those IDs must already exist.
  • Double-check that author_id values (1 through 5) match the IDs in your authors table. Run SELECT * FROM authors; to confirm.

Help me debug my books INSERT statement.

Insert sales records into the sales table

Sales records capture the transactions your bookstore has made. Each sale links to a specific book and records how many copies were sold on a given date.

  • Clear the SQL editor pane.
  • Insert 16 sales records by pasting the following SQL:
-- Insert sales records
INSERT INTO sales (book_id, quantity, sale_date) VALUES
(1, 3, '2024-01-15'),
(1, 2, '2024-02-20'),
(1, 1, '2024-03-10'),
(2, 2, '2024-01-22'),
(3, 4, '2024-02-05'),
(3, 1, '2024-03-18'),
(4, 2, '2024-01-30'),
(5, 3, '2024-02-14'),
(5, 2, '2024-03-25'),
(6, 1, '2024-01-10'),
(7, 5, '2024-02-28'),
(7, 3, '2024-03-15'),
(8, 1, '2024-03-01'),
(9, 4, '2024-01-05'),
(9, 2, '2024-02-18'),
(10, 1, '2024-03-22');

What does this code do?

Each row represents one sale: which book was sold (book_id), how many copies (quantity), and when (sale_date).

Notice that some books appear multiple times. Book 1 (1984) has three separate sales across January, February, and March. This is realistic. A real bookstore sells the same book on different days in different quantities.

Dates are stored as TEXT in ISO-8601 format (YYYY-MM-DD). SQLite does not have a dedicated date type, but this format sorts chronologically as plain text.

  • Click Execute SQL (or press Cmd+Enter / Ctrl+Enter).
  • Verify by running:
SELECT * FROM sales;

You should see 16 rows in the results pane, with sale dates ranging from 2024-01-05 to 2024-03-25.

Seeing fewer than 16 rows?

  • Check that you copied all 16 lines of VALUES. If you accidentally left some out, you can run the missing rows as a separate INSERT.
  • Make sure every row is separated by a comma, except the very last row which ends with a semicolon.

Help me troubleshoot my sales INSERT.

  • Click Write Changes in the toolbar to save all your inserts to the database file.

Why click Write Changes?

DB Browser for SQLite holds changes in memory until you explicitly write them to disk. If you close the application without clicking Write Changes, your data will be lost.

✔️ Awesome, I've got everything!

Great. Make sure you clicked Write Changes so your data is saved to bookstore.db.

ⓧ I'd like to double check the full code

Here is the complete INSERT code for all three tables. Compare it against what you ran:

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

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

-- Insert sales records
INSERT INTO sales (book_id, quantity, sale_date) VALUES
(1, 3, '2024-01-15'),
(1, 2, '2024-02-20'),
(1, 1, '2024-03-10'),
(2, 2, '2024-01-22'),
(3, 4, '2024-02-05'),
(3, 1, '2024-03-18'),
(4, 2, '2024-01-30'),
(5, 3, '2024-02-14'),
(5, 2, '2024-03-25'),
(6, 1, '2024-01-10'),
(7, 5, '2024-02-28'),
(7, 3, '2024-03-15'),
(8, 1, '2024-03-01'),
(9, 4, '2024-01-05'),
(9, 2, '2024-02-18'),
(10, 1, '2024-03-22');

Your bookstore now has real data. Next up, you will write queries to ask questions of this data and start getting answers back.

Query Your Data with SELECT, WHERE, and ORDER BY

Your bookstore database is loaded with authors, books, and sales records. Now comes the exciting part. You get to ask questions of your data.

SQL's real power is its ability to retrieve exactly the information you need from a table. In this step, you'll learn the three most fundamental query tools: SELECT to choose what columns to see, WHERE to filter rows, and ORDER BY to sort results.

In this step, get ready to:
  • Write SELECT queries to view your books data.
  • Filter results using WHERE clauses.
  • Sort your results with ORDER BY.
Write basic SELECT queries to view and filter books

Every SQL query starts with SELECT. This keyword tells the database which columns you want to retrieve.

  • Return to the Execute SQL tab in DB Browser for SQLite.
  • Clear any existing SQL in the editor pane.
  • Retrieve your book data by typing the following queries into the SQL editor:
-- View all books
SELECT * FROM books;

-- Find all Fiction books
SELECT title, price FROM books WHERE genre = 'Fiction';
  • Highlight the first query (SELECT * FROM books;) and press Ctrl+Return (macOS) or F5 (Windows) to run it.
  • Check the Results pane below the editor. You should see all 10 books with every column displayed.
  • Now highlight the second query (SELECT title, price FROM books WHERE genre = 'Fiction';) and press Ctrl+Return or F5 to run it.
  • You should see 4 rows in the Results pane: Norwegian Wood, Kafka on the Shore, Americanah, and Song of Solomon.

What do these queries do?

SELECT * FROM books retrieves every column (* means "all columns") from the books table. This is the quickest way to see everything in a table.

SELECT title, price FROM books WHERE genre = 'Fiction' retrieves only the title and price columns. The WHERE clause filters to only rows where the genre is exactly Fiction.

Run only what you highlight

When you have multiple queries in the editor, highlight the specific query you want to run before pressing Ctrl+Return or F5. If nothing is selected, DB Browser executes all statements and the Results pane shows only the last query's output.

Seeing an error instead of results?

Make sure each query ends with a semicolon (;). Missing semicolons are the most common cause of syntax errors when running multiple statements.

Check that you typed 'Fiction' with single quotes, not double quotes. SQL uses single quotes for text values.

Help me debug my SQL syntax

Use WHERE to filter by price and year

The WHERE clause works with comparison operators too. You can filter numbers using > (greater than), < (less than), and = (equals).

  • Filter by publication year and price by adding these queries below your existing SQL in the editor:
-- Find books published after 2000
SELECT title, published_year FROM books WHERE published_year > 2000;

-- Find books priced above $12
SELECT title, price FROM books WHERE price > 12.00;
  • Highlight the first new query and press Ctrl+Return or F5 to run it.
  • You should see 3 rows: Kafka on the Shore (2002), Half of a Yellow Sun (2006), and Americanah (2013).
  • Highlight the second new query and press Ctrl+Return or F5 to run it.
  • You should see 4 rows: Norwegian Wood, Kafka on the Shore, Half of a Yellow Sun, and Beloved.

What do these queries do?

WHERE published_year > 2000 filters for books published after the year 2000. The > operator means "greater than".

WHERE price > 12.00 filters for books costing more than $12. Because price is stored as a REAL (decimal number), you can compare it directly against numeric values.

Getting 0 rows back?

Double-check that you're using the > symbol (greater than), not < (less than). Swapping the direction of the comparison is a common mistake.

Also confirm you're querying the books table, not authors or sales.

Help me debug my WHERE clause

Sort your results with ORDER BY

So far, your results come back in the order the data was inserted. ORDER BY lets you control the sort order of your results.

  • Sort your book data by adding the following queries below your existing SQL in the editor:
-- Sort books by price (cheapest first)
SELECT title, price FROM books ORDER BY price ASC;

-- Sort books by year (newest first)
SELECT title, published_year FROM books ORDER BY published_year DESC;
  • Highlight the first query and press Ctrl+Return or F5 to run it.
  • You should see all 10 books sorted by price, with Animal Farm ($7.99) at the top.
  • Highlight the second query and press Ctrl+Return or F5 to run it.
  • You should see all 10 books sorted by year, with Americanah (2013) at the top.

What does ORDER BY do?

ORDER BY price ASC sorts results by the price column in ascending order (smallest to largest). ASC stands for ascending.

ORDER BY published_year DESC sorts by published_year in descending order (largest to smallest). DESC stands for descending. This puts the newest books first.

Results not sorted correctly?

Make sure you typed ASC or DESC after your column name. Without a direction, SQL defaults to ascending order.

If you see results from a different query, make sure you highlighted only the query you want to run before pressing Ctrl+Return or F5.

Help me troubleshoot my ORDER BY query

✔️ Awesome, I've got everything!

Great. You now have six working queries that retrieve, filter, and sort data from your bookstore.

ⓧ I'd like to double check the full code

Here is the full set of queries you should have in your SQL editor:

-- View all books
SELECT * FROM books;

-- Find all Fiction books
SELECT title, price FROM books WHERE genre = 'Fiction';

-- Find books published after 2000
SELECT title, published_year FROM books WHERE published_year > 2000;

-- Find books priced above $12
SELECT title, price FROM books WHERE price > 12.00;

-- Sort books by price (cheapest first)
SELECT title, price FROM books ORDER BY price ASC;

-- Sort books by year (newest first)
SELECT title, published_year FROM books ORDER BY published_year DESC;

You've just learned how to retrieve, filter, and sort data from a single table. Next up, you'll connect multiple tables together using JOIN queries to see how books relate to their authors and sales records.

Connect Tables with JOIN Queries

So far, every query you have written pulls data from a single table. But the real power of a relational database is in the relationships between tables.

Your books table stores an author_id number, but that number is meaningless on its own. To see the actual author name next to each book title, you need to connect the two tables. That is exactly what a JOIN does.

In this step, get ready to:
  • Write an INNER JOIN to display book titles alongside their author names.
  • Combine books and sales data to see which titles have sold and how many copies.
  • Use a LEFT JOIN to find books that have never been sold.
Show book titles with their author names

An INNER JOIN combines rows from two tables where a specified condition matches in both. If a row in one table has no matching row in the other, it is excluded from the results entirely.

  • Clear the Execute SQL editor in DB Browser, then paste and run this query:
-- Show book titles with their author names
SELECT books.title, authors.name AS author, books.genre
FROM books
INNER JOIN authors ON books.author_id = authors.id;

What does this query do?

The FROM books clause sets the starting table. INNER JOIN authors tells SQLite to bring in the authors table alongside it.

The ON books.author_id = authors.id part is the join condition. It matches each book to the author whose id equals that book's author_id. The AS author gives the name column a friendlier label in the results.

  • Check the Results pane. You should see 10 rows, one for each book, with the author's actual name displayed instead of a number.

Seeing an error or empty results?

  • Make sure you use books.author_id = authors.id in the ON clause. Swapping the column names produces no matches.
  • Check that each table name is prefixed before its column (e.g. books.title, not just title). When two tables are involved, SQLite needs to know which table each column belongs to.
  • Why is my INNER JOIN returning no results?
Show sales data alongside book titles

The sales table stores a book_id but no book title. Joining sales to books lets you see which titles actually sold and when.

  • Clear the editor, then paste and run this query:
-- Show sales with book titles and quantities
SELECT books.title, sales.quantity, sales.sale_date
FROM sales
INNER JOIN books ON sales.book_id = books.id
ORDER BY sales.sale_date;

What does this query do?

This time the starting table is sales. The join pulls in the matching book row for each sale using sales.book_id = books.id.

The ORDER BY sales.sale_date sorts the results chronologically, so you can see the sales timeline from earliest to most recent.

  • Check the Results pane. You should see 16 rows (one per sales record) with readable book titles, quantities, and dates sorted from January through March.

Results not in date order?

  • Make sure ORDER BY sales.sale_date is at the end of the query, after the JOIN clause. Placing it before the JOIN causes a syntax error.
  • My JOIN query results are not sorted correctly. What am I doing wrong?
Find books that have never been sold

An INNER JOIN only returns rows that have a match in both tables. If you want to find books with no sales at all, you need a LEFT JOIN. A LEFT JOIN keeps every row from the left table (books) and fills in NULL for columns from the right table (sales) when there is no match.

  • Clear the editor, then paste and run this query:
-- Find books that have never been sold
SELECT books.title
FROM books
LEFT JOIN sales ON books.id = sales.book_id
WHERE sales.id IS NULL;

What does this query do?

The LEFT JOIN attempts to match every book with its sales records. Books that have never been sold will have NULL in every sales column because no matching row exists.

The WHERE sales.id IS NULL filter keeps only the unmatched rows. The result is a list of books with zero sales. This pattern is extremely common in real-world analytics for finding "orphaned" records.

  • Check the Results pane. You should see Love in the Time of Cholera listed. This is the only book in your database with no sales record.

Seeing all 10 books instead of just one?

  • Make sure you included the WHERE sales.id IS NULL line. Without it, the LEFT JOIN returns every book (matched and unmatched).
  • Check that you wrote IS NULL, not = NULL. In SQL, NULL comparisons require IS NULL because NULL is not equal to anything, not even itself.
  • My LEFT JOIN is returning too many rows. Help me fix the WHERE clause.

Why did only one book show up?

Look back at your sales data. You inserted records for book_id values 1 through 10, but book 8 (Love in the Time of Cholera) only appears once. Actually, it does appear once with book_id = 8 in your sales table. If you see zero results, every book has at least one sale. If you see exactly one result, your data matches this guide perfectly.

✔️ Awesome, I've got everything!

Great. You have written all three JOIN queries successfully.

ⓧ I'd like to double check the full code

Here is the complete set of JOIN queries you should have run in this step:

-- Show book titles with their author names
SELECT books.title, authors.name AS author, books.genre
FROM books
INNER JOIN authors ON books.author_id = authors.id;

-- Show sales with book titles and quantities
SELECT books.title, sales.quantity, sales.sale_date
FROM sales
INNER JOIN books ON sales.book_id = books.id
ORDER BY sales.sale_date;

-- Find books that have never been sold
SELECT books.title
FROM books
LEFT JOIN sales ON books.id = sales.book_id
WHERE sales.id IS NULL;

You can now pull connected data from multiple tables in a single query. Next up, you will use GROUP BY and aggregate functions to answer real business questions like "Which author generated the most revenue?"

Analyze Your Data with GROUP BY and Aggregates

Your JOIN queries can now pull data from multiple tables at once. But a bookstore owner doesn't want to see every individual sale. They want answers to bigger questions: Which book sells the most? Which author brings in the most revenue?

This is where aggregate functions come in. Functions like SUM, COUNT, and AVG crunch multiple rows into a single summary value. Paired with GROUP BY, they become powerful analytical tools that answer real business questions.

In this step, get ready to:
  • Use SUM, AVG, and COUNT to summarize your bookstore data.
  • Group results by author to find who generates the most revenue.
  • Find the top 3 best-selling books using LIMIT.
Calculate total books sold with SUM and GROUP BY

The SUM function adds up values across multiple rows. When you combine it with GROUP BY, SQL groups rows that share a value together, then applies the function to each group separately.

  • In the Execute SQL tab, clear any previous queries and run this to find total copies sold per book:
-- Total quantity sold per book
SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
INNER JOIN sales ON books.id = sales.book_id
GROUP BY books.title
ORDER BY total_sold DESC;

What does this query do?

This query joins the books and sales tables, then groups all sales rows by book title. For each group, SUM(sales.quantity) adds up every quantity value in that group.

The AS total_sold part gives the calculated column a readable name. ORDER BY total_sold DESC sorts the results so the highest-selling book appears first.

  • Click the Execute SQL button (or press Cmd+Return on macOS or Ctrl+Return on Windows).
  • In the Results pane, you should see each book title with its total quantity sold. "One Hundred Years of Solitude" should appear near the top with 8 copies sold.

What is GROUP BY?

Think of GROUP BY as sorting rows into piles. All sales for "1984" go in one pile, all sales for "Norwegian Wood" go in another. Then the aggregate function (SUM, COUNT, AVG) runs once per pile and gives you one result row per group.

Now let's try two simpler aggregates that operate on an entire table without grouping.

  • Clear the SQL editor and run these two queries to find the average book price and total number of sales transactions:
-- Average book price
SELECT AVG(price) AS average_price FROM books;

-- Total number of sales transactions
SELECT COUNT(*) AS total_transactions FROM sales;

What do these queries do?

  • AVG(price) calculates the average across all values in the price column. No GROUP BY means the entire table is treated as one group.
  • COUNT(*) counts every row in the table. The * means "count all rows regardless of column values."
  • Execute both queries. You should see an average price around 11.79 and 16 total transactions.

Seeing only one result?

DB Browser shows results for the last executed statement when you run multiple queries at once. Select just one query (highlight it) and press the execute button to see its results individually.

Alternatively, run each query one at a time by placing your cursor on the query you want and clicking execute.

help me run multiple SQL queries in DB Browser for SQLite

Find revenue per author with multi-table GROUP BY

Revenue is price multiplied by quantity. To answer "which author generated the most revenue," you need to join all three tables: authors, books, and sales.

  • Clear the SQL editor and run this query to calculate total revenue per author:
-- Revenue per author
SELECT authors.name, SUM(books.price * sales.quantity) AS total_revenue
FROM authors
INNER JOIN books ON authors.id = books.author_id
INNER JOIN sales ON books.id = sales.book_id
GROUP BY authors.name
ORDER BY total_revenue DESC;

What does this query do?

  • The two INNER JOIN clauses chain all three tables together: authors to books (via author_id), then books to sales (via book_id).
  • books.price * sales.quantity calculates revenue for each individual sale. SUM(...) then adds up all sale revenues within each author's group.
  • GROUP BY authors.name tells SQL to create one result row per author, collapsing all their books and sales into a single revenue total.
  • Execute the query. You should see five authors listed with their total revenue, sorted from highest to lowest.

Getting an error about ambiguous column names?

When joining multiple tables, you must prefix column names with the table name (e.g., books.price not just price). Check that every column reference in your query uses the table.column format.

help me fix an ambiguous column name error in my SQL JOIN query

Find the top 3 best-selling books with LIMIT

The LIMIT clause restricts how many rows a query returns. Combined with ORDER BY ... DESC, it gives you a "top N" list.

  • Clear the SQL editor and run this query to find your bookstore's top 3 best-sellers:
-- Top 3 best-selling books
SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
INNER JOIN sales ON books.id = sales.book_id
GROUP BY books.title
ORDER BY total_sold DESC
LIMIT 3;

What does LIMIT do?

LIMIT 3 cuts the results after the first 3 rows. Because ORDER BY total_sold DESC puts the highest sellers first, you get exactly the top 3 books.

This is a common pattern in data analysis. "Top 10 customers," "5 most recent orders," and "3 lowest-rated products" all use the same ORDER BY + LIMIT combination.

  • Execute the query. You should see exactly 3 rows in the Results pane showing the top-selling books by total quantity.

Seeing more than 3 rows?

Make sure LIMIT 3 appears at the very end of the query, after ORDER BY. Also check that you don't have a semicolon before the LIMIT keyword. The semicolon marks the end of a statement, so anything after it is treated as a new query.

help me use LIMIT correctly in my SQL query

✔️ Awesome, I've got everything!

Great. Double check you have all five aggregate queries working in your Execute SQL tab.

ⓧ I'd like to double check the full code

Here is the complete set of aggregate queries from this step:

-- Total quantity sold per book
SELECT books.title, SUM(sales.quantity) AS total_sold
FROM books
INNER JOIN sales ON books.id = sales.book_id
GROUP BY books.title
ORDER BY total_sold DESC;

-- Revenue per author
SELECT authors.name, SUM(books.price * sales.quantity) AS total_revenue
FROM authors
INNER JOIN books ON authors.id = books.author_id
INNER JOIN sales ON books.id = sales.book_id
GROUP BY authors.name
ORDER BY total_revenue DESC;

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

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

-- Total number of sales transactions
SELECT COUNT(*) AS total_transactions FROM sales;

You can now turn raw sales data into meaningful business insights. Next up, you'll learn how to modify and delete data safely with UPDATE and DELETE.

Modify and Delete Data with UPDATE and DELETE

You've built a complete bookstore database, filled it with data, and written queries that extract real business insights.

In the real world, data changes constantly. Prices get adjusted, incorrect records get removed, and inventory gets corrected. In this step, you'll learn how to safely modify and remove data using UPDATE and DELETE.

In this step, get ready to:
  • Update a book's price and verify the change.
  • Delete a sales record using the preview-first technique.
  • Confirm your modifications with verification queries.
Update a book's price and verify the change

The UPDATE statement lets you change existing values in a table. You always pair it with a WHERE clause to target specific rows.

  • In the Execute SQL tab, clear the editor pane and type the following SQL to update the price of 1984 and then verify the change:
-- Update a book's price
UPDATE books SET price = 8.99 WHERE title = '1984';

-- Verify the update
SELECT title, price FROM books WHERE title = '1984';
  • Press Cmd+Enter (macOS) or Ctrl+Enter (Windows) to execute the SQL.

What does this code do?

  • UPDATE books SET price = 8.99 tells SQLite which table to modify and what the new value should be.
  • WHERE title = '1984' targets only the row where the title matches. Without a WHERE clause, every row in the table would be updated.
  • The SELECT query confirms the change took effect by fetching the updated row.
  • Check the Results pane. You should see one row with 1984 and a price of 8.99.
  • Click Write Changes in the main toolbar to save the modification to your bookstore.db file.

Still seeing the old price of 9.99?

  • Make sure both statements end with a semicolon. Multiple statements in one editor pane each need their own semicolon.
  • Check that you typed the title exactly as '1984' with single quotes. SQL string comparisons are case-sensitive.
  • If only the UPDATE ran but not the SELECT, highlight both statements and click the Execute SQL button in the toolbar to run them together.

Help me figure out why my UPDATE query didn't change the price.

Practice safe deletion by previewing with SELECT before DELETE

DELETE permanently removes rows from a table. A professional habit is to always preview what you are about to delete by running a SELECT with the same WHERE clause first.

  • Clear the editor pane and type the following SQL to preview, delete, and verify the removal of a sales record:
-- Preview what will be deleted
SELECT * FROM sales WHERE id = 16;

-- Delete a sales record
DELETE FROM sales WHERE id = 16;

-- Verify deletion
SELECT * FROM sales WHERE book_id = 10;

What does this code do?

  • The first SELECT shows you exactly which row will be deleted. This is the safety check. You confirm you are targeting the right record before removing it.
  • DELETE FROM sales WHERE id = 16 removes that single row permanently. Without the WHERE clause, every row in the table would be deleted.
  • The final SELECT confirms the record is gone by checking all sales for book_id = 10 (Song of Solomon). The result should be empty.
  • Run all three statements by pressing Cmd+Enter (macOS) or Ctrl+Enter (Windows).
  • Check the Results pane after the final query. You should see no rows returned, confirming the record has been removed.
  • Click Write Changes in the toolbar to persist the deletion to your database file.

Why preview before deleting?

In production databases, there is no undo button. A mistyped WHERE clause in a DELETE can wipe out thousands of rows instantly.

The preview-first technique catches mistakes before they become permanent. Data analysts and backend developers use this habit every day.

Query returned rows when you expected none?

  • If the final SELECT still shows a row, the DELETE may not have executed. Make sure all three statements have semicolons and were selected when you ran them.
  • Double-check that your DELETE targets id = 16 (not book_id = 16). The id column is the sale's unique identifier.

Help me figure out why my DELETE query didn't remove the row.

✔️ Awesome, I've got everything!

Great. Double check you've clicked Write Changes to save both the price update and the deletion to your bookstore.db file.

ⓧ I'd like to double check the full code

Here is the complete set of UPDATE and DELETE queries from this step:

-- Update a book's price
UPDATE books SET price = 8.99 WHERE title = '1984';

-- Verify the update
SELECT title, price FROM books WHERE title = '1984';

-- Preview what will be deleted
SELECT * FROM sales WHERE id = 16;

-- Delete a sales record
DELETE FROM sales WHERE id = 16;

-- Verify deletion
SELECT * FROM sales WHERE book_id = 10;

You now know how to safely modify and remove data. Your bookstore database is complete with a full CRUD cycle. Next, you can take on the Secret Mission and extend your database with a brand new Customers table.

Secret mission

Add a Customers Table

Your bookstore database tracks books and sales, but who is actually buying them? In this secret mission, you'll add a Customers table, link it to your sales data, and write queries to identify your most loyal customers.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine, 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 database file (your SQLite database with all tables and data).
  • SQL query files (create_tables.sql, insert_data.sql, queries_select.sql, queries_joins.sql, queries_aggregates.sql, queries_update_delete.sql, secret_mission.sql).
  • DB Browser for SQLite application (installed on your computer).

Keep everything running

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

  • Your bookstore.db file stays wherever you saved it. You can reopen it any time in DB Browser for SQLite.
  • Your .sql files serve as a reference library of all the queries you wrote.
  • DB Browser for SQLite remains installed and ready to use for future projects.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your files stay safe on disk until you return.

  • In DB Browser for SQLite, click File then Close Database.
  • Quit the application entirely.
  • When you come back, open DB Browser for SQLite and use File then Open Database to reload bookstore.db.

Delete - I don't want to use this again

Remove all project files and optionally uninstall DB Browser for SQLite.

  • Close DB Browser for SQLite if it is still open.
  • Delete your bookstore.db file. Locate it wherever you saved it and move it to the Trash (macOS) or Recycle Bin (Windows).
  • Delete any .sql files you saved during the project (create_tables.sql, insert_data.sql, queries_select.sql, queries_joins.sql, queries_aggregates.sql, queries_update_delete.sql, secret_mission.sql).

To uninstall DB Browser for SQLite:

macOS

  • Open Finder and navigate to your Applications folder.
  • Drag DB Browser for SQLite to the Trash.
  • Empty the Trash to complete the removal.

Windows

  • Open Settings.
  • Select Apps, then Apps & features.
  • Find DB Browser for SQLite in the list, click it, and select Uninstall.

Nice Work!

Nice Work!

You just built a real relational database from scratch and queried it like a data analyst.

You've learned how to:

  • Designed a multi-table relational schema with primary keys, foreign keys, and constraints, then populated it with realistic sample data.
  • Written SELECT queries with WHERE, ORDER BY, INNER JOIN, and LEFT JOIN to connect and filter data across multiple tables.
  • Analyzed data using GROUP BY with aggregate functions like COUNT, SUM, and AVG, and safely modified records with UPDATE and DELETE.
  • Extended the database with a Customers table and wrote advanced analytical queries to find top spenders and inactive customers.

Ready to quiz yourself?