179|SQL – Group By for Greatness

Challenges
, ,
1

BYU Student Author: @Marta_Ellsworth
Reviewers: @Andrew_Wilson, @Sterling_Lane
Estimated Time to Solve: 25 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 an employee in the accounting department at Precision Plane Parts. You’ve been asked to present some company data at this week’s catch-up meeting with all the department heads. You’ve decided to use SQL to ensure your data is accurate in case anyone has any questions. Follow the instructions to retrieve each report, so you can make a good impression on all the executives.

Instructions

  1. How many orders has each employee created? Only include employees who have created at least one order.
    a. Select the employee “first_name” and “last_name” from the “Employee” table.
    b. Select the count of the “emp_order_creation_id” from the “Orders” table and rename it to “Count_of_Orders”.
    c. Join the “Employee” table with the “Orders” table.
    d. Group by the employee “first_name” and “last_name”.
  2. What is the average quantity ordered for each inventory item?
    a. Select the “inventory_name” from the “Inventory” table.
    b. Select the average of the “qty_ordered” from the “OrderDetail” table. Round the average to two decimal places and rename the column to “avg_qty_ordered”.
    c. Join the Inventory and OrderDetail tables.
    d. Group by the “inventory_name”.
  3. What is the total payable amount for each vendor based on our outstanding orders?
    a. Select the “company_name” from the “Vendor” table.
    b. Multiply “qty_ordered” with “price_per_unit” from the “OrderDetail” table and then sum them.
    c. Join the “Vendor” table and the “Orders” table. Then join the “OrderDetail” to both tables.
    d. Group by the “company_name”.

Challenge176_Image
Challenge176_Image1189×538 19.3 KB

Data Files

Suggestions and Hints
  • To rename a column use AS in the SELECT statement (Count(o.emp_order_creation_id) AS Count_of_Orders).
  • When joining the “Employee” and “Orders” table use aliases for your tables (SELECT e.employee_id FROM Employee AS e (The e is the alias for the employee table.)).
  • Make sure you include all non-aggregate columns in the GROUP BY statement if you have aggregations in your SELECT statement.

Solution

Solution Code

Query 1

SELECT e.first_name, e.last_name, COUNT(o.emp_order_creation_id) AS Count_of_Orders 
FROM Employee AS e  
INNER JOIN Orders AS o ON e.employee_id = o.emp_order_creation_id 
GROUP BY e.first_name, e.last_name;

Query 2

SELECT i.inventory_name, ROUND(AVG(od.qty_ordered), 2) AS avg_qty_ordered 
FROM Inventory AS i  
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id 
GROUP BY i.inventory_name;

Query 3

SELECT v.company_name, SUM(qty_ordered * price_per_unit) AS Total_Payable 
FROM (Vendor AS v  
INNER JOIN Orders AS o ON v.vendor_id = o.vendor_id) 
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id 
GROUP BY v.company_name;

Challenge179_Solution.accdb
Solution Video: Challenge 179|SQL – Group By for Greatness

2

Time to complete: 25 minutes
Rating: Introductory

I enjoyed this challenge! I don’t have much experience with the INNER JOIN function, so it was great practice. Because I had syntax issues, I followed the video to achieve the solution.