FPeRsKMQ

1DROP DATABASE IF EXISTS OakvilleRec;
2
3CREATE DATABASE OakvilleRec;
4
5USE OakvilleRec;
6
7CREATE TABLE Student(
8	id INT NOT NULL AUTO_INCREMENT,
9	FirstName VARCHAR(25) NOT NULL,
10        LastName VARCHAR(25) NOT NULL,
11        age INT,
12	PRIMARY KEY(Id)
13);
14
15INSERT INTO Student
16    (FirstName, LastName, age)
17VALUES
18    ('John', 'Johnson', 10),
19    ('Bob', 'Bobson', 11),
20    ('Maddie', 'Maddison', 15),
21    ('Mary', 'Molson', 12),
22    ('Ed', 'Edison', 13),
23    ('Mike', 'Molson', 11);
24
25CREATE TABLE Course(
26	id INT NOT NULL AUTO_INCREMENT,
27	name VARCHAR(25) NOT NULL,
28	startTime TIME NOT NULL,
29	PRIMARY KEY(Id)
30);
31
32INSERT INTO Course
33    (name, startTime)
34VALUES
35    ('Swimming', '9:00'),
36    ('Tennis', '11:00'),
37    ('Soccer', '15:00');
38
39
40CREATE TABLE StudentCourse (
41    studentId INT NOT NULL,
42    courseId INT NOT NULL,
43    PRIMARY KEY (studentId, courseId),
44    CONSTRAINT student_fk
45    FOREIGN KEY (studentId) REFERENCES student(id),
46    CONSTRAINT course_fk
47    FOREIGN KEY (courseId) REFERENCES course(id)
48);
49
50INSERT INTO StudentCourse 
51    (studentId, courseId) 
52VALUES
53    ((SELECT id FROM student WHERE firstName = 'John'), (SELECT id FROM course WHERE name = 'Swimming')),
54    ((SELECT id FROM student WHERE firstName = 'John'), (SELECT id FROM course WHERE name = 'Tennis')),
55    ((SELECT id FROM student WHERE firstName = 'John'), (SELECT id FROM course WHERE name = 'Soccer')),
56    ((SELECT id FROM student WHERE firstName = 'Bob'), (SELECT id FROM course WHERE name = 'Swimming')),
57    ((SELECT id FROM student WHERE firstName = 'Bob'), (SELECT id FROM course WHERE name = 'Tennis')),
58    ((SELECT id FROM student WHERE firstName = 'Maddie'), (SELECT id FROM course WHERE name = 'Swimming')),
59    ((SELECT id FROM student WHERE firstName = 'Maddie'), (SELECT id FROM course WHERE name = 'Tennis')),
60    ((SELECT id FROM student WHERE firstName = 'Maddie'), (SELECT id FROM course WHERE name = 'Soccer')),
61    ((SELECT id FROM student WHERE firstName = 'Mary'), (SELECT id FROM course WHERE name = 'Swimming')),
62    ((SELECT id FROM student WHERE firstName = 'Mary'), (SELECT id FROM course WHERE name = 'Soccer')),
63    ((SELECT id FROM student WHERE firstName = 'Ed'), (SELECT id FROM course WHERE name = 'Swimming')),
64    ((SELECT id FROM student WHERE firstName = 'Ed'), (SELECT id FROM course WHERE name = 'Soccer')),
65    ((SELECT id FROM student WHERE firstName = 'Mike'), (SELECT id FROM course WHERE name = 'Tennis')),
66    ((SELECT id FROM student WHERE firstName = 'Mike'), (SELECT id FROM course WHERE name = 'Soccer'));