Many-to-many with junction tables in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Find all students enrolled in 'Math 101'

Given these tables:

Students(id, name)
Courses(id, title)
Enrollments(student_id, course_id)

What is the output of this query?

SELECT s.name FROM Students s
JOIN Enrollments e ON s.id = e.student_id
JOIN Courses c ON e.course_id = c.id
WHERE c.title = 'Math 101'
ORDER BY s.name;

SQL

CREATE TABLE Students (id INT, name VARCHAR(50));
CREATE TABLE Courses (id INT, title VARCHAR(50));
CREATE TABLE Enrollments (student_id INT, course_id INT);

INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Courses VALUES (10, 'Math 101'), (20, 'History 201');
INSERT INTO Enrollments VALUES (1, 10), (2, 10), (3, 20);

A

Alice
Bob

B

Alice
Charlie

C

Bob
Charlie

D

Alice
Bob
Charlie

💡 Hint

Look at which students are linked to the course titled 'Math 101' through the Enrollments table.

The query joins Students to Enrollments and then to Courses, filtering for 'Math 101'. Only Alice and Bob are enrolled in Math 101.

🧠 Conceptual

intermediate

Why use a junction table in many-to-many relationships?

In a database, why do we use a junction table to represent many-to-many relationships?

A To enforce one-to-one relationships between tables

B To avoid using primary keys in the related tables

C To combine two tables into one for faster queries

D To store additional data about the relationship and link two tables without duplicating data

💡 Hint

Think about how many-to-many relationships connect two sets of data without repeating information.

A junction table links two tables in a many-to-many relationship and can hold extra info about the link, avoiding data duplication.

📝 Syntax

advanced

Identify the syntax error in this many-to-many query

What error does this SQL query raise?

SELECT s.name, c.title
FROM Students s
JOIN Enrollments e ON s.id = e.student_id
JOIN Courses c ON e.course_id = c.id
WHERE c.title = 'History 201'
GROUP BY s.name;

A Syntax error: missing semicolon at end

B No error, query runs correctly

C Syntax error: 'c.title' must be in GROUP BY or aggregated

D Runtime error: table 'Enrollments' does not exist

💡 Hint

Check if all selected columns are properly grouped or aggregated.

When using GROUP BY, all selected columns must be in GROUP BY or aggregated. 'c.title' is selected but not grouped or aggregated, causing a syntax error.

❓ optimization

advanced

Optimize this many-to-many query for performance

Which change will improve performance of this query?

SELECT s.name, COUNT(*) AS course_count
FROM Students s
JOIN Enrollments e ON s.id = e.student_id
GROUP BY s.name
ORDER BY course_count DESC;

A Add an index on Enrollments.student_id

B Add an index on Students.name

C Remove GROUP BY clause

D Add an index on Enrollments.course_id

💡 Hint

Think about which column is used to join tables and filter data.

Adding an index on Enrollments.student_id speeds up the join operation by quickly locating matching rows.

🔧 Debug

expert

Why does this many-to-many query return duplicate rows?

Given these tables and data:

Students(id, name): (1, 'Anna'), (2, 'Ben')
Courses(id, title): (100, 'Physics'), (200, 'Chemistry')
Enrollments(student_id, course_id): (1, 100), (1, 200), (2, 100), (2, 100)

What causes this query to return duplicate rows?

SELECT s.name, c.title
FROM Students s
JOIN Enrollments e ON s.id = e.student_id
JOIN Courses c ON e.course_id = c.id
ORDER BY s.name, c.title;

A JOIN condition is incorrect and duplicates rows

B Duplicate rows in Enrollments cause repeated results

C Missing DISTINCT keyword in SELECT

D Courses table has duplicate titles causing duplicates

💡 Hint

Check the data in the junction table for repeated entries.

The Enrollments table has duplicate rows for student 2 and course 100, causing the query to return duplicates.