177|SQL – Intro to Join Statements

[Ella_Gatherum]

Time to complete: 10 minutes
Difficulty: easy
Solutions:

1. 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;

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

3. SELECT order_id, payable_amount
   FROM Invoice

4. SELECT inventory_name, company_name, emp_received_id, invoice_receipt_date
   FROM (Invoice i INNER JOIN Vendor v ON v.vendor_id = i.vendor_id) INNER JOIN inventory inv ON v.vendor_id = inv.preferred_vendor

[Dayna_Ranta]

Time to Complete: 10 Minutes
Rating: 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 INNER 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, 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;

[Carter_McQuivey]

Time: 20 minutes
Difficulty: Easy
Solutions:

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_id, Inventory.std_order_qty
FROM Vendor
INNER JOIN Inventory ON Vendor.vendor_id=Inventory.preferred_vendor;

Q3:
SELECT Invoice.order_id, Invoice.payable_amount
FROM Invoice;

Q4:
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;

[Tyler_Whitney]

Time to Complete: 20 mins
Difficulty: Easy-Moderate
Comments: Just have to remember the parenthesis
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
   INNER 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)

[Sam_Drummond]

Time: 25 minutes
Difficulty: Medium
Solution:
Q1
SELECT order_id, first_name, last_name
FROM Orders
INNER JOIN Employee On Orders.emp_order_creation_id=Employee.Employee_ID
;
Q2
SELECT company_name, inventory_name, std_order_qty
FROM Inventory
INNER JOIN Vendor On Inventory.preferred_vendor = Vendor.vendor_id;
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;

[Anna_Montgomery]

Time: 20 min
Difficulty: moderate
Solution:
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 AS i LEFT JOIN Vendor AS v ON i.preferred_vendor=v.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;

[Emma_Fromm]

Time to Complete: 20 minutes
Rating: Intermediate

1. Query 1 Solution:
   SELECT order_id, first_name, last_name
   FROM Orders INNER JOIN Employee ON Orders.emp_order_creation_id = Employee.employee_id;
2. Query 2 Solution:
   SELECT company_name, inventory_name, std_order_qty
   FROM Inventory INNER JOIN Vendor ON Inventory.preferred_vendor = Vendor.vendor_id;
3. Query 3 Solution:
   SELECT order_id, payable_amount
   FROM Invoice;
4. Query 4 Solution:
   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;

[Tyson_Burrup]

Time to Complete: 20 minutes
Difficulty: Intermediate
I enjoyed this challenge.
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;

[Tessa_Staley]

Time to complete: 20 min
Difficulty: medium
Solution:

1. SELECT Orders.order_id, Employee.first_name, Employee.last_name
   FROM Orders INNER JOIN Employee ON Orders.emp_order_creation_id=Employee.Employee_ID;

2. SELECT Vendor.company_name, Inventory.inventory_name, Inventory.std_order_qty
   FROM Inventory LEFT 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 (Inventory INNER JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id) INNER JOIN Invoice ON Vendor.vendor_id=Invoice.vendor_id;

[Ashleigh_Layton]

Time to complete: 30 min
Difficulty: Intermediate
Solution:

1. SELECT Orders.order_id, Employee.first_name, Employee.last_name
   FROM Orders
   INNER JOIN Employee ON Orders.emp_order_creation_id = Employee.employee_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 order_id, payable_amount
   FROM Invoice;
4. SELECT Inventory.inventory_id, Vendor.company_name, Invoice.emp_received_id, Invoice.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;

[Tyler_Loose]

Time to complete: 20 minutes
Difficulty: Beginner
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 INNER 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

[Jared_Divelbiss]

Time to Complete: 15
Difficulty: Beginner
Solution:

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 Vendor v
INNER JOIN Inventory 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 v.vendor_id = i.preferred_vendor)
INNER JOIN Invoice AS iv ON v.vendor_id = iv.vendor_id;

[Sydney_Clove]

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

[Adeline_Christensen]

Time to complete: 15 minutes
Difficulty: beginner

1. SELECT o.order_id, e.first_name, e.last_name
   FROM order as o
   INNERJOIN Employee as e ON o.emp_order_creation = e.employee_id

2. SELECT v.company_name, i.inventory_name, i.std_order_qty
   FROM Inventory as i LEFTJOIN 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, o.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

[Collin_Simon]

Time to complete: 20 minutes
Difficulty: Moderate
The tables in the image should show all columns in the table.

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 LEFT JOIN vendor AS v ON I.preferred_vendor=v.vendor_id;

SELECT order_id, payable_amount
From Invoice

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;

[Colter_Sill]

Time to Complete: 15 mins
Difficulty: beginner
Solutions:
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 v.vendor_id = i.preferred_vendor;
3.SELECT inv.order_id, inv.payable_amount
FROM Invoice AS inv;
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;

[Kameron_K_Sakamoto]

Time to Complete: 10min
Difficulty: Beginner
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.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;

[Thomas_Sanders]

Time to Complete: 20 min
Difficulty: Medium

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 Inventory
Inner Join Vendor on Inventory.preferred_vendor = Vendor.vendor_id;
Q3: SELECT order_id, payable_amount
From Invoice;
Q4: SELECT Inventory.inventory_name, Vendor.company_name, Invoice.emp_received_id
From (Inventory Inner Join Vendor on Inventory.preferred_vendor = Vendor.vendor_id)
Inner Join Invoice ON Vendor.vendor_id = Invoice.vendor_id;

[Sophie_Dong]

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;

[Lindsay_Shields]

Time: 20 minutes
Difficulty: easy-moderate

Q1:
SELECT order_id, first_name, last_name
FROM Orders INNER JOIN Employee ON Orders.emp_order_creation_id=Employee.Employee_ID;

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

Q3:
SELECT order_id, (qty_ordered * price_per_unit) AS payable_amount
FROM OrderDetail;

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;