TABLE CREATION:
Design a hotal database with the following tables:
hotel10(hotelno, hotalname, 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
SQL> select * from guest10;
GUESTNO GYESTNAME
------------- -------------------------
1 xyz
2 divi
3 sugan
4 kani
5 viji
6 nithi
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
5 rows selected.
WRITE A PL/SQL PACKAGE WITH
i. A PROCEDURE TO INSERT THE DETAILS INTO THE HOTEL10 TABLE.
ii. A FUNCTION WHICH TAKES HOTELNO AS INPUT AND RETURNS THE TOTAL INCOME
OF THE THAT HOTEL.
SQL> create or replace package htl_pkg as
2 procedure htl_insert(hno char, hna varchar2, cit varchar2);
3 function ret_htl_dtls(htlno in char) return number;
4 end htl_pkg;
5 /
Package created.
SQL> create or replace package body htl_pkg as
2 procedure htl_insert(hno char, hna varchar2, cit varchar2) as
3 begin
4 insert into hotel10 values(hno, hna, cit);
5 end htl_insert;
6 function ret_htl_dtls(htlno in char) return number as
7 income number;
8 begin
9 select sum(price) into income
10 from room10
11 where roomno in
12 (
13 select roomno
14 from booking10
15 where hotelno=htlno);
16 return income;
17 end ret_htl_dtls;
18 end htl_pkg;
19 /
Package body created.
BEFORE EXECUTION:
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
PACKAGE EXECUTION:
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line('total income is:'||htl_pkg.ret_htl_dtls
3 (&htl_no));
4 end;
5 /
Enter value for htl_no: 'h1'
old 3: (&htl_no));
new 3: ('h1'));
total income is:7000
PL/SQL procedure successfully completed.
SQL> begin
2 htl_pkg.htl_insert(&hotel_no, &hotel_name, &city);
3 end;
4 /
Enter value for hotel_no: 'h6'
Enter value for hotel_name: 'residency'
Enter value for city: 'cbe'
old 2: htl_pkg.htl_insert(&hotel_no, &hotel_name, &city);
new 2: htl_pkg.htl_insert('h6', 'residency', 'cbe');
PL/SQL procedure successfully completed.
AFTER PACKAGE EXECUTION:
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
h6 residency cbe
6 rows selected.
Comments