B. Generating Reports with SQL For this lab, you will be using joins (including multiple and self joins) along with other SELECT clauses & operators to generate reports from the ALLPOWDER schema. The schema appears on the last page. Include comments for each of your queries so I can locate each one quickly and easily. Your column headings and formatting must match those specified. 1. Top 10 Product Report (10 Points) The Sales Manager needs to see a report on the top 10 products sold. This report will list the category, manufacturer name (brand), the quantity sold for each product and the total sales amount for each product. The headings for this report, and the formatting requirements are as follows: Category Brand Quantity Sold Total Sales Amount $#####.## Use TO_CHAR to format the sales amount correctly. Sort the report by the highest quantity sold. You should also display only the first 10 products (hint: look up the SELECT TOP clause). 2. Customer Skills Report (10 Points) List the customers names who are not beginners in cross-country skiing (skill type). Include the following fields. Customer Name Skill Type Last, First Order by Customer Name (Last and then First). One classic example of a self-join is querying an employee table to get the names of managers since managers are employees too. SELECT emp.first_name || ' ' || emp.last_name "Employee Name", mgr.first_name || ' ' || mgr.last_name "Manager Name" FROM HR.Employees emp INNER JOIN HR.Employees mgr ON emp.manager_ID = mgr.employee_ID; Note: A self-join is possible only when you have a self-referencing table.

CSCI 2020: Database Fundamentals P a g e | 3 3. Rental Payment Report (10 points) List the first name and last name of all customers that have rented items. Display the total rental charges (rent fees for each rental plus any repair charges). Find only those customers whose total rental charges are between 500 and 700. Hint: this can be solved using a subquery but it is not the only way to solve it. First Name Last Name Total Rental Charges ####.## Sort by Total Rental Charges (descending), then by Customer Last/First Name (ascending). 4. Employee Sales Report (10 points) List ALL employees and any sales associated with them. The report should contain the following fields. Employee Name No. of Sales Sales Total Last, First ### $#####.## Sort by Employee Name (ascending). 5. Inventory Report (10 points) List any products in the inventory that do not have a sale associated with them. Display the products category and the total quantity on hand. Hint: this query requires two different types of joins. The report should contain the following fields. Item Category Total Quantity on Hand Sort by Item Category ascending.