· 8 years ago · Dec 02, 2017, 04:34 AM
1/* Cooper Van Kampen */
2/* Final Project */
3/*
4Sources:
5 https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html
6 https://www.w3schools.com/sql/sql_foreignkey.asp
7 https://www.w3schools.com/sql/sql_join.asp
8 ...and my previous assignments
9*/
10
11/* Cleanup */
12SET SQL_SAFE_UPDATES = 0;
13DROP SCHEMA IF EXISTS scheduling;
14
15/* Required paste */
16Create database if not exists scheduling;
17use scheduling;
18
19/* 1 */
20CREATE TABLE IF NOT EXISTS Person(
21 person_id INT(32) NOT NULL AUTO_INCREMENT,
22 first_name VARCHAR(16) NOT NULL,
23 last_name VARCHAR(16) NOT NULL,
24 PRIMARY KEY (person_id)
25);
26SELECT * FROM Person;
27
28/* 2 */
29CREATE TABLE IF NOT EXISTS Building(
30 building_id INT(16) NOT NULL AUTO_INCREMENT,
31 building_name VARCHAR(32),
32 PRIMARY KEY (building_id)
33);
34SELECT * FROM Building;
35
36/* 3 */
37CREATE TABLE IF NOT EXISTS Room(
38 room_id INT(16) NOT NULL AUTO_INCREMENT,
39 room_number INT(16) NOT NULL,
40 building_id INT(16) NOT NULL,
41 capacity INT(16) NOT NULL,
42 FOREIGN KEY (building_id)
43 REFERENCES Building(building_id),
44 PRIMARY KEY (room_id)
45);
46SELECT * FROM Room;
47
48/* 4 */
49CREATE TABLE IF NOT EXISTS Meeting(
50 meeting_id INT(32) NOT NULL AUTO_INCREMENT,
51 room_id INT(16) NOT NULL,
52 meeting_start DATETIME NOT NULL,
53 meeting_end DATETIME NOT NULL,
54 FOREIGN KEY (room_id)
55 REFERENCES Room(room_id),
56 PRIMARY KEY (meeting_id)
57);
58SELECT * FROM Meeting;
59
60/* 5 */
61CREATE TABLE IF NOT EXISTS Person_meeting(
62 person_id INT(32) NOT NULL,
63 meeting_id INT(32) NOT NULL,
64 FOREIGN KEY (person_id)
65 REFERENCES Person(person_id),
66 FOREIGN KEY (meeting_id)
67 REFERENCES Meeting(meeting_id),
68 PRIMARY KEY (person_id, meeting_id)
69);
70SELECT * FROM Person_meeting;
71
72/* 6 */
73INSERT INTO Person(first_name, last_name) VALUES ("Tom","Hanks");
74INSERT INTO Person(first_name, last_name) VALUES ("Anne","Hathaway");
75INSERT INTO Person(first_name, last_name) VALUES ("Tom","Cruise");
76INSERT INTO Person(first_name, last_name) VALUES ("Meryl","Streep");
77INSERT INTO Person(first_name, last_name) VALUES ("Chris","Pratt");
78INSERT INTO Person(first_name, last_name) VALUES ("Halle","Berry");
79INSERT INTO Person(first_name, last_name) VALUES ("Robert","De Niro");
80INSERT INTO Person(first_name, last_name) VALUES ("Julia","Roberts");
81INSERT INTO Person(first_name, last_name) VALUES ("Denzel","Washington");
82INSERT INTO Person(first_name, last_name) VALUES ("Melissa","McCarthy");
83SELECT * FROM Person;
84
85/* 7 */
86INSERT INTO Building(building_name) VALUES ("Headquarters");
87INSERT INTO Building(building_name) VALUES ("Main Street Building");
88SELECT * FROM Building;
89
90/* 8 */
91INSERT INTO Room(room_number, building_id, capacity) VALUES (100, 1, 5);
92INSERT INTO Room(room_number, building_id, capacity) VALUES (200, 1, 4);
93INSERT INTO Room(room_number, building_id, capacity) VALUES (300, 1, 10);
94INSERT INTO Room(room_number, building_id, capacity) VALUES (10, 2, 4);
95INSERT INTO Room(room_number, building_id, capacity) VALUES (20, 2, 4);
96SELECT * FROM Room;
97
98/* 9 */
99INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (1, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
100INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (1, '2016-12-25 10:00:00', '2016-12-25 12:00:00');
101INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (1, '2016-12-25 11:00:00', '2016-12-25 12:00:00');
102INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (2, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
103INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (4, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
104INSERT INTO Meeting(room_id, meeting_start, meeting_end) VALUES (5, '2016-12-25 14:00:00', '2016-12-25 16:00:00');
105SELECT * FROM Meeting;
106
107/* 10 */
108INSERT INTO Person_meeting(person_id, meeting_id) VALUES (1, 1);
109INSERT INTO Person_meeting(person_id, meeting_id) VALUES (10, 1);
110INSERT INTO Person_meeting(person_id, meeting_id) VALUES (1, 2);
111INSERT INTO Person_meeting(person_id, meeting_id) VALUES (2, 2);
112INSERT INTO Person_meeting(person_id, meeting_id) VALUES (3, 2);
113INSERT INTO Person_meeting(person_id, meeting_id) VALUES (4, 2);
114INSERT INTO Person_meeting(person_id, meeting_id) VALUES (5, 2);
115INSERT INTO Person_meeting(person_id, meeting_id) VALUES (6, 2);
116INSERT INTO Person_meeting(person_id, meeting_id) VALUES (7, 2);
117INSERT INTO Person_meeting(person_id, meeting_id) VALUES (8, 2);
118INSERT INTO Person_meeting(person_id, meeting_id) VALUES (9, 3);
119INSERT INTO Person_meeting(person_id, meeting_id) VALUES (10, 3);
120INSERT INTO Person_meeting(person_id, meeting_id) VALUES (1, 4);
121INSERT INTO Person_meeting(person_id, meeting_id) VALUES (2, 4);
122INSERT INTO Person_meeting(person_id, meeting_id) VALUES (8, 5);
123INSERT INTO Person_meeting(person_id, meeting_id) VALUES (9, 5);
124INSERT INTO Person_meeting(person_id, meeting_id) VALUES (1, 6);
125INSERT INTO Person_meeting(person_id, meeting_id) VALUES (2, 6);
126INSERT INTO Person_meeting(person_id, meeting_id) VALUES (3, 6);
127SELECT * FROM Person_meeting;
128
129/* 11 */
130SELECT meeting.meeting_id, meeting.meeting_start, meeting.meeting_end, room.building_id, room.room_number
131 FROM Meeting meeting, Person person, Person_meeting personmeeting, Room room WHERE person.person_id IN
132 (SELECT person.person_id FROM Person WHERE person.first_name = 'Tom' AND person.last_name = 'Hanks')
133 AND personmeeting.person_id = person.person_id
134 AND personmeeting.meeting_id = meeting.meeting_id
135 AND meeting.room_id = room.room_id;
136
137/* 12 */
138SELECT person.first_name, person.last_name FROM Person person, Person_meeting personmeeting WHERE person.person_id IN
139 (SELECT personmeeting.person_id FROM Person_meeting WHERE personmeeting.meeting_id = 2);
140
141/* 13 */
142SELECT person.first_name, person.last_name FROM Person person, Person_meeting personmeeting, Meeting meeting, Room room, Building building
143 WHERE room.building_id IN
144 (SELECT building.building_id FROM Building WHERE building.building_name = 'Main Street Building')
145 AND room.room_id = meeting.room_id
146 AND meeting.meeting_id = personmeeting.meeting_id
147 AND person.person_id = personmeeting.person_id;
148
149/* 14 */
150SELECT meeting.meeting_id, COUNT(*) FROM Meeting meeting, Person_meeting personmeeting WHERE meeting.meeting_id IN
151 (SELECT personmeeting.meeting_id FROM Person_meeting WHERE personmeeting.meeting_id = meeting.meeting_id)
152 GROUP BY meeting.meeting_id;
153
154/* 15 */
155SELECT person.first_name, person.last_name FROM Person person
156 INNER JOIN Person_meeting personmeeting ON personmeeting.person_id = person.person_id
157 INNER JOIN Meeting meeting ON meeting.meeting_id = personmeeting.meeting_id
158 WHERE meeting.meeting_end < '2016-12-25 12:00:00'
159 GROUP BY person.person_id;