INFO90002 S1 2022Assignment 2 - SQLDue: see LMSSubmission: Via the Canvas LMSWeighting: 10% of your total assessment (Graded out of 200 marks)WesternVic Car RentalsWesternVic Car Rentals is a medium size car rental company that currently has 5 depots in theWestern Suburbs of Melbourne and Western Victoria. It rents out cars for short periods (up to 6weeks). Customers can hire a vehicle from any depot and if necessary vehicles are transferredbetween depots. Rental price per day depends on the car category and the season (low, shoulder orhigh). The company keeps track of vehicles, their transfers, their rentals and their maintenance. Allstaff members are associated with a specific depot where they either perform admin duties orconduct maintenance of vehicles.Figure 1. WesternVic Car Rental Data ModelINFO90002Assignment 2 s2 2022 The University of Melbourne v1 2Instructions1. Rename all tables to have the last 4 digits in table names the same as the last 4 digits of yourstudent ID Download the file named CarRentals_9999.sql from the LMS. Open this file in a text editor, e.g. in MySQL Workbench, Notepad, Notepad++ or some otherText editor Change all occurrences of 9999 to the last 4 digits of your student ID (one way to do this isto perform a find and replace). For example, if your student ID is 12349876, your tables willget renamed as car9876, transfer9876, staff9876, etc.Note, if renaming is not done, you cannot get full marks, a heavy penalty of 50% will beapplied.2. Run the script on the engineering server (and / or on your local MySQL server). This will createthe car rentals database with all required tables and populate them with data.3. Write the following SQL statement and execute it.SELECT '123459999' as StuID, depot9999.*FROM depot9999(Note that in the above statement 123459999 should be replaced with your Student IDand both occurrences of 9999 would be last 4 digits of your student ID, as in step 1 above)Notice how each row contains your student id and all attributes from the depot table.You are expected to include your student ID in all queriesIt is expected that your script will produce correct results your code meets standards of quality as discussed in lecturesWrite a single SQL statement to answer the following questions. Do not use inline views / schema onread, views unless explicitly instructed to do so. Views, inline views and schema on read for Q1-Q10will earn 0 marks.If your result set is less than 10 rows, show ALL results. If it is longer, show at least 10 rows (a coupleof rows extra is not a problem). Specify how many results were returned in red font under thescreenshot.INFO90002Assignment 2 s2 ? 2022 The University of Melbourne v1 3Questions1. List all car categories and vehicles within each category. Your list should show categorycode, category description, registration, make, model, and depot name. List the resultin alphabetical order of category code, then by depot name, then make and model.(15 marks)2. How many rentals have been booked in each car category in 2021? The results shoulddisplay category code, category description and number of rentals sorted by categorycode.(15 marks)3. List the cars that had repairs of their glovebox. The results should display the carregistration, make and model, repair dates in and out, job description, staff membername who performed the repair. Order results by repair date in.(15 marks)4. List the registrations, makes and models of the cars that had more than 5 repair ormaintenance jobs. List results from highest to lowest number of jobs.(10 marks)5. List rental price per day for each car category in each season of whatever the currentyear happens to be (e.g. this year it is 2022). The results should display category code,category description, season description, season start date and rental price. Order theresult by category code, then season start date. The query should be usable in thefuture years.(20 marks)6. For category A vehicles, produce a list showing make, model and rental details (i.e. allcolumns from the rental table) for the whatever the previous year happen to be (i.e.currently it is 2021). Sort the results by registration number. Your query needs to beusable in the future years.Rental year to be determined from the first date of rental (i.e. collection date). Even if therental starts in one year (e.g. 2021) and ends in the following year, we consider only the yearwhen rental period started.(15 marks)7. For category C vehicles, produce a list showing make, model and rental details (i.e. allcolumns from the rental table) and rental price per day for the previous 2 years (i.e.2020 and 2021). Rename the price column as ‘Price per Day’. Sort the results by makeand model. Your query needs to be usable in the future years.Price per Day is determined checking for collection date being within a particularseason. Even if some rental dates are within one season and other days in another, forsimplification we assume that the collection date price applies to all rental dates.(25 marks)INFO90002Assignment 2 s2 ? 2022 The University of Melbourne v1 48. For categories D and E vehicles, produce a report for the previous year (i.e. 2021). Thereport should show category code, make, model and rental details (i.e. all columnsfrom the rental table), as well as rental price per day, number of rental days andcalculated cost of rental. Order the results by category code, make and model. Yourquery needs to be usable in the future years.Note, rental per day is charged for 24 hours. For simplification, we do not deal withcollection time and return time. So if a car is rented on 1/08/2022 and returned on2/08/2022, it’s 1 rental day; if a car is rented on 1/08/2022 and returned on3/08/2022, it’s 2 rental days.(30 marks)9. Count all transfers between depots for each car category. Your results need to showcategory code and category description and number of transfers. Order the results bycategory code.(15 marks)10. List all customers NOT from Victoria who rented a car. The results need to showclient’s first and last name and their postcode and be sorted first in the descendingorder of postcodes and then alphabetical order clients last names. Each customershould appear on the list only once.Note, Victorian postcodes start with 3.(15 marks)11.a. Write the SQL DDL to create a view that lists the depot id and depot name,regNo, make, model, prodYear, collection date and return date for cars that wererented in the current year (i.e. 2022). This view must be useful in the futureyears.You do not need to include StuIDYou need to provide 2 screenshots– the list of tables and views from the left pane of Workbench showing your createdview and– the results of running SELECT from your View.(15 marks)b. Using the View you created in Task 11a, list all depots (id and names) and theiraverage number of rental days per month in the current year (i.e. 2022). Theresults should be displayed as DepotID, depot name, Month, average number ofdays. The average number should be an integer (no fractional part). Order theresults by Depot ID and month number. You do not need to include months withno rentals.Note, if collection and return dates are in different months, work with the month of thecollection date.You must have your student ID in task b.(10 marks)INFO90002Assignment 2 s2 ? 2022 The University of Melbourne v1 5Submission Details:Submit a single PDF showing your answers to all questionsSpecify your student name and ID at the top of your answer document.Formatting requirements for your submissionFor each question, present an answer in the following format: Show the question number and question in black text. Show your answer (the SQL statement) in blue text (DO NOT use a screen shot) Show a screenshot of the result from Workbench. If your result set is less than 10rows, show ALL results. If it is longer, show at least 10 rows (few rows extra is allowed). Show how many rows were actually returned, in red text. Remember to include your student ID (the only exception is q.11a). Show each query on a separate page. You must not use in-line views, schema on read, views for questions unless explicitlyinstructed to do so (Q11 only).Example:Q.XX List all Mazda vehicles the company has. The output should show regNo, make, model,production year.SELECT '123459999' as StuID, regNo, Make, Model, ProdYearFROM vehicle9999WHERE make="Toyota";2 Rows returnedIMPORTANT: ATTEMPT EVERY QUESTION!INFO90002Assignment 2 s2 ? 2022 The University of Melbourne v1 6APPENDIX A. WesternVic Car Rental Business RulesClientsClients can be in WesternVic Car Rental system but have never rented a car.VehiclesWesternVic Car Rental is a budget type company so their vehicles are not very modern, some arequite old.StaffThere are admin staff and staff involved in vehicle maintenanceCar categoryType of car, e.g. compact, family, 4WD, etc.SeasonType of tourist season (high, shoulder, low)Season DatesShows start data and end date for each season typeSeason PricesSpecifies the price for each car category during specific season dates range.INFO90002A2 S2^2021 INFO90002 ? The University of Melbourne 7APPENDIX B. Sample Marking SchemaIn this sample marking rubric Questions 1 and 2 are referring to a different case study. They each are worth 10 marks out of 200. Please attempt everyquestion. The approach is as important as the result.