----------------------------------------------

-- Name: Jonas Wik

-- Date: 04-29-2001

-- Assignment 10

----------------------------------------------

drop table Booking;

drop table Guest;

drop table Room;

drop table Hotel;

create table Hotel

(Hotel_No char(3) Primary Key,

Name VarChar2(15) Not Null,

Address VarChar2(30));

create table Room

(Room_No char(4),

Hotel_No char(3) References Hotel,

Type VarChar2(10),

Price number not null,

Constraint PK_Room

Primary Key (Room_No, Hotel_No),

Constraint TypeCheck

check (Type in('Single','Double','Family')),

Constraint PriceCheck

check (Price between 30 and 200));

create table Guest

(Guest_No char(6) Primary Key,

Guest_Name VarChar2(30) Not Null,

Address VarChar2(30));

create table Booking

(Hotel_No char(3) references Hotel,

Guest_No char(6) references Guest,

Date_From Date Not null,

Date_To Date,

Room_No char(4),

Constraint PK_Booking

Primary Key (Hotel_No, Guest_No, Date_From),

Foreign Key (Hotel_No, Room_No) References Room (Hotel_No, Room_No));

Insert into Hotel

Values('H01', 'Grosvenor', 'London');

Insert into Hotel

Values('H05', 'Glasgow', 'London');

Insert into Hotel

Values('H07', 'Aberdeen', 'London');

Insert into Hotel

Values('H12', 'London', 'Glasgow');

Insert into Hotel

Values('H16', 'Aberdeen', 'Glasgow');

Insert into Hotel

Values('H24', 'London', 'Aberdeen');

Insert into Hotel

Values('H28', 'Glasgow', 'Aberdeen');

Insert into Room

Values('R001', 'H01', 'Single', '30');

Insert into Room

Values('R002', 'H01', 'Single', '35');

Insert into Room

Values('R003', 'H05', 'Single', '40');

Insert into Room

Values('R101', 'H05', 'Double', '35');

Insert into Room

Values('R103', 'H01', 'Double', '40');

Insert into Room

Values('R105', 'H12', 'Double', '45');

Insert into Room

Values('R201', 'H12', 'Family', '80');

Insert into Room

Values('R209', 'H01', 'Family', '150');

Insert into Room

Values('R115', 'H07', 'Family', '39');

Insert into Guest

Values('G01003', 'John White', '6 Lawrence Street, Glasgow');

Insert into Guest

Values('G01011', 'Mary Tregear', '5 tarbot Rd, Aberdeen');

Insert into Guest

Values('G02003', 'Aline Stewart', '64 Fern Dr, London');

Insert into Guest

Values('G02005', 'Mike Ritchie', '18 Tain St, London');

Insert into Guest

Values('G02007', 'Joe Keogh', '6 Achray St, Aberdeen');

Insert into Booking

Values('H01', 'G01003', '25-Apr-99', '14-May-99', 'R001');

Insert into Booking

Values('H01', 'G02003', '24-Apr-99', '26-Apr-99', 'R103');

Insert into Booking

Values('H01', 'G01011', '25-Apr-99', Null, 'R209');

Insert into Booking

Values('H01', 'G02003', '20-Aug-99', '29-Aug-99', 'R103');

Insert into Booking

Values('H07', 'G02003', '12-Aug-99', '15-Aug-99', 'R115');

Insert into Booking

Values('H01', 'G02003', '11-Apr-01', '02-May-01', 'R103');

-------------------------------------------------------------------

-- 1. Generates a report for the prices for each hotel room within

-- each hotel in London.

-------------------------------------------------------------------

set LineSize 60

set PageSize 50

Set Echo Off

Set Feedback off

Spool J:\Lab11

Ttitle 'CS363|Assignment 11'

Btitle 'Jonas Wik'

Col Price Format $999.99 Heading 'PRICE'

Col Hotel_No Format A6 Heading 'Hotel|Number'

Col Room_No Format A11 Heading 'Room Number'

Col Type Format A6 Heading 'TYPE'

Break on Hotel_No skip 1

Select Hotel_No, Room_No, Type, Price

From Room

Order By Hotel_No;

Spool Off

Ttitle off

Btitle off

Clear Col

Set Echo on

Set feedback on

-------------------------------------------------------------------

-- 2. Generates report for all Hotels with number of bookings greater

-- than 1.

-------------------------------------------------------------------

set LineSize 60

set PageSize 50

Set Echo Off

Set Feedback off

Spool J:\Lab11b

Ttitle 'CS363|Assignment 11b'

Btitle 'Jonas Wik'

Col Name Format A10 Heading 'Hotel|Name'

Col Hotel_No Format A6 Heading 'Hotel|Number'

Col num_Books Heading 'Num of|Bookings'

Break on HNo skip 1

Select HNo, Name, num_Books

From Hotel H,

(select B.Hotel_No as HNo,

Count(Hotel_No) as num_Books

from Booking B

Group by B.Hotel_No)

where Hno = H.Hotel_No

Order By HNo;

Spool Off

Ttitle off

Btitle off

Clear Col

Set Echo on

Set feedback on