Question

Using the sample code included at the end of the document to create the tables, Please write an anonymous PL/SQL program the following problems.

Problem 1. Print out estimated charge for rental ID 1 if the customer returns the tool in time. The charge is computed by the price in the price_tool table * number of units the customer plans to rent. E.g., if a customer rents a tool hourly for 5 hours, and the hourly rate for the tool is $6, the estimated charge should be $30. [30 points]

Problem 2. [30 points] Print out name of tools rented by Susan and their due time.

--------- Sample code

drop table rental cascade constraints;

drop table tool_price cascade constraints;

drop table tool cascade constraints;

drop table category cascade constraints;

drop table cust cascade constraints;

drop table time_unit cascade constraints;

create table cust(

cid int, -- customer id

cname varchar(50), --- customer name

cphone varchar(10), --- customer phone

cemail varchar(30), --- customer email

primary key(cid)

);

insert into cust values(1,'John','1234567888','[email protected]');

insert into cust values(2,'Susan','1235555555','[email protected]');

insert into cust values(3,'David','1237777777','[email protected]');

create table category(

ctid int, --- category id

ctname varchar(30), --- category name

parent int, --- parent category id

primary key(ctid),

foreign key (parent) references category

);

insert into category values(1,'mower',null);

insert into category values(2,'electric mower',1);

insert into category values(3,'gasoline mower',1);

insert into category values(4,'carpet cleaner',null);

create table tool

(tid int, --- tool id

tname varchar(50), -- tool name

ctid int, --- category id

quantity int,

primary key (tid),

foreign key (ctid) references category

);

insert into tool values(1,'21 inch electric mower',2,2);

insert into tool values(2,'30 inch large gasoline mower',3,2);

insert into tool values(3,'small carpet cleaner',4,2);

insert into tool values(4,'large carpet cleaner',4,2);

create table time_unit

(tuid int, --- time unit id

len interval day to second, --- length of unit, can be 1 hour, 1 day, etc.

min_len int, --- minimal number of units

primary key (tuid)

);

--- hourly minimal four hours.

insert into time_unit values(1, interval '1' hour, 4);

-- hourly minimal 1 day

insert into time_unit values(2, interval '1' day, 1);

--- weekly

insert into time_unit values(3, interval '7' day, 1);

create table tool_price

(tid int, --- too id

tuid int, --- time unit id

price number,

primary key(tid,tuid),

foreign key(tid) references tool,

foreign key(tuid) references time_unit

);

--- mower, $20 per 4 hours. $30 per day

insert into tool_price values(1,1,5.00);

insert into tool_price values(1,2,30);

insert into tool_price values(1,3,120);

insert into tool_price values(2,1,7.00);

insert into tool_price values(2,2,40);

insert into tool_price values(2,3,160);

insert into tool_price values(3,1,6.00);

insert into tool_price values(3,2,32);

insert into tool_price values(3,3,125);

insert into tool_price values(4,1,7.00);

insert into tool_price values(4,2,40);

insert into tool_price values(4,3,160);

create table rental

(

rid int, --- rental id

cid int, --- customer id

tid int, --- tool id

tuid int, --- time unit id

num_unit int, --- number of units, if unit = 1 hour, num_unit = 5 means 5 hours.

start_time timestamp, -- rental start time

end_time timestamp, --- suppose rental end_time

return_time timestamp,--- actual return time

credit_card varchar(20),

total number, --- total charge

primary key (rid),

foreign key(cid) references cust,

foreign key(tid) references tool,

foreign key(tuid) references time_unit

);

-- John rented a mower for 4 hours,

insert into rental values(1,1,1,1,4,timestamp '2019-08-01 10:00:00.00',null,null,'123456789',null);

-- susan rented a small carpet cleaner for one day

insert into rental values(2,2,3,2,1,timestamp '2019-08-11 10:00:00.00',null,null,'123456789',null);

--susan also rented a small mower for 5 hours, before 8 am case

insert into rental values(3,2,1,1,5,timestamp '2019-08-12 21:00:00.00',null,null,'123456789',null);

--david also rented a small carpet cleaner for 4 hours, after 10 pm case

insert into rental values(4,3,3,1,4,timestamp '2019-08-13 19:00:00.00',null,null,'12222828828',null);

commit;

Answer 1

PL/SQL is a block structured language to execute procedural statements along with SQL. It consists of 3 blocks -

1. DECLARE - declare variables.
2. BEGIN - executable code.
3. EXCEPTIONS - for exception handling.

Rest of the information is in the code itself.

1)

Program:

-- declare all the required variables
DECLARE   
rental_id INTEGER; -- rental id
tool_id INTEGER; -- tool id
time_unit_id INTEGER; -- time unit id
num_units INTEGER; -- number of units
cost_per_hour INTEGER; -- price of the tool per hour
estimated_cost INTEGER; -- estimated charge
-- executable statements
BEGIN
rental_id := 1; -- rental is 1
  
-- get tool id, tool unit id and number of units
-- from rental table for rental id = 1
select tid, tuid, num_unit
into tool_id, time_unit_id, num_units
from rental
where rid = rental_id;
  
-- get price corresponding to the tool id
-- and tool unit id extracted from the rental table
-- using the tool_price table
select price
into cost_per_hour
from tool_price
where tid = tool_id and tuid = time_unit_id;
  
-- estimated charge = number of units * cost per unit
estimated_cost := num_units * cost_per_hour;
  
-- printing of calculated estimate charge
dbms_output.put_line('Estimated charge for rental Id 1: '||num_units||' * $'||cost_per_hour||' = $'||estimated_cost);
  
END; -- end of program

Output:

Note - first line of output is IDE based and just shows that code was executed without any error.

Program screenshot:

2) Cursor is used to iterate multiple rows of a table.

Program:

DECLARE   
-- cursor to iterate rental table
CURSOR rental_table is
SELECT cid, tid, tuid, num_unit, start_time FROM rental;
  
cust_name VARCHAR(50); -- customer name
cust_id INTEGER; -- customer id corresponding to name
  
cids INTEGER; -- customer id for table rental
num_units INTEGER; -- number of units
start_time timestamp; -- start timestamp
end_time timestamp; -- end timestamp, initially null
tool_id INTEGER; -- tool id
time_unit_id INTEGER; -- time unit id
tool_name VARCHAR(50); -- tool name
time_length interval day to second; -- time length
  
total_time interval day to second; -- total time

-- executable statements
BEGIN
cust_name := 'Susan'; -- customer name is Susan
  
-- get customer id from cust table for customer name = 'Susan'
select cid
into cust_id
from cust
where cname = cust_name;
  
-- print statement
dbms_output.put_line('Tools and their due dates rented by Susan: ');
  
-- chr(9) used for tab
dbms_output.put_line('Tool name' || chr(9) || chr(9) || chr(9) || 'Due date');
  
-- iterate for all rows of rental
OPEN rental_table; -- open cursor
LOOP
FETCH rental_table into cids, tool_id, time_unit_id, num_units, start_time;
EXIT WHEN rental_table%notfound; -- end of rental table
  
-- calculations need to be done only for customer id corresponding to Susan
if (cids = cust_id) then
  
-- get tool name from tool table using tool id
select tname
into tool_name
from tool
where tid = tool_id;
  
-- get length of interval for a time id
select len
into time_length
from time_unit
where tuid = time_unit_id;
  
-- calculate due date by adding total interval to start date
total_time := time_length * num_units;
end_time := start_time + total_time;
  
-- required output
dbms_output.put_line(tool_name || chr(9) || end_time);
  
end if;
  
END LOOP;
CLOSE rental_table; -- close cursor
  
END; -- end of program

Output:

Program screenshot: