代写INFO90002、SQL语言编程代写

日期：2022-09-20 01:25

INFO90002 S1 2022

Assignment 2 - SQL

Due: see LMS

Submission: Via the Canvas LMS

Weighting: 10% of your total assessment (Graded out of 200 marks)

WesternVic Car Rentals

WesternVic Car Rentals is a medium size car rental company that currently has 5 depots in the

Western Suburbs of Melbourne and Western Victoria. It rents out cars for short periods (up to 6

weeks). Customers can hire a vehicle from any depot and if necessary vehicles are transferred

between depots. Rental price per day depends on the car category and the season (low, shoulder or

high). The company keeps track of vehicles, their transfers, their rentals and their maintenance. All

staff members are associated with a specific depot where they either perform admin duties or

conduct maintenance of vehicles.

Figure 1. WesternVic Car Rental Data Model

INFO90002

Assignment 2 s2 2022 The University of Melbourne v1 2

Instructions

1. Rename all tables to have the last 4 digits in table names the same as the last 4 digits of your

student 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 other

Text editor

Change all occurrences of 9999 to the last 4 digits of your student ID (one way to do this is

to perform a find and replace). For example, if your student ID is 12349876, your tables will

get renamed as car9876, transfer9876, staff9876, etc.

Note, if renaming is not done, you cannot get full marks, a heavy penalty of 50% will be

applied.

2. Run the script on the engineering server (and / or on your local MySQL server). This will create

the 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 ID

and 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 queries

It is expected that

your script will produce correct results

your code meets standards of quality as discussed in lectures

Write a single SQL statement to answer the following questions. Do not use inline views / schema on

read, views unless explicitly instructed to do so. Views, inline views and schema on read for Q1-Q10

will 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 couple

of rows extra is not a problem). Specify how many results were returned in red font under the

screenshot.

INFO90002

Assignment 2 s2 ? 2022 The University of Melbourne v1 3

Questions

1. List all car categories and vehicles within each category. Your list should show category

code, category description, registration, make, model, and depot name. List the result

in 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 should

display category code, category description and number of rentals sorted by category

code.

(15 marks)

3. List the cars that had repairs of their glovebox. The results should display the car

registration, make and model, repair dates in and out, job description, staff member

name 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 or

maintenance 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 current

year 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 the

result by category code, then season start date. The query should be usable in the

future years.

(20 marks)

6. For category A vehicles, produce a list showing make, model and rental details (i.e. all

columns 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 be

usable in the future years.

Rental year to be determined from the first date of rental (i.e. collection date). Even if the

rental starts in one year (e.g. 2021) and ends in the following year, we consider only the year

when rental period started.

(15 marks)

7. For category C vehicles, produce a list showing make, model and rental details (i.e. all

columns 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 make

and model. Your query needs to be usable in the future years.

Price per Day is determined checking for collection date being within a particular

season. Even if some rental dates are within one season and other days in another, for

simplification we assume that the collection date price applies to all rental dates.

(25 marks)

INFO90002

Assignment 2 s2 ? 2022 The University of Melbourne v1 4

8. For categories D and E vehicles, produce a report for the previous year (i.e. 2021). The

report should show category code, make, model and rental details (i.e. all columns

from the rental table), as well as rental price per day, number of rental days and

calculated cost of rental. Order the results by category code, make and model. Your

query needs to be usable in the future years.

Note, rental per day is charged for 24 hours. For simplification, we do not deal with

collection time and return time. So if a car is rented on 1/08/2022 and returned on

2/08/2022, it’s 1 rental day; if a car is rented on 1/08/2022 and returned on

3/08/2022, it’s 2 rental days.

(30 marks)

9. Count all transfers between depots for each car category. Your results need to show

category code and category description and number of transfers. Order the results by

category code.

(15 marks)

10. List all customers NOT from Victoria who rented a car. The results need to show

client’s first and last name and their postcode and be sorted first in the descending

order of postcodes and then alphabetical order clients last names. Each customer

should 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 were

rented in the current year (i.e. 2022). This view must be useful in the future

years.

You do not need to include StuID

You need to provide 2 screenshots

– the list of tables and views from the left pane of Workbench showing your created

view 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 their

average number of rental days per month in the current year (i.e. 2022). The

results should be displayed as DepotID, depot name, Month, average number of

days. The average number should be an integer (no fractional part). Order the

results by Depot ID and month number. You do not need to include months with

no rentals.

Note, if collection and return dates are in different months, work with the month of the

collection date.

You must have your student ID in task b.

(10 marks)

INFO90002

Assignment 2 s2 ? 2022 The University of Melbourne v1 5

Submission Details:

Submit a single PDF showing your answers to all questions

Specify your student name and ID at the top of your answer document.

Formatting requirements for your submission

For 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 10

rows, 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 explicitly

instructed 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, ProdYear

FROM vehicle9999

WHERE make="Toyota";

2 Rows returned

IMPORTANT: ATTEMPT EVERY QUESTION!

INFO90002

Assignment 2 s2 ? 2022 The University of Melbourne v1 6

APPENDIX A. WesternVic Car Rental Business Rules

Clients

Clients can be in WesternVic Car Rental system but have never rented a car.

Vehicles

WesternVic Car Rental is a budget type company so their vehicles are not very modern, some are

quite old.

Staff

There are admin staff and staff involved in vehicle maintenance

Car category

Type of car, e.g. compact, family, 4WD, etc.

Season

Type of tourist season (high, shoulder, low)

Season Dates

Shows start data and end date for each season type

Season Prices

Specifies the price for each car category during specific season dates range.

INFO90002

A2 S2^2021 INFO90002 ? The University of Melbourne 7

APPENDIX B. Sample Marking Schema

In 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 every

question. The approach is as important as the result.