1. Find the names of guests who live in Dubai and have not reserved a double roo
ID: 3728101 • Letter: 1
Question
1. Find the names of guests who live in Dubai and have not reserved a double room?
2. Find the names of guests who reserved all rooms located on the 2nd floor?
3. Find the name(s) and age(s) of the eldest guest(s)?
4. Find the average age of the guests for each city that has at least two guests living in that city. Sort the result alphabetically by the city name?
You are to use the following relational database schema to an next pages using SQL queries. answer the queries in the Guest id city age name Room number type floor Reservation Gid Rnumber day Each of the attributes is explained as follows: id: the unique ID of a guest name: the name of a guest city: the name of the city where a guest lives age: the age of a guest number: the unique number of a room type: the type of a room (single or double) floor: the floor where a room is located in Gid: the id of a guest (a foreign key referencing id) Rnumber: the number of a room ( a foreign key referencing number) day: the day in which a specific room was booked on by a specific guest . . . .Explanation / Answer
1 . SELECT name FROM Guest WHERE city = "dubai" AND id IN (SELECT Gid FROM Reservation WHERE Rnumber IN (SELECT number FROM Room WHERE type = "single"));
2 . SELECT name FROM Guest WHERE id IN (SELECT Gid FROM Reservation WHERE Rnumber IN (SELECT number FROM Room WHERE floor = 2));
3 . SELECT name, age FROM Guest WHERE id IN (SELECT Gid FROM Reservation WHERE day IN (SELECT MAX(day) FROM Reservation));
4 . SELECT AVG(age),city FROM Guest GROUP BY city HAVING COUNT(id)>=2 ORDER BY city;
//GROUP BY statement is used to group the result set by one or more columns.
//HAVING is used to place a condition, which is applied on the group of rows.
//ORDER BY clause is used to sort the tuples in a query result based on the values of some attributes.
//MAX(column_name) for the maximum value in the column_name column.
//COUNT(column_name) for the number of values in the column_name column.
//AVG(column_name) for the average of all values in the column_name column.
//the remaining are the basic ones, there are multiple ways for writing a sql query .I have used nested queries which are easy and avoids confusion. if you face any problem ,please post on the comment section i'll sort it out.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.