180|SQL – Intro to Filtering and Ordering Data

[Aaron_Greenwood]

Time to complete: 12 mins
Difficulty: Medium
Solution:

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;

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;

[Justin_Day]

Time to complete: 10 minutes
Challenge level: Easy
Solution Below:

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;

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;

[Tyler_Herget]

Time to complete: 20 min
Difficulty: Moderate
Solution:

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 o.order_id = od.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 e.employee_id = i.emp_received_id
   Where e.employee_id = 18
   Order By i.payable_amount DESC

[Zen_Villondo]

Time to complete: 18 minutes
Challenge level: moderate
Notes: not sure as to the significance of right join here

q1
SELECT i.inventory_name, od.inventory_id, o.order_id, qty_ordered, order_sent_date

FROM (OrderDetail AS od INNER JOIN Orders AS o ON od.order_id = o.order_id)

INNER JOIN Inventory AS i ON od.inventory_id = i.inventory_id

WHERE i.inventory_id=27

ORDER BY order_sent_date

q2
SELECT i.invoice_id, e.first_name, e.last_name, i.payable_amount

FROM invoice AS i

RIGHT JOIN employee AS e ON e.employee_id = i.emp_received_id

WHERE i.emp_received_id=18

ORDER BY i.invoice_number DESC

[William_Cole]

Time: 18 minutes
Difficulty: Easy

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_id = 27
   ORDER BY orderdetail.order_sent_date DESC

2. SELECT invoice_id, invoice.first_name, invoice.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

[Faith_Bangerter]

Time to complete: 15 minutes
Difficulty: Easy/Medium

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 i.emp_received_id = 18
   ORDER BY i.payable_amount DESC;

[Preston_Dudley]

Time to complete: 15 minutes
Challenge level: beginner
Solutions
Q1:
SELECT i.inventory_name, i.inventory_id, ord.order_id, ord.qty_ordered, o.order_sent_date
FROM (Inventory i INNER JOIN orderdetail ord ON ord.inventory_id = i.inventory_id)
INNER JOIN orders o ON o.order_id = ord.order_id
WHERE i.Inventory_id = 27
ORDER BY o.order_sent_date;

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

[Audrey_Vengesai]

Time to complete: 15 minutes
Difficulty: moderate
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 Invoice.emp_received_id = 18
   ORDER BY Invoice.payable_amount DESC;

[Finlay_Lofthouse]

Time: 5 min
Difficulty: Easy
Solutions
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 e.employee_id = 18

ORDER BY i.payable_amount DESC;

[Josue_Quilantan]

Time to Complete: 10 minutes
Difficulty: Easy
Solution:

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 = odinventory_id)
INNER JOIN Orders o ON od.order_id = o.order_id
WHERE i.inventory_id = 27
ORDER BY o.order_sent_date DESC

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_recieved_id = 18
ORDER BY i.payable_amount DESC

[Braden_Jones]

Time to Complete: 20 mins
Difficulty: Easy
Solutions:

SELECT inv.inventory_name, inv.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 inv ON od.inventory_id = inv.inventory_id
WHERE inv.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 Employee AS e
   INNER JOIN Invoice AS i ON i.emp_received_id = e.employee_id
   WHERE emp_received_id = 18
   ORDER BY i.payable_amount DESC;

[Jacob_Sumsion]

Time: 10 mins
Difficulty: easy

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;

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 inv.emp_received_id = 18
ORDER BY inv.payable_amount DESC;

[Joshua_Folau]

Time to complete: 10 minutes
Rating: Beginner
Solution:

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

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;

[Spencer_McArthur]

TTC 15 mins
Ranking easy
Solutions
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 o.order_id=od.order_id

WHERE i.inventory_id = 27

ORDER BY o.order_sent_date desc;
q2
SELECT invoice_id, first_name, last_name, 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 payable_amount desc;

[Annecy_Hansen]

Time to Complete: 15 minutes
Difficulty: Easy
Notes: Good practice with where function
Solution: See Below

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

[Ella_Gatherum]

Time to complete: 10 min
Difficulty: easy
Solutions:

1. SELECT i.inventory_name, i.inventory_id, o.order_id, od.qty_ordered, 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 order_sent_date DESC

2. SELECT invoice_id, first_name, last_name, payable_amount
   FROM invoice i INNER JOIN Employee e ON e.employee_id = i.emp_received_id
   WHERE emp_received_id = 18
   ORDER BY payable_amount desc

[Cragun_Bastian]

Time: 25 minutes
Difficulty: Medium

1. 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 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 Invoice.emp_received_id=18
   ORDER BY Invoice.payable_amount DESC;

[Sam_Drummond]

Time: 15
Difficulty: Medium
Solution:
Q1
SELECT inventory_name, od.inventory_id, od.order_id, qty_ordered, 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 od.inventory_id = 27
ORDER BY order_send_date desc;
Q2
SELECT invoice_id, first_name, last_name, 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;

[Tyler_Whitney]

Time to Complete: 20 mins
Difficulty: Easy to Moderate
Solution:

1. SELECT i.inventory_name, i.inventory_id, od.order_id, 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 i.emp_received_id =18
   ORDER BY i.payable_amount DESC

[Anna_Montgomery]

Time to Complete: 20 min
Difficulty: Moderate
Solution:
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;

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;