177|SQL – Intro to Join Statements

[Maddie_Riley]

Time to complete: 15 minutes
Difficulty: Intermediate

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

[Charlie_Canady]

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

[Morgan_Parker]

Time to complete: 20 min
Rating: 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_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

[Abigail_Simpson]

Time to Complete: 15 minutes
Difficulty: Beginner
Solution:
Query 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;s
Query 2
SELECT Vendor.company_name, Inventory.inventory_name, Inventory.std_order_qty
FROM Inventory LEFT JOIN Vendor ON Inventory.preferred_vendor=Vendor.vendor_id;
Query 3
SELECT order_id,payable_amount
FROM Invoice;
Query 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;

[Steven_Ramirez1]

Time to Complete: 18 Mins

Rating: Beginner

Solutions:

1

SELECT order_id, first_name, 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 Vendor

INNER JOIN Inventory on Vendor.vendor_id = Inventory.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;

[Tyler_Brickey]

Time to complete: 15 minutes
Difficulty: Intermediate
Comments: None

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 (Vendor INNER JOIN Inventory ON Vendor.vendor_id = Inventory.preferred_vendor)

INNER JOIN Invoice ON Vendor.vendor_id = Invoice.vendor_id

[Hunter_Hall]

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

[Kendall_Curtis]

Time to Complete: 15 minutes
Difficulty: Intermediate
Query 1
SELECT o.order.id, e.first_name, e.last_name
FROM Orders AS o
JOIN Employee AS e
ON o.emp_order_creation = e.employee_id;

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

Query 3
SELECT o.order_id, SUM(od.quantity * i.unit_cost) AS payable_amount
FROM Orders AS o
JOIN Order_Details AS or
ON o.order_id = or.order_id
JOIN Inventory AS i
ON or.inventory_id = i.inventory_id
GROUP BY o.order_id;

Quey 4
SELECT i.inventory_name, v.company_name, i.emp_received_id, i.invoice_receipt_date
FROM Inventory AS i
JOIN Vendor AS v
ON i.vendor_id = v.vendor_id
JOIN Invoice AS i
ON i.inventory_id = i.inventory_id;

[Andrew_Dolphin]

Time 10ish mins
Harder one

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 order_id, payable_amount
   From Invoice;
4. SELECT I.Inventory_name, V.company_name, IV.emp_received_id, IV.invoice_receipt_date
   From (Inventory I INNER JOIN Vendor V on v.vendor_ID = i.Preferred_vendor)
   INNER JOIN Invoice IV on IV.Vendor_ID = v.Vendor_ID;

[Lucas_Spillett]

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

[Jackson_Frank]

time to complete: 25 mins
difficulty: medium
no notes

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

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

[Madelyn_Davis]

Time to complete:15min
Rating: Easy

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

[Caden_Anderson]

time to complete 7 min
rating - medium

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;

[Judd_Belnap]

1. SELECT order_id, first_name, last_name
   FROM orders
   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 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;

[aefolsom]

Time to complete: 24mins
Difficulty: Intermediate
Solution:

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 Inventory Left JOIN Vendor on Vendor.vendor_id = inventory.preferred_vendor;
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 invoice.vendor_id = vendor.vendor_id;

[William_Kirk]

Time: 20 Minutes
Difficulty: Intermediate

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, V.company_name, Invoice.emp_received_id, Invoice.invoice_receipt_date
   FROM (Vendor AS V INNER JOIN Inventory ON Vendor.vendor_id=Inventory.preferred_vendor)
   INNER JOIN Invoice ON V.vendor_id=Invoice.vendor_id;

[Cade_Miller]

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 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, invoice.emp_received_id, invoice.invoice_receipt_date
   from (inventory as i inner join vendor as v on v.vendor_id = i.preferred_vendor)
   inner join invoice on v.vendor_id = invoice.vendor_id;

[Isabel_McKinlay]

Time to complete: 15 min
Difficulty: moderate
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 order_id, payable_amount
FROM Invoice
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;

[Jessica_Bischoff]

Time: 20
Difficulty: Moderate
Solutions:
Q1
SELECT o.order_id, e.first_name, e.last_name
FROM Orders o
INNER JOIN Employee e ON o.emp_reviewer_id = e.employee_id;
Q2
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;
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;

[Connor_Roddy]

Time to Complete: 20 minutes
Difficulty: Intermediate
This one was a little tricky but I enjoyed it overall.
Question 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;
Question 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;
Question 3
SELECT order_id, payable_amount
FROM Invoice;
Question 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;