Guaranteed upvote it done correctly :$)$

Standard Hardware Query Assignment

Store both query and output in one script file. Use schema for your section only. It would be from Section $1$ depending on which section you are in$.$ for all queries. When you have more than $30$ rows, show the first $4$ and the last $4$ rows with a comment in the middle indicating how many you have removed.

$1.$ List all the data in all tables. $($Submit query only. Do NOT submit output.$)$

$2.$ List the customer number and name of customers who live in BRAMPTON $($descending name order$).$

$3.$ List the name and number of customers who have placed orders but who do not have a sales rep number $($ascending name order$).$

$4.$ List the average unit price of all appliances on file.

$5.$ How many sports items are on file?

$6.$ What is the total value of appliances that are on hand?

$7.$ What is the average order price of sports items sold between January $2010$ and November $2015($inclusive$).$

$8.$ What is the minimum and maximum unit price of the parts? Change the column headings.

$9.$ What is the part number and description of the part$($s$)$ with that maximum price? Do not use a constant.

$10.$ List the name and full address of customers who have never placed an order?

$11.$ List all parts for which the part description starts with an A$,$ C or S$.($partno$,$ desc, price$)$

$12.$ List the same set of parts as #$11$ but add $($orderno$,$ orderdate$)$ if they were ordered.

$13.$ Print a list of all the sales reps that represent anyone with $\text{'}\_\_\_\_$Sh$\_\_\_\_\_\_\text{'}$ in their name. Do not print the same sales rep more than once.

$14.$ What is the total amount of each order placed by customers living in Brampton?

$15.$ What is the price of the cheapest part in each part class?

$16.$ Determine the customer number, name and address of customers living in Brampton who have purchased $$500$ or more of a part. Include the total amount of each part in the output. $($Ascending total amount order$)$

After queries have been debugged, they should be run just prior to submission in order to have the most current data.

This is the code for it:

$1.$

SELECT $*$ FROM customer;

SELECT $*$ FROM salesrep;

SELECT $*$ FROM part;

SELECT $*$ FROM salesorder;

SELECT $*$ FROM orderprod;

SELECT $*$ FROM invoice;

SELECT $*$ FROM invprod;

$2.$

SELECT custno, custname

FROM customer

WHERE custcity $=$ 'BRAMPTON'

ORDER BY custname DESC;

$3.$

SELECT c$.$custname, c$.$custno

FROM customer c

LEFT JOIN salesorder so ON c$.$custno $=$ so$.$custno

WHERE c$.$srepno IS NULL

ORDER BY c$.$custname ASC;

$4.$

SELECT AVG$($unitprice$)$ AS avg$\_$unit$\_$price

FROM part

WHERE partclass $=\text{'}$AP$\text{'}$;

$5.$

SELECT COUNT$(*)$ AS num$\_$sport$\_$items

FROM part

WHERE partclass $=\text{'}$SP$\text{'}$;

$6.$

SELECT SUM$($onhand $*$ unitprice$)$ AS total$\_$appliance$\_$value

FROM part

WHERE partclass $=\text{'}$AP$\text{'}$;

$7.$

SELECT AVG$($op$.$orderprice$)$ AS avg$\_$sport$\_$order$\_$price

FROM orderprod op

INNER JOIN salesorder so ON op$.$orderno $=$ so$.$orderno

WHERE op$.$partno IN $($SELECT partno FROM part WHERE partclass $=\text{'}$SP$\text{'})$

AND so$.$orderdate BETWEEN $\text{'}2010-01-01\text{'}$ AND $\text{'}2015-11-30\text{'}$;

$8.$

SELECT MIN$($unitprice$)$ AS min$\_$price, MAX$($unitprice$)$ AS max$\_$price

FROM part;

$9.$

SELECT partno, partdesc

FROM part

WHERE unitprice $=($SELECT MAX$($unitprice$)$ FROM part$)$;

$10.$

SELECT custname, CONCAT$($custstreet$,\text{'},\text{'},$ custcity, $\text{'},\text{'},$ custprov, $\text{'}\text{'},$ custpcode$)$ AS full$\_$address

FROM customer c

LEFT JOIN salesorder so ON c$.$custno $=$ so$.$custno

WHERE so$.$orderno IS NULL;

$11.$

SELECT partno, partdesc, unitprice

FROM part

WHERE partdesc LIKE $\text{'}$A$\%\text{'}$ OR partdesc LIKE $\text{'}$C$\%\text{'}$ OR partdesc LIKE $\text{'}$S$\%\text{'}$;

$12.$

SELECT p$.$partno, p$.$partdesc, p$.$unitprice, op$.$orderno, op$.$orderdate

FROM part p

LEFT JOIN orderprod op ON p$.$partno $=$ op$.$partno

LEFT JOIN salesorder so ON op$.$orderno $=$ so$.$orderno

WHERE p$.$partdesc LIKE $\text{'}$A$\%\text{'}$ OR p$.$partdesc LIKE $\text{'}$C$\%\text{'}$ OR p$.$partdesc LIKE $\text{'}$S$\%\text{'}$;

$13.$

SELECT DISTINCT srepno, srepname

FROM salesrep

WHERE srepname LIKE $\text{'}\%$Sh$\%\text{'}$;

$14.$

SELECT so$.$orderno, SUM$($op$.$orderqty $*$ op$.$orderprice$)$ AS total$\_$order$\_$price

FROM salesorder so

INNER JOIN orderprod op ON so$.$orderno $=$ op$.$orderno

INNER JOIN customer c ON so$.$custno $=$ c$.$custno

WHERE c$.$custcity $=$ 'BRAMPTON'

GROUP BY so$.$orderno;

$15.$

SELECT partclass, MIN$($unitprice$)$ AS cheapest$\_$price

FROM part

GROUP BY partclass;

$16.$

SELECT c$.$custno, c$.$custname, CONCAT$($c$.$custstreet, $\text{'},\text{'},$ c$.$custcity, $\text{'},\text{'},$ c$.$custprov, $\text{'}\text{'},$ c$.$custpcode$)$ AS full$\_$address, SUM$($op$.$orderqty $*$ op$.$orderprice$)$ AS total$\_$amount

FROM customer c

JOIN salesorder so ON c$.$custno $=$ so$.$custno

JOIN orderprod op ON op$.$orderno $=$ op$.$orderno

JOIN part p ON op$.$partno $=$ p$.$partno

WHERE c$.$custcity $=$ 'BRAMPTON'

GROUP BY c$.$custno, c$.$custname, full$\_$address

HAVING SUM$($op$.$orderqty $*$ op$.$orderprice$)>=500$

ORDER BY total$\_$amount ASC;

What I want you to do is to explain the codes $($each part$),$ get the SQL$,$ and take a screenshot to show the outputs for each part for this assignment, thanks :)