177|SQL – Intro to Join Statements

[Cannon_Hamilton]

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 i.order_id, i.payable_amount
FROM Invoice AS i;

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;

[Rylee_Schriever]

20 minutes
median
Enjoyed this challenge!

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

3. 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 Invoice.vendor_id=Vendor.vendor_id)
;

[Nate_Hansen]

Time to complete: 25 min
Rating: Medium
Comments: FUN!

1. SELECT Orders.order_id, first_name, 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 Inventory
   Inner Join Vendor ON Inventory.preferred_vendor = Vendor.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;

[Gavin_Ross]

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

[Katie_Kim]

Time to complete: 15 Minutes
Difficulty: Easy

Solutions:

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

[Faith_Bangerter]

Time to complete: 25 Minutes
Difficulty: Beginner

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

[Lilia_Fisher]

Time to complete: 15
Rating: Intermediate
Interesting to see when you can use Inner join and Left join, etc.

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

[Jacob_Sumsion]

Time to complete: 20 mins
rating: Intermediate

Solutions:

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

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

Q3
SELECT o.order_id, inv.payable_amount
FROM Orders AS o
INNER JOIN Invoice AS inv ON o.order_id = inv.order_id;

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;

[Ian_Bonine]

Time to complete: 20 Minutes
Difficulty: medium
Notes: Good problems

1, SELECT O.order_id, E.first_name, E.last_name
FROM Employee AS E
INNER JOIN Orders AS O ON E.employee_id = O.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 Orders.order_id, Invoice.payable_amount
FROM Orders 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;

[Connor_Smith]

Time to Complete: 15 minutes
Difficulty: Easy
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
Right 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 Right Join Vendor on Inventory.preferred_vendor=Vendor.vendor_id)
Right Join Invoice on Vendor.vendor_id=Invoice.vendor_id;

[Katelyn_Hamilton]

Time to complete: 20 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 Inventory i
   LEFT JOIN Vendor 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 i INNER JOIN Vendor v ON i.preferred_vendor=v.vendor_id)
   INNER JOIN Invoice inv ON v.vendor_id=inv.vendor_id;

[Andrew_Wells]

Time to complete: 25 minutes
Rating: moderate
Solutions:
1.
SELECT Orders.order_id, Employee.first_name, Employee.last_name
FROM Orders
INNER JOIN Employee ON Orders.emp_order_creation = 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;

[Rachel_Lopez]

Time to Complete: ~20 Minutes
Rating: Intermediate

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

[Molly_Pratt]

Time to Complete: 15 minutes
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 Inventory as i LEFT JOIN Vendor as v ON i.preferred_vendor=v.vendor_id;

3: SELECT iv.order_id, iv.payable_amount
FROM Invoice AS iv;

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;

[Tyson_Yee]

Time to Complete: 15 minutes
Rating: Beginner
Notes: Enjoyed the challenge!
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_id, 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;

[Chris_Sosa]

Time to complete: 10 minutes
Difficulty: Beginner

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 i.order_id, i.payable_amount
   FROM Invoice as i;
   4.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;

[Hailey_Smith]

Time: 25 minutes
Difficulty: Medium
1:
SELECT order_id, first_name, last_name
FROM Employee e
INNER JOIN Orders o ON e.employee_id = o.emp_order_creation_id;

2:
SELECT company_name, inventory_name, 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 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;

[Preston_Dudley]

Time to complete: 15 minutes
Difficulty: Beginner
Notes: Just forgot about the parenthesis in the last one

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 vendor v
INNER JOIN Inventory i ON v.vendor_id = i.preferred_vendor;

Q3:
SELECT order_id, payable_amount
FROM Invoice;

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

[Charlotte_Owsley]

Time to complete 25 minutes
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
INNER JOIN Vendor AS v ON i.preferred_vendor = v.vendor_ID;

Q3
SELECT o.order_id, i.payable_amount
FROM Orders AS o LEFT JOIN Invoice AS i ON o.order_id = i.order_id;

Q4
SELECT
i.inventory_name,
v.company_name,
inv.emp_received_id,
inv.invoice_receipt_date
FROM (((Invoice AS inv
INNER JOIN Orders AS o ON inv.order_id = o.order_id)
INNER JOIN OrderDetail AS od ON o.order_id = od.order_id)
INNER JOIN Inventory AS i ON od.inventory_id = i.inventory_id)
INNER JOIN Vendor AS v ON inv.vendor_id = v.vendor_id
WHERE i.preferred_vendor = v.vendor_id;

[Kylie_Eaquinto]

Time to complete: 20 Minutes
Difficulty: Moderate
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_name, company_name, emp_received_id, 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);