----------------------------------------------
-- Name: Jonas Wik
-- Date:
-- Assignment 9
----------------------------------------------
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', '
Insert into Hotel
Values('H05', '
Insert into Hotel
Values('H07', '
Insert into Hotel
Values('H12', '
Insert into Hotel
Values('H16', '
Insert into Hotel
Values('H24', '
Insert into Hotel
Values('H28', '
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
Insert into Guest
Values('G01011',
'Mary Tregear', '5 tarbot
Rd,
Insert into Guest
Values('G02003',
'Aline Stewart', '64
Insert into Guest
Values('G02005',
'Mike Ritchie', '18 Tain St,
Insert into Guest
Values('G02007',
'Joe Keogh', '6 Achray St,
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');
-------------------------------------------------------------------
-- 11.9: List the
names and addresses of all guests in
-- alphabetically ordered by name.
-------------------------------------------------------------------
Select Guest_Name, Address
From Guest
Where Address Like '%
Order By Guest_Name;
-------------------------------------------------------------------
-- 11.10: List
all double or family rooms with a price below 40
-- per night, in ascending order of price.
-------------------------------------------------------------------
Select *
From Room
where Price < 40 and Type != 'Single'
order by Price;
-------------------------------------------------------------------
-- 11.11: List
the Bookings for which no date_to has been spec.
-------------------------------------------------------------------
Select *
from Booking
where Date_To is null;
-------------------------------------------------------------------
-- 11.13: List
the Average price of a room
-------------------------------------------------------------------
Select Avg(Price)
From Room;
-------------------------------------------------------------------
-- 11.15: List
how many different guests have made bookings for
-- August.
-------------------------------------------------------------------
Select Count(distinct Guest_No)
from Booking
where (Date_From >=
'01-Aug-99') and (Date_From <= '31-Aug-99');
-------------------------------------------------------------------
-- 11.16: List
the price and type of all rooms at the Grosvenor
-- Hotel.
-------------------------------------------------------------------
Select Price, Type
From Room R, Hotel H
Where R.Hotel_No = H.Hotel_No
and H.Name = 'Grosvenor';
-------------------------------------------------------------------
-- 11.17: List
all Guests currently staying at the Grosvenor
-- Hotel.
-------------------------------------------------------------------
Select *
From Guest G
Where G.Guest_No in
(Select Guest_No
from
Booking
Where Date_From
<= SysDate and Date_To
>= SysDate);
-------------------------------------------------------------------
-- 11.20: List
the Rooms that are currently unoccupied at the
-- Grosvenor
Hotel.
-------------------------------------------------------------------
Select *
From Room
Where Room.Room_No in
(Select Room_No
from Booking
Where Date_From >
SysDate or Date_To < SysDate
and Booking.Hotel_No
in
(Select Hotel_No
From Hotel
Where Hotel.Name
like '%Grosvenor%'));
-------------------------------------------------------------------
-- 11.23: List
the Number of Rooms in Each Hotel in London.
-------------------------------------------------------------------
Select Hotel_No, Count(Room_No)
From Room
Where Hotel_No in
(Select Hotel_No
from Hotel
Where Address = 'London')
Group By Hotel_No;
-------------------------------------------------------------------
-- 11.25: List
the most commonly booked room type for each
-- Hotel in London.
-------------------------------------------------------------------
Select B.Hotel_No, R.Type, Count(B.Room_No)
From Booking B, Room R
Where B.Hotel_No in
(Select Hotel_No
From Hotel
Where Address
= 'London' ) and
B.Hotel_No = R.Hotel_No and
B.Room_No = R.Room_No
Group By B.Hotel_No, R.Type;