MySQL Task List The following tables have already been created for you. Each tab
ID: 3906182 • 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
Task One:
select n.first_name,n.last_name, b.building_name,r.room_number,m.meeting_start, m.meeting_end from person n
inner join person_meeting p on p.person_id=n.person_id
inner join meeting m on m.meeting_id = p.meeting_id
inner join room r on r.room_id = m.room_id
inner join building b on b.building_id = r.building_id
where n.first_name='Tom' and n.last_name='Hanks';
Task Two:
select n.first_name,n.last_name, b.building_name,r.room_number,m.meeting_start, m.meeting_end from person n
inner join person_meeting p on p.person_id=n.person_id
inner join meeting m on m.meeting_id = p.meeting_id
inner join room r on r.room_id = m.room_id
inner join building b on b.building_id = r.building_id
where m.meeting_id=2;
Task Three:
select n.first_name,n.last_name, b.building_name,r.room_number,m.meeting_id,m.meeting_start, m.meeting_end from person n
join person_meeting p on p.person_id=n.person_id
join meeting m on m.meeting_id = p.meeting_id
join room r on r.room_id = m.room_id
join building b on b.building_id = r.building_id and b.building_name ='Main Street';
Task four:
SELECT count(*),m.meeting_ID,m.meeting_start,m.meeting_end
from meeting m
join person_meeting p on p.meeting_ID=m.meeting_ID group by meeting_ID;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.