This is for an SQL project.. I just need the queries.

$1.$ Model Type Report. Display the following information about bicycles stored in the system, categorized

by the Model Type:

a$.$ The model type

b$.$ The count of each model type

c$.$ The average list price of each model type

d$.$ The average sale price of each model type

e$.$ The difference between the list price and sale price for each model type.

The results should be sorted in descending order with the model types that have the highest count

appearing first.

Note that there are only seven model types in the database, so the result of this query should only

have seven rows.

$2.$ Mountain Bike Customer Report. Display a list of all customers who purchased Mountain Bikes, to

include the following information:

a$.$ The Customer$$s last name

b$.$ The Customer$$s first name

c$.$ The Count of Mountain Bikes purchased

d$.$ The Average of the Cost, where Cost $=$ SalePrice $+$ SalesTax $+$ ShipPrice

e$.$ The Sum of the Cost, where Cost $=$ SalePrice $+$ SalesTax $+$ ShipPrice

The results should be sorted by the Count of Mountain Bikes Purchased $($in descending order$)$ and

secondarily by the Last Name and First Name $($both in ascending order$).$

Note that the total of the Count of Mountain Bikes Purchased column should match the number of

Mountain Bikes in the Model Type report.

$3.$ Outstanding Balance Report. Display a list of all Customers that still owe more than $$1$ to the

BikeShop. Note that you should use the CustomerTransactions table to calculate this amount$$not the

BalanceDue field in the Customer table. Also note that a negative balance here indicates the customer

must continue to make payments. The report should include the following:

a$.$ Customer$$s Last Name

b$.$ Customer's First Name

c$.$ The Total Amount Owed

d$.$ The Customer$$s Street Address

e$.$ The Customer$$s City name

f$.$ The Customer$$s State abbreviation

g$.$ The Customer$$s ZIP code

h$.$ The Customer$$s Phone Number

The results should be sorted by the customers$$ Last Name and then First Name.

$4.$ Employee Bike Tracking Report. Display the Bicycles and the parts that comprise them for a particular

employee. In this case, choose EmployeeID $99999,$ which corresponds to the record you inserted in

Part $1,$ Number $3.$ This report should contain the following:

a$.$ The Bicycle$$s Serial Number

b$.$ The Bicycle$$s Model Type

c$.$ The Bicycle$$s Paint Color

d$.$ The First and Last Name of the Painter $($foreign key to Employee$)$ who painted it$.$ Use CONCAT

to accomplish this. The painter$$s name should be in a single column.

e$.$ The Category of the Component part

f$.$ The Total Quantity for this Bike Part

g$.$ The Total List Price for this Component $($it should be the sum of: the list price times the

quantity$)$

As a hint, you will need to access five separate tables to accomplish this query correctly.

Sort the report by Bicycle Serial Number and then Component Category.

$5.$ Customers Without Transaction Report. Display a list of customers who have no Transactions in the

system. List the Bicycles they purchased, if applicable $($note that two people did not purchase bicycles,

but you should still show the people in this report$).$ Include the following information:

a$.$ Customer Name: First Name and Last Name CONCAT$$d into the same field.

b$.$ Customer Address

c$.$ Customer City Name

d$.$ Customer State Abbreviation

e$.$ Customer Zip Code

f$.$ Customer Transaction Date $($note that this should be null for all rows$)$

g$.$ Bicycle Serial Number

h$.$ Bicycle Model Type ID

i$.$ Bicycle List Price

Keep in mind that the Transaction Date would be null for all rows, but the Bicycle information will

be null for only two rows $($in other words, everyone except these two customers has a Bicycle$).$

Sort the report by ListPrice in descending order