181|SQL – Precision Plane Aggregation Adventure

BYU Student Author: @Sterling_Lane
Reviewers: @Andrew_Wilson, @Marta_Ellsworth
Estimated Time to Solve: 30 Minutes

This is an intro challenge that is part of the SQL Learning Path.

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
You are quickly becoming the resident go-to data analyst for the Precision Plane Parts accounts payable team! You’ve been assigned to help prepare some important reports for the CFO to help them prepare to present some important information to Precision Plane Parts’ shareholders. With full access to the Expenditures database at your disposal for quick analysis, you are fully equipped for the task. Download the starting database and jump into answering their questions below and save your results for each question as separate queries in Access.

Instructions

  1. Which of our preferred vendors from whom we purchase inventory has the lowest average inventory purchase price? Only present vendors with an average purchase price below $75. Limit the report to only two columns: company_name and avg_purchase_price.
    a. Select the company_name from the Vendor table.
    b. Select the average of the exp_purchase_price from the Inventory table and rename it to “avg_purchase_price”.
    c. Join the Inventory table to the Vendor table.
    d. Group by the company_name.
    e. Use the HAVING statement to limit the average exp_purchase_price to below $75.
    f. Order your report by the avg_purchase_price descending.
  2. How many employees do we currently have in each role in our accounts payable department? Only include roles with at least 3 employees in them. The report should be in order of employee_count with two columns: emp_role_description and employee_count.
    a. Select the emp_role_description from the EmployeeRole table.
    b. Select the count of the employee_id from the Employee table and rename it to employee_count.
    c. Join the Employee table to the EmployeeRole table.
    d. Group by the emp_role_description.
    e. Use the HAVING statement to limit the count of employees to having at least 3 people.
    f. Order your report by the employee_count ascending to match the CFO’s request.
  3. Which vendors sent us the most invoices in the first quarter of 2023? I’m only concerned from an efficiency perspective if a vendor is invoicing us more than 10 times in a single quarter. This report should also only have two columns: company_name and total_invoices.
    a. Select the company_name from the Vendor table.
    b. Select the count of the invoice_id from the Invoice table and rename it to total_invoices.
    c. Join the Invoice table to the Vendor table.
    d. Filter to only include invoices with a month between 1 and 3 and a year that equals 2023.
    e. Group by the company_name
    f. Use the HAVING statement to limit the count of invoices to > 10 invoices sent.
    g. Order your report by the total_invoices descending.
  4. Which vendors have we spent the most money on Purchase Orders for on inventory that we typically order monthly? Give me the total amount of money we have committed to each vendor this year. I only care about vendors where our total amount spend exceeds $100,000. This report should only have two columns: company_name and order_total.
    a. Select the company_name from the Vendor table.
    b. Select the sum of the qty_ordered multiplied by the price_per_unit in the OrderDetail table. Round this to two decimal places and rename it to order_total.
    c. Join the four required tables together: OrderDetail, Inventory, Orders, and Vendor.
    d. Filter the std_expenditure_freq to only include monthly invoices.
    e. Group by the company_name
    f. Use the HAVING statement to limit the order_total to orders above $100,000.
    g. Order your report by the order_total descending.

Data Files

Suggestions and Hints
  • The exp_purchase_price in the Inventory table is what you can use to pull average purchase prices.
  • The HAVING statement only filters on aggregated values like averages and counts. If you want to filter on non-aggregated values, you’ll need to use a WHERE statement.
  • The invoice_receipt_date should be used to limit your results for Question 3. Consider looking up Access Date Functions if you get stuck on how to pull out the month and/or year from a date column.
  • The only way to get the total for each order is to multiply the quantity ordered by the price per unit for each order. This is what you’ll need to aggregate and filter to meet the CFO’s requirements.
  • The Inventory table specifies the expected order frequency for a given inventory item.

Solution

Solution Code

Query 1

SELECT v.company_name, AVG(i.exp_purchase_price) AS avg_purchase_price 
FROM Inventory AS i INNER JOIN Vendor AS v ON i.preferred_vendor = v.vendor_id 
GROUP BY v.company_name 
HAVING AVG(i.exp_purchase_price) < 75 
ORDER BY 2 DESC; 

Query 2

SELECT emp_role_description, COUNT(e.employee_id) AS employee_count 
FROM Employee AS e INNER JOIN EmployeeRole AS er ON e.employee_role_id = er.emp_role_id 
GROUP BY emp_role_description 
HAVING COUNT(e.employee_id) > 2 
ORDER BY 2; 

Query 3

SELECT v.company_name, COUNT(i.invoice_id) AS total_invoices 
FROM Invoice AS i INNER JOIN Vendor AS v ON i.vendor_id = v.vendor_id 
WHERE MONTH(i.invoice_receipt_date) < 4 AND YEAR(i.invoice_receipt_date) = 2023 
GROUP BY v.company_name 
HAVING COUNT(i.invoice_id) > 10 
ORDER BY 2 DESC; 

Query 4

SELECT v.company_name, ROUND(SUM(od.qty_ordered * od.price_per_unit),2) AS order_total 
FROM ((OrderDetail AS od INNER JOIN Inventory AS i ON od.inventory_id = i.inventory_id) INNER JOIN Orders AS o ON od.order_id = o.order_id) INNER JOIN Vendor AS v ON o.vendor_id = v.vendor_id 
WHERE i.std_expenditure_freq = "monthly" 
GROUP BY v.company_name 
HAVING SUM(od.qty_ordered * od.price_per_unit) > 100000 
ORDER BY 2 DESC; 

Challenge181_Solution.accdb
Solution Video: Challenge 181|SQL – Precision Plane Aggregation Adventure