177|SQL – Intro to Join Statements

[Genevieve_Nichols]

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

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

Q4: SELECT inve.[inventory_name], v.[company_name], invo.[emp_received_id], invo.[invoice_receipt_date]
FROM (Inventory AS inve INNER JOIN Vendor AS v ON inve.[preferred_vendor] = v.[vendor_id]) INNER JOIN Invoice AS invo ON v.[vendor_id] = invo.[vendor_id];

[Josh_Tanner]

Time to Complete: 15 Minutes
Difficulty: Moderate
Notes: Great way to help me remember the differences between the JOINs!
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;

[Nolan_Ward]

Time to complete: 20
Difficulty: Easy

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 company_name, inventory_name, std_order_qty
FROM Vendor AS V INNER JOIN Inventory AS 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 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;

[Andrew_Benson]

Time to Complete: 15 minutes
Difficulty: Easy
Solutions:
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 i
INNER JOIN Vendor v ON i.preferred_vendor = v.vendor_id
3.
SELECT order_id, payable_amount
FROM Invoice
4.
SELECT inventory_name, company_name, emp_received_id, 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)

[Clyn_Hansen]

Time to Complete: 10 Mins
Rating: Beginner

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

[Liam_Smith]

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

[James_Lloyd]

Time to complete: 25 mins
Difficulty: Medium
Solutions: →

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

Select v.company_name, i.inventory_name, v.std_order_qty
From Vendor v
Inner Join Inventory i On i.preferred_vendor = v.vendor_id

Select order_id, payable_amount
From Invoice

Select i.inventory_name, v.company_name, n.emp_received_id, n.invoice_receipt_date
From Inventory i
Inner Join Vendor v On v.vendor_id = i.preferred_vendor
Inner Join Invoice n On n.vendor_id = v.vendor_id

[Moa_Redford]

SELECT order_id, first_name, last_name
FROM Order
JOIN order ON employee me from Orders.emp_order_creation_id=Employee.Employee_ID ;

SELECT company_name, inventory_name, std_order_qty
FROM Inventory
JOIN Inventory ON vendor preferred_vendor = vendor_id ;

SELECT order_id, payable_amount
FROM Invoice ;

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;

[Samantha_Cassell]

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

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

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;

[Jackson_Bond]

Time to complete: 15
Level of difficulty: Medium
Good practice for the different types of joins

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

[Elizabeth_Bisel]

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

[jacksonbutler10]

Time to Complete: 15 minutes
Difficulty: Easy
Solution: See Below

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 order_id, payable_amount
FROM Invoice

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

[Zachary_Kuta]

Time to Complete: 20 minutes
Difficulty: Easy

1. SELECT order_id, first_name, last_name
   FROM Employee
   Inner Join Orders ON Employee.Employee_ID=Orders.emp_order_creation_id
   2.SELECT company_name, inventory_name, 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_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 Vendor.vendor_id=Invoice.vendor_id

[Coleman_Halls]

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

[Mackay_Roberts]

Time to Complete: 20 minutes
Difficulty: Medium

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

[Abigail_Jones]

Time to Complete: 14 minutes
Difficulty: Easy
Comments: None
Solution:

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

[Anela_Zamarron]

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

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

Query 3
Select order_id, payable_amount
From Invoice;

Query 4
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 = inv.vendor_id;

[Cannon_Harmer]

Time 20 minutes
Difficulty: Moderate
Solution:

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

[Claire_Walters]

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

[Elizabeth_Brown]

Time to complete: 10 minutes
Rating: Beginner
Solutions:

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

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

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