Question

Write a pl/sql block of code that will make a reservation for a customer for any destination. The destination id, number of peolpe going and date will be entered by the user. Your code should calculate the cost and enter the data in the sales table. No destination can have more than 10 people going during the same dates. If the number is greater than 10 raise 'Sold_out' exception and print a message 'Sorry we are sold out for ths destination. Try a different date'. Show the exception worked in your output.

This is the Scrip for this database

drop table zk_sales;
drop table zk_destinations;
drop table zk_hotels;

Create table zk_Hotels
( Hotel_ID      number Primary Key,
Hotel_name    varchar2(25)
);


Create table zk_Destinations
( dest_id        number Primary Key       ,
Dest_name       Varchar2(20)            ,
Dest_location   Varchar2(20)            ,
Price_Person    real                    ,
Hotel_id        number                  ,
Golf            Varchar2(3)             ,
Hiking          Varchar2(3)             ,
Beach           Varchar2(3)             ,
Sking           Varchar2(3)             ,
AmusementPark   Varchar2(3)             ,
CampGround      Varchar2(3)             ,
constraint dest_hotelId_FK foreign Key(hotel_id)
references zk_Hotels(hotel_id)
);

Create table zk_Sales
(Sale_ID        number Primary Key     ,
dest_id number                  ,
Cust_Name       Varchar2(20)            ,
Balance real                    ,
reservation_start_date Date            ,
reservation_end_date    Date            ,
Confirmation    Varchar2(15)            ,
guest_count     number                  ,
constraint      sales_destid_FK Foreign key(dest_id)
references zk_Destinations(dest_id)
);


        

insert into zk_hotels
Values(0,'Camp Ground');
insert into zk_hotels
Values(1,'Hilton Garden');
insert into zk_hotels
Values(2,'Mariott Hotel');
insert into zk_hotels
Values(3,'Double Tree');
insert into zk_hotels values(4, 'on Ship');


insert Into zk_destinations
Values(100, 'Disney World', 'Orlando Fl', 500.0, 1, 'Yes', 'Yes', 'Yes','No', 'Yes', 'Yes');

insert Into zk_destinations
Values(110, 'Europe Cruise', 'San Francisco Ca', 800.0, 4, 'No', 'No', 'No','No', 'No', 'No');

insert Into zk_destinations
Values(120, 'Myrtle Beach', 'Myrtle Beach SC', 700.0, 2, 'Yes', 'Yes', 'Yes','No', 'No', 'No');

insert Into zk_destinations
Values(130, 'Snow Shoe Mtn', 'Silver Spring VA', 400.0, 3, 'No', 'Yes', 'No','Yes', 'No', 'No');

insert Into zk_destinations
Values(140, 'Colorado Ski' , 'Denver Co', 500.0, 3,'No', 'Yes', 'No','Yes', 'No', 'No');

insert Into zk_destinations
Values(150, 'KOA Camp grounds', 'Harrisburg PA', 100.0, 0, 'Yes', 'Yes', 'no','No', 'Yes', 'Yes');

Insert into zk_sales
Values(1, 100, 'Mr. Goodwill', 1000.0, to_date('01/14/2014','mm/dd/yyyy'), to_date( '01/24/2014','mm/dd/yyyy'), 'Confirmed', 2);

Insert into zk_sales
Values(2, 100, 'Mrs. Smith', 1000.0, to_date('01/14/2014','mm/dd/yyyy'), to_date( '01/24/2014','mm/dd/yyyy'), 'Confirmed', 2);

Insert into zk_sales
Values(3, 120, 'Mr. Jones', 2400.0, to_date('02/14/2014','mm/dd/yyyy'), to_date( '02/24/2014','mm/dd/yyyy'), 'Cancelled', 3);

Insert into zk_sales
Values(4, 140, 'Mr. Brown', 600.0, to_date('02/10/2014','mm/dd/yyyy'), to_date( '02/20/2014','mm/dd/yyyy'), 'Confirmed', 1);

Insert into zk_sales
Values(5, 150, 'Ms. Farell', 400.0, to_date('02/20/2014','mm/dd/yyyy'), to_date( '02/27/2014','mm/dd/yyyy'), 'Confirmed', 4);

Insert into zk_sales
Values(6, 150, 'Ms. Long', 200.0, to_date('03/01/2014','mm/dd/yyyy'), to_date( '03/07/2014','mm/dd/yyyy'), 'Confirmed', 2);
Insert into zk_sales
Values(7, 150, 'Mr. Carpenter', 200.0, to_date('04/15/2014','mm/dd/yyyy'), to_date( '04/21/2014','mm/dd/yyyy'), 'Confirmed', 2);
Insert into zk_sales
Values(8, 140, 'Mr. Khan', 1200.00, to_date('01/14/2014','mm/dd/yyyy'), to_date( '1/24/2014','mm/dd/yyyy'), 'Pending', 2);
Insert into zk_sales
Values(9, 100, 'Mr. Krum', 1000.0, to_date('02/20/2014','mm/dd/yyyy'), to_date( '1/27/2014','mm/dd/yyyy'), 'Pending', 2);
Insert into zk_sales
Values(10, 120, 'Mrs Fern', 2000.0, to_date('05/01/2014','mm/dd/yyyy'), to_date( '05/14/2014','mm/dd/yyyy'), 'Confirmed', 5);

Answer 1

DECLARE
d_id number;
ppl number;
sdt Date;
edt Date;

name varchar2(20);

tot number;
gc number;
cost real;
pr real;
m number;
BEGIN
d_id := :d_id;
ppl := :ppl;
sdt := :sdt;
edt := :edt;
name := :name;
select sum(guest_count) into gc from zk_sales where dest_id = d_id and reservation_start_date=sdt and confirmation ='Confirmed';
tot := gc+ppl;
IF (tot>10) THEN
   dbms_output.put_line('sorry we are sold out for this destination. Try a different date');
ELSE
   select Price_person into pr from zk_destinations where dest_id = d_id;
   cost := ppl * pr;
   select max(sale_id) into m from zk_sales;
   m :=m+1;
   insert into zk_sales values(m, d_id, name, cost, sdt, edt, 'Confirmed', ppl);
END IF;
END;

explanation:

• declaration section consists of all the variables used in the program
• d_id -> destination id, sdt -> start date, edt ->end date, ppl-> no. of people, name->name of customer
• d_id := :d_id   this statement prompts the user to enter d_id value, in the same way all other variables
• select sum(guest_count).................................................. This query returns no. of people going on a particular date
• if the total exceeds 10 exception message is printed
• if not, the values entered by user are inserted into the table zk_sales after calculating the cost
• for sale_id, I have taken Maximum of previous Id's and added 1 to it, instead of that we can use auto increment of the Id which has to be given while creating the table like create table zk_sales(sale_id number auto_increment, .......................................... primary key(sale_id));

note: please ask, if there are any doubts in the solution, thank you.