Junction Table - SQL Programming

A junction table records links between two entity tables. Each link row stores the keys from both sides.

Program

Play the script to choose a course and list enrolled students through the link table.

junction_table.sql

CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
WITH params(course_code) AS (VALUES ()) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;

CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
WITH params(course_code) AS (VALUES ()) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;

many-to-many A student can take many courses and a course can have many students.

junction table `enrollments` stores one row per student-course link.

two joins The query joins through the link table to recover names and course codes.