This assginment requires SQLitstudio

Note that you should execute (run) the query for each question only once and do not need to store your queries in SQLiteStudio. To complete these exercises, you need to consult the textbook (chapter 4) and online help https://www.w3schools.com/sql/. Keywords are included in the assignment to help you locate the related online help for each exercise.

When the questions for this assignment do not specify which rows to show in the results, assume that all rows should be shown. Similarly, when the questions do not specify which columns to show in the results, assume that all columns should be shown. When the questions specify that only certain columns should be included in the results, this does not imply that other columns cannot be used in the query (they should simply not be part of the output).

3.2) WHERE. Use the OrderHeaders table and create a query that displays all orders (rows):

a) placed in 2019 (you can assume that there is no later orders).

b) that were shipped after they were required.

c) that have not yet been shipped (Hint: statement shipped date is null).

3.3) DISTINCT. Use the OrderDetails table and create a query that displays:

a) all unique products sold (only show Product IDs in the results).

b) all unique products sold with a unit price of more than $50.

3.4) ORDER BY.

Use the products table and create a query that shows:

a) products sorted by UnitsInStock in descending order.

3.5) LIKE and WILDCARDS.

Use the Customers table and create a query that shows all customers with:

a) "manager" in the ContactTitle.

b) company names that start with F. Also submit a screenshot of output 3.5.b.

1. ALIASES and CASE. Create new columns (fields) in the results.

Note that aliases are temporaryand not stored for reuse in a query.

a) Use the Products table and create a query that shows Product Name, Units In Stock, Units On Order, Reorder Level, and a calculated field named Units Available defined as Units In Stock + Units On Order.

b) Use the Products table and create a query that shows Product Name, Units In Stock, Units On Order, Reorder Level, the calculated field Units Available (copy and paste the query you created in 4a), and a new calculated field named Reorder Needed that displays Yes if UnitsAvailable < ReorderLevel and otherwise No. Also submit a screenshot of output 4.b.

2. Create VIEWS.

Note that views are virtual tables. If you need to troubleshoot a view at some point you can simply delete it and recreate it.

a) Copy and paste the query in 4a and create a view called ProductAvailability based on this query.

b. Using the ProductAvailability view that you created in 5a, create a query that only shows products that need to be ordered (that have UnitsAvailable less than the ReorderLevel).

3. GROUP and AGGREGATE (e.g., COUNT, AVG, SUM):

a) Using the Products table, create a query that shows for each supplier: the SupplierID and the number of products associated with the supplier (name this field NumberOfItems).

b) Using the OrderDetails table, create a query that shows for each order the OrderID and the total quantity sold (name this field TotalQuantity).

c) Using the OrderDetails table show for each product: (i) the ProductID, (ii) the average sales unit price (name this field AverageUnitPrice; you can simply calculate the average for each product across the different order detail rows and you do not need to adjust the average for the quantity sold in each order), (iii) the total quantity sold (name this field SumOfQuantitySold), and (iv) the number of times it has been sold (name this field NumberOfSales; you can simply calculate the number of sales orders).