Week 02 SQL Part 2 - Code-the-Dream-School/rails-guidebook GitHub Wiki

Week Topic Learning Objectives Key Resources
2 SQL Part 2
  • Learn to do SELECT queries that aggregate data: sums, averages, etc.
  • Learn how the SELECT query is processed: order of operations
  • SQL for adding, modifying, and deleting entries
  • SQL for managing database schema: creating and modifying tables
 Lesson Materials

Coding Assignment

Assignment Rubric

Question 1: Retrieve the ProductName and TotalCount for all orders for a product.

SELECT Products.ProductName, SUM(OrderDetails.Quantity) AS TotalCount 
FROM OrderDetails 
JOIN Products ON OrderDetails.ProductID = Products.ProductID 
GROUP BY Products.ProductID 
ORDER BY Products.ProductName;

Question 2: Retrieve the customer name and OrderDetailsID for all the OrderDetails for each customer, ordered by customer name.

SELECT Customers.CustomerName, OrderDetails.OrderDetailsID 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
ORDER BY Customers.CustomerName;

Question 3: Extend the previous query to join the Products table, including customer name, product name, and total quantity of that product ordered.

SELECT Customers.CustomerName, Products.ProductName, SUM(OrderDetails.Quantity) AS TotalQuantity 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
JOIN Products ON OrderDetails.ProductID = Products.ProductID 
GROUP BY Customers.CustomerID, Products.ProductID 
ORDER BY Customers.CustomerName;

Question 4: Extend the previous query to include total price as well as customer name, product name, and total quantity.

SELECT Customers.CustomerName, Products.ProductName, SUM(OrderDetails.Quantity) AS TotalQuantity, 
       (SUM(OrderDetails.Quantity) * Products.Price) AS TotalPrice 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
JOIN Products ON OrderDetails.ProductID = Products.ProductID 
GROUP BY Customers.CustomerID, Products.ProductID 
ORDER BY Customers.CustomerName;

Question 5: Add a new Order to the Orders table.

INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, ShipperID) 
VALUES (1001, 76, 5, '2024-07-23', 1);

Question 6: Add three OrderDetails specifying the ID of the new Order created.

INSERT INTO OrderDetails (OrderDetailsID, OrderID, ProductID, Quantity, UnitPrice) 
VALUES (5001, 1001, 1, 10, 5.00), 
       (5002, 1001, 2, 15, 7.50), 
       (5003, 1001, 3, 5, 10.00);

Question 7: Delete the third of the OrderDetails just added.

DELETE FROM OrderDetails WHERE OrderDetailsID = 5003;

Question 8: Update the Products table, increasing the price by 0.50 for every product with a price less than $20.

UPDATE Products 
SET Price = Price + 0.50 
WHERE Price < 20;

Question 9: Try to delete Customer 76. What happens? What would you have to delete to delete this customer?

Explanation:

  • Attempting to delete Customer 76 will fail due to foreign key constraints.
  • You need to delete related records in OrderDetails and Orders tables before deleting the customer.

Delete from OrderDetails:

DELETE FROM OrderDetails WHERE OrderID IN (SELECT OrderID FROM Orders WHERE CustomerID = 76);

Delete from Orders:

DELETE FROM Orders WHERE CustomerID = 76;

Delete from Customers:

DELETE FROM Customers WHERE CustomerID = 76;
⚠️ **GitHub.com Fallback** ⚠️