IS $441$ HW$7($SQL $3)$

$(18+10=28$ points total $-$ base point still $18,10$ extra points here$)$

$$

$$

$$

Write the SQL codes needed to complete each of tasks listed below.$$ The data are in a file called Order$2018.$mdb$.$ Run your SQL codes on the given DB$,$ and paste the query results immediately following the corresponding SQL statement: code, result; code, result.

Note: One of the tables is named $$Order$.$ Please note that $$Order$$ is also an SQL reserved word; so the table Order, when being referred to in its full name, must be enclosed in a pair of brackets as in the form $[$Order$].||1-2$: $2$ pts each; $3-10$: $3$ pts each

$$

$1.$ List all items that were sold for at least $30\%$ more than cost in the month of December. Hint: Use function MONTH$($date$)$ to return the month$\_$# of a date.

$2.$ List the inventory category and total quantity $($by each category$)$ ordered in the year $2015($in descending order of total quantity$).$ Hint: Use the function YEAR$($date$)$ similar to MOMTH$($date$).$

$3.$ List the Spring products ordered by florists that had a list price over $$200.$

$[$Watch: $(1)$ Join MANY tables!! $(2)$ same field name appear in multiple tables; $(3)$ same field name in multiple tables could mean different things!!$]$

$4.[$This problem has TWO questions$]$ List the customer ID and total orders for each customer, in descending order of total orders.$($Compute $[1]$once for total number of orders, and $[2]$ once for total dollar amount $$ so two queries$)$

$$

Self join:

$5.$ List the employees$$ names who are NOT in the same city as their managers, together with the names of those managers $($displayed in the same row as the employee$).$ Display also the employee$$s city and the manager$$s city for verification.

$6.$ List the manager name and total sales revenue attributable to each manager.$($The list should be in descending order by total sales revenue.$)$Self$-$join!$$

a$.$ You can FIRST solve the easier alternative: $$List the employees $($EID$,$ Name, City$)$ and their managers$($EID$,$ Name, City$)$ for those employees who are in the same city as their manager$.$ When you get comfortable and get the right result, then approach w the original Question $7.$

$$

Correlated subquery:

$7.$ List the item ID and description of those items whose price is higher than the average price of items in its category.

Display also the average price of the categories and name them CatAvg $($so the columns will be: itemID, description, listPrice, CatAvg$).$

Hint: The same logic as today's demo $$List restaurants whose sales are higher than the average sales in its own city$$

$$ passing a parameter from outer query into the inner query.

$8.$ Display the list price, cost, and profit margin $($very easy to figure out right?$)$ of those products whose profit margin is higher than the average margin in its own category.

$$

Outer join $($Use the Charter DB for HW $6)$

$9.$ List the employee number, employee last name, and pilot license type $($if any$)$ for all employees. $($Hint: outer join, because $$if any$$ some of them are not pilots$)$

$10.$ Chap $6,$ P$.293,6-45$ to be solved w outer join.