Multiple table JOINs in MySQL - Mini Project: Build & Apply

Multiple Table JOINs in MySQL

📖 Scenario: You are managing a small online bookstore database. The database has three tables: authors, books, and sales. You want to find out which authors have sold books, and details about those sales.

🎯 Goal: Build a MySQL query that joins the authors, books, and sales tables to list each author's name, the title of their book, and the number of copies sold.

📋 What You'll Learn

Create a table called authors with columns author_id (INT) and author_name (VARCHAR).

Create a table called books with columns book_id (INT), title (VARCHAR), and author_id (INT).

Create a table called sales with columns sale_id (INT), book_id (INT), and copies_sold (INT).

Write a SELECT query that uses JOINs to combine these tables and show author_name, title, and copies_sold.

Use INNER JOINs to only show books that have sales.

💡 Why This Matters

🌍 Real World

Online bookstores and many other businesses use multiple related tables to organize data about products, customers, and sales.

💼 Career

Understanding how to join multiple tables is essential for database querying roles, data analysis, and backend development.

1

Create the authors table and insert data

Create a table called authors with columns author_id (INT) and author_name (VARCHAR(50)). Insert these exact rows: (1, 'Jane Austen'), (2, 'Mark Twain'), (3, 'J.K. Rowling').

MySQL

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

Need a hint?

Use CREATE TABLE authors (author_id INT, author_name VARCHAR(50)); and then INSERT INTO authors with the given values.

2

Create the books table and insert data

Create a table called books with columns book_id (INT), title (VARCHAR(100)), and author_id (INT). Insert these exact rows: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2), (103, 'Harry Potter and the Sorcerer''s Stone', 3).

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

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

Need a hint?

Use CREATE TABLE books (book_id INT, title VARCHAR(100), author_id INT); and then INSERT INTO books with the given values.

3

Create the sales table and insert data

Create a table called sales with columns sale_id (INT), book_id (INT), and copies_sold (INT). Insert these exact rows: (1001, 101, 500), (1002, 102, 300), (1003, 103, 1000).

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Harry Potter and the Sorcerer''s Stone', 3);

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

Need a hint?

Use CREATE TABLE sales (sale_id INT, book_id INT, copies_sold INT); and then INSERT INTO sales with the given values.

4

Write a JOIN query to list authors, book titles, and copies sold

Write a SELECT query that uses INNER JOINs to combine authors, books, and sales. Select author_name, title, and copies_sold. Use the exact JOIN syntax: FROM authors JOIN books ON authors.author_id = books.author_id JOIN sales ON books.book_id = sales.book_id.

MySQL

CREATE TABLE authors (
  author_id INT,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Jane Austen'),
(2, 'Mark Twain'),
(3, 'J.K. Rowling');

CREATE TABLE books (
  book_id INT,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2),
(103, 'Harry Potter and the Sorcerer''s Stone', 3);

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

INSERT INTO sales (sale_id, book_id, copies_sold) VALUES
(1001, 101, 500),
(1002, 102, 300),
(1003, 103, 1000);

-- Write the JOIN query below
-- Your code here

Need a hint?

Use INNER JOINs to connect the tables by their IDs and select the requested columns.