please prepare my SQL DB and 6 SQL queries
Semester Examinations 01) Please prepare the My SQL Database and SQL Queries with screenshot of the below SQL queries results. Use the relations CUSTOMER, RENTAL, CAR, and VEHICLE_CATEGORY provided below. These relations describe the activities in which customers hire cars from a car hire service firm. The relevant data on customers, cars, and rental activities are stored in a database. Relations CUSTOMER(CustomerID, FirstName, LastName, Address, Phone, LicenseNo) CAR(RegoNo, MakeAndModel, ManufacturedYear, NumOfSeats, CategoryID) Foreign Key (CategoryID) References Vehicle_Category VEHICLE_CATEGORY(CategoryID, CategoryName, DailyHireRate) RENTAL(RentalID, CustomerID, RegoNo, StartDate, EndDate, Cost) Foreign Key (CustomerID) References Customer Foreign Key (RegoNo) References Car Note: The following E-R diagram may assist you to understand the relations as described as above. CUSTOMERCustomerIDFirstNameLastNameAddressPhoneLicenseNoCARRegoNoMake&ModelNumOfSeatsManufacturedYearVEHICLE_CATEGORYCategoryIDCategoryNameDaily hire rateRENTALRentalIDStartDateEndDate[Cost]MakeRentBelong tod Tables CUSTOMER CustomerID FirstName LastName Address Phone LicenseNo 1 David Hacker 101 Yammba road, Rockhampton 0749008877 089 777 123 2 Tony Morrison 98 South street, Melbourne 0490787772 088777555 3 Issac Newton 90 Heaven road, Sydney 0299001122 099 776 123 4 James Farrell 101 St Lucia Garden, Brisbane 0733099000 090 566 777 5 David Land 345 Illinois road, Brisbane 0739000554 456 000 999 6 Peter Garry 201 South port road, Gold Coast 0745676766 234 090 767 RENTAL RentalID CustomerID RegoNo StartDate EndDate Cost 1 4 NAK455 1/07/2019 3/07/2019 $105.00 2 4 QWA321 5/07/2019 7/07/2019 $180.00 3 1 QWA321 2/06/2019 5/06/2019 $240.00 4 2 LLP677 15/06/2019 21/06/2019 $280.00 5 3 SUN909 15/07/2019 18/07/2019 $140.00 6 3 NAK455 2/08/2019 5/08/2019 $140.00 CAR RegoNo MakeAndModel NumOfSeats ManufacturedYear CategoryID ABC455 Toyota Camry 5 1 LLP677 Toyota Hilux 4 2 NAK455 Toyota Corolla 4 2017 1 PAK561 Nissan Navara 5 2017 3 QWA321 VW Caravelle 9 2017 3 SOU320 GM Cardillac 7 2011 4 SUN909 VW Passat 5 2000 1 QLD101 Volvo XC60 5 2017 5 VEHICLE_CATEGORY CategoryID CategoryName DailyHireRate 1 sedan $35.00 2 ute $40.00 3 minivan $60.00 4 limos $450.00 5 suv $55.00 Note that: · You are asked to provide a general solution to each request. If the database contents change, each of your queries should continue to answer the information requested correctly. · Simple queries are preferred; if your queries are unnecessarily complex you may lose marks. · For the given sample data, your queries should be able to generate the same data and column names as shown in the result table for each request. · You are not required to sort the results in any order unless requested. · State any assumption that you make to clarify your understanding of the information request. Question 1 (2 marks) List the details of cars that are Toyota sedans. The details include registration number, make and model, the number of seats, manufactured year, category ID, and category name. RegoNo MakeAndModel NumOfSeats ManufacturedYear CategoryID CategoryName ABC455 Toyota Camry 5 1 sedan NAK455 Toyota Corolla 4 2017 1 sedan Question 2 (2 marks) Which cars have never been rented out? List the details that include the car’s registration number, make and model, number of seats, manufactured year, category as well as the daily hire rate. RegoNo MakeAndModel NumOfSeats ManufacturedYear Category DailyHireRate ABC455 Toyota Camry 5 sedan $35.00 PAK561 Nissan Navara 8 2017 minivan $60.00 QLD101 Volvo XC60 5 2018 suv $55.00 SOU320 GM Cardillac 7 2011 limos $450.00 Question 3 (2 marks) Which cars have been rented out more than once? Show the car’s registration number, make and model, manufactured year, and the number of rentals. RegoNo MakeAndModel ManufacturedYear NumOfRental NAK455 Toyota Corolla 2017 2 QWA321 VW Caravelle 2017 2 Question 4 (2 marks) Find out all rental activities that have the costs higher than the average cost. This includes the customer names, car registration numbers, make and models, the start hire dates, end hire dates and the costs. FirstName LastName RegoNo MakeAndModel StartDate EndDate Cost David Hacker QWA321 VW Caravelle 02/06/2019 05/06/2019 $240.00 Tony Morrison LLP677 Toyota Hilux 15/06/2019 21/06/2019 $280.00 Question 5 (2 marks) How many times of rental activity were from each customer? Show the customer name, phone, and the number of rentals. Order the list so that the customers who rented the most appear first. FirstName LastName Phone NumbOfRental James Farrell 0733099000 2 Issac Newton 0299001122 2 Tony Morrison 0490787772 1 David Hacker 0749008877 1 Question 6 (2 marks) Define a stored procedure with a parameter that represents the category name of a car. When the stored procedure is called, the value of parameter is passed to the procedure. The procedure will display the details of all cars with the value of parameter passed. For example, if the value of category name is ‘sedan’, when the stored procedure is executed, it will display the following result: RegoNo MakeAndModel NumOfSeats ManufacturedYear CategoryName DailyHireRate ABC455 Toyota Camry 5 sedan $35.00 NAK455 Toyota Corolla 4 2017 sedan $35.00 SUN909 VW Passat 5 2000 sedan $35.00 4