Answers to practice -1

Questions click here

create table hotel02(hotelno varchar(20),hotelname char(20), city char(20));

create table room02(roomno varchar(5),hotelno varchar(2),type char(10),price varchar(10));

create table booking02(hotelno number(10),guestno number(10),date_from varchar(30),date_to varchar(30),roomno number(10))
insert into booking02 values(005,135,'26-aug-2010','30-sep-2010',212)
insert into booking02 values(005,135,'26-aug-2010',30-sep-2010',212)
insert into booking02 values(004,235,'16-aug-2010','1-sep-2010',112)
select * from booking02
insert into booking02 values(003,035,'12-sep-2010','10-oct-2010',103)
insert into booking02 values(002,025,'22-jul-2010','15-aug-2010',301)
insert into booking02 values(001,023,'23-jun-2010','25-aug-2010',204)
insert into booking02 values(001,023,'23-jun-2010',25-aug-2010',204)



select hotelname from hotel02 where city='london'

Alter table booking02 add PRIMARY KEY(hotelno,guestno,date_from)
Alter table room02 add PRIMARY KEY(roomno,hotelno)
Alter table hotel02 add PRIMARY KEY(hotelno)



create table guest02(guestno varchar(5),guestaddress varchar(50),guestname char(10),
                  primary key (guestno));


update room02 set price =price+price*0.05;


select * from room02




select*from booking02 where date_to IS NULL

select count(*)from hotel02

select avg(price)from room02

SELECT HotelNo, COUNT(RoomNo) AS count FROM Room02 GROUP BY Hotelno;

select r.hotelno,COUNT(roomno)from room02 r, hotel02 h where r.hotelno=h.hotelno AND city='london'GROUP BY r.hotelno 

SELECT MAX(X) AS MostlyBook FROM (SELECT Type, COUNT(Type) AS X FROM Booking02 b, Hotel02 h, Room02 r
          WHERE r.roomno = b.roomno AND b.hotelno = h.hotelno AND h.city LIKE '%london%' GROUP BY Type);

select type,price from hotel02,room02 where hotel02.hotelno=room02.hotelno and hotelname='taj'

SELECT * FROM Guest02 WHERE GuestNo IN
(SELECT GuestNo FROM Booking02 WHERE Date_From <= SYSDATE
AND (Date_To >= SYSDATE OR Date_To IS NULL)
AND HotelNo =(SELECT HotelNo FROM Hotel02
WHERE HotelName = 'Grosvenor'));

SELECT r.* FROM Room r LEFT JOIN
(SELECT g.GuestName,h.HotelNo,b.RoomNo FROM Guest02 g,Booking02 b,Hotel02 h
WHERE g.GuestNo = b.GuestNo AND b.HotelNo = h.HotelNo AND
h.HotelName= 'Grosvenor' AND
b.Date_From <=CURRENT_DATE AND b.Date_To >=CURRENT_DATE) AS X
ON r.HotelNo = X.HotelNo AND r.RoomNo = X.RoomNo;


SELECT SUM(price) FROM Booking02 b, Room02 r, Hotel02 h
WHERE (b.Date_From <= CURRENT_DATE AND
b.Date_To >= CURRENT_DATE) AND
r.Hotelno = h.HotelNo AND r.RoomNo = b.RoomNo;

SELECT * FROM Room02 r
WHERE RoomNo NOT IN
(SELECT RoomNo FROM Booking02 b,Hotel02 h
WHERE (Date_From <= CURRENT_DATE AND
Date_To >= CURRENT_DATE) AND
b.HotelNo = h.HotelNo AND HotelName ='pune');

SELECT SUM(Price) FROM Room02 r
WHERE Room_No NOT IN
(SELECT RoomNo FROM Booking02 b, Hotel02 h
WHERE (Date_From <= CURRENT_DATE AND
Date_To >= CURRENT_DATE) AND
b.HotelNo = h.HotelNo AND HotelName = 'pune');