MySQL data retrieval Task List The following tables have already been created fo
ID: 3903742 • Letter: M
Question
MySQL data retrieval
Task List The following tables have already been created for you. Each table is already populated with all of the necessary records.
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
To use the “scheduling” database, use the following command:
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
Q1) 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
SELECT person_id, first_name , last_name, meeting_start, meeting_end, room_number FROM person JOIN person_meeting ON PERSON.person_id = person_meeting.person_id JOIN meeting ON meeting.meeting_id = person_meeting.meeting_id JOIN room ON room.room_id = meeting.room_id WHERE PERSON.first_name = 'Tom' AND PERSON.last_name = 'Hanks';
Q2) Find all the people that are attending meeting ID 2
SELECT person_id, first_name , last_name, meeting_start, meeting_end, room_number FROM person JOIN person_meeting ON PERSON.person_id = person_meeting.person_id JOIN meeting ON meeting.meeting_id = person_meeting.meeting_id JOIN room ON room.room_id = meeting.room_id WHERE meeting.meeting_id = 2;
Q3) Find all the people who have meetings in the Main Street building
SELECT person_id, first_name , last_name, meeting_start, meeting_end, room_number, building_name FROM person JOIN person_meeting ON PERSON.person_id = person_meeting.person_id JOIN meeting ON meeting.meeting_id = person_meeting.meeting_id JOIN room ON room.room_id = meeting.room_id JOIN building ON ROOM.room_number = building.building_id WHERE building.building_name = 'Main Street building'
Q4) Find the number of attendees for every meeting
SELECT COUNT(person_id) AS 'Count_of_meeting_attendees', meeting_id, meeting_start, meeting_end FROM person JOIN person_meeting ON PERSON.person_id = person_meeting.person_id JOIN meeting ON MEETING.meeting_id = person_meeting.?meeting_id GROUP BY (meeting_id, meeting_start, meeting_end);
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.