Guideline:
- There are 4 problems with 100 points in total.
- There is no limit on how much time you spend on the exam except that you must submit your answer by 11/4, 11:59 pm Eastern time. No late submission will be accepted. You can submit your answer up to three times but only the last one counts.
- You should work on the exam by your own (you may look up textbook, slides, etc.), and should not ask anyone else for help.
- You may ask clarification questions with the instructor. You should ask these questions via email and should not post on discussion board. Basically there should be no discussion on the exam on discussion board.
- If a clarification question is common to others, the instructor will post the answer as an announcement. So please check course announcement regularly.
- For SQL and PL/SQL programs, just submit the statements themselves. There is no need to print out results or show screen shots.
A Hotel Database used in the EXAM.
drop table reservation;
drop table room_rate;
drop table room;
drop table guest;
create table guest(
gid integer, -- guest ID
gname varchar(50), -- guest name
gaddress varchar(100), -- guest address
gcard varchar(16), -- credit card
primary key (gid));
insert into guest values (1, 'John', '123 Hilltop Rd, 21250', '1233123498709898');
insert into guest values (2, 'Alice', '2230 Baltimore National Pike,21042', '7233553455557777');
insert into guest values (3, 'Bob', '145 Main St, 21043', '9999344455556666');
create table room(
rid integer, --- room id
rsize varchar(20), --- room size, e.g., '2 queen size bed',
primary key (rid));
insert into room values(123,'2 queen size bed');
insert into room values(400,'1 king size bed');
insert into room values(423,'2 queen size bed');
create table room_rate(
room_rate_id integer, --- a primary key for room_rate.
rid integer, --- room id
startdate date, --- start date of this rate
enddate date, --- end date of this rate
price number, --- room price within that period
primary key (room_rate_id),
foreign key (rid) references room);
--- The price of a room does no change from a startdate to an enddate
insert into room_rate values(1, 123, date '2016-10-1',date '2016-12-19',123);
insert into room_rate values(2, 123, date '2016-12-20',date '2017-1-10',163);
insert into room_rate values(3, 400, date '2016-10-1',date '2016-12-19',113);
insert into room_rate values(4, 400, date '2016-12-20',date '2017-1-10',153);
create table reservation(
reservation_id integer, --- reservation id
gid integer, --- guest id
rid integer, --- room id
checkindate date, --- check in date
checkoutdate date, --- check out date
numguest integer, --- number of guests
total number, --- total charge
primary key(reservation_id),
foreign key (gid) references guest,
foreign key (rid) references room);
insert into reservation values(1, 1, 123, date '2016-10-1', date '2016-10-2', 2, null);
insert into reservation values(2, 3, 400, date '2016-12-22', date '2016-12-27', 1, null);
insert into reservation values(3, 2, 123, date '2016-12-27', date '2016-12-29', 2, null);
insert into reservation values(4, 1, 123, date '2016-12-21', date '2016-12-23', 2, null);
Problem 1: Please write ONE SQL statement to implement each of the following tasks. Please do not hardcode to skip joins (your code need to work regardless of rows in the tables).
Task 1: Return the price for room 123 on Nov 15, 2016. Hint: the price of a room does not change from a startdate to enddate
Task 2: Return total number of reservations made by guest John. Do not hardcode gid.
Task 3: Return the name of each guest and the total number of reservations per guest.
Task 4: Return the names of guests who has made at least two reservations.
Task 5: return the price of the room John has reserved on October 1st, 2016.
Hint: join is needed. In addition, you need to compare October 1st, 2016 with the checkindate, checkoutdate as well as with startdate (from room_rate table) and enddate.
Problem 2: Please write an anonymous PL/SQL program to compute the sum of 13, 23, 33, ..., 1003. Here n3 means n*n*n (cube of n).
Problem 3: Please write an anonymous PL/SQL program to print out the room size of room 400. Please use implicit cursor and handle exception.
Problem 4: Please write an anonymous PL/SQL program to print out the checkin date and checkout date of reservations made by John. Hint: think about whether you should use implicit or explicit cursor.