180|SQL – Intro to Filtering and Ordering Data

Challenges
, , ,
1

BYU Student Author: @Andrew_Wilson
Reviewers: @Sterling_Lane, @Marta_Ellsworth
Estimated Time to Solve: 20 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
What an eventful first week at Precision Plane Parts. As you continue your journey to SQL mastery, today we will focus on Filtering and Ordering data. As you will remember, filtering is done by using the WHERE statement and ordering is done using the ORDER BY statement. Once again, your boss has asked for a series of reports, and it is your job to find the information that she needs!

Instructions
Query 1 - We have been running out of Fire Extinguishers (Inventory_ID 27) a lot recently. Please find the order detail for orders containing fire extinguishers

  • Column names should be inventory_name, inventory_id, order_id, qty_ordered, order_sent_date
  • Join Inventory, OrderDetail and Orders tables
  • Filter to WHERE inventory_id=27
  • Order by the sent date from the most recent to the oldest order

Query 2 – There has been some suspicious activity recently. I need to know all of the invoices that were received by Michael Cohen whose employee id is 18

  • Column names should be invoice_id, first_name, last_name, payable_amount
  • Join the Invoice and Employee tables
  • Filter to where emp_received_id = 18
  • Order by the largest value invoice first

Challenge176_Image
Challenge176_Image1189×538 19.3 KB

Data Files

Suggestions and Hints
  • You will need to join the Orders, Inventory and Order Detail tables to find the necessary information
  • To order from newest to oldest, use the DESC command in the ORDER BY line
  • You can filter on a column even when it is not displayed

Solution

Solution Code

Query 1

SELECT I.Inventory_Name, I.Inventory_ID, OD.Order_ID, OD.Qty_Ordered, O.Order_Sent_Date 
FROM (Inventory AS I INNER JOIN OrderDetail AS OD ON I.Inventory_id=OD.Inventory_ID) INNER JOIN Orders AS O ON OD.Order_Id=O.Order_ID 
WHERE I.Inventory_ID = 27 
ORDER BY O.Order_Sent_Date DESC;

Query 2

SELECT I.Invoice_ID, E.First_Name, E.Last_Name, I.Payable_Amount 
FROM Invoice AS I RIGHT JOIN Employee AS E ON E.Employee_ID = I.Emp_Received_ID 
WHERE i.Emp_Received_ID = 18 
ORDER BY i.Payable_Amount DESC;

Challenge180_Solution.accdb
Solution Video: Challenge 180|SQL – Intro to Filtering and Ordering Data

2

Time to complete: 18 minutes
Challenge level: beginner
Rating: 3.5/5

3

Solution.pbix (998.9 KB)

4

Time to Complete: 25 minutes
Challenge level: Beginner
Rating: 4/5

I enjoyed this challenge!