use hoteldb; CREATE TABLE HOTEL ( hotelNo numeric primary key , name varchar(40) , address varchar(40) , city varchar(200) ); CREATE TABLE ROOM ( roomNo numeric Primary Key , hotelNo numeric References HOTEL , type varchar(20) , price dec(9,2) );

CREATE TABLE GUEST ( guestNo numeric primary key , name varchar(40) , address varchar(40) );

CREATE TABLE BOOKING ( hotelNo numeric REFERENCES Hotel , guestNo numeric REFERENCES Guest , dateFrom date NOT NULL , dateTo date , roomNo numeric REFERENCES ROOM );

INSERT INTO HOTEL VALUES (1,'Hilton Hotel','123 Main st','New Jersey'); INSERT INTO HOTEL VALUES (2,'Holiday Inn','50 3rd Ave.', 'New York'); INSERT INTO HOTEL VALUES (3,'Governor Hotel','8243 Berkley Sq.','London'); INSERT INTO HOTEL VALUES (4,'New World Hotel','30 East River Rd', 'Hong Kong'); INSERT INTO HOTEL VALUES (5,'Santos Hotel','15 Main Rd', 'Philippines');

insert into room values (1,1,'Family',200); insert into room values (2,1,'Family',200); insert into room values (3,1,'Single',100); insert into room values (4,1,'Single',100); insert into room values (5,2,'Family',80); insert into room values (6,2,'Family',85); insert into room values (7,2,'Family',80); insert into room values (8,3,'Single',35); insert into room values (9,3,'Double',115); insert into room values (10,3,'Double',115); insert into room values (11,3,'Family',150); insert into room values (12,4,'Single',30); insert into room values (13,4,'Single',30); insert into room values (14,4,'Single',30); insert into room values (15,4,'Double',90); insert into room values (16,4,'Double',90); insert into room values (17,4,'Double',90); insert into room values (18,4,'Family',110); insert into room values (19,4,'Family',110); insert into room values (20,5,'Double',38); insert into room values (21,5,'Double',38); insert into room values (22,5,'Single',32); insert into room values (23,5,'Single',32);

insert into guest values (1,'John Doe','Los Angeles'); insert into guest values (2,'Mary Jane','New York'); insert into guest values (3,'Hank Kim','London'); insert into guest values (4,'Bob Jones','London'); insert into guest values (5,'Paul John','New York'); insert into guest values (6,'Ann Smith','New York'); insert into guest values (7,'James Mann','London'); insert into guest values (8,'Mary Mann','London'); insert into guest values (9,'Jim Jones','New York'); insert into guest values (10,'Tom Jones','Los Angeles'); insert into guest values (11,'Jimmy Johnson','Boston'); insert into guest values (12,'Harry Coleman','Dallas'); insert into guest values (13,'Bob James','Los Angeles'); insert into guest values (18,'John Mary','London'); insert into guest values (21,'Jeff Bridges','London'); insert into guest values (23,'Larry Cohen','New York');

*****************************************************************************************************************************

1. Using the hoteldb schema, write SQL that will provide a list of Family Rooms and their prices. The output should have:

   Hotel Name, Address, Room No, Type, Price.

2. Using the hoteldb schema, write SQL that will provide the average price of all rooms per hotel. The output should have:

   Hotel Name, Average Price

3. Using the hoteldb schema, write SQL to list the names and addresses of all guests that live in London, alphabetically ordered by name. The output should have:

   Guest Name, Address, City