Lab Assignment: Group Functions and Subqueries

LAB OVERVIEW

Scenario and Summary

Lab will introduce the concept of group functions and subqueries to meet more complex report requirements.This lab may be completed using either DeVry's Omnymbus EDUPE-APP lab environment, or a local copy of the MySQL database running on your own computer using the ACME database tables. The lab will utilize a set of tables that are created and populated by the script file (create_ACME_db.sql).

A few IMPORTANT things to note if using EDUPE MySQL:

**There can be NO SPACES in alias names given to a column. For example:

Select unit_price as "Retail Price " from items; --this does NOT work in EDUPE MySQL.

Any of the following WILL WORK:

Select unit_price as "RetailPrice" from items;
Select unit_price as "Retail_Price" from items;
Select unit_price as Retail_Price from items;
Select unit_price as RetailPrice from items;

**Any calculated fields MUST be given an alias (and note above NO SPACES in alias). For example:
select unit_price * 2 from items;--this does NOT work in EDUPE MySQL

This will work:

select unit_price * 2 as NewPricefrom items;

Deliverables

• Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each exercise, and the output showing the results obtained.Be sure your LAST NAME is on the file.

LAB STEPS: Complete each of the exercises below.

1. Write a query to determine the total number of orders for each customer. Display the orderid and display the total with a heading of TotalOrders (note no spaces).

2. Write a query to determine the number of customers for each sales rep. Display the sales rep ID and display the total with a heading of TotalCustomers. Sort the display by sales rep ID.

3. Write a query to determine the total number of sales reps in offices 22 and the total number of sales reps in office 13. Display the office Id and display the total number of reps with a heading of Total_Reps.

4. Write a query to determine the total number of customers for these sales reps: Sales rep 5, 1, and 8.Display the sales rep Id and display the total number of customers with a heading of Total_Customers.

5. Write a query to determine the total number of customers for each of the Sales Reps. But only display the sales reps and their number of customers if that total number of customers is greater than 2. Display the total with a heading of Total_Customers.

6. Write a query to determine the total number of customers for each of the Sales Reps that have a creditlimit that is greater than 40,000. But only display the sales reps and their number of customers if that total number of customers is greater than 1. Display the total number of customers with a heading of Total_Customers.

7. Write a query to determine the total dollar amount of all purchases for each customer. Display the customer Id and the total dollar amount of all purchases for each customer. Sort by customer id.

8. Write a query to determine the total dollar amount of all purchases for ONLY customers 2114, 2103, and 2108. Give the total purchase amount a heading of TotalSalesAmount. Display the customer Id and the total dollar amount of purchases for each customer id . Sort by the total sales.

9. Write a query to determine the total dollar amount of all purchases for all customers. But instead of displaying the custid, display the customer company name (You need a join). Give the total purchase amount a heading of TotalSalesAmount. Display the customer company name and the total dollar amount of purchases for each company.

10. Re-do the previous problem but only display those company namesthat have a total purchase amount that is greater than $20000. Only display the company names.

11. Display the average purchase order amount for the customers. Round the amount to two decimals.

12. Display the number of products ACME sells and what the average price is for their products.

13. Write a query to display the description of a product and the number of orders for that product. Sort the display by the description.

14. Redo the previous problem but this time just display those product names and the number of sales that have more than 3 sales.

15. Use a SubQueryto determine which sales reps have a sale that is greater than the average sale. Display the sales rep ID. Only show the sales rep ID once.

16. Use a SubQuery to display the city of all offices whose sales are below the average target for all the offices.

17. Use a SubQuery to list all sales rep IDs that have an average sales dollar amount greater the average sales dollar amount of the sales reps. You can use a correlated sub query or an correlated subquery. You need a correlated sub query if you wish to compare the average sales dollar amount to the average sales dollar amount to all the other sales reps NOT including the the sales rep you are comparing to.

Attachment:- Create_ACME_db.rar