Can you please do it as soon as possible:

Thanks

Objectives:

Create tables

Design and implement simple PL/SQL functions

Use PL/SQL function in SQL query

Small database:

This is a relatively simplified database to store data on clients, cars, and rentals. The RESERVATION table includes the data on past rentals and future reservations. The cars can be rented only once a day. The START_DATE and END_DATE include time, but rentals are calculated in full days. This means that a car rented on 2017-10-10 23:30:00 and returned on 2017-10-11 01:00:00 has been rented for two day.

This is not the best designJ but it is gives us a chance to exercise some PL/SQL and SQL

Step 1:

Create three tables and add data. V_ID, R_ID, C_ID are numbers and primary keys. Make sure that you specify the entity integrity and referential integrity constraints.

Problem 1 Age (function)

The manager is furious: some cars have been rented to the clients, who are not 21 years old... and to some who are more than 80 years old. The vehicle reservation system should check the age of the person renting the car. Your task is to write a function to calculate age (in whole years) based on DOB. The age is calculated as of today (SYSDATE on the server).The function can be tested using SQL SELECT statement, for example,

SELECT CALC_AGE(actual parameter) FROM DUAL;

List all clients (C_ID, F_NAME, L_NAME, AGE, issue) who are younger than 21and who are older than 80 years. Use one select statement and list the clients who have age issues. If they are too young, specify too young. If they are too old specify too old. Sort by the client id and use your new function.

Submit:

Create statement for the function.

Select statement to list clients

Results from the query (2).

Problem 2 Car Usage (function)

The manager wants to check the past utilization(number of days rented out) of cars in current year (SYSDATE on the server). Your task is to write a function to return total number of days rented for a specified car (the parameter is V_ID). Include only the past rentals (do not include reservations or rentals not ended as of SYSDATE). The function has one formal parameter: V_ID (number).

List car makes (V_MAKE) and their utilization (using your function). Sort the results from the most popular makes to the least popular makes. Include only the makes with some rentals.

Submit:

Create statement for the function.

Select statement to list all cars and their utilization (using your function).

Results from the query (2).

RESERVATION RID START DATE END DATE C ID V ID TOTAL_CosT CLIENT C ID F NAME L_NAME DOB CITY GENDER VEHICLE V ID VIN V MAKE COST_PER_DAY RESERVATION RID START DATE END DATE C ID V ID TOTAL_CosT CLIENT C ID F NAME L_NAME DOB CITY GENDER VEHICLE V ID VIN V MAKE COST_PER_DAY