S9EXrisA

1DROP DATABASE IF EXISTS scheduling;
2CREATE DATABASE IF NOT EXISTS scheduling;
3
4USE scheduling;
5
6CREATE TABLE IF NOT EXISTS Person(
7person_id INT (10) PRIMARY KEY AUTO_INCREMENT,
8first_name VARCHAR (25) NOT NULL,
9last_name VARCHAR (25) NOT NULL
10);
11
12CREATE TABLE IF NOT EXISTS Building(
13building_id INT (10) PRIMARY KEY AUTO_INCREMENT,
14building_name VARCHAR (30) NOT NULL
15);
16
17CREATE TABLE IF NOT EXISTS Room(
18room_id INT (10) PRIMARY KEY KEY AUTO_INCREMENT,
19room_number INT (5) NOT NULL,
20building_id INT (10) NOT NULL,
21capacity INT (5) NOT NULL,
22
23CONSTRAINT building_fk FOREIGN KEY (building_id)
24	REFERENCES Building (building_id)
25);
26
27CREATE TABLE IF NOT EXISTS Meeting(
28meeting_id INT (10) PRIMARY KEY AUTO_INCREMENT,
29room_id INT (10) NOT NULL,
30meeting_start DATETIME NOT NULL,
31meeting_end DATETIME NOT NULL,
32
33CONSTRAINT room_fk FOREIGN KEY (room_id)
34	REFERENCES Room (room_id)
35);
36
37CREATE TABLE IF NOT EXISTS Person_meeting(
38person_id INT (10) NOT NULL,
39meeting_id INT (10) NOT NULL,
40
41CONSTRAINT person_meeting_pk PRIMARY KEY (person_id, meeting_id),
42CONSTRAINT person_fk FOREIGN KEY (person_id)
43	REFERENCES Person (person_id),
44CONSTRAINT meeting_fk FOREIGN KEY (meeting_id)
45	REFERENCES Meeting (meeting_id)
46);
47
48INSERT INTO Person(first_name, last_name)
49	VALUES ('Tom', 'Hanks');
50INSERT INTO Person(first_name, last_name)
51	VALUES ('Anne', 'Hathaway');
52INSERT INTO Person(first_name, last_name)
53	VALUES ('Tom', 'Cruise');
54INSERT INTO Person(first_name, last_name)
55	VALUES ('Meryl', 'Streep');
56INSERT INTO Person(first_name, last_name)
57	VALUES ('Chris', 'Pratt');
58INSERT INTO Person(first_name, last_name)
59	VALUES ('Halle', 'Berry');
60INSERT INTO Person(first_name, last_name)
61	VALUES ('Robert', 'De Niro');
62INSERT INTO Person(first_name, last_name)
63	VALUES ('Julia', 'Roberts');
64INSERT INTO Person(first_name, last_name)
65	VALUES ('Denzel', 'Washington');
66INSERT INTO Person(first_name, last_name)
67	VALUES ('Melissa', 'McCarthy');
68
69INSERT INTO Building (building_name)
70	VALUES ('Headquarters');
71INSERT INTO Building (building_name)
72	VALUES ('Main Street Building');
73    
74INSERT INTO Room (room_number, building_id, capacity)
75	VALUES (100, 1, 5);
76INSERT INTO Room (room_number, building_id, capacity)
77	VALUES (200, 1, 4);
78INSERT INTO Room (room_number, building_id, capacity)
79	VALUES (300, 1, 10);
80INSERT INTO Room (room_number, building_id, capacity)
81	VALUES (10, 2, 4);
82INSERT INTO Room (room_number, building_id, capacity)
83	VALUES (20, 2, 4);
84
85INSERT INTO Meeting (room_id, meeting_start, meeting_end)
86	VALUES (1, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
87INSERT INTO Meeting (room_id, meeting_start, meeting_end)
88	VALUES (1, '2016-12-25 10:00:00', '2016-12-25 12:00:00');
89INSERT INTO Meeting (room_id, meeting_start, meeting_end)
90	VALUES (1, '2016-12-25 11:00:00', '2016-12-25 12:00:00');
91INSERT INTO Meeting (room_id, meeting_start, meeting_end)
92	VALUES (2, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
93INSERT INTO Meeting (room_id, meeting_start, meeting_end)
94	VALUES (4, '2016-12-25 09:00:00', '2016-12-25 10:00:00');
95INSERT INTO Meeting (room_id, meeting_start, meeting_end)
96	VALUES (5, '2016-12-25 14:00:00', '2016-12-25 16:00:00');
97    
98INSERT INTO Person_meeting (person_id, meeting_id)
99	VALUES (1, 1);
100INSERT INTO Person_meeting (person_id, meeting_id)
101	VALUES (10, 1);
102INSERT INTO Person_meeting (person_id, meeting_id)
103	VALUES (1, 2);
104INSERT INTO Person_meeting (person_id, meeting_id)
105	VALUES (2, 2);
106INSERT INTO Person_meeting (person_id, meeting_id)
107	VALUES (3, 2);
108INSERT INTO Person_meeting (person_id, meeting_id)
109	VALUES (4, 2);
110INSERT INTO Person_meeting (person_id, meeting_id)
111	VALUES (5, 2);
112INSERT INTO Person_meeting (person_id, meeting_id)
113	VALUES (6, 2);
114INSERT INTO Person_meeting (person_id, meeting_id)
115	VALUES (7, 2);
116INSERT INTO Person_meeting (person_id, meeting_id)
117	VALUES (8, 2);
118INSERT INTO Person_meeting (person_id, meeting_id)
119	VALUES (9, 3);
120INSERT INTO Person_meeting (person_id, meeting_id)
121	VALUES (10, 3);
122INSERT INTO Person_meeting (person_id, meeting_id)
123	VALUES (1, 4);
124INSERT INTO Person_meeting (person_id, meeting_id)
125	VALUES (2, 4);
126INSERT INTO Person_meeting (person_id, meeting_id)
127	VALUES (8, 5);
128INSERT INTO Person_meeting (person_id, meeting_id)
129	VALUES (9, 5);
130INSERT INTO Person_meeting (person_id, meeting_id)
131	VALUES (1, 6);
132INSERT INTO Person_meeting (person_id, meeting_id)
133	VALUES (2, 6);
134INSERT INTO Person_meeting (person_id, meeting_id)
135	VALUES (3, 6);
136    SELECT * FROm person_meeting;
137
138#11
139SELECT m.meeting_id, m.meeting_start, m.meeting_end, r.building_id, r.room_number 
140	FROM Meeting m, Person p, Person_meeting pm, Room r
141	WHERE p.person_id IN
142    (SELECT p.person_id FROM Person WHERE 
143    p.first_name = 'Tom' AND p.last_name = 'Hanks')
144    AND pm.person_id = p.person_id
145    AND pm.meeting_id = m.meeting_id
146    AND m.room_id = r.room_id;
147
148#12
149SELECT p.first_name, p.last_name FROM Person p, Person_meeting pm
150	WHERE p.person_id IN
151    (SELECT pm.person_id FROM Person_meeting 
152    WHERE pm.meeting_id = 2);
153
154#13
155SELECT p.first_name, p.last_name 
156	FROM Person p, Person_meeting pm, Meeting m, Room r, Building b
157    WHERE r.building_id IN
158	(SELECT b.building_id FROM Building 
159    WHERE b.building_name = 'Main Street Building')
160    AND r.room_id = m.room_id
161    AND m.meeting_id = pm.meeting_id
162    AND p.person_id = pm.person_id;
163
164#14
165SELECT m.meeting_id, count(*) FROM Meeting m, Person_meeting pm
166	WHERE m.meeting_id in
167    (SELECT pm.meeting_id FROM Person_meeting
168    WHERE pm.meeting_id = m.meeting_id)
169    GROUP BY m.meeting_id;
170
171#15
172SELECT p.first_name, p.last_name FROM Person p 
173INNER JOIN Person_meeting pm
174	ON pm.person_id = p.person_id
175INNER JOIN Meeting m
176	ON m.meeting_id = pm.meeting_id
177	WHERE m.meeting_end < '2016-12-25 12:00:00'
178    GROUP BY p.person_id;