How would I write SQL query statements like: 1. List the names and addresses of

Question

How would I write SQL query statements like:

1. List the names and addresses of all guests from London (Address contains string "London") sorted by name in ascending order.

2. List all guests whose address is missing. List all double or family rooms with a price below 40 per night sorted in ascending order of price.

3. For each room type, list the type and the average price, sorted by the average price in descending order.

4. List the number of different guests (not Guest_No) who have bookings during April 2005.

5. For each guest who has made at least one booking, list the guest number and the total number of bookings the guest has made, sorted by guest number.

6. For each hotel that has at least one booking during April 2005, list the hotel number, the total number of bookings the hotel has for April 2005 and the latest Date_from for such bookings, sorted by the total number of bookings.

7. List all bookings that start in the current month of the current year. The query should work for any month of any year without modification.

8. For each room type of each hotel, list the hotel number, room type, the highest and the lowest room prices for the room type. Sort the result by hotel number and then room type.

9. For each room type of each hotel, list the hotel number and room type with the highest and the lowest room prices, if the highest price is at least 100 or the lowest price is at most 30, sorted by hotel_no and the highest price.

The tables are

Hotel ( Hotel_No, Name, Address)

Room ( Room_No, Hotel_No, RoomType, Price)

Guest ( Guest_No, Guest_Name, Address)

Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No)

Explanation / Answer

Given tables are:

Hotel ( Hotel_No, Name, Address)
Room ( Room_No, Hotel_No, RoomType, Price)
Guest ( Guest_No, Guest_Name, Address)
Booking (Hotel_No, Guest_No, Date_From, Date_To, Room_No)

1)List the names and addresses of all guests from London (Address contains string "London") sorted by name in ascending order.

Ans:

select Guest_Name,Address from Guest WHERE address LIKE ‘%London%’ order by Guest_Name ASC;

2)List all guests whose address is missing.

Ans:

select Guest_Name from Guest where Address = null;

List all double or family rooms with a price below 40 per night sorted in ascending order of price

ANS:

select Room_No,Price from Room WHERE price < 40 AND type IN (‘D’, ‘F’) order by Price ASC;

3)For each roomType list the type and the average price, sorted by the average price in descending order

select RoomType,AVG(Price) from Room order by AVG(Price) DESC;

4)List the number of different guests (not Guest_No) who have bookings during April 2005.

ANS:

select count(DISTINCT GuestNo) FROM Booking
WHERE (dateFrom <= DATE’2004-08-01’ AND dateTo >= DATE’2004-08-01’) OR
(dateFrom >= DATE’2004-08-01’ AND dateFrom <= DATE’2004-08-31’);

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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