Description

Instructions:

·         Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements.  If you are caught cheating on this, you will receive a zero on this assignment and be reported to the Dean of Students.  Also this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more.

·         What to turn in

o   Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid.  Please make sure the lastname and firstname you use matches what is in Canvas.

o   Submit your .sql file on Canvas before the deadline.  Late submissions receive 50% off.  No submissions will be accepted 24 hours after the deadline.

·         The SQL problems below will be based on the DDL script that is posted on the Canvas instructions.  Download that script and run it before you start.

Summary Problems:

1.      Write a SELECT statement that returns these columns:

The count of the number of products in the Products table with a column alias of product_count

The minimum of the list_price columns in the Products table with column alias of min_price

The max of the list_price column in the Products table with column alias of max_price

2.      Write a SELECT statement that returns one row for each customer that has ordered products with these columns:

The Customer_ID column from the Orders table

The count of the products in the Order_Items table with column alias of products_purchased

The item price of the most expensive product purchase by the customer on order_items with a good column alias of most_expensive_purchase

3.      Update statement from previous question but adding an additional column called “average_price” that returns the average price of the products in on each order_items.  Make sure to round the values in this column to 2 decimals.  Add a sort to the query to sort by products_purchased descending then most_expensive_purchase descending

4.      Write a SELECT statement that returns one row for each customer that has orders with these columns:

The first_name column from the Customers table

The last_name column from the Customers table

The sum of the item price in the Order_Items table multiplied by the quantity in the Order_Items table

The sum of the discount amount column in the Order_Items table multiplied by the quantity in the Order_Items table

The sum of the (item_price – discount) times the quantity amount column in the Order_Items table with a column alias of final_revenue

Sort the result set in descending sequence by the revenue for each customer.

5.      Write a SELECT statement that returns one row for each order and the associated products with these columns:

The customer_id from the Orders table

A count of the product_ids on the Order_Items table with a column alias of product_count

The sum of the item_price on the Order_Items table with a column alias of total_spend

Return only those rows where the customer has more than 1 order.

Sort the result set in descending sequence by product_count and total_spend.

6.      Modify the solution to previous question so it only counts/totals line items that have an item_price greater than 400.

7.      Write a SELECT statement that answers this question: What is the item_price each product that have a category of 1 or 2? Return these columns:

The category name from the Categories table

The product name from the Products table

The average list price of product in the Products tables

Round the list_price to 2 decimals

Use the ROLLUP operator to include a row that gives the grand total.

8.      Write a SELECT statement that answers this question: Which customers have ordered more than one product? Return these columns:

The email address from the Customers table

The count of distinct products from the customer’s orders

Subquery Problems:

9.      Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword.

SELECT DISTINCT product_name

FROM products p JOIN order_items oi

  ON p.product_id = oi.product_id

ORDER BY product_name;

10.  Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products?

Return the product_name, list_price, and date_added columns for each product.

Sort the results by the product_name.

11.  Write a SELECT statement that returns the customer_id, first_name, last_name, email_address columns from the Customer table.  Return one row for each customer that has never had an order on in the Orders table. To do that, use a subquery that pulls the customer_id that has no corresponding order_id.  Hint: Use a left join to write the subquery.

12.  Write a SELECT statement that returns four columns: first_name, last_name, order_id, and the order total for each customer.  To do this, you can group the result set by first_name, last_name, or order_id columns. In addition, you must calculate the order total from the columns in the Order_Items table.  i.e. (item_price – discount)*quantity

Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s first_name, last_name, and the largest order (based on order_total)for that customer. To do this, you can group the result set by the first_name, last_name.  Sort the final results by last_name.

13.  Write a SELECT statement that returns the order_id, order_date, and ship_amount of each orders that has a unique ship_amount. In other words, don’t include orders that have the same ship_amount as another order.

Sort the results by the order_id column.

14.  Write a statement that will return one row per customer, representing the customer’s most recent order (the one with the latest date). Each row should include these three columns: customer_id, email_address, and order_date.  Do this using a subquery in the main query’s FROM clause.  Hint: Start with coding the subquery that pulls customers and their latest order.  Then join this subquery as an inline view to customers to complete the query.