For this assignment you must use SI schema. You are to create five queries as follows.

1. Print the total dollar value, the average dollar value of service visits (parts and labour costs) and the number of those visits for Jaguar and Mercedes car makes that were sold between January 2018 and November 2018 inclusive (5 marks).
2. SI schema contains customers that have bought one or more vehicles. They must be classified using the following criteria:
   1. Customers that have bought only one car (Silver level)
   2. Customers that have bought two cars (Gold level)
   3. Customers that have bought more than two cars (Platinum level)
   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 Acura car make(s). (5 marks)
3. Using SET operations, display a list of customers that both bought a car from SI and serviced that car at SI. (Hint: consider the serial number) (5 marks)
4. Using a single SELECT statement, show a list of total money spent on the labour cost of servicing Land Rover cars. Show the subtotals for each model as well as each make (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 Land Rover as well as the cumulative sale price totals. (5 marks)

Specialty Imports E-R Diagram NOT NULL 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) 7 NOT NULL custhphone CHAR(13) NOT NULL custbphone CHAR(13) NOT NULL carserial CHAR(8) SALEINV od saleinvno CHAR(6) saledate DATE 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) discount NUMBER(8.2) commission NUMBER(8.2) ) carsaleprice NUMBER(9.2) ( PROSPECT custame VARCHAR2(20) 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) custname VARCHAR2(20) NOT NULL SERVINV servinvno CHAR(5) 5 servdate DATE NOT NULL custname VARCHAR2(20) NOT NULL carserial CHAR(8) NOT NULL partscost NUMBER(7.2) laborcost NUMBER(7.2) taxrate NUMBER(4.2) DEFAULT 13.0 carmake VARCHAR2(10) NOT NULL carmodel VARCHAR2(10) caryear CHAR (4) carcolour VARCHAR2(12) ) cartrim VARCHAR2(16) optioncode CHAR2(4) Text TRADEINV SERVWORK servinyno CHAR(5) 5 workdesc VARCHAR2(80) PURCHINV purchinyno CHAR(6) purchfrom VARCHAR2(15) saleinvno CHAR(6) carserial CHAR(8) tradeallow NUMBER(9,2) EMPLOYEE empname VARCHAR2(20) startdate DATE NOT NULL commissionrate NUMBER(3.1) title VARCHAR2(26) manager VARCHAR2(20) ( purchdate DATE + BASEOPTION optioncode CHAR(4) carserial CHAR(8) INVOPTION OPTIONS optioncode CHAR(4) optiondesc VARCHAR2(30) optioncost NUMBER(7.2) saleinvno CHAR(6) oc optioncode CHAR(4) optionprice NUMBER(72) optionlistprice NUMBER(72)