Project $1(10\%)$ Due June $23$rd by $10$pm Group Project $14($No exceptions$)$

Note: Copying and pasting from AI or providing solutions to other groups is not acceptable. Such actions will result in a grade reduction and in extreme cases could result in a zero and a charge of academic misconduct.

Using the Assign$1$ database, populate the database with sufficient data to test your queries $($See Note below$).$ Develop and document the SQL queries necessary to satisfy the following. Only show in the output the details required by the specifics of the query. No unnecessary or debug columns. The term $$list details$$ means all the columns from the specified table $$ no more and no less.

$--1.$ List the details for all orders for the customer named "Charlie" that contained sales for the item "Apples". Sequence the output by order no$.$

$--2.$ List the order no$.$ for orders that have more than $2$ listed sale item entries in $2022$

$--3.$ List the customers details for customers who have placed orders items in the "Fruit" $($FR$)$ or "Vegetable" $($VG$)$ catgeory in $2022.$ Show each customer only once. Sequence by customer name.

$--4.$ List the id $,$ description and category of items where the QOH is less than the reorder level. Sequence by item id within category

$--5.$ List the details for the items that have been sold at a discount on any order $($Where the sale price less than list price$).$ Sequence by item description within category

$--6.$ List the details for all customers that have ordered items with characters "Bananas" anywhere in its description. Sequence by customer name.

$--7.$ List the description of the item with the maximum quantity on hand of any item in the "Fruit" $($FR$)$ category.

$--8.$ List the total quantity sold for the item with the description "Green Bananas" in Ontario in $2022$

$--9.$ List the item details for all sold items in $2019.$ Sequence the output by item description.

$--10.$ Count the number of times an item in the vegetable $($VG$)$ category has appeared on an order in $2022$

$--11.$ Count the number of different item categories represented by sales in Quebec in August $2019$

$--12.$ List the details for the customers that purchased the item "Broccoli" in $2022.$ Show each customer only once and sequence alphabetically by customer name

$--13.$ List for each item by item id$,$ the highest sale value $($sale price $*$ qty sold $)$ appearing on any order. Sequence from highest to lowest sale value

$--14.$ List the total value of sales $($sale price $*$ qty sold$)$ for each item category in $2022.$ Order the output by lowest value to highest.

$--15.$ List the customer id for the Ontario customers that have placed $2$ or more orders in $2022$

$--16.$ List in alphabetic sequence by item description the details of any items ordered by the customer named "Jones". Show each item only once

$--17.$ List the customer details for those customers who have bought broccoli in $2022$ and who reside in either Ontario or Quebec. Sequence by customer no$.$

$--18.$ Show by item id the total value $($sale price $*$ qty sold$)$ for each sale item in August $2022$ where that item appeared on at least $2$ orders in that same time period

$--19.$ List the total inventory value $($item cost $*$ qty on hand $)$ for each item in the $"$VG$"$ category

$--20.$ Write the following commands. I must be able to test run i$),$ ii$)$ and iii$)$ below in sequence.

$--20$ a$).$ Add a new sale item to the database

$--20$ b$).$ Update the qtysold by adding $50$ new units $($Use a formula. Don't directly hard code the new value$)$

$--20$ c$)$ Delete the sale item

$--$Note: I must be able to test run i$),$ ii$)$ and iii$)$ above in sequence.

Note: Queries that do not return sufficient data will be marked as wrong

All queries must generate some output

Queries with explicit sequencing must return a minimum of $3$ rows

An aggregate query must have at least one row of output that is using at least $2$ input rows $($e$.$g$.$ a count of $2,$ not $0$ or $1)$

Use column aliases where appropriate.

Suppress duplicate rows where appropriate for output based on your actual data even if not specifically requested in the question.

Have one group member submit a Zip file $($not RAR$)$ thru D$2$L that will contain the following files:

i$)$ A text file containing names and student ids for the group.

ii$)$ The mdf and log files of the database as modified with your test data.

$($Note: to copy or zip these database files they must be detached or offline$)$

iii$)$ The SQL file containing your answers $($with the questions$)$ to the queries.