Question A: Write an SQL query that return a list of pairs of people sharing an

Question

Question A:

Write an SQL query that return a list of pairs of people sharing an office. Nu duplicates. Output should look like:

Question B:

Write an SQL query that returns meetings where the number of atendees who have not declined (status), exceeds the capacity of the meeting room.

Many thanks in advance!

ROOMS: capacit ro0 Room1 Room2 Room3 Room4 4 PEOPLE useri rou Adam Smith Benny Hi Lharlie Chablin Donna James Edith Room2 Room Room2 Room3 Room Room1 RoomL ta ta ta Gary Potter arrypotter PARTIOPANTS eeti 289 289 929 929 status Room1 Room3 MEETINGS date 2017-03-23 2017-04-15 what eeti 289 929 slot 15 38 owne nce meetin r meetin room3 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

Explanation / Answer

Hi... Please check below.

Question 1:

Select distinct ST2.office,
substring(
(
Select ','+ST1.name AS [text()]
From people ST1
Where ST1.office = ST2.office
ORDER BY ST1.office
For XML PATH ('')
), 2, 1000) [people]
From people ST2;

Question 2:

select count(a.meetid) from meetings a, participants b where b.status!='d' and a.meetid=b.meetid;

Please check above and let me know any issues. Thank you. All the best.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

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