SQL Problem

Question

Can anyone please solve the following problem......

Consider 3 tables
HOTEL(Hotel_No,Name,Address)
ROOM(Room_No,Hotel_No,Type,Charge)
BOOKING(Hotel_No,Guest_No,Date_From,Date_To,Room_No)
GUEST(Guest_No,Name,Address)

Note:Underlined fields denote the primary key of the respective table

Queries
1)List the details of all rooms at Taj hotel, including the name of the guest staying in the room, if the room is occupied.
2)What is total income from bookings for the Taj hotel today?
3)List the unoccupied rooms of the Taj hotel
4)What is lost income from unoccupied rooms of Taj hotel?
5)What is lost income from unoccupied rooms at each hotel today?
6)What is most commonly booked room type for each hotel in Delhi?

Rosi Sreenivasa Reddy · Answer

i wrote all the queries above Consider 3 tables HOTEL(Hotel_NoNameAddress) ROOM(Room_NoHotel_NoTypeCharge) BOOKING(Hotel_NoGuest_NoDate_FromDate_ToRoom_No) GUEST(Guest_NoNameAddress) Note:Underlined fields denote the primary key of the respective table Queries 1)List the details of all rooms at Taj hotel including the name of the guest staying in the room if the room is occupied. Ans: select Distinct g.Nameb.Room_Noh.Name from hotel h right outer join booking b on b.Hotel_No=h.Hotel_No inner join guest g on b.Guest_No=g.Guest_No right outer join Room r on b.Hotel_No=r.Hotel_No where b.Hotel_No=1 2)What is total income from bookings for the Taj hotel today? Ans: select SUM(Charge) as TotalIncome from Room where Room_No in (select Room_No from Booking where Hotel_No=1 and Date_From='08/05/2013') 3)List the unoccupied rooms of the Taj hotel ans:select * from room where Room_No not in(select Room_No from Booking where Hotel_No=1) and Hotel_No=1 4)What is lost income from unoccupied rooms of Taj hotel? ans:select SUM(Charge) as LostIncome from room where Room_No not in (select Room_NO from Booking where Hotel_No=1) and Hotel_No=1 5)What is lost income from unoccupied rooms at each hotel today? ans:select SUM(Charge) as LostIncomeOfAllHotels from room where Room_No not in (select Room_NO from Booking where Hotel_No=1) 6)What is most commonly booked room type for each hotel in Delhi? ans: select Max(r.Type) as CommonlyBookedMin(r.Type) as uncommonlybooked from Room r inner join Hotel h on r.Hotel_No=h.Hotel_No where h.Address='Delhi' ------------------------------------------------------------------------------------------------------------------------------------ structures of the table::: --HOTEL(Hotel_NoNameAddress) create table HOTEL ( Hotel_No int primary key Name varchar(50) Address varchar(60) ) --ROOM(Room_NoHotel_NoTypeCharge) create Table Room ( Room_No Int primary Key Hotel_No int foreign key references HOTEL(Hotel_No) Type Varchar(20) Charge float ) --GUEST(Guest_NoNameAddress) create Table GUEST ( Guest_No Int primary key Name Varchar(50) Address varchar(50) ) --BOOKING(Hotel_NoGuest_NoDate_FromDate_ToRoom_No) Create Table Booking ( Hotel_No int not null Guest_No int not null Date_From datetime not null Date_To datetime Room_No int foreign key references Room(Room_No) )

Nilesh Sanyal · Answer

Madamthe first query only returns the results of the guest staying in the room of hotel_no 1 but it does not returns other unoccupied rooms for that hotel. Other queries work fine.

Nilesh Sanyal · Answer

Thank you very much for the solution if I have a question I shall post it.

Rosi Sreenivasa Reddy · Answer

sorry for that i create by using composite key

Rosi Sreenivasa Reddy · Answer

Hi Sanyal We can't create more than one primary key in a table(For Example: BOOKING ( Hotel_NoGuest_NoDate_From Date_ToRoom_No) ).