Assignments Back to Syllabus
Home Work 2 Due before Feb 17
then less than $20 untill you reach less than $100. Insert the results into a
table numbers_table with two columns rate_per_day NUMBER(3) and
num_of_cars NUMBER(5).
2. Write a procedure , del_cust(x number), to delete all customers from
the customers table who have not booked a car for x number of days.
3. Create a table maxcosts with two columns, car_group_name, max_cost,
Write a PL/SQL procedure using a cursor which inserts the appropriate data from the cars table into
maxcosts table. Note: group by car_group_name.
Due before Feb 24
You need to extract details of bookings which have unpaid amount due of $200 or more
and whose rental start date was 90 days or more ago, set the severity code to 1. If the amount due is more than $500 or start date is more tham 180 days ago, set the severity code to 2. If the rental start date is 365 or more days ago, set the severity code to 3. Write the details to a table oldbills which has four columns: customer_name, date_rent_start, amount_due and severity.
You need to extract details of the average duration and average cost of bookings for each car. These should be written to an exiting table called averages which has four columns: registration, model_name, ave_period, and ave_amt.
Write PL/SQL program to analyse the bookings and cars tables to do this. Some cars will be new and will not have any bookings yet; use an exception handler to set both averages to zero in this case.
Write a trigger that flags customers that that have attained one of the severity levels of Homework 3. Print the customer name and severity if such a customer tries to book a car. .
Back to Syllabus