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

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

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

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

Hint

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

Practice

1. What is the main purpose of joining more than two tables in SQL?

easy

A. To combine related data from multiple tables into one result set

B. To delete data from multiple tables at once

C. To create new tables automatically

D. To backup tables in the database

Solution

1. Step 1: Understand the concept of JOIN

   JOIN is used to combine rows from two or more tables based on related columns.

2. Step 2: Apply to multiple tables

   Joining more than two tables extends this idea to combine data from several tables into one result.

3. Final Answer:

   To combine related data from multiple tables into one result set -> Option A

4. Quick Check:

   Joining multiple tables = combine data [OK]

Hint: Joining means combining data from tables step-by-step [OK]

Common Mistakes:

• Thinking JOIN deletes or creates tables
• Confusing JOIN with backup or delete operations
• Assuming JOIN works without conditions

2. Which of the following is the correct syntax to join three tables A, B, and C on columns A.id = B.a_id and B.id = C.b_id?

easy

A. SELECT * FROM A, B, C WHERE A.id = B.a_id, B.id = C.b_id;

B. SELECT * FROM A JOIN B ON A.id = B.a_id, C ON B.id = C.b_id;

C. SELECT * FROM A JOIN B JOIN C ON A.id = B.a_id AND B.id = C.b_id;

D. SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id;

Solution

1. Step 1: Check JOIN syntax for multiple tables

   Each JOIN must have its own ON condition to specify how tables connect.

2. Step 2: Validate SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id;

   SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id; correctly joins A to B with ON, then B to C with ON separately.

3. Final Answer:

   SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON B.id = C.b_id; -> Option D

4. Quick Check:

   Each JOIN needs its own ON condition [OK]

Hint: Use separate ON for each JOIN clause [OK]

Common Mistakes:

• Combining multiple ON conditions in one JOIN
• Using commas with JOIN incorrectly
• Missing ON clause for a JOIN

3. Given tables:
Students(id, name),
Enrollments(student_id, course_id),
Courses(id, title)
What will the query below return?

SELECT Students.name, Courses.title FROM Students JOIN Enrollments ON Students.id = Enrollments.student_id JOIN Courses ON Enrollments.course_id = Courses.id;

medium

A. List of all students and all courses regardless of enrollment

B. List of student names with the titles of courses they are enrolled in

C. List of courses with no students enrolled

D. Syntax error due to missing WHERE clause

Solution

1. Step 1: Analyze JOINs in the query

   Students join Enrollments on student ID, then Enrollments join Courses on course ID, linking students to their courses.

2. Step 2: Understand SELECT output

   The query selects student names and course titles for matching enrollments, showing which student is in which course.

3. Final Answer:

   List of student names with the titles of courses they are enrolled in -> Option B

4. Quick Check:

   JOINs link students to their courses [OK]

Hint: JOIN chains link related data stepwise [OK]

Common Mistakes:

• Thinking JOIN returns all combinations without conditions
• Expecting courses without students to appear
• Assuming WHERE is required for JOIN

4. Identify the error in the following SQL query joining three tables:

SELECT * FROM A JOIN B ON A.id = B.a_id JOIN C ON A.id = C.a_id;

medium

A. Missing WHERE clause for filtering

B. No error, query is correct

C. JOIN condition for table C should use B's columns, not A's

D. JOIN keyword is missing before table C

Solution

1. Step 1: Review JOIN conditions

   First JOIN connects A and B on A.id = B.a_id, which is correct.

2. Step 2: Check second JOIN condition

   Second JOIN connects C using A.id = C.a_id, but logically C should join via B, not A, to maintain correct relationships.

3. Final Answer:

   JOIN condition for table C should use B's columns, not A's -> Option C

4. Quick Check:

   JOIN conditions must link correct tables [OK]

Hint: Check JOIN ON uses correct table columns [OK]

Common Mistakes:

• Using wrong table columns in JOIN condition
• Assuming WHERE is needed for JOIN
• Ignoring logical table relationships

5. You have tables:
Orders(order_id, customer_id),
Customers(customer_id, name),
Payments(payment_id, order_id, amount).
Write a query to find each customer's name and the total amount they paid across all orders. Which query is correct?

hard

A. SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name;

B. SELECT Customers.name, Payments.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id;

C. SELECT Customers.name, SUM(Payments.amount) FROM Customers, Orders, Payments WHERE Customers.customer_id = Orders.customer_id AND Orders.order_id = Payments.order_id;

D. SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Payments ON Customers.customer_id = Payments.order_id GROUP BY Customers.name;

Solution

1. Step 1: Understand the relationships

   Customers link to Orders by customer_id; Orders link to Payments by order_id.

2. Step 2: Check aggregation and grouping

   We need total payment per customer, so SUM and GROUP BY Customers.name are required.

3. Step 3: Validate options

   SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name; correctly joins tables and groups by customer name with SUM of payments. SELECT Customers.name, Payments.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id; lacks aggregation. SELECT Customers.name, SUM(Payments.amount) FROM Customers, Orders, Payments WHERE Customers.customer_id = Orders.customer_id AND Orders.order_id = Payments.order_id; misses GROUP BY. SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Payments ON Customers.customer_id = Payments.order_id GROUP BY Customers.name; joins wrong columns.

4. Final Answer:

   SELECT Customers.name, SUM(Payments.amount) FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id JOIN Payments ON Orders.order_id = Payments.order_id GROUP BY Customers.name; -> Option A

5. Quick Check:

   JOIN + SUM + GROUP BY = correct total per customer [OK]

Hint: Use JOINs with GROUP BY and SUM for totals [OK]

Common Mistakes:

• Missing GROUP BY when using SUM
• Joining tables on wrong columns
• Selecting aggregated and non-aggregated columns without GROUP BY