Hotel Database

The following are different sections in this page.

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 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 DataType Meaning
RNO number(3) 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 T-Transport.
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 amount 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 sundays.
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 under 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 days 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 and 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 least 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 staying 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 number 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 by ','.

CREATE OR REPLACE FUNCTION CUSTOMERNAMES(PRTYPE CHAR) RETURN VARCHAR 
IS
   CURSOR CUSTCUR AS
     SELECT CNAME FROM CUSTOMERS
     WHERE RNO IN ( 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.SERVICEAMOUNT('ALL') FROM CUSTOMERS R WHERE CNAME= 'RAUL';