7:06 Files = PART III: SQL Below is the SQL script to create a database of three tables: Client, Order, and Delivery. create table client idClient varchar2(5) constraint cl primary key, FirstNameClient varchar2(15), LastNameClient varchar2(15). CityClient varchar2(15), phoneClient Number(10), emailClient varchar2(30) ); create table Rental ( idRental varchar2(5) constraint c2 primary key, dateStartRental Date, dateEndRental Date, RentalAmount number(15) constraint c3 check RentalAmount >= 0), IdClient varchar2(5) NOT NULL, IdCar varchar2(5) NOT NULL, CONSTRAINT c4 FOREIGN KEY (IdClient) REFERENCES Client (idClient), CONSTRAINT CF FOREIGN KEY (IdCar) REFERENCES Car (idCar) ); Create table Car { IdCar varchar2(5) constraint c6 primary key, BrandCar varchar2(25), IdTypeCar varchar2(5) NOT NULL, CONSTRAINT 7 FOREIGN KEY (IdTypeCar) REFERENCES TypeCar (IdTypeCar) } 7:06 Files Create table TypeCar { idTypeCar varchar2(5) constraint c8 primary key, NameTypeCar varchar2(25), RentalPrice Money NOT NULL } = INSERT INTO CLIENT VALUES ('cl'. 'Ahmed'. "Salim', 'Salalah', '95213654', 'ahmed@hil.com'); INSERT INTO CLIENT VALUES ('c2', 'Ali', 'Jaber', 'Muscat', '92277651', 'ali@hil.com'); INSERT INTO CAR VALUES ('carl', 'Camry', 'TI'); INSERT INTO CAR VALUES ('car2', 'Yaris', "T2'); INSERT INTO TYPECAR VALUES ('TI', "Intermediate', '20'); INSERT INTO TYPECAR VALUES (T2', 'Small', '12'); INSERT INTO RENTAL VALUES ('RI', '1-1- 2005', '11-1-2005', 200, 'cl', 'car!'); INSERT INTO RENTAL VALUES ('R2', '1-2- 2005', '11-2-2005', 120, 'cl', 'car2'); INSERT INTO RENTAL VALUES ('R3', '1-3- 2005', '11-3-2005', 200, 'c2', 'car!'); INSERT INTO RENTAL VALUES ('R4', '1-4- 2005', '8-4-2005', 140, 'c2', carl'); INSERT INTO RENTAL VALUES ('R5', '1-5- 2005', '5-5-2005', 48, 'c2', 'car2'); 1) Draw the CDM which corresponds to the above SQL script. 7:06 Files 2) Answer the following questions in SQL: a/ Give all information about all rentals ordered by Rental start date (latest on top). b/ Give idClient of clients who live in Muscat or Sur. = c/Give number of clients (how many clients are stored in the database) whose first name starts with 'A'. d/ Give the idRental of the rental(s) made by idClient 'c1'. el Give the idRental of rental(s) made by client 'Ahmed Salim'. 7:06 Files f/ Give for each idClient the number of rentals he made. g/ Give the number of cars of each car type (identified by Name TypeCar). h/ Give for each idClient, the total of rental amounts of the rentals he made), but only if this total is more than 500. i/ Give the idRental of the rental which has the highest rental amount. 7:06 Files j/ Delete from the database all rentals made by client "c1". k/ As the government has imposed new taxes, we want to increase by 15% the Rental price of car types. 1 Give list of clients (identified by id Client) who never made a rental. = m/ We want to remove the email from table client. Give the corresponding SQL query client. Give the corresponding SQL query n/ In the database, we have actually only clients from Muscat or Salalah. We have 2 offices (one in Muscat and one in Salalah). We want to allow employees in Muscat office to see clients who live in Muscat only. How could the database admin implement this? Give the SQL query which does this