**For each SQL statement that you construct in the steps below, you must provide

Question

**For each SQL statement that you construct in the steps below, you must provide the screenshot of your SQL statement**

2.Create the below tables using SQL.

1.Create the Person table. Person(person_id,first_name,last_name)

Primary Key: person_id; All the other fields are required.

2.Create the Building table. Building (building_id,building_name)

Primary Key: building_id; All the other fields are required.

3.Create the Room table. Room(room_id, room_number, building_id, capacity)

Primary Key: room_id; All the other fields are required.

4.Create the Meeting table. Meeting (meeting_id,room_id,meeting_start,meeting_end)

Primary Key: meeting_id; ; All the other fields are required.

5.Create the Person-Meeting intersection table. Person_meeting(person_id,meeting_id)

Primary Key: person_id, meeting_id

6.Insert the below data in the Person table

(Tom, Hanks)

(Anne", Hathaway)

(Tom, Cruise)

(Meryl", Streep)

(Chris, Pratt)

(Halle, Berry)

(Robert, De Niro)

(Julia, "Roberts")

(Denzel, "Washington)

(Melissa, McCarthy)

7.Insert the below data in the Building table

(Headquarters)

(Main Street Building)

8.Insert the below data in the Room table

(room_number, building_id, capacity)

(100, 1, 5)

(200, 1, 4)

(300, 1, 10)

(10, 2, 4)

(20, 2, 4)

9.Insert the below data in meeting table

meeting (room_id, meeting_start, meeting_end)

(1, 2016-12-25 09:00:00, 2016-12-25 10:00:00)

(1, 2016-12-25 10:00:00, 2016-12-25 12:00:00)

(1, 2016-12-25 11:00:00, 2016-12-25 12:00:00)

(2, 2016-12-25 09:00:00, 2016-12-25 10:00:00)

(4, 2016-12-25 09:00:00, 2016-12-25 10:00:00)

(5, 2016-12-25 14:00:00, 2016-12-25 16:00:00)

10.Insert the below data in person_meeting table

person_meeting (person_id, meeting_id)

(1, 1)

(10, 1)

(1, 2)

(2, 2)

(3, 2)

(4, 2)

(5, 2)

(6, 2)

(7, 2)

(8, 2)

(9, 3)

(10, 3)

(1, 4)

(2, 4)

(8, 5)

(9, 5)

(1, 6)

(2, 6)

(3, 6)

11.Find all the meetings that Tom Hanks has to attend

12.Find all the people that are attending meeting ID 2

13.Find all the people who have meetings in the Main Street building

14.Find the number of attendees for every meeting

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

Explanation / Answer

Hello there,

*** I am writing here all your sql queries, so NO need to attach screenshot; i think this will be cool.

1) Create table Person (person_id int, first_name varchar(20), last_name varchar(20), Primary key (person_id));

2) Create table Building (building_id int, building_name varchar(20), Primary key (building_id));

3) Create table Room (room_id int, room_number int, building_id int, capacity int, Primary key (room_id));

4) Create table Meeting (meeting_id int, room_id int, meeting_start Datetime, meeting_end Datetime, Primary key (meeting_id));

5) Create table Person_meeting (person_id int, meeting_id int, Primary key (person_id,meeting_id));

6) * insert into Person (first_name,last_name) values (Tom, Hanks);

* insert into Person (first_name,last_name) values (Anne", Hathaway);

  * insert into Person (first_name,last_name) values (Tom, Cruise);

* As it is you can add all the values in the Person table.

7) * insert into Building (building_name) values (Headquarters);

* insert into Building (building_name) values (Main Street Building);

8) * insert into Room (room_number, building_id, capacity) values (100, 1, 5);

   * insert into Room (room_number, building_id, capacity) values (200, 1, 4);

* As it is you can add all the values in the Room table.

9) * insert into meeting (room_id, meeting_start, meeting_end) values (1, 2016-12-25 09:00:00, 2016-12-25 10:00:00);

* insert into meeting (room_id, meeting_start, meeting_end) values (1, 2016-12-25 10:00:00, 2016-12-25 12:00:00);

* As it is you can add all the values in the meeting table.

10) * insert into person_meeting (person_id, meeting_id) values (1,1);

* insert into person_meeting (person_id, meeting_id) values (10,1 );

* As it is you can add all the values in the person_meeting table.

11) select * from meeting where meeting_id in (select meeting_id from person_meeting where person_id in (select person_id from Person where first_name = 'Tom' and last_name = 'Hanks'));

* this has 2 inner queries innermost query will return the person_id of tom hanks only, and other inner query will return the all meeting_id's of person_id, which belongs to tom hanks, and then outer query will return meeting details by meeting_id.

12) select * from Person where person_id in (select person_id from person_meeting where meeting_id = 2);

this inner query will return all the person_ids which associated with meeting_id 2.

13) select * from Person where person_id in (select person_id from person_meeting where meeting_id in (select meeting_id from meeting where room_id in (select room_id from room where building_id in(select building_id from building where building_name ='Main Street Building'))));

it has complete mapping of all the table, finds person_id from person_meeting table which finds all the meeting_id of room which has associated with the building 'Main Street Building'.

14) select meeting_id,sum(capacity) as Attendees from room r,meeting m where r.room_id = m.room_id group by meeting_id;

15) select p.first_name,p.last_name from Person p inner join person_meeting pm where p.person_id = pm.person_id and pm.meeting_id in (select meeting_id from meeting where meeting_end < '2016-12-25 12:00:00');

Hope, you got all your solutions, feel free to ask any queries and to give feedback.

Thank you

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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