MySQL Task List The following tables have already been created for you. Each tab
ID: 3907284 • Letter: M
Question
MySQL
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
If you have any doubts, please give me comment...
1)
SELECT first_name, last_name, builiding_name, room_number, meeting_start, meeting_end
FROM ((((person P JOIN person_meeting PM ON p.person_id = PM.person_id) JOIN meeting M ON PM.meeting_id=M.meeting_id) JOIN room R ON M.room_id=R.room_id) JOIN building B ON R.building_id = B.building_id)
WHERE first_name ='Tom' AND last_name = 'Hanks';
2)
SELECT first_name, last_name, builiding_name, room_number, meeting_start, meeting_end
FROM ((((person P JOIN person_meeting PM ON p.person_id = PM.person_id) JOIN meeting M ON PM.meeting_id=M.meeting_id) JOIN room R ON M.room_id=R.room_id) JOIN building B ON R.building_id = B.building_id)
WHERE meeting_id = 2;
3)
SELECT first_name, last_name, builiding_name, room_number, meeting_start, meeting_end
FROM ((((person P JOIN person_meeting PM ON p.person_id = PM.person_id) JOIN meeting M ON PM.meeting_id=M.meeting_id) JOIN room R ON M.room_id=R.room_id) JOIN building B ON R.building_id = B.building_id)
WHERE builiding_name='Main Street';
4)
SELECT COUNT(*) AS attendees, meeting_id, meeting_start, meeting_end
FROM meeting M JOIN person_meeting PM ON M.meeting_id=PM.meeting_id
GROUP BY meeting_id, meeting_start, meeting_end;
5)
SELECT first_name, last_name, meeting_id, meeting_start, meeting_end
FROM meeting M JOIN person_meeting PM USING(meeting_id), person P JOIN USING(person_id)
WHERE meetings_start<='Dec 25 2016 12:00:00';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.