177|SQL – Intro to Join Statements

[Ava_Egan]

Time to Complete: 10 minutes
Difficulty: Intermediate
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;

[Jane_Cai]

Time to complete: 15 minutes
Difficulty: Beginner
Comment: Simple challenge to remember how to do JOIN statements again

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 Inventory

INNER JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id;
Q3:
SELECT order_id, payable_amount

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

[Yoomin_Jang]

Time to complete: 30 mins
Difficulty: Easy

1. SELECT emp_role_description
   FROM EmployeeRole;
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_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;

[Taina_Carvalho]

Time to complete: 15
Rating: Beginner
Solution:
Q1: 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;

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

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

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

[Layton_Allred]

Time to complete: 25 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
   RIGHT JOIN Inventory AS i ON v.vendor_id = i.preferred_vendor;
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);

[Emma_Hooper]

Time: 18 Minutes
Rating: Between Beginner and Intermediate
Great review on Inner Joins specifically
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_id=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;

[Ethan_Anderson]

Time to complete: 10 minutes
Difficulty: Easy

1. SELECT Orders.order_id, Employee.first_name, Employee.last_name
   FROM Employee INNER JOIN Orders 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 Vendor.vendor_id = Inventory.preferred_vendor;

3. SELECT OrderDetail.order_id, Invoice.payable_amount
   FROM (Orders INNER JOIN OrderDetail ON Orders.order_id = OrderDetail.order_id) INNER JOIN Invoice ON Orders.order_id = Invoice.order_id;

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;

[Kaden_Westerlind]

Time to Complete: 15 minutes
Difficulty: easy
Solution:
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
RIGHT 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
From (Inventory i INNER JOIN vendor v ON v.vendor_id = i.preferred_vendor)
INNER JOIN Invoice inv ON inv.vendor_id = v.vendor_id;

[Meg_Nielsen]

Time to complete: 25 minutes
Difficulty: Beginner

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 Vendor.vendor_id = Inventory.preferred_vendor

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

[Yangshi_Liu]

Time to complete: 30 minutes
Difficulty: Intermediate

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
   LEFT 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;

[Matthew_Pope]

Time to Complete: 20 Minutes
Difficulty: Moderate
Solution:
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 AS i
LEFT JOIN Vendor as v ON i.preferred_vendor = v.vendor_id;
Q3:
SELECT iv.order_id, iv.payable_amount
FROM Invoice AS iv;
Q4:
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;

[Jake_Lewis]

Time to Complete: 10 Minutes
Rating: Easy

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 Invoice.order_id, Invoice.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

[Abigail_Anderson]

Time to complete: 15 Minutes
Level: Beginner

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

[Spencer_Hales]

Time to Complete: 10 minutes
Difficulty: Easy

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

[Ava_Neuenschwander]

Time to complete: 10
Difficulty: Beginner
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;

[Thomas_Ricks]

Time to Complete 30min
Rating: Intermediate
Notes: Enjoyed the challenge.

1. SELECT o.order_id, e.first_name, e.last_name
   FROM Employee AS e
   INNER JOIN Orders AS o ON o.emp_order_creation_id = e.employee_id
   2.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
   3.SELECT order_id, payable_amount
   FROM Invoice
   4.SELECT inv.inventory_name, v.company_name, i.emp_received_id, i.invoice_receipt_date
   FROM ((Inventory as inv INNER JOIN Vendor v ON inv.preferred_vendor = v.vendor_id)
   INNER JOIN Invoice i ON v.vendor_id= i.vendor_id);

[Jake_Rich]

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

[Ethan_Plothow]

Time to complete: 45 mins
Difficulty: Moderate

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
RIGHT JOIN Inventory ON Vendor.vendor_id = Inventory.preferred_vendor;

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

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

[David_Hulme]

Time to complete: 45 mins
Difficulty: Moderate

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
RIGHT JOIN Inventory ON Vendor.vendor_id = Inventory.preferred_vendor;

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

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

[Claire_Campbell]

time to complete: 10 minutes
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 company_name, inventory_name, std_order_qty
   FROM Inventory INNER JOIN vendor ON inventory.preferred_vendor = vendor.vendor_id;
3. SELECT invoice.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;