HotelBranch (hotelBranchNo, hotelName, city) HotelRoom (roomNo, hotelBranchNo, t

Question

HotelBranch (hotelBranchNo, hotelName, city) HotelRoom (roomNo, hotelBranchNo, type, price) Reservation (hotelBranchNo, CID, dateFrom, dateTo, roomNo) Customer (CID, customerName, customerAddress) With given schemas above where HotelBranch contains hotel details and hotelBranchNo is the primary key; HotelRoom contains room details for each hotel branch and (roomNo, hotelBranchNo) forms the primary key; Reservations contains details of bookings and (hotelBranchNo, guestNo, dateFrom) form s the primary key; Customer contains guest details and guestNo is the primary key. Roomt Types "D for Double, F for family) Provide the SQL statements for creating the tables above. you may choice each attribute data type as you think is best. You should also maintain the referential integrity constraints in your statements. Provide the SQL statements for each of the following: List full details of all hotels List full details of all hotels in Zavidovic. List the names and addresses of all guests in Tuzla, alphabetically ordered by name. List all double or family rooms with a price below 20.00 KM per night, in ascending order of price. List the bookings for which no dateTo has been specified. How many hotel branches are there? What is the average price of a room? List the average price for rooms for each type in each branch as descending order? What is the total revenue per night from all double rooms? How many different customers have made reservations for June 2016?

Explanation / Answer

CREATION OF TABLES:

i)

CREATE TABLE HOTELBRANCH(
HOTELBRANCHNO NUMBER(5) PRIMARY KEY,
HOTELNAME VARCHAR2(15),
CITY VARCHAR2(15)
);

ii)

CREATE TABLE HOTELROOM
(
ROOMNO NUMBER(4),
HOTELBRANCHNO NUMBER(5) REFERENCES HOTELBRANCH ON DELETE CASCADE,
TYPE VARCHAR2(1),
PRICE NUMBER(8,2),
PRIMARY KEY(ROOMNO, HOTELBRANCHNO)
);

iv)

CREATE TABLE CUSTOMER
(
CID NUMBER(7) PRIMARY KEY,
CUSTOMERNAME VARCHAR2(30),
CUSTOMERADDRESS VARCHAR2(50)
);

iii)

CREATE TABLE RESERVATION
(
HOTELBRANCHNO NUMBER(5),
CID NUMBER(7) REFERENCES CUSTOMER ON DELETE CASCADE,
DATEFROM DATE,
DATETO DATE DEFAULT NULL,
ROOMNO NUMBER(4),
FOREIGN KEY(HOTELBRANCHNO, ROOMNO) REFERENCES HOTELROOM(HOTELBRANCHNO, ROOMNO),
PRIMARY KEY(HOTELBRANCHNO, CID, DATEFROM)
);

SQL QUERIES:

1.
SELECT * FROM HOTELBRANCH;

2.
SELECT * FROM HOTELBRANCH
WHERE CITY LIKE 'Zavidovic';

3.
SELECT CUSTOMERNAME, CUSTOMERADDRESS FROM CUSTOMER
WHERE CID IN
(SELECT CID FROM RESERVATION
WHERE HOTELBRANCHNO IN
(SELECT HOTELBRANCHNO FROM HOTELBRANCH
WHERE HOTELNAME LIKE 'Tuzla'))
ORDER BY CUSTOMERNAME;

4.
SELECT * FROM HOTELROOM
WHERE PRICE < 20.00
ORDER BY PRICE;

5.
SELECT * FROM RESERVATION
WHERE DATETO IS NULL;

6.
SELECT COUNT(*) FROM HOTELBRANCH;

7.
SELECT Avg(PRICE) AS AVERAGEPRICE FROM HOTELROOM;

8.
SELECT HOTELBRANCHNO, TYPE, Avg(PRICE) AS AVERAGEPRICE FROM HOTELROOM
GROUP BY HOTELBRANCHNO, TYPE
ORDER BY DESC;

9.
SELECT Sum(PRICE) AS REVENUE FROM HOTELROOM
WHERE TYPE = 'D';

10.
SELECT COUNT(DISTINCT CID) AS DifferentCustomers FROM RESERVATION
WHERE DATE LIKE '%JUN-2016';

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.