The question SQL Oracle Database 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 I started on it but I don't feel like I fully understand how to solve it did I use window frame clause correctly can you please do a step by step solution select c carmake, avg(partscost laborcost) OVER(ORDER BY servdate NULLS LAST RANGE BETWEEN (2015 09 10) PRECEDING and (2018 12 10) FOLLOWING) from SI servinv s, SI car c where s carserial c carserial and (CARMAKE UPPER('Acura') or CARMAKE UPPER('Mercedes') or CARMAKE Upper('Jaguar')) Diagram for tables the database is an Oracle Database I used Oracle SQL Developer to do what I have so far more info customer (custname, custstreet, custcity, custprovince, custpostal, custhphone, custbphone) All customers will be stored in this table Primary key is custname The users will be responsible to ensure that customers' names are unique in the table options (optioncode, optiondesc, optioncost, optionlistprice) All optional equipment for all makes of cars are stored here Options are given once and for all Users should not add or delete any options from the options table optioncode primary key optiondesc the description of the optional equipment optioncost the amount of money the optional equipment cost to Specialty Imports optionlistprice the targeted price of the optional equipment The actual sale price of an optional equipment for a particular sale is in invoption table (optionprice) employee (empname, startdate, commissionrate, title, manager ) This table has a recursive relationship empname primary key startdate the date the employee was hired commissionrate the rate of the commission for an employee The rate is stored as a percentage Example a 2 commission rate is stored as 2 0, or a 2 5 commission rate is stored as 2 5 title the employee's job title manager foreign key to the employee table Manager column refers to empname column i e each manager is an employee and each employee (except for the general manager) has a manager Each manager can manage multiple employees purchinv (purchinvno, purchfrom, purchdate) purchinvno the purchase invoice number of the car It is usually found in the bill of sale that Specialty Imports receives together with the car from the factory or the business the car was bought from If the car is a trade in, Specialty Imports prepares a bill of sale that would have a purchase invoice no and that number will be placed here purchfrom the business or the person the car was purchased from purchdate the date that car was purchased by Specialty Imports car (carserial, custname , carmake, carmodel, caryear, extcolor, cartrim, enginetype, purchinvno , purchcost, freightcost, carlistprice) carserial primary key custname This is the owner of the car If a car is unsold (available for sale), the custname will be NULL i e empty This is our means of determining if a car is available for sale Cars brought in for service will always have an owner's name purchinvno foreign key to purchinv table It is usually found in the bill of sale that Specialty Imports receives together with the car from the factory or the business the car was bought from The same purchinvno may be in multiple cars, as one purchase invoice may comtain multiple cars and it will be null if the car is in the table solely for the purpose of service If the car is a trade in, Specialty Imports prepares a bill of sale that would have a purchase invoice no and that number will be placed here carmake, carmodel and caryear are self explanatory extcolor the exterior color of the car cartrim the interior color purchcost what specialty imports paid to acquire the car for sale purposes This column is placed in car table as opposed to purchinv table, due to the fact that a purchinv (purchinvno) may contain multiple cars Again this column will be null if the car is in the table solely for the purpose of service freightcost what was paid to bring the car over from wherever the car was purchased This is passed on to the customer carlistprice This goes on the windshield Specialty Imports is happy if the car can be sold for this much but it is usually sold for less The actual sale price is the 'net' field in the 'saleinv' table baseoption ( optioncode , carserial ) These are the options put on a car by the manufacturer There is no price field in this table because it is included in the vehicle price Concatenated primary key (carserial, optioncode) for the M N relationship The combination of both columns are a composite primary key carserial is a foreign key to car optioncode is a foreign key to options saleinv (saleinvno, saledate, salesman , custname , carserial , insfire, inscollision, insliability, insproperty, taxrate, licfee, discount, commission, carsaleprice) A new record is inserted each time a car is sold saleinvno primary key saledate is the date the car is sold to the customer salesman employee who sold the car It is a foreign key to employee table custname foreign key to customer table This field cannot be left null We must always know the customer carserial foreign key which identifies the car that is sold insfire, inscollision, insliability and insproperty Used if we assist the customer in acquiring insurance The value can only be either 'N' or 'Y' ( yeso) taxrate the rate at which the tax amount will be calculated Stored as percentage and the default is 13 0 (13 ) licfee entered if we assist the customer in acquiring license plates This is a pass through cost discount negotiated with salesman This is the amount to be deducted from the carsaleprice (see bellow) commission what the sales person is paid for the sale The customer must not see this, which is the reason why it does not show up in the sale invoice form carsaleprice the amount of money Specialty Imports has decided to sell the car for tradeinv ( saleinvno , carserial , tradeallow) Here is the information stored about the cars that are traded in A customer may trade in more than one car and use the trade in allowance toward the purchase of a new vehicle The combination of saleinvno and carserial is a composite primary key saleinvno foreign key to saleinv table carserial foreign key to car table tradeallow amount given to customer for the trade in invoption ( saleinvno , optioncode , optionprice) These are the extra options added to a car at the time of sale The customer must pay extra for these options so there is a saleprice field The combination of saleinvno and optioncode is a composite primary key saleinvno foreign key to saleinv table optioncode foreign key to options table optionprice the sale price of the optional equipment servinv (servinvno, serdate, custname , carserial , partscost, laborcost, taxrate) One record goes into this table each time a car is serviced servinvno primary key servdate date the sevice was performed custname foreign key to customer customer must be known carserial foreign key to car must be known partcost the amount of money the customer will be charged for the cost of the parts used during the service laborcost the amount of money for the labor taxrate the rate at which the tax amount will be calculated Stored as percentage and the default is 13 0 (13 ) servwork ( servinvno , workdesc) Notice that the whole record is a concatenated key A car being serviced would have multiple things done to it servinvno foreign key to servinv workdesc a text field indicated what what done to the car prospect ( custname , carmake, carmodel, caryear, carcolor, cartrim, optioncode ) This table tracks the types of cars customers are interested in A customer can want several different types of cars or combinations of characteristics custname , optioncode both foreign keys to customer and options tables respectively carmake, carmodel, caryear, carcolor and cartrim are the same attributes found in car table This table also has no primary key instead the combination of all of the fields is a unique key New to chegg so sorry if I formated the question incorrectly Ok I fixed the image if you need anything else please comment I really need this help ok Thats all the information about the database tables that I can provide REGIONS EMPLOYEES JOBS region id NUMBER employee id NUMBER (6 0) job id VARCHAR2(10) region name VARCHAR2(25) first name VARCHAR2(20) NOT NULL job title VARCHAR2(35) NOT NULL last name VARCHAR2(25) NOT NULL min salary NUMBER(6,0) email VARCHAR2(25) NOT NULL max salary NUMBER(6, 0) phone number VARCHAR2(20) COUNTRIES hire date NOT NULL DATE country id CHAR(2) job id VARCHAR2(10) NOT NULL NUMBER(8, 2) salary country name VARCHAR2(25) region id NUMBER commissiom pct NUMBER(2, 2) manager id NUMBER(6, 0) department id NUMBER(4,0) JOB HISTORY DEPARTMENTS employee id NUMBER(6, 0) OH department id NUMBER 4 0) start date DATE NOT NULL LOCATIONS location id NUMBER(4 0) street address VARCHAR2(40) postal code VARCHAR2(12) city VARCHAR2(30) NOT NULL state province VARCHAR2(25) country id CHAR(2) department name VARCHAR2(30) NOT NULL end date DATE NOT NULL manager id location id NUMBER(6, 0) NUMBER (4,0) job id department id VARCHAR(10) NOT NULL NUMBER (4,0)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 30, 2024

The question SQL Oracle Database : Print the total amount, the average dollar value of service visits (parts and labour costs) and the number of

The question SQL Oracle Database :

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.

I started on it but I don't feel like I fully understand how to solve it did I use window_frame_clause correctly?can you please do a step by step solution?

select c.carmake, avg(partscost+laborcost) OVER(ORDER BY servdate NULLS LAST RANGE BETWEEN (2015-09-10) PRECEDING and (2018-12-10) FOLLOWING) from SI.servinv s, SI.car c where s.carserial = c.carserial and (CARMAKE= UPPER('Acura') or CARMAKE= UPPER('Mercedes') or CARMAKE=Upper('Jaguar'));

Diagram for tables the database is an Oracle Database. I used Oracle SQL Developer to do what I have so far

image text in transcribed

more info:

customer (custname, custstreet, custcity, custprovince, custpostal, custhphone, custbphone)

All customers will be stored in this table.
Primary key is custname. The users will be responsible to ensure that customers' names are unique in the table.

options (optioncode, optiondesc, optioncost, optionlistprice)

All optional equipment for all makes of cars are stored here. Options are given once and for all. Users should not add or delete any options from the options table.
optioncode - primary key
optiondesc - the description of the optional equipment.
optioncost - the amount of money the optional equipment cost to Specialty Imports.
optionlistprice - the targeted price of the optional equipment. The actual sale price of an optional equipment for a particular sale is in invoption table (optionprice)

employee (empname, startdate, commissionrate, title, manager)

This table has a recursive relationship.

empname - primary key
startdate - the date the employee was hired
commissionrate - the rate of the commission for an employee. The rate is stored as a percentage. Example a 2% commission rate is stored as 2.0, or a 2.5% commission rate is stored as 2.5
title - the employee's job title.
manager - "foreign key" to the employee table. Manager column refers to empname column; i.e each manager is an employee and each employee (except for the general manager) has a manager. Each manager can manage multiple employees.

purchinv (purchinvno, purchfrom, purchdate)

purchinvno - the purchase invoice number of the car. It is usually found in the bill of sale that Specialty Imports receives together with the car from the factory or the business the car was bought from. If the car is a trade-in, Specialty Imports prepares a bill of sale that would have a purchase invoice no. and that number will be placed here.
purchfrom - the business or the person the car was purchased from.
purchdate - the date that car was purchased by Specialty Imports.

car (carserial, custname, carmake, carmodel, caryear, extcolor, cartrim, enginetype, purchinvno, purchcost, freightcost, carlistprice)

carserial - primary key
custname - This is the owner of the car.
- If a car is unsold (available for sale), the custname will be NULL i.e. empty. This is our means of determining if a car is available for sale.
- Cars brought in for service will always have an owner's name.
purchinvno - foreign key to purchinv table. It is usually found in the bill of sale that Specialty Imports receives together with the car from the factory or the business the car was bought from. The same purchinvno may be in multiple cars, as one purchase invoice may comtain multiple cars and it will be null if the car is in the table solely for the purpose of service. If the car is a trade-in, Specialty Imports prepares a bill of sale that would have a purchase invoice no. and that number will be placed here.
carmake, carmodel and caryear are self-explanatory.
extcolor - the exterior color of the car
cartrim - the interior color
purchcost - what specialty imports paid to acquire the car for sale purposes. This column is placed in car table as opposed to purchinv table, due to the fact that a purchinv (purchinvno) may contain multiple cars. Again this column will be null if the car is in the table solely for the purpose of service.
freightcost - what was paid to bring the car over from wherever the car was purchased. This is passed on to the customer.
carlistprice - This goes on the windshield. Specialty Imports is happy if the car can be sold for this much but it is usually sold for less. The actual sale price is the 'net' field in the 'saleinv' table.

baseoption (optioncode, carserial)

These are the options put on a car by the manufacturer. There is no price field in this table because it is included in the vehicle price.

Concatenated primary key (carserial, optioncode) for the M:N relationship. The combination of both columns are a composite primary key.
carserial is a foreign key to car
optioncode is a foreign key to options

saleinv (saleinvno, saledate, salesman, custname, carserial, insfire, inscollision, insliability, insproperty, taxrate, licfee, discount, commission, carsaleprice)

A new record is inserted each time a car is sold.

saleinvno - primary key.
saledate is the date the car is sold to the customer
salesman - employee who sold the car. It is a foreign key to employee table.
custname - foreign key to customer table. This field cannot be left null. We must always know the customer.
carserial - foreign key which identifies the car that is sold.
insfire, inscollision, insliability and insproperty - Used if we assist the customer in acquiring insurance. The value can only be either 'N' or 'Y' ( yeso).
taxrate - the rate at which the tax amount will be calculated. Stored as percentage and the default is 13.0 (13%).
licfee - entered if we assist the customer in acquiring license plates. This is a pass through cost.
discount - negotiated with salesman. This is the amount to be deducted from the carsaleprice (see bellow).
commission - what the sales person is paid for the sale. The customer must not see this, which is the reason why it does not show up in the sale invoice form.
carsaleprice - the amount of money Specialty Imports has decided to sell the car for.

tradeinv (saleinvno, carserial, tradeallow)

Here is the information stored about the cars that are traded in. A customer may trade-in more than one car and use the trade in allowance toward the purchase of a new vehicle.

The combination of saleinvno and carserial is a composite primary key
- saleinvno - foreign key to saleinv table
- carserial - foreign key to car table
tradeallow - amount given to customer for the trade-in.

invoption (saleinvno, optioncode, optionprice)

These are the extra options added to a car at the time of sale. The customer must pay extra for these options so there is a saleprice field.

The combination of saleinvno and optioncode is a composite primary key
- saleinvno - foreign key to saleinv table
- optioncode - foreign key to options table
optionprice - the sale price of the optional equipment

servinv (servinvno, serdate, custname, carserial, partscost, laborcost, taxrate)

One record goes into this table each time a car is serviced.

servinvno - primary key
servdate - date the sevice was performed
custname - foreign key to customer - customer must be known
carserial - foreign key to car - must be known
partcost - the amount of money the customer will be charged for the cost of the parts used during the service
laborcost - the amount of money for the labor
taxrate - the rate at which the tax amount will be calculated. Stored as percentage and the default is 13.0 (13%).

servwork (servinvno, workdesc)

Notice that the whole record is a concatenated key. A car being serviced would have multiple things done to it.
servinvno - foreign key to servinv
workdesc - a text field indicated what what done to the car

prospect (custname, carmake, carmodel, caryear, carcolor, cartrim, optioncode)

This table tracks the types of cars customers are interested in. A customer can want several different types of cars or combinations of characteristics.

custname, optioncode - both foreign keys to customer and options tables respectively.
carmake, carmodel, caryear, carcolor and cartrim are the same attributes found in car table.
- This table also has no primary key; instead the combination of all of the fields is a unique key

New to chegg so sorry if I formated the question incorrectly. Ok I fixed the image if you need anything else please comment I really need this help.

ok Thats all the information about the database tables that I can provide.

REGIONS EMPLOYEES JOBS region id NUMBER employee id NUMBER (6.0) job_id VARCHAR2(10) region_name VARCHAR2(25) first_name VARCHAR2(20) NOT NULL job_title VARCHAR2(35) NOT NULL last_name VARCHAR2(25) NOT NULL min_salary NUMBER(6,0) email VARCHAR2(25) NOT NULL max_salary NUMBER(6, 0) phone_number VARCHAR2(20) COUNTRIES hire_date NOT NULL DATE country_id CHAR(2) job_id VARCHAR2(10) NOT NULL NUMBER(8, 2) salary country_name VARCHAR2(25) region_id NUMBER commissiom_pct NUMBER(2, 2) manager_id NUMBER(6, 0) department_id NUMBER(4,0) JOB_HISTORY DEPARTMENTS employee_id NUMBER(6, 0) OH department_id NUMBER 4.0) start_date DATE NOT NULL LOCATIONS location_id NUMBER(4.0) street_address VARCHAR2(40) postal_code VARCHAR2(12) city VARCHAR2(30) NOT NULL state_province VARCHAR2(25) country_id CHAR(2) department_name VARCHAR2(30) NOT NULL end_date DATE NOT NULL manager_id location_id NUMBER(6, 0) NUMBER (4,0) job_id department_id VARCHAR(10) NOT NULL NUMBER (4,0)