SQL Problem

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

i wrote all the queries above


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.
Ans: select Distinct g.Name,b.Room_No,h.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 CommonlyBooked,Min(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_No,Name,Address)
create table HOTEL
(
Hotel_No int primary key,
Name varchar(50),
Address varchar(60)
 )
 --ROOM(Room_No,Hotel_No,Type,Charge)

create Table Room
(
Room_No Int primary Key,
Hotel_No int foreign key references HOTEL(Hotel_No),
Type Varchar(20),
Charge float
)
--GUEST(Guest_No,Name,Address)

create Table GUEST
(
Guest_No Int primary key,
Name Varchar(50),
Address varchar(50)
)
--BOOKING(Hotel_No,Guest_No,Date_From,Date_To,Room_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)

)

Hi Sanyal,
    We can't create more than one primary key in a table(For Example:BOOKING(Hotel_No,Guest_No,Date_From,Date_To,Room_No)).