177|SQL – Intro to Join Statements

Andrew_Wilson · April 30, 2024, 12:56am

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

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
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
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?)
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

Challenge177_Data.accdb

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

Daxton_Jergensen · December 7, 2024, 6:54pm

Time to complete: 30 minutes
Difficulty: Intermediate
Notes: Really enjoyed this challenge. Brought back good memories from IS 402!

SELECT
Orders.order_id,
Employee.first_name,
Employee.last_name
FROM
Orders
JOIN
Employee
ON
Orders.emp_order_creation_id = Employee.employee_id;
SELECT
Vendor.company_name,
Inventory.inventory_name,
Inventory.std_order_qty
FROM
Inventory
JOIN
Vendor
ON
Inventory.preferred_vendor = Vendor.vendor_id;
SELECT
Orders.order_id,
SUM(OrderDetail.qty_ordered * OrderDetail.price_per_unit) AS payable_amount
FROM
Orders
JOIN
OrderDetail
ON
Orders.order_id = OrderDetail.order_id
GROUP BY
Orders.order_id;
SELECT
Inventory.inventory_name,
Vendor.company_name,
Invoice.emp_received_id,
Invoice.invoice_date AS invoice_receipt_date
FROM
Invoice
JOIN
Inventory
ON
Invoice.vendor_id = Inventory.preferred_vendor
JOIN
Vendor
ON
Inventory.preferred_vendor = Vendor.vendor_id;

Meadow_Mitchell · February 20, 2025, 2:45am

Time to complete: 10 minutes
Difficulty: Beginner
Notes: Enjoyed the challenge!
1.SELECT o.order_id, e.first_name, e.last_name
FROM Orders o
INNER JOIN Employee e ON o.emp_order_creation = e.Employee_ID;
2. SELECT company_name, inventory_name, std_order_qty
FROM Inventory
INNER JOIN Vendor ON Inventory.preferred_vendor = Vendor.vendor_id;
3.SELECT order_id, payable_amount
FROM Invoice;
4. SELECT Inventory.inventory_name, Vendor.company_name, Invoice.emp_received_id, Invoice.invoice_receipt_date
FROM (Vendor INNER JOIN Inventory ON Vendor.vendor_id = Inventory.preferred_vendor)
INNER JOIN Invoice ON Vendor.vendor_id = Invoice.vendor_id;

Gabrielle_Johnson · February 20, 2025, 7:48pm

Time to Complete: 10 minutes
Difficulty: Easy
Solution: See Below
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;
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;
3:
SELECT order_id, payable_amount
FROM Invoice;
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;

Porter_Rich · February 20, 2025, 8:53pm

Time to Complete: 10 Mins
Rating: Beginner
Solutions:

SELECT Orders.order_id, Employee.first_name, Employee.last_name
FROM Orders INNER JOIN Employee ON Orders.emp_order_creation_id = Employee.employee_id;
SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Vendor AS v RIGHT JOIN Inventory AS i ON v.vendor_id = i.preferred_vendor;
SELECT order_id, payable_amount
FROM Invoice;
SELECT i.inventory_name, v.company_name, iv.emp_received_id, iv.invoice_receipt_date
FROM ((Inventory AS i INNER JOIN Vendor AS v ON i.preferred_vendor = v.vendor_id) INNER JOIN Invoice AS iv ON v.vendor_id = iv.vendor_id);

ShortyoShire · February 20, 2025, 10:00pm

Time to Complete: 11 minutes
Difficulty: Easy
Comments: None
Solution:
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;
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;
3:SELECT order_id, payable_amount
FROM Invoice;
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;

Madelyn_Boulton · February 20, 2025, 10:52pm

Time to complete: 10 minutes
Difficulty: Beginner
Notes: I need to remember parenthesis for joins

SELECT o.order_id, e.first_name, e.last_name
FROM Orders o
INNER JOIN Employee e ON o.emp_order_creation_id = e.Employee_ID;
SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Inventory i
LEFT JOIN Vendor v ON i.preferred_vendor=v.vendor_id;
3.SELECT invoice.order_id, invoice.payable_amount
FROM Invoice;
SELECT i.inventory_name, v.company_name, inv.emp_received_id, inv.invoice_receipt_date
FROM (Invoice inv
LEFT JOIN Vendor v ON inv.vendor_id = v.vendor_id)
LEFT JOIN Inventory i ON i.preferred_vendor = v.vendor_id;

Kathryn_Thompson_Whi · February 21, 2025, 5:50pm

Time: 15 mins
Difficulty: Medium
Solution:
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;
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
3.
SELECT order_id, payable_amount
FROM Invoice
4.
SELECT i.inventory_name, v.company_name, iv.emp_received_id, iv.invoice_receipt_date
FROM (Inventory AS i INNER JOIN Vendor AS v ON i.preferred_vendor=v.vendor_id)
INNER JOIN Invoice AS iv ON v.vendor_id = iv.vendor_id;

Aaron_Greenwood · February 22, 2025, 4:54am

Time to complete: 30 mins
Difficulty: Moderate
Solutions:

SELECT o.order_id, e.first_name, e.last_name
FROM Orders o INNER JOIN Employee e ON o.emp_order_creation_id=e.employee_id;

SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Inventory i INNER JOIN Vendor v ON i.preferred_vendor=v.vendor_id;

SELECT order_id, payable_amount
FROM Invoice;

SELECT i.inventory_name, v.company_name, iv.emp_received_id, iv.invoice_receipt_date
FROM (Inventory i INNER JOIN Vendor v ON i.preferred_vendor=v.vendor_id) INNER JOIN Invoice iv ON v.vendor_id=iv.vendor_id;

Miles_Maynes · February 22, 2025, 7:36am

Time to complete: 15 minutes
Difficulty: Easy
Solution:

SELECT [Orders].order_id, [Employee].first_name, [Employee].last_name
FROM[Orders]
Inner Join [Employee] on [Orders].emp_order_creation_id=[Employee].employee_id
SELECT [Vendor].company_name, [Inventory].inventory_name, [Inventory].std_order_qty
FROM [Inventory]
INNER JOIN [Vendor] on [Inventory].preferred_vendor=[Vendor].vendor_id
SELECT order_id, payable_amount
FROM [Invoice]
SELECT [Inventory].inventory_name, [Vendor].company_name, [Invoice].emp_received_id, [Invoice].invoice_receipt_date
FROM ([Inventory] INNER JOIN [Vendor] ON [Inventory].preferred_vendor = [Vendor].vendor_id)
INNER JOIN [Invoice] ON [Vendor].vendor_id = [Invoice].vendor_id

Emily_Cook · February 22, 2025, 4:54pm

Time to Complete: 15 minutes
Difficulty: Easy

SELECT orders.order_id, employee.first_name, employee.last_name
FROM ORDERS
INNER JOIN Employee ON orders.emp_order_creation_id=employee.employee_ID
SELECT v.company_name, I.inventory_name, I.std_order_qty
FROM Inventory I
INNER JOIN Vendor v ON v.vendor_id=I.preferred_vendor;
SELECT order_id, payable_amount
FROM Invoice;
SELECT i.inventory_name, v.company_name, invoice.emp_received_id, invoice.invoice_receipt_date
FROM ((Inventory i INNER JOIN Vendor v ON i.preferred_vendor=v.vendor_id) INNER JOIN Invoice ON v.vendor_id=invoice.vendor_id);

Joshua_Anderson · February 22, 2025, 5:08pm

Time to complete: 15 Minutes
Difficulty: Beginner

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 ;

SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Vendor AS v
INNER JOIN Inventory AS i ON i.preferred_vendor = v.vendor_id;
3)
SELECT order_id, payable_amount
FROM Invoice;
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;

Nolan_Brewer · February 22, 2025, 5:35pm

Time to complete: 20 Minutes
Difficulty: Beginner
Q1
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;
Q2
SELECT v.company_name, i.inventory_name, i.std_order_qty

FROM Inventory i

INNER JOIN Vendor v ON i.preferred_vendor = v.vendor_id;
Q3
SELECT SUM(od.qty_ordered * od.price_per_unit) AS payable_amount, o.order_id

FROM Orders o

INNER JOIN OrderDetail od ON o.order_id = od.order_id

GROUP BY o.order_id;
Q4
SELECT Inventory.inventory_name, Vendor.company_name, Invoice.emp_received_id, Invoice.invoice_date as invoice_receipt_date

FROM (Inventory

INNER JOIN Vendor on Inventory.preferred_vendor = Vendor.vendor_id)

INNER JOIN Invoice on Vendor.vendor_id = Invoice.vendor_id;

Lucas_Argente · February 22, 2025, 7:12pm

Time to complete: 20 Minutes
Difficulty: Easy
Solution:
Q1:
SELECT Orders.order_id, Employee.first_name, Employee.last_name
FROM Orders
INNER JOIN Employee ON Orders.emp_order_creation_id=Employee.Employee_ID;
Q2:
SELECT Vendor.company_name, Inventory.inventory_name, Inventory.std_order_qty
FROM Vendor
INNER JOIN Inventory ON Vendor.vendor_id=Inventory.preferred_vendor;
Q3:
SELECT order_id, payable_amount
FROM Invoice;
Q4:
SELECT inventory_name, company_name, emp_received_id, invoice_receipt_date
FROM (Inventory
INNER JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id)
INNER JOIN Invoice ON Vendor.vendor_id=Invoice.vendor_id;

Cooper_Wise · February 22, 2025, 11:17pm

Time to complete: 20 min
Difficulty: Easy
Solution:
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
2.
SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Vendor AS v
INNER JOIN Inventory AS i
ON i.preferred_vendor = v.vendor_id
3.
SELECT order_id, payable_amount
FROM Invoice
4.
SELECT i.inventory_name, vnd.company_name, inv.emp_received_id, inv.invoice_receipt_date
FROM (Inventory as i
INNER JOIN Vendor as vnd
ON i.preferred_vendor = vnd.vendor_id)
INNER JOIN Invoice as inv
ON vnd.vendor_id = inv.vendor_id

McKenna_Hall · February 23, 2025, 12:55am

Time to complete: 25 minutes
Difficulty: Intermediate
Solution:

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;
SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Inventory AS i
INNER JOIN Vendor AS v ON i.preferred_vendor = v.vendor_id;
SELECT order_id, payable_amount
FROM Invoice;
SELECT inv.inventory_name, v.company_name, i.emp_received_id, i.invoice_receipt_date
FROM (Invoice AS i
INNER JOIN Vendor AS v ON i.vendor_id = v.vendor_id)
INNER JOIN Inventory AS inv ON v.vendor_id = inv.preferred_vendor;

Amanda_Garton · February 23, 2025, 4:00am

SELECT order_id, first_name, last_name
FROM Orders
INNER JOIN Employee ON orders.emp_order_creation_id = employee.employee_ID
SELECT company_name, inventory_name, std_order_qty
FROM Inventory
LEFT JOIN Vendor ON inventory.preferred_vendor = vendor.vendor_id;
SELECT order_id, payable_amount
FROM Invoice;
SELECT inventory_name, company_name, emp_received_id, invoice_receipt_date
FROM (Inventory
INNER JOIN Vendor ON vendor.vendor_id = inventory.preferred_vendor)
INNER JOIN Invoice ON invoice.vendor_id = vendor.vendor_id;

Shane_Hawkins · February 23, 2025, 4:40am

Time to complete: 20 minutes
Difficulty: Beginner
Solution: See Below

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;
SELECT v.company_name, i.inventory_name, i.std_order_qty
from Inventory as i
inner join Vendor as v on i.preferred_vendor=v.vendor_id
SELECT iv.order_id, iv.payable_amount
from invoice as iv
SELECT i.inventory_name, v.company_name, iv.emp_received_id, iv.invoice_receipt_date
from (Inventory as i
inner join vendor as v on i.preferred_vendor=v.vendor_id)
inner join invoice as iv on v.vendor_id=iv.vendor_id

Meilan_Rhoton · February 23, 2025, 6:17am

Time to complete: 20 minutes
Difficulty: Beginner/Intermediate

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
SELECT v.company_name, i.inventory_name, i.std_order_qty
FROM Inventory AS i
INNER JOIN Vendor AS v ON i.preferred_vendor = v.vendor_id;
SELECT i.order_id, i.payable_amount
FROM Invoice AS i
SELECT Inventory.Inventory_name, Vendor.company_name, Invoice.emp_received_id, Invoice.invoice_receipt_date
FROM (Inventory
INNER JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id)
INNER JOIN Invoice ON Vendor.vendor_id=Invoice.vendor_id

Henry_Chen · February 23, 2025, 7:03am

Time 20 Minutes
Median
1.
SELECT Orders.order_id, Employee.first_name, Employee.last_name
FROM Orders INNER JOIN Employee ON Employee.Employee_ID = Orders.emp_order_creation_id;
2.SELECT Vendor.company_name, Inventory.inventory_name, Inventory.std_order_qty
FROM Vendor INNER JOIN Inventory ON Inventory.preferred_vendor=Vendor.vendor_id;

3.SELECT Orders.order_id, Invoice.payable_amount
FROM ((Vendor
INNER JOIN Inventory ON Inventory.preferred_vendor = Vendor.vendor_id)
INNER JOIN Orders ON Orders.vendor_id = Vendor.vendor_id)
INNER JOIN Invoice ON Invoice.vendor_id = Vendor.vendor_id;

4.SELECT Inventory.inventory_name,Vendor.company_name, Invoice.emp_received_id, Invoice.invoice_receipt_date
FROM( (Inventory
INNER JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id)
INNER JOIN Invoice ON Invoice.vendor_id=Vendor.vendor_id)
;

Topic		Replies	Views
176\|SQL – Precision Plane Data Presentation Challenges managerial , sql , intro , planes	324	1308	February 27, 2025
181\|SQL – Precision Plane Aggregation Adventure Challenges managerial , sql , intro , planes , having	12	159	March 13, 2025
178\|SQL – Unions with Precision Challenges sql , union , intro	319	947	February 27, 2025
179\|SQL – Group By for Greatness Challenges sql , intro , group_by	316	992	February 27, 2025
180\|SQL – Intro to Filtering and Ordering Data Challenges filter , sql , intro , order	320	1021	February 27, 2025

177|SQL – Intro to Join Statements

Related topics