Many to many

SQL · Unit 12: Designing tables

Full plan
SQL
8 exercises

Another database, the school, with its students, its courses, and its grades. Henry, student 3, takes Music, course 4. Which table is that written in?

In the store, an order belonged to a single customer, and a customer had many orders. That's a one-to-many relationship. In the school there's no "one" side. Henry takes three courses, and Music has two students. A course_id cell in students isn't enough, and neither is a student_id in courses. That's a many-to-many relationship.

A third table records it, grades, with one row for each pair of student and course. You've been using it since unit 4.

SELECT student_id, course_id, grade
FROM grades
WHERE student_id = 3
  OR course_id = 4;

Returns

student_id	course_id	grade
3	1	5.5
3	2	8
3	4	10
5	4	7.5

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Multiple choice
The school wants to record which classroom each course is taught in. A course is always taught in the same classroom, and several courses are taught in one classroom. There will be a classrooms table. Where does the foreign key go?
2. Complete the code
The table is dropped, with its fourteen grades, which in this app come back on the next run, and rebuilt empty. Then Leo, student 6, enrolls in Chess, course 5, and gets a 9. Fill in the blanks with the table each foreign key in grades points to.
3. Find the bug
Henry, student 3, enrolls in Chess, course 5. Here SQL answers UNIQUE constraint failed: grades.student_id, grades.course_id. Which line has the problem?
4. Predict the output
Leo, student 6, has his grade in Math, course 1, corrected from 4 to 5. Leo also takes Science, course 3, with a 6.5. What does the SELECT return?
5. Find the bug
Someone rebuilds the requests table, and Sara's two requests no longer get in. Here SQL answers UNIQUE constraint failed: requests.student_id. Which line has the problem?
6. Put the lines in order
The school adds a new course, Theater, which Irene will teach and which is worth 2 credits. It gets id 6. Sara, student 7, is the first to enroll. Put in order the two statements that record it and the SELECT that shows who takes course 6. Two lines don't belong.
7. Multiple choice
Back at the store. orders is in the middle too, between customers and products, and yet it has its own id instead of a two-column key. Why?
8. Write the code
The store wants each customer to mark their favorite products. A customer can mark several, a product can be marked by several customers, and nobody marks the same one twice. Create the favorites table with two columns, in this order. customer_id is a required integer that points to the id of customers. product_id is a required integer that points to the id of products. The primary key is the two together, in that same order. Write the type as INTEGER.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →