Assignment 1 This assignment will test your knowledge and skills acquired during the first three weeks of this course including Database Designing and Implementation course review. Assignment Requirements For this assignment you must use the SI schema. Using only syntax and techniques we have covered in class or on SLATE, you are to create five (5) Oracle SQL queries, one for each numbered point below. Print the total dollar and the average dollar value of service visits (parts plus labour costs) and the number of those visits for each of the car makes Mercedes and Jaguar which were sold from January 2017 to the end of 2018. Sort the output by highest number of visits to lowest. (5 marks) SI schema contains customers that have bought one or more vehicles. They must be classified using the following criteria: Customers that have bought only one car (Emerald class) Customers that have bought two cars (Ruby class) Customers that have bought more than two cars (Diamond class) Using a single SELECT statement, display a list of customer names and what type of buyer they are for all those customers that have bought car make Jaguar. (5 marks) Using SET operations, display a list of customers that are interested in a car (prospect table) of the same make which they already own. (5 marks) Using a single SELECT statement, show a list of total money spent on the labour cost of servicing Acura and Honda cars. Show the subtotals for each model as well as each make. For this query: do NOT remove any rows from your output. (5 marks) Write a query using analytic functions that will show the serial number, the model and the sale price of each model of make Mercedes as well as the cumulative sale price totals. (5 marks)

This assignment will test your knowledge and skills acquired during the first three weeks of this course including Database Designing and Implementation course review. Assignment Requirements For this assignment you must use the SI schema. Using only syntax and techniques we have covered in class or on SLATE, you are to create five (5) Oracle SQL queries, one for each numbered point below. 1. Print the total dollar and the average dollar value of service visits (parts plus labour costs) and the number of those visits for each of the car makes Mercedes and Jaguar which were sold from January 2017 to the end of 2018. Sort the output by highest number of visits to lowest. (5 marks) 2. SI schema contains customers that have bought one or more vehicles. They must be classified using the following criteria: a. Customers that have bought only one car (Emerald class) b. Customers that have bought two cars (Ruby class) c. Customers that have bought more than two cars (Diamond class) Using a single SELECT statement, display a list of customer names and what type of buyer they are for all those customers that have bought car make Jaguar. (5 marks) 3. Using SET operations, display a list of customers that are interested in a car (prospect table) of the same make which they already own. (5 marks) 4. Using a single SELECT statement, show a list of total money spent on the labour cost of servicing Acura and Honda cars. Show the subtotals for each model as well as each make. For this query: do NOT remove any rows from your output. (5 marks) 5. Write a query using analytic functions that will show the serial number, the model and the sale price of each model of make Mercedes as well as the cumulative sale price totals