Question

Using the HotelDB tables, provide the following result as a screen image.

WRITE SQL to retrieve rows from any hotel with Family room types and price less than $150.

use hoteldb;
CREATE TABLE HOTEL
( hotelNo numeric primary key
, name varchar(40)
, address varchar(40)
, city varchar(200)
);
CREATE TABLE ROOM
( roomNo numeric Primary Key
, hotelNo numeric References HOTEL
, type varchar(20)
, price dec(9,2)
);

CREATE TABLE GUEST
( guestNo numeric primary key
, name varchar(40)
, address varchar(40)
);

CREATE TABLE BOOKING
( hotelNo numeric REFERENCES Hotel
, guestNo numeric REFERENCES Guest
, dateFrom date NOT NULL
, dateTo date
, roomNo numeric REFERENCES ROOM
);

INSERT INTO HOTEL VALUES (1,'Hilton Hotel','123 Main st','New Jersey');
INSERT INTO HOTEL VALUES (2,'Holiday Inn','50 3rd Ave.', 'New York');
INSERT INTO HOTEL VALUES (3,'Governor Hotel','8243 Berkley Sq.','London');
INSERT INTO HOTEL VALUES (4,'New World Hotel','30 East River Rd', 'Hong Kong');
INSERT INTO HOTEL VALUES (5,'Santos Hotel','15 Main Rd', 'Philippines');

insert into room values (1,1,'Family',200);
insert into room values (2,1,'Family',200);
insert into room values (3,1,'Single',100);
insert into room values (4,1,'Single',100);
insert into room values (5,2,'Family',80);
insert into room values (6,2,'Family',85);
insert into room values (7,2,'Family',80);
insert into room values (8,3,'Single',35);
insert into room values (9,3,'Double',115);
insert into room values (10,3,'Double',115);
insert into room values (11,3,'Family',150);
insert into room values (12,4,'Single',30);
insert into room values (13,4,'Single',30);
insert into room values (14,4,'Single',30);
insert into room values (15,4,'Double',90);
insert into room values (16,4,'Double',90);
insert into room values (17,4,'Double',90);
insert into room values (18,4,'Family',110);
insert into room values (19,4,'Family',110);
insert into room values (20,5,'Double',38);
insert into room values (21,5,'Double',38);
insert into room values (22,5,'Single',32);
insert into room values (23,5,'Single',32);

insert into guest values (1,'John Doe','Los Angeles');
insert into guest values (2,'Mary Jane','New York');
insert into guest values (3,'Hank Kim','London');
insert into guest values (4,'Bob Jones','London');
insert into guest values (5,'Paul John','New York');
insert into guest values (6,'Ann Smith','New York');
insert into guest values (7,'James Mann','London');
insert into guest values (8,'Mary Mann','London');
insert into guest values (9,'Jim Jones','New York');
insert into guest values (10,'Tom Jones','Los Angeles');
insert into guest values (11,'Jimmy Johnson','Boston');
insert into guest values (12,'Harry Coleman','Dallas');
insert into guest values (13,'Bob James','Los Angeles');
insert into guest values (18,'John Mary','London');
insert into guest values (21,'Jeff Bridges','London');
insert into guest values (23,'Larry Cohen','New York');

Answer 1

The following screen image is the result of the commands that are written in order to use the database hotel IDB and the creation of four tables HOTEL, ROOM, GUEST and BOOKING along with their individual records as written in INSERT statement as given in the question.

The screen image of all for table creation along with their entity is attached below.

In order to fetch the entire row from any hotel that are having the FAMILY room type and price less than 150 dollars then it use both the HOTEL and ROOM table which is interlinked by the NATURAL JOIN as associated with the hotel number(hotelNo).

The condition applied in WHERE clause where type of room table is family and price of room table is less than $150.

It also use AND operator so that when both the condition are satisfied true then only the data retrieved.

Along with that, the entire row of the hotel details is fetched from HOTEL table and the output of the given query is also attached with screen image.

The SQL command is:

Select hotel.hotelNo, hotel.name, hotel.address, hotel.city from HOTEL NATURAL JOIN ROOM where ROOM.type="Family" AND ROOM.Price<150;