Hotel Database The following are different sections in this page. Tables Structure Answers to Queries Answers to PL/SQL assignments Converting Relational Model to Object Relation Model
Tables Structure Table
Meaning
TARIFF
Contains information about different types of rooms and tariff for each type.
ROOMS
Contains information about available rooms
CUSTOMERS Contains information about customers customers staying in rooms SERVICES
Contains information about services rendered to customers in rooms
TARIFF Table Column
DataType
Meaning
RTYPE
char(2)
Room type. Primary key.
RDESC
varchar2(20) Description of the type.
PRICE
number(4)
Tariff for the room type per day.
ROOMS Table Column
DataType
Meaning
RNO
number(3)
Room number.Primary key.
RTYPE
char(2)
Type of the room. Foreign key references TARIFF table.
FLOOR
number(2)
Floor number in which room is.
STATUS
char(1)
Status of the room. Y-available, N-occupied, and M-Maintenance.
CUSTOMERS Column
RNO
DataType
number(3)
Meaning
Room number of the room in which customer is staying. Foreign key references ROOMS table
CNAME
varchar2(20) Customer name.
ADDRESS
varchar2(20) Address of the customer.
PURPOSE
varchar2(30) Purpose of the stay.
CHECKIN
date
Date and time at which customer checked in.
ADVANCE
number(5)
Advance paid by the customer.
SERVICES Table Column
DataType
Meaning
RNO
number(3)
Room number for which service is rendered.
STYPE
char(1)
Type of service rendered. R-Restaurant, L - Laundry, and TTransport.
SDESC
varchar2(20) Description of the service rendered.
AMT
number(5)
Amount of the service rendered.
QUERIES Display the rooms which are available. select * from rooms where status='Y';
Display cname,checkin,number of days since checkin. select cname,checkin,CEIL(sysdate-checkin) Days,advance from customers;
Display the customers either from Delhi or Banglore checked in on 2-jul-03 select * from customers where address in('Banglore','Delhi') and checkin='2-jul-03';
Display the customers who are there for more than a week. select * from customers where (trunk(sysdate-checkin)>7);
Change the status of the room 101 to available. update rooms set status ='Y' where rno=101;
Display services of type restaurant of today. select * from services where trunc(sysdate)=ds and stype='R';
Display cname,check in date,check out date if check out date is 7 days from check in date for customers who checked in within last 3 days. select cname,checkin,checkin + 7 checkout from customers where sysdate-checkin <=3 ;
Display the services where description contain 'Dinner' and amoun t is less than 100. select * from services where sdesc='Dinner' and amt < 100;
Display the customers who checked in after 9 PM. select * from customers where To_number(To_char(checkin,'HH24')) > 21;
Display the details of services made on sunda ys. select * from services where To_char(ds,'fmday')='sunday';
Display the customers who checked-in in the current month. select * from customers where To_char(checkin,'mmyy')=To_char(sysdate,'mmyy');
Display the details where breakfast was served after 10 AM. select * from services where sdesc like '%breakfast%' and To_char(ds,'HH24') > 10;
Insert the details of the service with the following details Rno:103,Service type :Transport,Description : Toyota Colas, Date & Time: 3-jul-2003 10 AM, amount:200 insert into services values(103,to_date('3-7-2003 10','dd-mm-yyyy hh24'),'T','Toyota colas',200);
Display Rno, cname, number of days since checked in and number of days to go assuming the customer will leave on comming monday. select rno,cname, sysdate - checkin NoDays, next_day(sysdate,'Mon') - sysdate DaysToGo from customers;
Display rno, service description & Time of service of all resturant service of today. select rno, sdesc, to_char(ds,'hh24:mi') time from services where trunc(sysdate) = trunc(ds);
Display rtype, floor, rno, price rounded to 100 for all the rooms that are either available or u nder maintenance. select r.rtype,floor,rno,round(price,-2) price from rooms r,tariff t where r.rtype=t.rtype and status in('Y','M');
Display the highest rno for each room type. select rtype,max(rno) from rooms group by rtype;
Display the number of floors. select max(floor) from rooms;
Display the rno for which purpose is 'Official' & total services bill is more than 2000. select c.rno,sum(amt) from customers c,services s where c.rno=s.rno and purpose like '%official%' group by c.rno having sum(amt)>2000;
Display the least floor in which we have a single A/C room available now. select min(floor) from rooms where rtype='SA' and status = 'Y';
Display rno, Service type, average amount. select rno,stype,avg(amt) from services group by rno,stype;
Display rno, transport bill for transactions of today & yesterday. select rno,sum(amt) TransBill from services where sysdate - ds <= 1 and stype='T' group by rno;
Display date, stype,total bill. select ds,stype,sum(amt) from services
group by stype,ds;
Display the room type in which more than 2 rooms are under maintenance. select rtype from rooms where status='M' group by rtype having count(*)>2;
Display rno, service type, total amount along with total amount for each service and grand total. select rno,stype,sum(amt) from services group by rollup(rno,stype);
Display the details of rooms which are in the same floor as room 103. select r1.* from rooms r1, rooms r2 where r2.rno = 103 and
r1.floor = r2.floor;
Display the rno & total bill. select rno, sum(amt) + ceil(sysdate - checkin) * price from customers c, services s, rooms r, tariff t where c.rno = r.rno and r.rtype = t.rtype and c.rno = s.rno group by rno,checkin,price; -- CHEKIN and PRICE are required as we used them in SELECT
Display the rooms for which we have a customer from Delhi checked in. select r.rno, rtype, cname from customer c, rooms r where c.rno = r.rno and address like '%Delhi%';
Display the rtype and total service amount for rooms of that type. select t.rtype, sum(amt) from rooms r, customers c, tariff t, services s where r.rno = r.rno and c.rno and s.rno and t.rtype = r.rtype group by t.rtype;
Display the rno, cname, service description and price including the customers who do not have any service. select c.rno,cname, sdesc, price from customers c, services s where c.rno = s.rno(+); -- outer join
Display rtype,rno,floor,cname and number of da ys since check in for customers who have taken dinner on 4th july 2003. select rtype,r.rno,floor,cname,sysdate-checkin from rooms r,customers c,services s where r.rno=s.rno=c.rno and sdesc like '%DINNER%' and trunc(ds)='4-jul-03';
Display cname if the total service bill is more than 1000. select cname from customers c, services s where c.rno = s.rno group by cname having sum(amt) > 1000;
Display rtype in which we have more than 2 customers staying on official work. select rtype from rooms r,customers c where purpose like '%OFFICIAL%' and r.rno=c.rno group by rtype having count(*)>2;
Display the customers who are in AC rooms.
select * from customers where rno in (select rno from rooms
where rtype like '_A' );
Display the customers who checked in the current month and the total bill for services is more than 1000. select * from customers where To_char(checkin,'mmyy')=To_char(sysdate,'mmyy') and rno in (select rno from services group by rno having sum(amt) > 500 );
Display the rooms for which the price is more than 500. select * from rooms where rtype in (select rtype from tariff where price > 500 );
Display customers in rooms that are in 2nd floor an d the purpose is official. select * from customers where purpose like '%OFFICIAL%' and rno in ( select rno from rooms where floor=2);
Display the customer who checked in the most recently. select * from customers where checkin= ( select max(checkin) from customers );
Display the room types in which we have more than 10 customers. select * from tariff where rtype in ( select rtype from rooms where status='N' group by rtype having count(*)>10 );
Display room type & number of customers who who have made use of restaurant service. select rtype,count(*) from rooms r,customers c where r.rno=c.rno and c.rno in ( select rno from services where stype='R' ) group by rtype;
Display the details of room types in which at le ast one customer has paid more than 5000 advance. select rtype from rooms where rno in ( select rno from customers where advance >5000 );
Display the services made to customers who have come from Hyderabad or the customers who have come from Banglore on official visit. select * from services where rno in ( select rno from customers where address like '%HY%' ) or rno in ( select rno from customers where address like '%BANGALORE%' and purpose like '%OFFICIAL%' );
Display the room types in which we have either more than two rooms are occupied or more than two rooms are in maintenance. select * from tariff where rtype in ( select rtype from rooms where status='N' group by rtype having count(*)>2 ) or rtype in ( select rtype from rooms where status='M' group by rtype having count(*)>2 );
Display the details of customers with highest bill for resturent. select * from customers where rno in ( select rno from services where stype='R' group by rno having sum(amt)= ( select max(sum(amt)) from services where stype='R' group br rno ) );
Display rtype, cname, rno for the customers who checked in most recently into each room type. select rtype,cname,r.rno from customers c,rooms r where c.rno=r.rno and checkin = ( select max(checkin) from customers,rooms where customers.rno=rooms.rno and rtype=r.rtype );
Subtract the room rent from advance for customer in room 103. update customers set advance = advance - ( select ceil(sysdate-checkin) * price from tariff t, rooms r, customers c where t.rtype = r.rtype and c.rno = r.rno and rno = 103) where rno = 103;
Display the details of the customer who is sta ying for maximum number of days. select * from customers where checkin = ( select min(checkin) from customers );
Display the details for services for rooms where the price is more than 500. select * from services where rno in ( select rno from rooms r, tariff t where r.rtype = t.rtype and price > 500);
Delete the services of rooms in which we have customers checked in on 10th july. delete from services where rno in ( select rno from customers where checkin='25-jul-03' );
Insert a new room in fourth floor of type 'SA' the room number must be (highest room numbe r on that floor) + 1. insert into rooms values( (select NVL(max(rno),400)+1 from rooms where floor=4),'SA',4,'Y');
Create a new table called Restaurant_Services and copy the data from services table related to restaurant in to the table. create table resturent_service as select rno,ds,sdesc,amt from services where stype='R'
PL/SQL ASSIGNMENTS Check whether any customer is in room 202 and change the status to maintenance if no customer is present. DECLARE V_STATUS CHAR(1); BEGIN SELECT STATUS INTO V_STATUS FROM ROOMS WHERE RNO=202; IF V_STATUS <> 'N'THEN UPDATE ROOMS SET STATUS = 'M' WHERE RNO = 202; COMMIT; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('ROOM CAN NOT BE KEPT UNDER MAINTENANCE.'); END;
Remove the details of the customer 'JAMES' as he checked out. DECLARE V_RNO ROOMS.RNO%TYPE V_C NUMBER(1); BEGIN SELECT RNO INTO V_RNO FROM CUSTOMERS WHERE CNAME='JAMES'; DELETE FROM SERVICES WHERE RNO= V_RNO; DELETE FROM CUSTOMERS WHERE RNO= V_RNO; UPDATE ROOMS SET STATUS='Y' WHERE RNO=V_RNO; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('CUSTOMER NOT FOUND.'); END; /
Find out the number of rooms occupied in each type and change tariff based on that as follows: IF occupation is less than 3 reduce the price by 20%, IF occupation is less than 6 reduce the price by 10%. DECLARE CURSOR RTCUR IS SELECT RTYPE FROM TARIFF; V_COUNT NUMBER(2); BEGIN FOR REC IN RTCUR LOOP SELECT COUNT(*) INTO V_COUNT FROM CUSTOMERS C,ROOMS R WHERE C.RNO=R.RNO AND RTYPE = REC.RTYPE; IF V_COUNT < 3 THEN UPDATE TARIFF SET PRICE=PRICE*.8 WHERE RTYPE=REC.RTYPE; ELSIF V_COUNT < 6 THEN UPDATE TARIFF SET PRICE=PRICE*.9 WHERE RTYPE=REC.RTYPE; END IF; END LOOP; COMMIT; END;
Display 5th to 10th transaction of customer in room 102. DECLARE CURSOR SERVICECUR AS SELECT * FROM SERVICES WHERE RNO = 102; BEGIN FOR R IN SERVICECUR LOOP EXIT WHEN SERVICECUR%ROWCOUNT > 10; IF SERVICECUR%ROWCOUNT >= 5 THEN DBMS_OUTPUT.PUT_LINE(R.RNO || ',' || R.DS || ',' || R.STYPE || ',' || R.AMT); END IF; END LOOP; END;
Create a procedure that takes room number and performs checkout operation. CREATE OR REPLACE PROCEDURE CUSTCHECKOUT(PRNO NUMBER) IS BEGIN DELETE FROM SERVICES WHERE RNO=PRNO; DELETE FROM CUSTOMERS WHERE RNO=PRNO; UPDATE ROOMS SET STATUS='Y' WHERE RNO=PRNO; COMMIT; END;
Create a function that takes room type and returns balance to be paid by customer. CREATE OR REPLACE FUNCTION BILL(PRNO NUMBER) RETURN NUMBER IS V_SUM NUMBER(6); V_DAYS NUMBER(2); V_PRICE NUMBER(5); V_ADV NUMBER(5); BEGIN SELECT RTYPE INTO V_RTYPE FROM ROOMS WHERE RNO=PRNO AND STATUS='N'; SELECT PRICE INTO V_PRICE FROM TARIFF WHERE RTYPE = V_RTYPE; SELECT ADVANCE,CEIL(SYSDATE-CHECKIN) INTO V_ADV,V_DAYS FROM CUSTOMERS WHERE RNO=PRNO; SELECT SUM(AMT) INTO V_SUM FROM SERVICES WHERE RNO=PRNO; RETURN (V_DAYS*V_PRICE + V_SUM)- V_ADV; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('EITHER ROOM NUMBER IS INVALID OR NOT OCCUPIED!'); END;
Create a function that takes room type and returns the name of customers saperated b y ','. CREATE OR REPLACE IS CURSOR CUSTCUR SELECT CNAME WHERE RNO IN
FUNCTION CUSTOMERNAMES(PRTYPE CHAR) RETURN VARCHAR AS FROM CUSTOMERS ( SELECT RNO FROM ROOMS WHERE RTYPE = PRTYPE);
CUST VARCHAR2(200) := ''; BEGIN FOR R IN CUSTCUR LOOP -- ADD , ONLY BEFORE SECOND CUSTOMER NAME ONWARDS IF CUST <> '' THEN CUST := CUST || ','; END IF; CUST := CUST || R.CNAME; END LOOP; RETURN CUST;
END;
Create a trigger that changes the status of the room after customer checks in. CREATE OR REPLACE TRIGGER TRG_cHANGE_STATUS AFTER INSERT ON CUSTOMERS FOR EACH ROW BEGIN UPDATE ROOMS SET STATUS='N' WHERE RNO=:NEW.RNO; END;
Create a trigger that makes the room available after customer checks out. CREATE OR REPLACE TRIGGER TRG_cHANGE_STATUS AFTER DELETE ON CUSTOMERS FOR EACH ROW BEGIN UPDATE ROOMS SET STATUS='Y' WHERE RNO=:NEW.RNO; END;
Write a function which takes a table name and delete rows from the table and return the number of rows deleted. CREATE OR REPLACE FUNCTION DELETEROWS(TABLENAME VARCHAR2) RETURN NUMBER IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' || TABLENAME ||; RETURN SQL%ROWCOUNT; END;
Converting Relational model to Object-Relational Model Create an object type for Room type CREATE OR REPLACE TYPE ROOMTYPE_TYPE AS OBJECT ( RTYPE CHAR(2), RDESC VARCHAR2(15), PRICE NUMBER(4), MEMBER FUNCTION NETPRICE RETURN NUMBER, MEMBER PROCEDURE CHANGEPRICE(NEWPRICE NUMBER) );
CREATE OR REPLACE TYPE BODY ROOMTYPE_TYPE IS MEMBER FUNCTION NETPRICE RETURN NUMBER IS BEGIN -- ADD SALES TAX TO THE TARIFF RETURN PRICE + PRICE * 0.12; END; MEMBER PROCEDURE CHANGEPRICE(NEWPRICE NUMBER) IS BEGIN PRICE := NEWPRICE; END;
END;
Create an object table that contains objects of ROOMTYPE_TYPE objects CREATE TABLE ROOMTYPES OF ROOMTYPE_TYPE;
Insert rows into ROOMTYPES table INSERT INTO ROOMTYPES VALUES ('SA','SINGLE A/C',800); INSERT INTO ROOMTYPES VALUES ('DA','DOUBLE A/C',1200); INSERT INTO ROOMTYPES VALUES ('AS','A/C SUITE',2000);
Create object type for ROOM_TYPE to represent a room CREATE OR REPLACE TYPE ROOM_TYPE AS OBJECT ( RNO NUMBER(4), RTYPE REF ROOMTYPE_TYPE, FLOOR NUMBER(2), STATUS CHAR(1), MEMBER PROCEDURE CHANGETYPE(NEWTYPE CHAR), MEMBER FUNCTION ISAVAILABLE RETURN BOOLEAN ); / CREATE OR REPLACE TYPE BODY ROOM_TYPE IS MEMBER PROCEDURE CHANGETYPE(NEWTYPE CHAR) IS ROOMTYPE_REF REF ROOMTYPE_TYPE; BEGIN -- GET REF TO NEW TYPE SELECT REF(R) INTO ROOMTYPE_REF FROM ROOMTYPES R WHERE RTYPE = NEWTYPE; RTYPE := ROOMTYPE_REF; END; MEMBER FUNCTION ISAVAILABLE RETURN BOOLEAN IS BEGIN IF STATUS = 'Y' THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; END; /
Create an object table that contains objects of ROOM_TYPE objects CREATE TABLE ROOMS OF ROOM_TYPE;
Insert rows into ROOMS table INSERT INTO ROOMS VALUES( 1, (SELECT REF(R) FROM ROOMTYPES R WHERE RTYPE = 'SA'),1,'Y'); INSERT INTO ROOMS VALUES( 2, (SELECT REF(R) FROM ROOMTYPES R WHERE RTYPE = 'SA'),1,'Y'); INSERT INTO ROOMS VALUES( 3, (SELECT REF(R) FROM ROOMTYPES R WHERE RTYPE = 'DA'),1,'Y');
INSERT INTO ROOMS VALUES(101,(SELECT REF(R) FROM ROOMTYPES R WHERE RTYPE = 'DA'),2,'Y');
Following is a PL/SQL block that uses CHANGETYPE method to change room type DECLARE R ROOM_TYPE; BEGIN SELECT VALUE(R) INTO R FROM ROOMS R WHERE RNO = 3; R.CHANGETYPE('SA'); UPDATE ROOMS R SET VALUE(R) = R WHERE RNO = 3; END;
Create object type for service called SERVICE_TYPE CREATE OR REPLACE TYPE SERVICE_TYPE AS OBJECT ( STYPE CHAR(2), SDESC VARCHAR2(20), SD DATE, AMT NUMBER(5) );
Create a table type that contains objects of SERVICE_TYPE CREATE TYPE SERVICES_TABLE AS TABLE OF SERVICE_TYPE;
Create object type to represent customer along with services CREATE OR REPLACE TYPE CUSTOMER_TYPE AS OBJECT ( RNO REF ROOM_TYPE, CNAME VARCHAR2(20), CHECKIN DATE, SERVICES SERVICES_TABLE, MEMBER FUNCTION ROOMRENT RETURN NUMBER, MEMBER FUNCTION SERVICEAMOUNT(STYPE CHAR) RETURN NUMBER, MEMBER FUNCTION NODAYS RETURN NUMBER ); /
CREATE OR REPLACE TYPE BODY CUSTOMER_TYPE IS MEMBER FUNCTION ROOMRENT RETURN NUMBER IS ROOM ROOM_TYPE; RTYPE ROOMTYPE_TYPE; BEGIN -- GET PRICE OF THE ROOM SELECT DEREF(RNO) INTO ROOM FROM DUAL; SELECT DEREF(ROOM.RTYPE) INTO RTYPE FROM DUAL; RETURN RTYPE.PRICE; END; MEMBER FUNCTION SERVICEAMOUNT(STYPE CHAR) RETURN NUMBER IS TOTAMT NUMBER(5) := 0; BEGIN FOR I IN SERVICES.FIRST..SERVICES .LAST LOOP
IF SERVICES(I).STYPE = STYPE OR STYPE = 'ALL' THEN TOTAMT := TOTAMT + SERVICES(I).AMT; END IF; END LOOP; RETURN TOTAMT; END; MEMBER FUNCTION NODAYS RETURN NUMBER IS BEGIN RETURN SYSDATE - CHECKIN; END; END; /
Create an object table that contains details of customers. As CUSTOMER_TYPE has a table (nested table), use NESTED TABLE option to specify the table name for nested table. CREATE TABLE CUSTOMERS OF CUSTOMER_TYPE NESTED TABLE SERVICES STORE AS SERVICES_NT;
Insert some rows into CUSTOMERS table INSERT INTO CUSTOMERS VALUES( (SELECT REF(R) FROM ROOMS R WHERE RNO = 1), 'SACHIN TENDULKAR', SYSDATE - 4,SERVICES_TABLE()); INSERT INTO CUSTOMERS VALUES( (SELECT REF(R) FROM ROOMS R WHERE RNO =2), 'RAUL', SYSDATE - 2, SERVICES_TABLE());
Insert rows into SERVICES table of CUSTOMERS table INSERT INTO TABLE ( SELECT SERVICES FROM CUSTOMERS WHERE CNAME = 'RAUL') VALUES('RE','DINNER',SYSDATE,250); INSERT INTO TABLE ( SELECT SERVICES FROM CUSTOMERS WHERE CNAME = 'RAUL') VALUES('TR','TRIP TO THEATER',SYSDATE,200);
Get details of rooms SELECT RNO, FLOOR, R.RTYPE.RTYPE, R.RTYPE.PRICE FROM ROOMS R;
Get service details of customer RAUL SELECT SDESC FROM TABLE ( SELECT SERVICES FROM CUSTOMERS WHERE CNAME = 'RAUL')
Get room rent and total for services of customer raul SELECT R.ROOMRENT(),R.SERVICEAMOU NT('ALL') FROM CUSTOMERS R WHERE CNAME= 'RAUL';
