Many-to-many with junction tables in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the junction table named 'student_courses'.

SQL

SELECT [1] FROM student_courses;

Drag options to blanks, or click blank then click option'

Acourse_id

Bstudent_id

Cstudents

D*

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name instead of selecting all columns.

Trying to select from a table that doesn't exist.

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to join 'students' and 'student_courses' on the student ID.

SQL

SELECT students.name, student_courses.course_id FROM students JOIN student_courses ON students.[1] = student_courses.student_id;

Drag options to blanks, or click blank then click option'

Aid

Bstudent_id

Ccourse_id

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name that doesn't exist in 'students'.

Mixing up the foreign key and primary key columns.

The 'students' table's primary key is usually 'id', which matches 'student_id' in the junction table.

3fill in blank

hard

Fix the error in the query to get course names for each student by joining all three tables.

SQL

SELECT students.name, courses.name FROM students JOIN student_courses ON students.id = student_courses.student_id JOIN courses ON courses.[1] = student_courses.course_id;

Drag options to blanks, or click blank then click option'

Astudent_id

Bcourse_id

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using the foreign key column name instead of the primary key in the join condition.

Joining on columns that don't relate.

The 'courses' table's primary key is 'id', which matches 'course_id' in the junction table.

4fill in blank

hard

Fill both blanks to create a junction table named 'author_books' with foreign keys to 'authors' and 'books'.

SQL

CREATE TABLE author_books (author_id INT, book_id INT, FOREIGN KEY (author_id) REFERENCES [1](id), FOREIGN KEY (book_id) REFERENCES [2](id));

Drag options to blanks, or click blank then click option'

Aauthors

Bauthor_books

Cbooks

Dbook_authors

Attempts:

3 left

💡 Hint

Common Mistakes

Referencing the junction table itself instead of the related tables.

Mixing up table names.

The junction table references the primary keys of 'authors' and 'books' tables.

5fill in blank

hard

Fill all three blanks to select student names and their course names using joins on the junction table.

SQL

SELECT [1].name, [2].name FROM [3] JOIN student_courses ON students.id = student_courses.student_id JOIN courses ON courses.id = student_courses.course_id;

Drag options to blanks, or click blank then click option'

Astudents

Bcourses

Denrollments

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table alias or name in the SELECT clause.

Confusing the junction table with main tables.

The query selects from 'students' and 'courses' tables joined through 'student_courses'.