Many-to-many with junction tables in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a many-to-many relationship in databases?

It is a relationship where multiple records in one table relate to multiple records in another table. For example, students can enroll in many courses, and courses can have many students.

beginner

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

A junction table breaks down a many-to-many relationship into two one-to-many relationships. It stores pairs of related record IDs from the two tables, making the relationship manageable and queryable.

intermediate

What columns does a junction table usually have?

It usually has at least two columns, each holding a foreign key that references the primary key of one of the related tables. Sometimes it also has its own primary key or additional data.

intermediate

Write a simple SQL query to find all courses a student with ID 1 is enrolled in, using a junction table named Enrollment.

SELECT Courses.* FROM Courses JOIN Enrollment ON Courses.CourseID = Enrollment.CourseID WHERE Enrollment.StudentID = 1;

intermediate

How does a junction table improve data integrity in many-to-many relationships?

By using foreign keys, it ensures that only valid records from the related tables can be linked. This prevents invalid or orphaned relationships and keeps data consistent.

What does a junction table do in a many-to-many relationship?

A Deletes duplicate records automatically

B Stores only one record per table

C Stores pairs of related record IDs from two tables

D Creates a one-to-one relationship

A junction table stores pairs of IDs from the two related tables to represent many-to-many relationships.

Which of these is true about foreign keys in a junction table?

A They reference primary keys in related tables

B They are always primary keys themselves

C They store duplicate data

D They are not needed in junction tables

Foreign keys in a junction table reference primary keys in the related tables to maintain valid links.

If a student can enroll in many courses and a course can have many students, what kind of relationship is this?

A One-to-one

B No relationship

C One-to-many

D Many-to-many

This is a many-to-many relationship because multiple students can enroll in multiple courses.

Which SQL clause is commonly used to connect tables through a junction table?

A JOIN

B ORDER BY

C GROUP BY

D WHERE

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

What is a common name for a table that links two tables in a many-to-many relationship?

A Primary table

B Junction table

C Lookup table

D Index table

A junction table links two tables in a many-to-many relationship.

Explain how a many-to-many relationship is implemented using a junction table.

Think about how two tables connect through a third table storing pairs of IDs.

Describe how you would write a SQL query to find all related records using a junction table.

Consider joining the main tables through the junction table on matching IDs.