Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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.

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.

image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Current Trends In Database Technology Edbt 2006 Edbt 2006 Workshops Phd Datax Iidb Iiha Icsnw Qlqp Pim Parma And Reactivity On The Web Munich Germany March 2006 Revised Selected Papers Lncs 4254

Authors: Torsten Grust ,Hagen Hopfner ,Arantza Illarramendi ,Stefan Jablonski ,Marco Mesiti ,Sascha Muller ,Paula-Lavinia Patranjan ,Kai-Uwe Sattler ,Myra Spiliopoulou ,Jef Wijsen

2006th Edition

3540467882, 978-3540467885

More Books

Students also viewed these Databases questions