Joining more than two tables in SQL - Mini Project: Build & Apply

Joining More Than Two Tables in SQL

📖 Scenario: You work at a small bookstore that keeps track of books, authors, and sales. The data is stored in three tables: books, authors, and sales. You want to find out which author sold which book and how many copies were sold.

🎯 Goal: Build an SQL query that joins the books, authors, and sales tables to show the book title, author name, and number of copies sold.

📋 What You'll Learn

Create the books table with columns book_id and title.

Create the authors table with columns author_id and name.

Create the sales table with columns sale_id, book_id, and copies_sold.

Add a column author_id to the books table to link books to authors.

Write an SQL query joining all three tables to get title, name, and copies_sold.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses keep data in multiple tables and need to join them to get combined information.

💼 Career

Understanding how to join multiple tables is essential for database querying roles like data analyst, backend developer, and database administrator.

1

Create the books table with sample data

Create a table called books with columns book_id (integer), title (text), and author_id (integer). Insert these exact rows: (1, 'The Great Adventure', 101), (2, 'Mystery of the Night', 102), (3, 'Learning SQL', 103).

SQL

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the authors table with sample data

Create a table called authors with columns author_id (integer) and name (text). Insert these exact rows: (101, 'Alice Johnson'), (102, 'Bob Smith'), (103, 'Carol Lee').

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (1, 'The Great Adventure', 101),
  (2, 'Mystery of the Night', 102),
  (3, 'Learning SQL', 103);

-- Create the authors table and insert data
-- Your code here

Need a hint?

Remember to use CREATE TABLE and INSERT INTO for the authors table.

3

Create the sales table with sample data

Create a table called sales with columns sale_id (integer), book_id (integer), and copies_sold (integer). Insert these exact rows: (1, 1, 150), (2, 2, 200), (3, 3, 120).

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (1, 'The Great Adventure', 101),
  (2, 'Mystery of the Night', 102),
  (3, 'Learning SQL', 103);

CREATE TABLE authors (
  author_id INTEGER,
  name TEXT
);

INSERT INTO authors (author_id, name) VALUES
  (101, 'Alice Johnson'),
  (102, 'Bob Smith'),
  (103, 'Carol Lee');

-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO to add the sales data.

4

Write an SQL query joining all three tables

Write an SQL query that joins books, authors, and sales tables to select the title from books, name from authors, and copies_sold from sales. Use books.author_id = authors.author_id and books.book_id = sales.book_id as join conditions.

SQL

CREATE TABLE books (
  book_id INTEGER,
  title TEXT,
  author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
  (1, 'The Great Adventure', 101),
  (2, 'Mystery of the Night', 102),
  (3, 'Learning SQL', 103);

CREATE TABLE authors (
  author_id INTEGER,
  name TEXT
);

INSERT INTO authors (author_id, name) VALUES
  (101, 'Alice Johnson'),
  (102, 'Bob Smith'),
  (103, 'Carol Lee');

CREATE TABLE sales (
  sale_id INTEGER,
  book_id INTEGER,
  copies_sold INTEGER
);

INSERT INTO sales (sale_id, book_id, copies_sold) VALUES
  (1, 1, 150),
  (2, 2, 200),
  (3, 3, 120);

-- Write the SQL query joining all three tables
-- Your code here

Need a hint?

Use JOIN twice to connect the three tables using the specified keys.