177|SQL – Intro to Join Statements

Challenges
, , ,
203

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 Vendor AS v

INNER JOIN Inventory AS i ON v.vendor_id = i.preferred_vendor;
3:
SELECT o.order_id, i.payable_amount

FROM Orders AS o

INNER JOIN Invoice AS i ON o.vendor_id = i.vendor_id;
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;

204

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

205

Time to Complete: 15 minutes
Difficulty: Medium
Comments: N/A

SELECT o.order_id, e.first_name, e.last_name
FROM Orders as o,
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
JOIN Vendor ON i.preferred_vendor = v.vendor_if

SELECT order_id, payable_amount
FROM Invoice

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

206

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

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

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

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

207

Time: 30 minutes
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
    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;
208
  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;

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

  1. SELECT order_id, payable_amount

FROM Invoice;

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

209

Time: 15 Minutes
Rating: Medium
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 Vendor AS v
INNER JOIN Inventory AS i
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;

210

Time to Complete: 25 mintues
Difficulty: Beginner
Notes: I have to remember when to use the different joins depending on the instructions

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

  1. 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;
211

Time to complete: 18
Difficulty: Beginner

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;

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

Select order_id, payable_amount
from invoice;

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;

212

Time to Complete: 25 minutes
Difficulty: Beginner
Query 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_i;
Query 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;
Query 3: SELECT i.order_id, i.payable_amount
FROM Invoice AS i;
Query 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);

213

Solution
Q1
SELECT Orders.order_id, e.first_name, e.last_name
From Orders
LEFT JOIN Employee AS e ON Orders.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 i.inventory_name, v.company_name, Invoice.emp_received_id, Invoice.invoice_receipt_date
FROM (Inventory AS i INNER JOIN Vendor as v ON i.preferred_vendor=v.vendor_id)
INNER JOIN Invoice ON v.vendor_id=Invoice.vendor_id;

214

Time to Complete: 10 Mins
Rating: Easy

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

215

Time to complete: 25 minutes
Difficulty: Intermediate
Notes:
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, 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;

216

20 Minutes to complete,
Moderate Difficulty
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;

SELECT company_name, inventory_name, std_order_qty
From inventory
inner Join Vendor on inventory.preferred_vendor = vendor.vendor_ID;

SELECT order_id, price_per_unit as payable_amount
From orderdetail;

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

217

Time: 10 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
    INNER JOIN Vendor AS v ON i.preferred_vendor =v.vendor_id
  2. 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)
218

time to complete: 20 minutes
difficulty: easy
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_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 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;

219

Time: 10 minutes
Rating: Beginner

  1. 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
  2. SELECT v.company_name, i.inventory_name, i.std_order_qty
    FROM Vendor as V
    LEFT JOIN Inventory AS i ON i.prefered_vendor = v.vendor_id
  3. SELECT o.order_id, i.payable_amount
    FROM Orders AS o
    LEFT JOIN Invoice AS i ON o.order_id = i.order_id
  4. SELECT inv.inventory_name, v.company_name, i.emp_received_id, i.invoice_receipt_date
    FROM Invoice AS i
    JOIN Vendor as v ON v.vendor_id = i.vendor_id
    JOIN Inventory as inv ON inv.preferred_vendor = v.vendor_id
220

Time to Complete 25
Difficulty Easy
Solution 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 Vendor AS v
INNER JOIN Inventory AS i ON v.vendor_id = i.preferred_vendor

Q3: SELECT o.order_id, inv.payable_amount
FROM (Invoice AS inv INNER JOIN Vendor AS v ON inv.vendor_id = v.vendor_id)
INNER JOIN Orders AS o ON o.vendor_id = v.vendor_id

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

221

Time to complete: 12 minutes
Rating: Easy

SELECT order_id, first_name, last_name
FROM orders as o
INNER JOIN employee as e on o.emp_order_creation_id = e.employee_id;

SELECT company_name, inventory_name, std_order_qty
FROM inventory AS i INNER JOIN vendor AS v ON i.preferred_vendor=v.vendor_id;

SELECT order_id, payable_amount
from invoice

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;

222

Time to complete: 20 min
Difficulty: Median
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 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;