Question

Hotel (hotelno(PK), hotelname, city)

•         Room (roomno (PK), hotelno (PK,FK), type, price) type can be single, double, family

•         Booking (hotelno(PK,FK), guestno(PK,FK), startdate(PK), enddate, roomno(PK,FK))

•         Guest (guestno(PK), guestname, guestaddress)

1-Display on the screen the hotel name and city of all room type family.

2-Display hotel name and city for the guests currently staying at the Holiday Inn Hotel.

Answer 1

SOLUTION:

The following solution has been implemented in SQL SERVER database. Given below are the list of queries that represent the table creation and the queries corresponding to the SOLUTION concerned.

CREATION:

create table Hotel(hotelNo int PRIMARY KEY, hotelName varchar(30), city varchar(30));
create table Room(roomNo int, hotelNo int, type varchar(20), price decimal,
constraint pk_Room_Hotel primary key(roomNo, hotelNo));
alter table Room add constraint fk_Room_hotelNo FOREIGN KEY(hotelNo) REFERENCES Hotel(hotelNo);
create table Guest(guestNo int PRIMARY KEY, guestName varchar(50) NOT NULL, guestAddress varchar(100));
create table Booking(hotelNo int, guestNo int, dateFrom date, dateTo date, roomNo int,
constraint pk_Booking PRIMARY KEY(hotelNo,guestNo,dateFrom));

SAMPLE DATASET USED

SOLUTION QUERIES

1-Display on the screen the hotel name and city of all room type family.

SELECT hotelName, city 
FROM HOTEL 
WHERE hotelNo IN(SELECT hotelNo 
                                        FROM Room 
                                        WHERE Type='Family');

2-Display hotel name and city for the guests currently staying at the Holiday Inn Hotel.

SELECT Hotel.hotelName, Hotel.city FROM Booking, Hotel
WHERE Booking.hotelNo = Hotel.hotelNo and Hotel.hotelNo=(SELECT hotelNo 
                                FROM Hotel 
                                WHERE hotelName='Holiday Inn Hotel');

OUTPUT

Hope this helps.