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';