177|SQL – Intro to Join Statements

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
Welcome back to Precision Plane Parts! Today you will be generating easily readable reports for your manager. Often in our database, we use primary keys and foreign keys to identify parts, however, most people haven’t memorized every part number, or everyone’s employee_id. Because of that, we are going to join tables in order to pull information from different tables. In this challenge, your job is to create reports to answer the questions found below.

Instructions

  1. Using the order table, join the employee table to show the name of the employee that created each order.
    a. Columns should be order_id, first_name, last_name
    b. Select the columns listed above. Make sure to identify which table they come from (TableName.ColumnName)
    c. Join Orders and Employee tables on Orders.emp_order_creation_id=Employee.Employee_ID
  2. We want to know the company names that are listed as preferred vendors for inventory items and what our standard order quantity is.
    a. Columns should be company_name, inventory_name, std_order_qty
    b. Select columns listed above
    c. Join Inventory table to Vendor table
  3. We are trying to figure out our Cost of Goods Sold for recent orders. What amount is payable for each order placed.
    a. Columns should be order_id, payable_amount
    b. Join relevant tables. (Hint: Do we need more than one table?)
  4. Which employee received each invoice for orders in the database? Assume we only purchase inventory items from our preferred vendors.
    a. Columns should be inventory_name, company_name, emp_received_id, invoice_receipt_date
    b. Join the Inventory, Vendor and Invoice tables

Data Files

Suggestions and Hints

Query 3 – Not all tables need to be joined together all of the time. Sometimes it is easier to just use the data found on a single table.

Query 4 – If you need to join across multiple tables in Microsoft Access, create your first join as normal. Put your first join in parentheses and add your second join outside the parentheses.

Solution

Solution Code

Query 1

SELECT O.Order_id, E.First_Name, E.Last_Name 
FROM Orders AS O INNER JOIN Employee AS E ON O.emp_order_creation_id=E.Employee_ID; 

Query 2

SELECT V.Company_Name, i.inventory_name, I.Std_order_qty 
FROM Inventory AS I LEFT JOIN vendor AS v ON I.preferred_vendor=v.vendor_id; 

Query 3

SELECT Order_ID, Payable_Amount 
FROM Invoice; 

Query 4

SELECT I.Inventory_Name, v.Company_Name, INV.Emp_Received_ID, INV.Invoice_Receipt_Date 
FROM (Inventory AS I INNER JOIN Vendor AS V ON I.Preferred_Vendor=V.Vendor_ID) INNER JOIN Invoice AS INV ON V.Vendor_ID=INV.Vendor_ID; 

Challenge177_Solution.accdb
Solution Video: Challenge 177|SQL – Intro to Join Statements