hotel(hotelno*,hotelname,city)
room(roomno*,hotelno*,type,price)
booking(hotelno*,guestno*,datefrom*,dateto,roomno)
guest(guestno*,guestname,guestaddress)

(a) Write create table statement for one of the tables above. Use your best judgement for data types.
Write at least 1 insert, update, delete statement for any of the above tables.

create table hotel (
    hotelno int,
    hotelname varchar(20),
    city varchar(20),
    primary key(hotelno)
);

insert into hotel (hotelno,hotelname,city) 
  values (1,'Marriott','New York');

update hotel set hotelname='Hilton' where hotelno=1;

delete hotel where hotelno=1


(b) List the names and addresses of all guests with 
bookings for a hotel in London, alphabetically ordered by name.

select g.guestname,g.guestaddress
from guest g,booking b,hotel h
where 
    g.guestno = b.guestno and
    b.hotelno = h.hotelno and
    h.city = 'London'
order by g.guestname

(c) What is the average price of a room?

select avg(price)
from room

(d) What is the lost income from unoccupied rooms at the 
Grosvenor Hotel?

select a.hotelno, a.amnt - b.amnt as lostamnt
from (
    select h.hotelno,sum(r.price) as amnt
    from room r,hotel h
    where 
        r.hotelno = h.hotelno and
        h.hotelname = 'Grosvenor Hotel'
    ) a,
   (select h.hotelno,sum(r.price) as amnt
    from booking b, room r, hotel h 
    where 
        b.datefrom <= NOW() and 
        (b.dateto is null OR b.dateto > NOW()) and
        b.hotelno = r.hotelno and
        b.roomno = r.roomno and
        r.hotelno = h.hotelno and
        h.hotelname = 'Grosvenor Hotel'
    group by h.hotelno
   ) b
where a.hotelno = b.hotelno

(e) What is the most commonly booked room type for each hotel 
in London?

select h.hotelno, r.type, count(b.roomno) as cnt
from booking b, hotel h, room r 
where b.hotelno = h.hotelno and
     b.hotelno = r.hotelno and
     b.roomno = r.roomno
     h.city = 'London'


select a.hotelno, a.type, a.cnt
from ( 
    select h.hotelno, r.type, count(b.roomno) as cnt
    from booking b, hotel h, room r 
    where b.hotelno = h.hotelno and
        b.hotelno = r.hotelno and
        b.roomno = r.roomno
        h.city = 'London'
    ) a,
    (select h.hotelno, r.type, count(b.roomno) as cnt
     from booking b, hotel h, room r 
     where b.hotelno = h.hotelno and
         b.hotelno = r.hotelno and
         b.roomno = r.roomno
         h.city = 'London'
     ) b
where a.hotelno = b.hotelno
group by a.hotelno, a.type, a.cnt
having a.cnt = max(b.cnt)


(f) Which hotel has the most lost income from unoccupied rooms?

hotel(hotelno*,hotelname,city)
room(roomno*,hotelno*,type,price)
booking(hotelno*,guestno*,datefrom*,dateto,roomno)
guest(guestno*,guestname,guestaddress)

select top 1 a.hotelno, a.amnt - b.amnt as lostamnt
from (
    select h.hotelno,sum(r.price) as amnt
    from room r,hotel h
    where 
        r.hotelno = h.hotelno
    group by h.hotelno

    ) a,
   (
    select h.hotelno,sum(r.price) as amnt
    from booking b, room r, hotel h 
    where 
        b.datefrom <= NOW() and 
        (b.dateto is null OR b.dateto > NOW()) and
        b.hotelno = r.hotelno and
        b.roomno = r.roomno and
        r.hotelno = h.hotelno
    group by h.hotelno
   ) b
where a.hotelno = b.hotelno
order by a.amnt - b.amnt DESC


in SQL Server: to get first 10 results you'll do:
 select top 10 from blah where glah

in Oracle: to get first 10 results you'll do:
 select ... from blah where glah and rownum <= 10;




2. (10 pts) Explain how the GROUP BY clause works. 
What is the difference between WHERE and HAVING clauses?

group by... groups :-)

having is the same as where, except it applies to groups defined by 
the group by thingie.

3. (10 pts) Construct an E-R diagram for a school (ie: your college), that has a set of students, faculty,
courses, sections, registration, etc., Document all assumptions that you make about the mapping constraints.
Tip: Take some time to think about it. List “objects” and “events” in your design (from above
questions).

----------
| blah   |      <----- E-R
----------
|        |
|        |------------- something
|________|



4. (20 pts) Repeat previous question only using UML.


----------
| blah   |      <----- UML
----------    
|        |
|        |------------- something
|________|


(20 pts) For the following, use:

     branch-name loan-number amount
     Downtown L-170 3000
     Redwood L-230 4000
     Rerryridge L-260 1700
        loan

     customer-name loan-number
      Jones L-170
      Smith L-230
      Hayes L-155
        borrower

 (a) Show result of “loan inner join borrower”.

     Downtown L-170 3000 Johnes L-170
     Redwood L-230 4000  Smith L-230
          
 (b) Show result of “loan left outer join borrower”.

     Downtown L-170 3000 Johnes L-170
     Redwood L-230 4000  Smith L-230
     Rerryridge L-260 1700 NULL NULL
     
 (c) Show result of “borrower right outer join loan”.

     Johnes L-170 Downtown L-170 3000
     Smith L-230 Redwood L-230 4000  
     NULL NULL Rerryridge L-260 1700

 (d) Show “loan full outer join borrower using (loan-number)”.

     Downtown L-170 3000 Johnes L-170
     Redwood L-230 4000  Smith L-230
     Rerryridge L-260 1700 NULL NULL
     NULL  NULL NULL   Hayes L-155

6. (10 pts) Assume that you are responsible for selecting a 
new DBMS product for a group of users at your
organization. To undertake this exercise, you must first 
establish a set of requirements for the group and
then identify a set of features that a DBMS product 
must provide to fulfill the requirements. 
Describe the process of evaluating and selecting the 
best DBMS product.

I'd do blah blah blah blah blah blah blah and a 
bit of glah glah glah glah glah with maybe some of meh.