177|SQL – Intro to Join Statements

[John_Colton]

Time: 15 minutes
Difficulty Beginner

1. Select o.order_id, employee.first_name, employee.last_name
   From Orders o
   Inner Join Employee on o.emp_order_creation_id=Employee.Employee_ID

2. Select vendor.company_name, I.inventory_name, I.std_order_qty
   From Inventory I
   Inner Join Vendor on I.preferred_vendor = vendor.vendor_id

3. Select O.order_id, O.payable_amount
   From OrderDetail O

4. Select inventory.inventory_name, vendor.company_name, invoice.emp_received_id, invoice.invoice_receipt_date
   From (Invoice
   Inner Join Vendor on invoice.vendor_id= vendor.vendor_id)
   Inner Join Inventory on invoice.vendor_id=inventory.preferred_vendor

[Adam_Pendleton]

Time to complete: 15
Rating: 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 = 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 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;

[Will_Snow]

Time: 20
Difficulty: Medium
Q1
SELECT Orders.order_id, Employee.first_name, Employee.last_name
FROM Orders
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
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 (Vendor INNER JOIN Inventory ON Vendor.vendor_id = Inventory.preferred_vendor)
INNER JOIN Invoice ON Vendor.vendor_id = Invoice.vendor_id;

[Ava_Lassiter]

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

[Austin_Birch]

Time to Complete: 20 Mins
Rating: Medium
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 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);

[Christian_Townsend]

Time to complete: 17.489 Minutes
Difficulty: In between easy and medium.
Comments: Why can’t we see the whole table? Really uspetting and sad tbh… Idk why I work so hard.
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_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;

[Tiana_Holt]

Time to complete: 15 minutes
Difficulty: mediu,
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 i

INNER JOIN Vendor v ON i.preferred_vendor = v.vendor_id;
Q3
SELECT SUM(od.qty_ordered * od.price_per_unit) AS payable_amount, o.order_id

FROM Orders o

INNER JOIN OrderDetail od ON o.order_id = od.order_id

GROUP BY o.order_id;
Q4
SELECT Inventory.inventory_name, Vendor.company_name, Invoice.emp_received_id, Invoice.invoice_date as 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;

[Spencer_Hakes]

Time to Complete: 14
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 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 ;

[Tyler_Whitesides]

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

[Eliza_Simister]

Time to complete: 20 mins
Difficulty: Beginner

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

[Lily_Flint]

Time to complete: 10 minutes
Rating: 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 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 (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;

[Knox_Stokes]

Time to Complete: 15
Rating: easy to medium difficulty

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

SELECT order_id, payable_amount
FROM Invoice

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

[Ryan_Garrett]

Time to complete:15 minutes
Level of dificulty:medium
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 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;

[Jessica_Bertolio]

Time to Complete: 25 minutes
Rating: Easy
Solution:

1. 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) LEFT JOIN Invoice ON vendor.vendor_id = invoice.vendor_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 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) LEFT JOIN Invoice ON vendor.vendor_id = invoice.vendor_id;

[Katie_S_Richards]

Time to Complete: 15 min
Difficulty: medium
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, 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;

[Ryan_Muir]

Time: 20 minutes
Difficulty: Medium

Q1.
Select o.order_id, employee.first_name, employee.last_name
From Orders o
Inner Join Employee on o.emp_order_creation_id=Employee.Employee_ID
Q2.
Select vendor.company_name, I.inventory_name, I.std_order_qty
From Inventory I
Inner Join Vendor on I.preferred_vendor = vendor.vendor_id
Q3.
Select O.order_id, O.payable_amount
From OrderDetail O
Q4. Select inventory.inventory_name, vendor.company_name, invoice.emp_received_id, invoice.invoice_receipt_date
From Invoice
Inner Join Vendor on invoice.vendor_id= vendor.vendor_id
Inner Join Inventory on invoice.vendor_id=inventory.preferred_vendor

[Malachi_Mazey]

Time to complete: 20 mins
Difficulty: little harder than easy
Solution:

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

[Colton_Grimshaw]

Time to Complete: 15 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;

[Jerem_Porter]

Time to complete: 20 minutes
Difficulty: Beginner/Intermediate
Notes: Was interesting to have to join 3 tables together!
Q1
SELECT o.order_id, e.first_name, e.last_name

FROM Orders AS o

LEFT 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
LEFT JOIN Inventory AS i 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 inv.inventory_name, v.company_name, i.emp_received_id, i.invoice_receipt_date
FROM (Invoice AS i
INNER JOIN Vendor as v ON v.vendor_id = i.vendor_id)
INNER JOIN Inventory as inv ON inv.preferred_vendor = v.vendor_id

[Aaron_Smith]

Time to complete: 15 minutes
Difficulty: Easy
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
Inner JOIN Vendor as v on i.preferred_vendor = v.vendor_id;

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

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;