Question
Thanks in advance.
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
a.) select name from people;
b.) select name,office from people;
c.) select name,office from people;
d.) select name from People P
inner join Participants PP on P.userid = PP.userid
inner join Meetings M on M.meetid = PP.meetid
where date = 22/08/2013.
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;
Do let me know if you face any issue.....Please upvote :) ....Have a great day !!
