Database Systems: SQL Queries, Table Creation and Data Archiving

This document presents a comprehensive solution to a database systems assignment, focusing on SQL queries and database manipulation. The solution addresses various SQL tasks, including retrieving data based on specific criteria, calculating revenue, counting bookings within a given time frame, and listing guest and room details. The assignment covers querying data from multiple tables using JOIN operations and filtering results based on conditions. Furthermore, the solution includes creating a view to calculate and display average room rates for hotels, demonstrating the use of aggregate functions and sorting. The assignment also involves identifying and displaying specific guest information, creating a new table for archiving historical booking data, and implementing INSERT and DELETE operations to manage data. This solution provides a detailed understanding of SQL syntax and database design principles.

Running head: DATABASE SYSTEMS
DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1
DATABASE SYSTEMS
1. List the names and addresses of all Australian guests, alphabetically ordered by name.
select GUEST_FNAME, GUEST_LNAME, GUEST_ST_ADDRESS, GUEST_SUBURB,
GUEST_STATE, GUEST_COUNTRY from GUEST where GUEST_COUNTRY='Australia'
order by GUEST_FNAME asc;
2. What would be the total revenue per night from all 'Studio' rooms if all rooms are
occupied and booked at their advertised rate?
select sum(r.RM_DAILY_RATE) as revenue from room r inner join room_booking rb ON
rb.RM_ID=r.RM_ID inner join reservation re on re.RES_ID=rb.RES_ID and
RM_TYPE='Studio';
3. How many bookings fall in October 2019?

2
DATABASE SYSTEMS
select count(res_id) from reservation where EXTRACT(YEAR FROM res_booking_time)=2019
and EXTRACT(month FROM res_booking_time)=10;
4. List names of guests who have booked a room at the Beachside Apartments on 13th
October 2019.
select g.guest_fname, g.guest_lname from guest g inner join reservation re on
re.guest_id=g.GUEST_ID inner join room_booking rb on rb.RES_ID=re.RES_ID inner join
room r on r.RM_ID=rb.RM_ID inner join hotel h on h.HTL_ID=r.HTL_ID and
h.HTL_NAME='Beachside Apartments' and re.RES_BOOKING_TIME='2019-10-13';

3
DATABASE SYSTEMS
5. List the room numbers that are ‘currently’ occupied at Holiday Inn Express.
select r.rm_id from hotel h inner join room r on r.htl_ID=h.HTL_ID inner JOIN room_booking
rb on rb.RM_ID=r.RM_ID inner join reservation re on re.RES_ID=rb.RES_ID and
h.HTL_NAME='Holiday Inn Express';
6. List the hotel names and the total number of rooms in each hotel in Indonesia.
select h.HTL_NAME, count(r.RM_ID) from hotel h inner join room r on r.HTL_ID=h.HTL_ID
and h.HTL_COUNTRY='indonesia' group by h.HTL_NAME;

4
DATABASE SYSTEMS
7. Create a 'view' containing the names of all hotels worldwide and their average room
rate. Sort the view by increasing price. Finally display the view.
View:
create view view1 as (select h.HTL_NAME, avg(r.RM_DAILY_RATE) as 'Average room rate'
from hotel h inner join room r on r.HTL_ID=h.HTL_ID group by h.HTL_NAME order
by avg(r.RM_DAILY_RATE) asc);
Display:
select * from view1;

5
DATABASE SYSTEMS
8. Find out how many children are accompanying Boris Johnson in each of his stays? Show
the hotel names and corresponding number of children.
select h.HTL_NAME, count(rb.RMBK_NUM_CHILD) as 'No of Children' from guest g inner
join reservation re on re.guest_id=g.GUEST_ID inner join room_booking rb on
rb.RES_ID=re.RES_ID inner join room r on r.RM_ID=rb.RM_ID inner join hotel h on
h.HTL_ID=r.HTL_ID and g.GUEST_FNAME='Boris' and g.GUEST_LNAME='Johnson' group
by h.HTL_NAME;
9. Find phone numbers of all the guests who have or had a booking at Crown Towers in
room number 215.
select g.guest_phone from guest g inner join reservation re on re.guest_id=g.GUEST_ID inner
join room_booking rb on rb.RES_ID=re.RES_ID inner join room r on r.RM_ID=rb.RM_ID
inner join hotel h on h.HTL_ID=r.HTL_ID and h.HTL_NAME= 'Crown Towers' and
r.rm_number=215;

6
DATABASE SYSTEMS
10. Create a separate table with the same structure as the Reservations table to hold
archive records. Using INSERT statement, copy the records of all past bookings (that
ended before today) from the Reservation table to the archive table. Then delete all
archived bookings from the Reservation table.
Create table:
create table archive_reservation (RES_ID int(11) primary key, GUEST_ID int(11) not null,
RES_CHECKIN date not null, RES_CHECKOUT date not null, RES_BOOKING_TIME
datetime not null, FOREIGN key (GUEST_ID) REFERENCES GUEST(GUEST_ID));
Insert:
INSERT into archive_reservation select * from reservation where RES_BOOKING_TIME<
date(now());

7
DATABASE SYSTEMS
Drop Foreign keys referred from reservation table:
ALTER TABLE `room_booking` DROP CONSTRAINT `FK_rmbk_reservation`;
Delete Data:
Delete from reservation where RES_BOOKING_TIME< date(now());