This question is in relation to relational databases and MySQL. I\'ve done a, b

Question

This question is in relation to relational databases and MySQL. I've done a, b and c. I need help with d, e and f.

I did a like this:

SELECT name FROM people;

I did b like this:

SELECT name, office FROM people;

I did c like this:

SELECT name, room FROM people

RIGHT JOIN rooms ON people.office = rooms.room

In d I need an output that only shows the names of the people in my database who has a meeting on a particular date.

In e I need an output that shows a list of pairs of people sharing an office. No duplicates.

In f I need an output that shows the meetings where the number of attendees who have NOT declined exceeds the capacity of the meeting room.

Please make sure you understand the way the tables are built and connected to each other before you provide an answer. You can see how each table is built and a description of its colums above.

My outputs should be as wanted above, nor more nor less columns.

Many thanks in advance and have afreat day.

Rooms: room: the name of a room, capacity: the number of people that it will hold People: userid: unique user name, name: ordinary name, group: vip, tap, phd, office: a room or NULL Participants: meetid: the id of the meeting, pid: a userid or a room, status: u(nknown), a(ccept), d(ecline) Meetings: meetid: a unique id, date: the date of the meeting, slot: 8,9.,..,18, owner: the userid of the owner of the meeting, what: a textual description of the meeting Equipment: room: the name of a room, type: the type of equipment Write SQL statements that do the following (using MySQL, include your queries and the output) Write an SQL query that retrieves everyone (using their name) currently stored in the database. Write an SQL query that retrieves everyone currently stored in the database along with the office they use if any. Write an SQL query that retrieves everyone currently stored in the database along with their respective offices as well as the offices that are not occupied by anyone. Write an SQL query that retrieves the names of all people with a meeting on 22nd of August 2013. Write an SQL query that return a list of pairs of people sharing an office. Examples: Tom, Anja; Chris, Ea. Do not return duplicates. Write an SQL query that returns meetings where the number of attendees who have not declined exceeds the capacity of the meeting room a. b. c. d. e. f.

Explanation / Answer

Solution:

d)

SELECT name FROM People INNER JOIN Meeting ON People.userid = Meeting.Owner WHERE Date = '22/08/2013'

Explanation:

As given in the table the owner in the Meeting table is the foreign key from People and we have assumed that in the owner column all the people who attended the meeting will be considered as the owner of the meeting. you can work on the date format as per your database.

e)

SELECT name,office FROM people GROUP BY name, office

ORDER BY office desc;

f)

SELECT * FROM Participants P

INNER JOIN Rooms R on P.pid = R.room

WHERE capacity < ( SELECT count(*) FROM Participants P WHERE status = a AND meetid = P.meetid GROUP BY status )A;

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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