Print the total amount, the average dollar value of service visits (parts and labour costs) and the number of those visits for Acura, Mercedes and Jaguar car makes that are sold between September 2015 and December 2018 inclusive (5 marks). SI schema contains customers that have bought one or more vehicles. They can be classified using the following criteria: Customers that have bought only one car (one-time buyer) Customer that have bought two cars (two-time buyer) Customers that have bought more than two cars (frequent buyer) Using a SINGLE SELECT statement, display a list of customers with their names and what type of buyer they are for all those customers that have bought Mercedes car makes. (5 marks) Using SET operations, display a list of customers that are interested in a car (prospect table) of the same make and model which they already own. (5 marks) Show a list of total amount of money spent on the labour cost of servicing Toyota cars. Show the subtotals for each model (5 marks) Write a query using analytic functions that will show the serial number, the price of each JAGUAR car as well as the cumulative sale price totals. (5 marks)

Specialty Imports E-R Diagram CAR CUSTOMER custname VARCHAR2(20) custstreet VARCHAR2(20) NOT NULL custcity VARCHAR2(20) NOT NULL custprovince CHAR(2) NOT NULL DEFAULT ON custpostal CHAR(7) NOT NULL custhphone CHAR(13) ) NOT NULL custophone CHAR(13) NOT NULL carserial CHAR(8) custname VARCHAR2(20) SALEINV saleinvno CHAR(6) saledate DATE NOT NULL salesman VARCHAR2(20) NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL insfire CHAR(1) inscollision CHAR(1) insliability CHAR(1) insproperty CHAR(1) taxrate NUMBER(4.2) DEFAULT 13.0 licfee NUMBER(6,2) carmake VARCHAR2(10) NOT NULL carmodel VARCHAR2(10) NOT NULL ) caryear CHAR(4) NOT NULL extcolour VARCHAR2(12) NOT NULL cartrim VARCHAR2(16) NOT NULL enginetype VARCHAR2(10) NOT NULL enginetype VARCHAR2(10) NOT NULL purchinvno CHAR(6) purchcost NUMBER(9.2) freightcost NUMBER(92) carlistprice NUMBER(9.2) discount NUMBER(82) PROSPECT custname VARCHAR2(20) NOT NULL carmake VARCHAR2(10) NOT NULL carmodel VARCHAR2(10) caryear CHAR(4) carcolour VARCHAR2(12) ( cartrim VARCHAR2(16) SERVINV servinvno CHAR(5) servdate DATE NOT NULL custname VARCHAR2(20) NOT NULL ( carserial CHAR(8) NOT NULL partscost NUMBER(72) laborcost NUMBER(7.2) taxrate NUMBER(4.2) DEFAULT 13.0 commission NUMBER(8,2) carsaleprice NUMBER(9.2) optioncode CHAR2(4) Text PURCHINV TRADEINV EMPLOYEE SERVWORK servinyno CHAR(5) workdesc VARCHAR2(80) saleinvno CHAR(6) carserial CHAR(8) purchinvno CHAR(6) purchfrom VARCHAR2(15) purchdate DATE tradeallow NUMBER(9.2) empname VARCHAR2(20) startdate DATE NOT NULL commissionrate NUMBER(3.1) title VARCHAR2(26) manager VARCHAR2(20) ++ OPTIONS optioncode CHAR(4) optiondesc VARCHAR2(30) optioncost NUMBER(7.2) optionlistprice NUMBER(7.2) BASEOPTION optioncode CHAR(4) carserial CHAR(8) INVOPTION saleinvno CHAR(6) oc optioncode CHAR(4) optionprice NUMBER(7.2)