Table Name: person Field Type Notes person_id int(8) ? Primary key ? Auto-increm

Question

Table Name: person
Field Type Notes
person_id int(8) ? Primary key
? Auto-increment value
? Required
first_name varchar(25) ? Required
last_name varchar(25) ? Required

Table Name: building
Field Type Notes
building_id int(8) ? Primary key
? Auto-increment value
? Required
building_name varchar(50) ? Required

Table Name: room
Field Type Notes
room_id int(8) ? Primary key
? Auto-increment value
? Required
room_number varchar(10) ? Required
building_id int(8) ? Required
capacity int(8) ? Required

Table Name: meeting
Field Type Notes
meeting_id int(8) ? Primary key
? Auto-increment value
? Required
room_id int(8) ? Required
meeting_start datetime ? Required
meeting_end datetime ? Required

Table Name: person_meeting
Field Type Notes
person_id int(8) ? Primary key
? Required
meeting_id int(8) ? Primary key
? Required

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Task One: Find all the meetings that Tom Hanks has to attend
Construct the SQL statement to find all the meetings that Tom Hanks has to attend. Display the following columns:

? Person’s first name
? Person’s last name
? Building name
? Room number
? Meeting start date and time
? Meeting end date and time

Task Two: Find all the people that are attending meeting ID 2
Construct the SQL statement to find all the people that are attending meeting ID 2. Display the following columns:

? Person’s first name
? Person’s last name

? Building name
? Room number
? Meeting start date and time
? Meeting end date and time

Task Three: Find all the people who have meetings in the Main Street building
Construct the SQL statement to find all the people who have meetings in the Main Street building. Display the following columns:

? Person’s first name
? Person’s last name
? Building name
? Room number
? Meeting ID
? Meeting start date and time
? Meeting end date and time

Task Four: Find the number of attendees for every meeting
Construct the SQL statement to find the number of attendees for every meeting. Display the following columns:

? Count of meeting attendees
? Meeting ID
? Meeting start date and time
? Meeting end date and time

Task Five: Find All of the People that Have Meetings Only Before Dec. 25, 2016 at Noon Using INNER JOINs
Construct the SQL statement to find all of the people that have meetings only before Dec. 25, 2016 at noon using INNER JOINs. Display the following columns:

? Person’s first name
? Person’s last name
? Meeting ID
? Meeting start date and time
? Meeting end date and time

Explanation / Answer

Task One: Find all the meetings that Tom Hanks has to attend

SELECT P.first_name, P.last_name, B.building_name, R.room_number, M.meeting_start, M.meeting_end FROM person_meeting AS PM, person AS P, meeting AS M, room AS R, building AS B
WHERE PM.person_id = P.person_id AND PM.meeting_id = M.meeting_id AND M.room_id = R.room_id AND R.building_id = B.building_id
HAVING CONCAT(first_name,' ',last_name) = 'Tom Hanks'

Task Two: Find all the people that are attending meeting ID 2

SELECT P.first_name, P.last_name, B.building_name, R.room_number, M.meeting_start, M.meeting_end FROM person_meeting AS PM, person AS P, meeting AS M, room AS R, building AS B
WHERE PM.meeting_id = 2 AND PM.person_id = P.person_id AND PM.meeting_id = M.meeting_id AND M.room_id = R.room_id AND R.building_id = B.building_id

Task Three: Find all the people who have meetings in the Main Street building

SELECT P.first_name, P.last_name, B.building_name, R.room_number, M.meeting_id, M.meeting_start, M.meeting_end FROM person_meeting AS PM, person AS P, meeting AS M, room AS R, building AS B
WHERE PM.person_id = P.person_id AND PM.meeting_id = M.meeting_id AND M.room_id = R.room_id AND R.building_id = B.building_id AND B.building_name = 'Main Street building'

Task Four: Find the number of attendees for every meeting

SELECT COUNT(*) '# of attendees', M.meeting_id, M.meeting_start, M.meeting_end FROM person_meeting AS PM, person AS P, meeting AS M
WHERE PM.person_id = P.person_id AND PM.meeting_id = M.meeting_id
GROUP BY PM.meeting_id

Task Five: Find All of the People that Have Meetings Only Before Dec. 25, 2016 at Noon Using INNER JOINs

SELECT P.first_name, P.last_name, M.meeting_id, M.meeting_start, M.meeting_end FROM person_meeting AS PM
INNER JOIN person AS P ON PM.person_id = P.person_id
INNER JOIN meeting AS M ON PM.meeting_id = M.meeting_id
HAVING M.meeting_start = '2016-12-25 12:00:00'

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.