Write the SQL statements for the following

Ashi M

Hi ,

I 'm new to DataBase, is there any one who can help me to this kind of questions answer.

Thanks

Ashi,

1. The following tables form part of a database held in a relational DBMS:

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

where Hotel contains hotel details and Hotel_No is the primary key Room contains room details for each hotel and (Hotel_No, Room_No) forms the primary key.

Booking contains details of the bookings and the primary key comprises (Hotel_No, Guest_No, Date From) and Guest contains guest details and Guest_No is the primary key.

Write the SQL statements for the following

1. What is the total daily revenue from all the double rooms?
2. How many different guests have made bookings for August, 2006?
3. List the price and type of all rooms at the hotel Land Mark.
4. What is the total income from bookings for the hotel Manor today?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Hemant Srivastava13y ago
Yeah.. That should also work.

If you problem is resolved, could you accept the answer.
Ashi M13y ago
Q2. SELECT COUNT(DISTINCT guestno) FROM booking WHERE (datefrom <= '8/31/06' AND dateto >= '8/1/06'); Will this work ?
Hemant Srivastava13y ago
Query for the "What is the total daily revenue from all the double rooms"
SELECT SUM(Price)
FROM Hotel
INNER JOIN Room ON Room.Hotel_No = Hotel.Hotel_No
INNER JOIN Booking ON Booking.Hotel_No = Hotel.Hotel_No AND Booking.Room_No = Room.Room_No
WHERE Room.Type = 'Double'
Hemant Srivastava13y ago
Query for the "What is the total income from bookings for the hotel Manor today"
SELECT SUM(Price)
FROM Hotel
INNER JOIN Room ON Room.Hotel_No = Hotel.Hotel_No
INNER JOIN Booking ON Booking.Hotel_No = Hotel.Hotel_No AND Booking.Room_No = Room.Room_No
WHERE Hotel.Name = 'Manor' AND (DATEDIFF(DAY, GETDATE(), Date_From) = 0)
Hemant Srivastava13y ago
Query for the "List the price and type of all rooms at the hotel Land Mark."
SELECT Price, Type FROM Hotel
INNER JOIN Room ON Room.Hotel_No = Hotel.Hotel_No
WHERE Hotel.Name = 'Land Mark'
Hemant Srivastava13y ago
Query for the "How many different guests have made bookings for August, 2006?"
SELECT COUNT(Booking.Guest_No) FROM Booking
INNER JOIN Guest ON Guest.Guest_No = Booking.Guest_No
WHERE Date_From BETWEEN 'Aug 1 2006' AND 'Aug 31 2006'