What is a many-to-many relationship and why do we need junction tables?

A many-to-many relationship exists when multiple records in one table can relate to multiple records in another table. For example, students can enroll in multiple courses, and courses can have multiple students. Junction tables are needed because relational databases cannot directly model many-to-many relationships with foreign keys alone. The junction table contains foreign keys to both entities, with each row representing one relationship.

How do you query data through a junction table?

Use two JOINs: first join from entity A to the junction table, then join from junction table to entity B. For example: SELECT s.name, c.name FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id. This pattern connects the two entities through their relationship stored in the junction table.

What additional data can be stored in junction tables and why is this useful?

Junction tables can store metadata about the relationship itself, such as enrollment_date, priority, status, or quantity. This is useful because the relationship often has properties beyond just the connection. For example, when a student enrolls in a course, you might want to track when they enrolled, their grade, or their attendance status.

Design tables for a blog system where posts can have multiple tags and tags can be used by multiple posts.

-- Main entities

CREATE TABLE posts (

post_id INT PRIMARY KEY,

title VARCHAR(200),

content TEXT,

created_date DATE

);

CREATE TABLE tags (

tag_id INT PRIMARY KEY,

tag_name VARCHAR(50) UNIQUE

);

-- Junction table

CREATE TABLE post_tags (

post_id INT,

tag_id INT,

created_date DATE DEFAULT CURRENT_DATE,

PRIMARY KEY (post_id, tag_id),

FOREIGN KEY (post_id) REFERENCES posts(post_id),

FOREIGN KEY (tag_id) REFERENCES tags(tag_id)

);

Junction table post_tags connects posts and tags with composite primary key to prevent duplicates and foreign key constraints for referential integrity.

Write a query to find all students enrolled in more than 3 courses.

SELECT s.student_name, COUNT(e.course_id) as course_count

FROM students s

INNER JOIN enrollments e ON s.student_id = e.student_id

GROUP BY s.student_id, s.student_name

HAVING COUNT(e.course_id) > 3

ORDER BY course_count DESC;

JOIN through junction table, GROUP BY student, use HAVING to filter aggregated results for students with more than 3 courses.

Find products that belong to both "Electronics" and "Gaming" categories.

SELECT p.product_name

FROM products p

INNER JOIN product_categories pc1 ON p.product_id = pc1.product_id