Cis $111$ Assignment #$3$ Chapter $5$ & $6$ Query

ProblemsPrepare a script file just as you did for the previous

assignments.$$Chapter $5$ Query Problems$$Query #$1(3$ pts$)$: Create a summary query that

will display the number of orders, the earliest order date, the

latest order date by country for Germany, France or Spain.$$Query #$2(3$ pts:$$ Display the highest and

lowest unit price, the total and the average product sales for all

orders whose customers are from the United States. Product Sales is

a calculate field: Unit Price $*$ Quantity $*(1$ Discount$).$ There

should be only one row of output.$$Query #$3(4$ pts$)$: Create the following summary

query where it will be grouped by Product name for all Seafood or

Dairy Products: List the average quantity ordered, the highest

quantity ordered, and the total product sales amount $($Product Sales

is a calculate field: Unit Price $*$ Quantity $*(1$ Discount$)).$Query #$4(4$ pts$)$:$$ You are to create a

query that will display the number of orders grouped by territory

description $.$ This should only list territories where the number of

orders are $100$ or more. The Having clause must be used for the

criteria.$$Chapter $6$ Query Problems$$Query #$5(4$ pts$)$: Create a query that will

return the customer id$,$ company name and country for those

customers without an order. Use the Not In operator with a

subquery.$$Query #$6(4$ pts$)$: Using the ALL keyword create a

query that returns orders that have a quantity larger than the

quantities ordered for product ID $15$ or $16.$ Display the Order ID$,$

Order date, Product ID$,$ Quantity ordered, Unit price and

discount.$$ The subquery should return all of the quantities

ordered for a product id $15$ or $16.$Query #$7(4$ pts$)$ Create a query that will

display the order id$,$ customer id$,$ order date, shipped date and the

ship freight cost $($freight$)$ for those orders that are greater

than the freight average of all orders whose ship country is USA or

Canada.$$ The subquery should determine what the freight

average is and passes that to the outer query.$$Query #$8(4$ pts$)$: Create a query using a

subquery within the FROM clause to retrieve the top $5$ customers

based on the number of orders received.$$ The outer query

should display the customer id$,$ the date of the first order, the

date of the last order and the total number of orders

received.$$ Refer to the example on page $201$ of your textbook.

Extra Credit$(2$ points$)$ is given if the subquery

within the From clause uses a Common Table Expression$($CTE$).$