Many-to-many example

manytomany.db

# For http://stackoverflow.com/a/7296873/396458

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

students:
id | first  | last
=====================
1  | John   | Lee
2  | Jane   | Wilson
3  | Daniel | Gomez
 
classes:
id | name    | teacher_id
==========================
1  | Biology | 2
2  | Physics | 4
3  | English | 77
 
student_classes
s_id | c_id
======================
  1  |  2   # John is taking Physics 
  1  |  3   # John is taking English
  2  |  2   # Jane is taking Physics
  3  |  1   # Daniel is taking Biology
  
  -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y
     
Create table Class (ClsID varchar2(10) primary Key, 
    title Varchar2(30),
    Instructor Varchar2(30), 
    Day Varchar2(15), 
    time Varchar2(10));

Create table Student (StudID varchar2(15) primary Key, 
    Name Varchar2(35),
    Major Varchar2(35), 
    Classyr Varchar2(10), 
    Status  Varchar2(10));  

Create table Stud_class (studid Varchar2 (15) not null,
ClsId varchar2 (14) not null,
Foreign key (Studid) references Student(Studid), 
Foreign key (ClsId) references Class(ClsID),
UNIQUE (Studid, ClsID));