TABLE CREATION:
Design a hotel database with the following tables:
hotel10(hotelno, hotelname, city)
SQL> create table hotel10(hotelno char(2) primary key, hotelname varchar2(30), city varchar2(25));
Table created.
room10(roomno, type, price)
SQL> create table room10(roomno number primary key, hotelno char(2), type varchar2(15), price number(10, 2));
Table created.
guest10(guestno, gyestname)
SQL> create table guest10(guestno number primary key, gyestname varchar2(25));
Table created.
booking10(hotelno, guestno, datefrom, dateto, roomno)
SQL> create table booking10(hotelno char(2), guestno number, datefrom date, dateto date, roomno number);
Table created.
CONSTAINTS ADDED TO TABLES:
SQL> alter table room10 add constraint hotel_fk foreign key (hotelno) references hotel10(hotelno);
Table altered.
SQL> alter table booking10 add constraint fk_gt foreign key (guestno) references guest10(guestno);
Table altered.
SQL> alter table booking10 add constraint htl_fk foreign key (hotelno) references hotel10(hotelno);
Table altered.
SQL> alter table booking10 add constraint fk_room foreign key (roomno) references room10(roomno);
Table altered.
TABLE DESCRIPTIONS:
SQL> desc hotel10;
Name Null? Type
-------------------------- ------------------ ----------------------------
HOTELNO NOT NULL CHAR(2)
HOTELNAME VARCHAR2(30)
CITY VARCHAR2(25)
SQL> desc room10;
Name Null? Type
---------------------- -------------------- ----------------------------------
ROOMNO NOT NULL NUMBER
HOTELNO CHAR(2)
TYPE VARCHAR2(15)
PRICE NUMBER(10,2)
SQL> desc guest10;
Name Null? Type
-------------------------- --------------------- ----------------------------
GUESTNO NOT NULL NUMBER
GYESTNAME VARCHAR2(25)
SQL> desc booking10;
Name Null? Type
---------------------- --------- ------------------
HOTELNO CHAR(2)
GUESTNO NUMBER
DATEFROM DATE
DATETO DATE
ROOMNO NUMBER
INSERTING VALUES IN HOTEL10 TABLE:
SQL> insert into hotel10 values('h1', 'paradise inn', 'chennai');
1 row created.
SQL> insert into hotel10 values('h2', 'taj hotels', 'chennai');
1 row created.
SQL> insert into hotel10 values('h3', 'paradise inn', 'bangalore');
1 row created.
SQL> insert into hotel10 values('h4', 'taj hotels', 'mumbai');
1 row created.
SQL> insert into hotel10 values('h5', 'oberai', 'pune');
1 row created.
INSERTING VALUES IN ROOM10 TABLE:
SQL> insert into room10 values(100, 'h1', 'deluxe', 5500);
1 row created.
SQL> insert into room10 values(101, 'h2', 'deluxe', 1450);
1 row created.
SQL> insert into room10 values(102, 'h3', 'deluxe', 2750);
1 row created.
SQL> insert into room10 values(104, 'h4', 'deluxe', 5500);
1 row created.
SQL> insert into room10 values(105, 'h5', 'deluxe', 2000);
1 row created.
SQL> insert into room10 values(106, 'h1', 'ac', 1500);
1 row created.
SQL> insert into room10 values(107, 'h2', 'ac', 1250);
1 row created.
INSERTING VALUES IN GUEST10 TABLE:
SQL> insert into guest10(guestno, gyestname) values(1, 'xyz');
1 row created.
SQL> insert into guest10(guestno, gyestname) values(2, 'divi');
1 row created.
SQL> insert into guest10(guestno, gyestname) values(3, 'sugan');
1 row created.
SQL> insert into guest10(guestno, gyestname) values(4, 'kani');
1 row created.
SQL> insert into guest10(guestno, gyestname) values(5, 'viji');
1 row created.
SQL> insert into guest10(guestno, gyestname) values(6, 'nithi');
1 row created.
INSERTING VALUES IN BOOKING10 TABLE:
SQL> insert into booking10 values('h1', 2, '5-feb-2009', '15-feb-2009', 100);
1 row created.
SQL> insert into booking10 values('h1', 2, '1-jan-2009', '5-jan-2009', 106);
1 row created.
SQL> insert into booking10 values('h2', 1, '9-feb-2009', '16-feb-2009', 101);
1 row created.
SQL> insert into booking10 values('h3', 3, '1-mar-2009', '8-mar-2009', 102);
1 row created.
SQL> insert into booking10 values('h4', 5, '07-mar-2009', '15-mar-2009', 104);
1 row created.
DISPLAYING TABLE CONTENTS:
SQL> select * from hotel10;
HO HOTELNAME CITY
---- ------------------------------ -------------------------
h1 paradise inn chennai
h2 taj hotels chennai
h3 paradise inn bangalore
h4 taj hotels mumbai
h5 oberai pune
SQL> select * from room10;
ROOMNO HO TYPE PRICE
------------- ---- --------------- ----------
100 h1 deluxe 5500
101 h2 deluxe 1450
102 h3 deluxe 2750
104 h4 deluxe 5500
105 h5 deluxe 2000
106 h1 ac 1500
107 h2 ac 1250
7 rows selected.
SQL> select * from guest10;
GUESTNO GYESTNAME
------------- -------------------------
1 xyz
2 divi
3 sugan
4 kani
5 viji
6 nithi
6 rows selected.
SQL> select * from booking10;
HO GUESTNO DATEFROM DATETO ROOMNO
----- -------------- ----------------- --------------- --------------
h1 2 05-FEB-09 15-FEB-09 100
h1 2 01-JAN-09 05-JAN-09 106
h2 1 09-FEB-09 16-FEB-09 101
h3 3 01-MAR-09 08-MAR-09 102
h4 5 07-MAR-09 15-MAR-09 104
DML QURIES:
a. List the guest staying in hotel 'paradise inn'.
SQL> select distinct(gyestname)
2 from guest10
3 join booking10 using(guestno)
4 join hotel10 using (hotelno)
5 where guestno=guestno and
6 hotel10.hotelname='paradise inn';
GYESTNAME
-------------------------
divi
sugan
b. List the no of rooms in each hotel.
SQL> select count(roomno) as no_of_rooms
2 from room10
3 group by hotelno;
NO_OF_ROOMS
---------------------
2
2
1
1
1
c. Display the details of the hotels in which the guest 'xyz' stayed.
SQL> select * from hotel10
2 join booking10 using(hotelno)
3 join guest10 using(guestno)
4 where gyestname='xyz';
GUESTNO HO HOTELNAME CITY DATEFROM DATETO ROOMNO GYESTNAME
------------- ----- -------------------- -------------- ----------------- ---------------- --------------- -------------------------
1 h2 taj hotels chennai 09-FEB-09 16-FEB-09 101 xyz
d. List the average price of every room in hotel no 'h1'.
SQL> select avg(price), type as room_type
2 from room10
3 where hotelno='h1'
4 group by type;
AVG(PRICE) ROOM_TYPE
---------------- -----------------
5500 deluxe
1500 ac
e. List the name of the cities where hotel 'taj' is located.
SQL> select hotelname, city from hotel10
2 where hotelname like 'taj%';
HOTELNAME CITY
-------------------- -------------------------
taj hotels chennai
taj hotels Mumbai
WRITE A PL/SQL TRIGGER TO CHECK WHETHER THE PRICE IS GREATER THAN ZERO
WHENEVER RECORD IS INSERTED INTO THE TABLE.
SQL> set serveroutput on
SQL> create or replace trigger price_trig5
2 before insert on room10 for each row
3 declare price_zero_exception exception;
4 begin if(:new.price<=0)then
5 dbms_output.put_line('The prize of a room should
6 not be less than or equla to zero');
7 raise price_zero_exception;
8 end if;
9 end;
10 /
Trigger created.
BEFORE TRIGGER EXECUTION:
SQL> select * from room10;
ROOMNO HO TYPE PRICE
------------ ----- --------------- ----------------
100 h1 deluxe 5500
101 h2 deluxe 1450
102 h3 deluxe 2750
104 h4 deluxe 5500
105 h5 deluxe 2000
106 h1 ac 1500
107 h2 ac 1250
7 rows selected.
EXECUTION:
SQL> begin
2 commit;
3 insert into room10 values(&room_no, &hotel_no,&room_type, &price);
4 exception
5 when others then
6 rollback;
7 end;
8 /
Enter value for room_no: 120
Enter value for hotel_no: 'h4'
Enter value for room_type: 'f class'
Enter value for price: 1200
old 3: insert into room10 values(&room_no, &hotel_no,&room_type, &price);
new 3: insert into room10 values(120, 'h4','f class', 1200);
PL/SQL procedure successfully completed.
AFTER TRIGGER EXECUTION:
SQL> select * from room10;
ROOMNO HO TYPE PRICE
------------ ----- --------------- ----------
100 h1 deluxe 5500
101 h2 deluxe 1450
102 h3 deluxe 2750
104 h4 deluxe 5500
105 h5 deluxe 2000
106 h1 ac 1500
107 h2 ac 1250
120 h4 f class 1200
8 rows selected.
TRIGGER EXECUTION:
SQL> begin
2 commit;
3 insert into room10 values(&room_no, &hotel_no,&room_type, &price);
4 exception when others then
5 rollback;
6 end;
7 /
Enter value for room_no: 124
Enter value for hotel_no: 'h6'
Enter value for room_type: 'a class'
Enter value for price: 0
old 3: insert into room10 values(&room_no, &hotel_no,&room_type, &price);
new 3: insert into room10 values(124, 'h6','a class', 0);
PL/SQL procedure successfully completed.
Comments