180|SQL – Intro to Filtering and Ordering Data

[McKenna_Hall]

Time to complete:15 minutes
Rating: Intermediate
Notes: Having the solutions is really helpful so that I can check my work!
Solution:

1. SELECT i.inventory_name, i.inventory_id, od.order_id, od.qty_ordered, o.order_sent_date
   FROM (Orders AS o
   INNER JOIN OrderDetail AS od ON o.order_id = od.order_id)
   INNER JOIN Inventory AS i ON od.inventory_id = i.inventory_id
   WHERE i.inventory_id = 27
   ORDER BY o.order_sent_date DESC;

2. SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
   FROM Invoice AS i
   INNER JOIN Employee AS e ON i.emp_received_id = e.employee_id
   WHERE emp_received_id = 18
   ORDER BY i.payable_amount DESC;

[Cannon_Hamilton]

Q1: SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
FROM (Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id=od.inventory_id)
INNER JOIN Orders AS o ON od.order_id=o.order_id
WHERE i.inventory_id= 27
ORDER BY o.order_sent_date DESC;

Q2: SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
FROM Invoice AS i
INNER JOIN Employee AS e ON i.emp_received_id=e.employee_id
WHERE i.emp_received_id= 18
ORDER BY i.payable_amount DESC;

[Meilan_Rhoton]

Time to complete:10 minutes
Rating: Intermediate

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory AS i
   INNER JOIN OrderDetail AS od ON i.inventory_id=od.inventory_id)
   INNER JOIN Orders AS o ON od.order_id=o.order_id
   WHERE i.inventory_id=27
   ORDER BY o.order_sent_date DESC;

2. SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
   FROM Invoice AS i
   RIGHT JOIN Employee AS e ON i.emp_received_id=e.employee_id
   WHERE i.emp_received_id=18
   ORDER BY i.Payable_Amount DESC;

[Rylee_Schriever]

20 minutes
medium

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
   FROM ((Inventory i INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id) INNER JOIN Orders o ON od.order_id=o.order_id)
   WHERE i.inventory_id=27
   ORDER BY o.order_sent_date DESC;
2. SELECT i.invoice_id, i.payable_amount, e.first_name, e.last_name
   FROM Invoice i RIGHT JOIN Employee e ON i.emp_received_id = e.employee_id
   WHERE i.emp_received_id = 18
   ORDER BY i.payable_amount DESC;

[Gavin_Ross]

Time to complete: 10 mins
Rating: Beginner
Solution:

1. SELECT Inventory.inventory_name, Inventory.inventory_id, OrderDetail.order_id, OrderDetail.qty_ordered, Orders.order_sent_date
   FROM (Inventory INNER JOIN OrderDetail ON Inventory.inventory_id = OrderDetail.inventory_id)
   INNER JOIN Orders ON OrderDetail.order_id = Orders.order_id
   WHERE Inventory.inventory_id = 27
   ORDER BY Orders.order_sent_date DESC;

2. SELECT Invoice.invoice_id, Employee.first_name, Employee.last_name, Invoice.payable_amount
   FROM Invoice INNER JOIN Employee ON Invoice.emp_received_id = Employee.employee_id
   WHERE emp_received_id = 18
   ORDER BY Invoice.payable_amount DESC;

[Nate_Hansen]

Time to complete: 20 min
Difficulty: Medium
Comments: Great skill to know

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory AS i INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id) INNER JOIN Orders AS o ON od.order_id = o.order_id
   WHERE i.inventory_id = 27
   ORDER BY Order_sent_date DESC;

2. SELECT inv.invoice_id, E.first_name, E.Last_name, inv.payable_amount
   FROM Invoice AS inv
   RIGHT JOIN Employee AS E ON inv.emp_received_id = E.employee_id
   WHERE E.employee_id = 18
   ORDER BY inv.payable_amount DESC;

[Ian_Bonine]

Time to complete 20
Difficulty: intermediate

1,

SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
FROM Orders AS o
INNER JOIN (Inventory AS i
INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id)
ON o.order_id = od.order_id
WHERE i.inventory_id = 27
ORDER BY o.order_sent_date DESC;

2,
SELECT invoice.invoice_id, Employee.first_name, Employee.last_name, invoice.payable_amount

FROM Employee

INNER JOIN invoice ON Employee.employee_id = invoice.emp_received_id

where invoice.emp_received_id = 18
order by invoice.payable_amount DESC

[Connor_Smith]

Time to Complete: 15 minutes
Difficulty: Easy
1.SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
From (Inventory as i Inner Join OrderDetail as od on i.inventory_id=od.inventory_id)
Inner Join Orders as o on od.order_id=o.order_id
Where i.inventory_id=27
Order by order_sent_date desc;
2.SELECT i.invoice_id, v.first_name, v.last_name, i.payable_amount
From Invoice as i
Inner Join vendor as v on i.vendor_id=v.vendor_id
Where emp_received_id=18
Order by payable_amount desc;

[Andrew_Wells]

Time: 20 Min
Rating: Medium
Solutions:
1.
SELECT Inventory.inventory_name, Inventory.inventory_id, OrderDetails.order_id, OrderDetails.qty_ordered, Orders.order_sent_date
FROM Inventory INNER JOIN OrderDetails ON Inventory.inventory_id = OrderDetail.inventory_id
INNER JOIN Orders ON OrderDetail.order_id = Orders.order_id
WHERE Inventory.inventory_id=27
ORDER BY Orders.orders_sent_date DESC;
2.
SELECT Invoice.invoice_id, Employee.first_name, Employee.last_name, Invoice.payable_amount
FROM Invoice
RIGHT JOIN Employee ON Employee.employee_id = Invoice.emp_received_id
WHERE Invoice.emp_received_id = 18
ORDER BY Invoice.payable_amount DESC;

[Katie_Kim]

Time to complete: 15 minutes
Difficulty: Medium
Solution:

1. SELECT i.inventory_name, i.inventory_id, od.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory i INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id) INNER JOIN Orders o ON od.order_id=o.order_id
   WHERE i.inventory_id=27
   ORDER BY o.order_sent_date DESC;
2. SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount FROM Invoice i INNER JOIN Employee e ON i.emp_received_id=e.employee_id
   WHERE i.emp_received_id=18
   ORDER BY i.payable_amount DESC;

[Katelyn_Hamilton]

Time to complete: 15 minutes
Difficulty: Mid
Solution:

1. SELECT i.inventory_name, i.inventory_id, od.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory i INNER JOIN OrderDetail od ON i.inventory_id=od.inventory_id)
   INNER JOIN Orders o ON od.order_id=o.order_id
   WHERE i.inventory_id = 27
   ORDER BY o.order_sent_date DESC;
2. SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
   FROM Invoice i
   INNER JOIN Employee e ON i.emp_received_id=e.employee_id
   WHERE i.emp_received_id = 18
   ORDER BY i.payable_amount DESC;

[Tyson_Yee]

Time to Complete: 10 minutes
Rating: Beginner
Solutions:

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory AS i INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id)
   INNER JOIN Orders AS o ON od.order_id = o.order_id
   WHERE i.inventory_id = 27
   ORDER BY order_sent_date DESC;

2. SELECT inv.invoice_id, e.first_name, e.last_name, inv.payable_amount
   FROM Invoice AS inv
   INNER JOIN Employee AS e ON inv.emp_received_id = e.employee_id
   WHERE e.employee_id = 18
   ORDER BY inv.payable_amount DESC;

[Chris_Sosa]

Time: 9 Minutes
Difficulty: Beginner

1. SELECT i.inventory_name, i.inventory_id, od.order_id, od.qty_ordered
   FROM (Inventory AS i INNER JOIN Orders AS o ON i.preferred_vendor = o.vendor_id)
   INNER JOIN OrderDetail AS od ON o.order_id = od.order_id
   WHERE i.inventory_id = 27
   ORDER BY od.qty_ordered DESC;
2. SELECT inv.invoice_id, e.first_name, e.last_name, inv.payable_amount
   FROM Employee AS e
   INNER JOIN Invoice AS inv ON e.employee_id = inv.emp_received_id
   WHERE inv.emp_received_id = 18
   ORDER BY inv.payable_amount DESC;

[Molly_Pratt]

Time to Complete: 10 minutes
Difficulty: Easy
Solution:

1: SELECT i.inventory_name, i.inventory_id, od.order_id, od.qty_ordered, o.order_sent_date
FROM (Inventory as i INNER JOIN OrderDetail as od ON i.inventory_id=od.inventory_id) INNER JOIN Orders as o ON od.order_id=o.order_id
WHERE i.inventory_id=27
ORDER BY o.order_sent_date DESC;

2: SELECT iv.invoice_id, iv.payable_amount, e.first_name, e.last_name
FROM Invoice as iv INNER JOIN Employee as e ON iv.emp_received_id=e.employee_id
WHERE e.employee_id=18
ORDER BY iv.payable_amount DESC;

[Rachel_Lopez]

Time to Complete: 20 minutes
Rating: Intermediate
Comments: Took me a while to figure out the nested joins and where in the query to put certain commands

Q1: SELECT Inventory.inventory_name, Inventory.inventory_id, Orders.order_id, OrderDetail.qty_ordered, Orders.order_sent_date
FROM (Inventory INNER JOIN OrderDetail ON Inventory.inventory_id = OrderDetail.inventory_id) INNER JOIN Orders ON OrderDetail.order_id = Orders.order_id
WHERE Inventory.inventory_id = 27
ORDER BY Orders.order_sent_date DESC;

Q2: SELECT Invoice.invoice_id, Employee.first_name, Employee.last_name, Invoice.payable_amount
FROM Invoice INNER JOIN Employee ON Invoice.emp_received_id = Employee.employee_id
WHERE Invoice.emp_received_id = 18
ORDER BY Invoice.payable_amount DESC;

[Hailey_Smith]

Time: 10 minutes
Difficulty: Intermediate

1: SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
FROM (Inventory i INNER JOIN OrderDetail od ON i.inventory_id = od.inventory_id)
INNER JOIN Orders o ON od.order_id = o.order_id
WHERE i.inventory_id = 27
ORDER BY o.order_sent_date DESC;

2: SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
FROM (Invoice i INNER JOIN Employee e ON i.emp_received_id = e.employee_id)
WHERE i.emp_received_id = 18
ORDER BY i.payable_amount DESC;

[Lilia_Fisher]

Time to complete: 15
Rating: Intermediate
Glad to use the WHERE clause.
1.SELECT i.Inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
FROM Orders AS o INNER JOIN (Inventory AS i INNER JOIN OrderDetail AS od ON i.inventory_id = od.inventory_id) ON o.order_id = od.order_id
WHERE i.inventory_id=27
ORDER BY o.order_sent_date ASC;
2. SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
FROM Employee AS e INNER JOIN Invoice AS i ON e.employee_id = i.emp_received_id
WHERE i.emp_received_id=18
ORDER BY i.payable_amount DESC;

[Kate_Munger]

Time to Complete: 12 minutes
Difficulty: Intermediate

1:
SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
FROM (Inventory as i INNER JOIN OrderDetail as od ON i.inventory_id = od.inventory_id)
INNER JOIN Orders as o ON od.order_id = o.order_id
WHERE i.inventory_id = 27
ORDER BY o.order_sent_date DESC;
2:
SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
FROM Invoice as i
INNER JOIN Employee as e ON i.emp_received_id = e.employee_id
WHERE e.employee_id = 18
ORDER BY i.payable_amount DESC;

[Amanda_Garton]

1. SELECT inventory_name, inventory.inventory_id, orderdetail.order_id, qty_ordered, order_sent_date
   FROM (Inventory
   INNER JOIN OrderDetail ON Inventory.inventory_id = OrderDetail.inventory_id)
   INNER JOIN Orders ON OrderDetail.order_id = Orders.order_id
   WHERE inventory.inventory_id = 27
   ORDER BY order_sent_date DESC;
2. SELECT invoice_id, first_name, last_name, payable_amount
   FROM Invoice
   RIGHT JOIN Employee ON invoice.emp_received_id = employee.employee_id
   WHERE emp_received_id = 18
   ORDER BY payable_amount DESC;

[Austin_Smith]

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, o.order_sent_date
   FROM (Inventory as i INNER JOIN OrderDetail as od ON i.inventory_id = od.inventory_id)
   inner Join Orders as o ON od.order_id = o.order_id
   WHERE i.inventory_id = 27
   ORDER BY o.order_sent_date DESC

2.SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount
FROM Invoice as i
right JOIN Employee as e ON i.emp_received_id = e.employee_id
WHERE e.employee_id = 18
ORDER BY i.payable_amount DESC