REM answers to questions in exercise 3

rem 1

select r.roomno, price, type
from room r, hotel h
where r.hotelNo = h.hotelNo and hotelName ='Grosvenor Hotel';

rem 2

select *
from room
where hotelno in
           (select hotelno
            from hotel
            where city = 'Sydney') and price between 100 and 150;


rem or

select r.*
from room r, hotel h
where r.hotelno = h.hotelno and city ='Sydney'and price between 100 and 150;

rem 3  the purpose of  todate() here is to remove the time part

select guestName, guestAddress
from guest g, booking b, hotel h
where g.guestNo= b.guestNo and  b.hotelNo=h.hotelNo and hotelName = 'Grosvenor Hotel'
and to_date(sysdate) between to_date(dateFrom) and to_date(dateTo);


rem or

select guestName, guestAddress
from guest
where guestno in (select guestno
                  from booking
                  where   to_date(sysdate) between to_date(dateFrom) and to_date(dateTo) and
                          hotelno = (select hotelno from hotel where hotelname= 'Grosvenor Hotel'));


rem 4

select a.*, guestName
from ( select r.*
       from room r, hotel h
       where r.hotelNo = h.hotelNo and hotelName ='Grosvenor Hotel')  a,
     (select b.*, guestName
      from booking b, guest g
      where b.guestNo = g.guestNo and
      to_date(sysdate) between to_date(dateFrom) and to_date(dateTo)
      ) gb
where a.roomNo = gb.roomNo(+) and a.hotelNo = gb.hotelNo(+);


rem 5

select sum(price)
from booking b, room r, hotel h
where b.roomNo = r.roomNo and b.hotelNo = r.hotelNo and h.hotelNo= r.hotelNo and hotelName ='Grosvenor Hotel'
and  to_date(sysdate) between to_date(dateFrom) and to_date(dateTo);


rem or

select sum(price)
from booking b, room r
where b.roomNo = r.roomNo and b.hotelNo = r.hotelNo
      and r.hotelno = (select hotelno from hotel where    hotelName ='Grosvenor Hotel')
      and  to_date(sysdate) between to_date(dateFrom) and to_date(dateTo);


rem 6
select roomNo
from (
select roomNo, r.hotelNo
from room r, hotel h
where h.hotelNo = r.hotelno and hotelName= 'Grosvenor Hotel'
Minus
select roomNo, hotelno
from booking
where to_date(sysdate) between to_date(dateFrom) and to_date(dateTo));

rem or


select roomNo
from room r, hotel h
where h.hotelNo = r.hotelno and hotelName= 'Grosvenor Hotel'
Minus
select b.roomNo
from booking b, hotel h
where to_date(sysdate) between to_date(dateFrom) and to_date(dateTo)
       and hotelName= 'Grosvenor Hotel' and b.hotelNo =h.hotelNo;


rem or

select roomNo
from room r, hotel h
where h.hotelNo = r.hotelno and hotelName= 'Grosvenor Hotel'
      and not exists   (select * from booking b
                        where b.roomNo = r.roomNo and b.hotelNo =r.hotelNo
                        and to_date(sysdate) between to_date(dateFrom) and to_date(dateTo));

rem or

select roomNo
from room r, hotel h
where h.hotelNo = r.hotelno and hotelName= 'Grosvenor Hotel'
      and not exists   (select * from booking b
                        where b.roomNo = r.roomNo and b.hotelNo =r.hotelNo
                        and to_date(sysdate) between to_date(dateFrom) and to_date(dateTo));

rem 7

select sum(price)
from room r, hotel h
where h.hotelNo = r.hotelno and hotelName= 'Grosvenor Hotel'
      and not exists   (select * from booking b
                        where b.roomNo = r.roomNo and b.hotelNo =r.hotelNo
                        and to_date(sysdate) between to_date(dateFrom) and to_date(dateTo));



 rem 8

 select hotelNo, count(roomNo)
  from room
 group by hotelNo;

rem 9   suppose we need hotelno and hotel name

select h.hotelNo, hotelName
from room r, hotel h
where r.hotelNo =h.hotelNo and city ='Sydney'
having count(roomNo)>50
group by h.hotelNo, h.hotelname;



rem or

select hotelNo, hotelName
from hotel
where hotelno in (select h.hotelNo
                  from room r, hotel h
                  where r.hotelNo =h.hotelNo and city ='Sydney'
                  having count(roomNo)>50
                  group by h.hotelNo
                  );

 rem or

select hotelno, hotelname
 from (
       select h.hotelNo, hotelName, count(roomNo) as ct
       from room r, hotel h
       where r.hotelNo =h.hotelNo and city ='Sydney'
       group by h.hotelNo, h.hotelname)
 where   ct >50    ;

rem 10

 Find the maximum, minimum, and average room prices for the hotels in Sydney

 understanding 1:    max/min/avg of all rooms in all hotels  in Sydney

 select max(price), min(price), avg(price)
 from room r, hotel h
 where  r.hotelno = h.hotelno and city ='Sydney';

 understanding 2:    max/min/avg of all rooms in each hotel in sydney

 select r.hotelno, max(price), min(price), avg(price)
 from room r, hotel h
 where  r.hotelno = h.hotelno and city ='Sydney'
 group by r.hotelno;

rem 11

select Avg(cnt)
from (select hotelNo, count(*) as cnt
      from booking
      group by hotelNo
     UNION
     select hotelNo, 0
     from hotel
     where hotelNo not in (select hotelNo
                          from booking)
     )

rem or 


select ave(cnt) from (
select hno, count(roomno) as CNT
from 
(select h.hotelno as hno, b.* 
from hotel h, booking b
where h.hotelno = b.hotelno(+)) 
group by hno)
; 


rem 12

select A1.hNo, type
from
(select b.hotelNo as hNo, type, count(*) as CNT
from hotel h,  booking b, room r
where city='Sydney' and h.hotelNo=b.hotelNo
   and r.roomNo=b.roomNo and r.hotelNo = b.hotelNo
group by b.hotelNo, type)  A1,
(select hNo, max(CNT) as CNT1
from
(select b.hotelNo as hNo, type, count(*) as CNT
from hotel h,  booking b, room r
where city='Sydney' and h.hotelNo=b.hotelNo
   and r.roomNo=b.roomNo and r.hotelNo = b.hotelNo
group by b.hotelNo, type)
group by hNo)   B1
where A1.hNo=B1.hNo and A1.CNT =B1.CNT1;



rem or


select b.hotelno,type, count(*) as CNT
from room r, booking b, hotel h
where h.hotelNo = b.hotelno and city='Sydney' and r.hotelno= b.hotelno and r.roomno=b.roomno
group by b.hotelno, type
having (b.hotelno, count(*)) in  (select hotelno, max(cnt)
                              from
                             (select b.hotelno,type, count(*) as CNT
                               from room r, booking b, hotel h
                               where h.hotelNo = b.hotelno and city='Sydney' and r.hotelno= b.hotelno and r.roomno=b.roomno
                              group by b.hotelno, type)
                              group by hotelno);


rem or

 create view bookingcount As
select b.hotelno,type, count(*) as CNT
from room r, booking b, hotel h
where h.hotelNo = b.hotelno and city='Sydney' and r.hotelno= b.hotelno and r.roomno=b.roomno
group by b.hotelno, type;

create view maxbooktype As
select hotelno, max(cnt) cnt1
from bookingcount
group by hotelno;


select a.hotelno, type
from bookingcount a, maxbooktype b
where a.hotelno= b.hotelno and cnt=cnt1;



rem 13

create view expensiveRoom as
select R.hotelNo, hotelName, roomNo, price
from hotel H, room R
where price >=200 and H.hotelNo = R.hotelNo;


rem 14

Grant select 
on Hotel 
to public; 





































































