Subject Code :- INFO90002
Title :- Internet Technologies
Assessment Type :- Assignment
Weighting :- 10% of your total assessment
INFO90002 Internet Technologies Assignment
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 Customers can hire a vehicle from any depot and if necessary vehicles are transferred between depots. 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.
Instructions :-
1. Re name all tables to have the last 4 digits 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 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.
2. Run the script on the engineering server and or on your local My SQL server. This will create the car rentals data base 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 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
Questions
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-Q9 will earn 0 marks.
1.List all vehicles the company owns. Only show registration make model depot name and depot email. List the result in alphabetical order of depot name.
2.List all depot names that have 5 or more vehicles.
3.List the number of rentals for each month of each year. Order the result by month then year. Base your query on the car collection date.
INFO90002 Internet Technologies Assignment
4. List all clients for whom rental collection date is in one month and the return date is in a different month e.g. vehicle collection happened in March 2021 and return was in April 2021. Be sure to eliminate duplicates from your result set.
5.List the registrations makes and models of the cars that have never been transferred to another depot.
6.List client ID and Client Full Name for those who rented a car more than twice in the year 2020.
7. List all vehicles and their average number of rental days per year The results should be displayed as Year Reg No make model average number of days.
8. Count the number of cars rented in each quarter of each year Your output should show rental year quarter number of cars rented.
Quarter 1 contains months Jan Feb March quarter 2 – April May June etc. Note year 2022 has records for quarter 1 only.
9. List all cars that had maintenance work that included tyres Your out put should show Reg No make model job description Order the output by make and model.
INFO90002 Internet Technologies Assignment
10. List all details of vehicles that are older than 7 years and have 5 seats Show depot name not number. Order results by make and model.
11. a. Write the SQL DDL to create a view that lists the staff ID full name mobile phone number and depot name they work in for all staff who are NOT involved in maintenance i.e. admin staff
b. Using the View you created in Task 11 a write a query to identify staff work phone number not their mobile List staff member’s full name depot name and depot phone number.
ORDER This INFO90002 Internet Technologies Assignment NOW And Get Instant Discount
