Many-to-many with junction tables in SQL - Mini Project: Build & Apply

Many-to-many with junction tables

📖 Scenario: You are building a simple database for a library. Books can have multiple authors, and authors can write multiple books. To represent this many-to-many relationship, you will use a junction table.

🎯 Goal: Create tables for Books and Authors, then create a junction table called BookAuthors to link books and authors. Insert sample data and write a query to find all authors for a specific book.

📋 What You'll Learn

Create a Books table with columns BookID (primary key) and Title.

Create an Authors table with columns AuthorID (primary key) and Name.

Create a junction table BookAuthors with columns BookID and AuthorID to link books and authors.

Insert at least two books and three authors with appropriate links in BookAuthors.

Write a query to select all author names for the book titled 'The Great Adventure'.

💡 Why This Matters

🌍 Real World

Many real-world databases use many-to-many relationships, such as students enrolled in multiple courses or products with multiple tags.

💼 Career

Understanding how to model and query many-to-many relationships is essential for database design and is a common task in software development and data analysis jobs.

1

Create Books and Authors tables

Write SQL statements to create a table called Books with columns BookID as an integer primary key and Title as text. Also create a table called Authors with columns AuthorID as an integer primary key and Name as text.

SQL

-- Create Books table
-- Create Authors table

Hint

Use CREATE TABLE statements with the specified columns and types.

2

Create the junction table BookAuthors

Write a SQL statement to create a junction table called BookAuthors with columns BookID and AuthorID. Both columns should be integers and together form the primary key. This table links books and authors.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

-- Create BookAuthors junction table

Hint

Define both columns as integers and set a composite primary key on both.

3

Insert sample data into Books, Authors, and BookAuthors

Insert two books into Books: (1, 'The Great Adventure') and (2, 'Mystery of the Night'). Insert three authors into Authors: (1, 'Alice Smith'), (2, 'Bob Johnson'), and (3, 'Carol Lee'). Then insert links into BookAuthors: book 1 with authors 1 and 2, and book 2 with author 3.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE BookAuthors (
    BookID INTEGER,
    AuthorID INTEGER,
    PRIMARY KEY (BookID, AuthorID)
);

-- Insert sample data into Books, Authors, and BookAuthors

Hint

Use INSERT INTO statements with the exact values given.

4

Query authors for 'The Great Adventure'

Write a SQL query to select the Name of all authors who wrote the book titled 'The Great Adventure'. Use the tables Books, Authors, and BookAuthors with appropriate joins.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT
);

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE BookAuthors (
    BookID INTEGER,
    AuthorID INTEGER,
    PRIMARY KEY (BookID, AuthorID)
);

INSERT INTO Books (BookID, Title) VALUES (1, 'The Great Adventure'), (2, 'Mystery of the Night');

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Alice Smith'), (2, 'Bob Johnson'), (3, 'Carol Lee');

INSERT INTO BookAuthors (BookID, AuthorID) VALUES (1, 1), (1, 2), (2, 3);

-- Write query to find all authors for 'The Great Adventure'

Hint

Use JOINs to connect the three tables and filter by the book title.

Practice

1. What is the main purpose of a junction table in a many-to-many relationship?

easy

A. To store pairs of related records from two tables using foreign keys

B. To store all data from both tables in one place

C. To replace one of the original tables completely

D. To create a one-to-one relationship between tables

Solution

1. Step 1: Understand many-to-many relationships

   Many-to-many means each record in one table can relate to many records in another table, and vice versa.

2. Step 2: Role of junction table

   A junction table holds pairs of foreign keys from both tables to link related records without duplicating data.

3. Final Answer:

   To store pairs of related records from two tables using foreign keys -> Option A

4. Quick Check:

   Junction table = pairs of foreign keys [OK]

Hint: Junction tables link two tables with pairs of keys [OK]

Common Mistakes:

• Thinking junction table stores all data from both tables
• Confusing junction table with a single main table
• Assuming junction table creates one-to-one links

2. Which SQL statement correctly creates a junction table named StudentCourse linking Student and Course tables by their IDs?

easy

A. CREATE TABLE StudentCourse (StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Student(ID));

B. CREATE TABLE StudentCourse (ID INT PRIMARY KEY, StudentID INT, CourseID INT);

C. CREATE TABLE StudentCourse (StudentID INT UNIQUE, CourseID INT UNIQUE);

D. CREATE TABLE StudentCourse (StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID));

Solution

1. Step 1: Define junction table columns

   It needs two columns for foreign keys: StudentID and CourseID.

2. Step 2: Set primary key on both columns

   Primary key on (StudentID, CourseID) ensures unique pairs and no duplicates.

3. Final Answer:

   CREATE TABLE StudentCourse (StudentID INT, CourseID INT, PRIMARY KEY (StudentID, CourseID)); -> Option D

4. Quick Check:

   Junction table needs composite primary key [OK]

Hint: Use composite primary key on both foreign keys [OK]

Common Mistakes:

• Using UNIQUE on individual columns instead of composite key
• Missing one foreign key column
• Not defining primary key on the pair

3. Given tables Author, Book, and junction table AuthorBook with columns AuthorID and BookID, what does this query return?

SELECT Author.Name, Book.Title FROM Author
JOIN AuthorBook ON Author.ID = AuthorBook.AuthorID
JOIN Book ON Book.ID = AuthorBook.BookID;

medium

A. A list of authors and the titles of books they wrote

B. A list of books without any author names

C. A list of all authors with all books, including unrelated pairs

D. An error because of missing WHERE clause

Solution

1. Step 1: Understand JOINs with junction table

   The query joins Author to AuthorBook by AuthorID, then AuthorBook to Book by BookID, linking authors to their books.

2. Step 2: Result of the query

   It returns pairs of author names and book titles where the author wrote the book, no unrelated pairs included.

3. Final Answer:

   A list of authors and the titles of books they wrote -> Option A

4. Quick Check:

   JOINs with junction table = related pairs only [OK]

Hint: JOIN junction table to get related pairs only [OK]

Common Mistakes:

• Thinking it returns all combinations of authors and books
• Expecting an error without WHERE clause
• Ignoring the role of junction table in filtering

4. You wrote this query to find all students and their courses:

SELECT Student.Name, Course.Title FROM Student
JOIN StudentCourse ON Student.ID = StudentCourse.StudentID
JOIN Course ON Course.ID = StudentCourse.CourseID
WHERE StudentCourse.StudentID = Student.ID;

But it has a problem. What is the problem?

medium

A. The WHERE clause is redundant and causes a syntax error

B. Missing alias for tables causes ambiguity

C. The WHERE clause is unnecessary because JOIN already matches IDs

D. StudentCourse.CourseID is missing in the WHERE clause

Solution

1. Step 1: Analyze the JOIN conditions

   The JOINs already match Student.ID to StudentCourse.StudentID and Course.ID to StudentCourse.CourseID.

2. Step 2: Check the WHERE clause

   The WHERE clause repeats the JOIN condition, which is unnecessary but not an error; however, it does not filter or add value.

3. Final Answer:

   The WHERE clause is unnecessary because JOIN already matches IDs -> Option C

4. Quick Check:

   JOIN matches IDs, WHERE clause redundant [OK]

Hint: JOIN conditions handle matching; WHERE often not needed here [OK]

Common Mistakes:

• Assuming WHERE clause causes syntax error
• Adding unnecessary conditions that duplicate JOINs
• Confusing alias usage with errors

5. You have tables Employee, Project, and junction table EmployeeProject with EmployeeID and ProjectID. How do you find employees who work on all projects listed in Project?

hard

A. Join EmployeeProject and Project, then filter with WHERE ProjectID IS NOT NULL

B. Use GROUP BY EmployeeID and HAVING count of projects equal to total projects count

C. Select employees with a simple JOIN to EmployeeProject without grouping

D. Use DISTINCT on EmployeeID in EmployeeProject without counting projects

Solution

1. Step 1: Count total projects

   Find total number of projects from Project table.

2. Step 2: Group EmployeeProject by EmployeeID

   Count how many projects each employee works on.

3. Step 3: Use HAVING to compare counts

   Only select employees whose project count equals total projects count.

4. Final Answer:

   Use GROUP BY EmployeeID and HAVING count of projects equal to total projects count -> Option B

5. Quick Check:

   Group and count projects per employee = all projects [OK]

Hint: Group by employee, HAVING count = total projects [OK]

Common Mistakes:

• Not grouping and counting projects per employee
• Using WHERE instead of HAVING for aggregate filtering
• Ignoring total projects count in comparison