Week 01 SQL Part 1 - Code-the-Dream-School/rails-guidebook GitHub Wiki

Week Topic Learning Objectives Key Resources
1 SQL Part 1
  • Understand what relational databases are
  • Learn what SQL is
  • Understand why this is important to the back-end developer
  • Know the CRUD operations
  • Learn the SQL used in query operations: SELECT, constraints, ordering
  • Understand primary and foreign keys
  • Learn the SQL needed for joins
 Lesson Materials

Coding Assignment

Overview

  • Database Structure:
    • Tables: Customers, Orders, Products, OrderDetails.
  • Ambiguous Column Names:
    • Qualifying column names with table names when doing joins.
    • Using aliases for brevity.

Examples of SQL Queries

Simple join:

SELECT Customers.CustomerID, CustomerName, OrderID 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

With alias:

SELECT c.CustomerID, CustomerName, OrderID 
FROM Customers c 
JOIN Orders o ON c.CustomerID = o.CustomerID;

Compound join:

SELECT o.OrderID, Quantity, ProductName 
FROM Orders o 
JOIN OrderDetails od ON o.OrderID = od.OrderID 
JOIN Products p ON od.ProductID = p.ProductID;

One to many associations:

SELECT CustomerName, COUNT(OrderID) AS OrderCount 
FROM Customers c 
JOIN Orders o ON c.CustomerID = o.CustomerID 
GROUP BY c.CustomerID;

Many to many associations:

SELECT o.OrderID, ProductName, Quantity 
FROM Orders o 
JOIN OrderDetails od ON o.OrderID = od.OrderID 
JOIN Products p ON od.ProductID = p.ProductID 
ORDER BY o.OrderID;

Concepts to Understand

Ambiguous Column Names: Qualifying references to avoid ambiguity. Compound Joins: Joining multiple tables in a single query. One to Many Associations: Foreign keys and their role in relationships. Many to Many Associations: Use of join tables to handle complex relationships.

Assignment Rubric

Sample Student Response for Assignment 1

Question 1: Retrieve the first 10 rows of the Customer table, ordered by CustomerName.

SELECT * FROM Customers ORDER BY CustomerName LIMIT 10;

Question 2: Retrieve the names and prices for all products that cost less than $20.

SELECT ProductName, Price FROM Products WHERE Price < 20;

Question 3: Retrieve all employees whose last name starts with C.

SELECT * FROM Employees WHERE LastName LIKE 'C%';

Question 4: Retrieve the order ID and customer name for all orders where the customer name starts with A.

SELECT Orders.OrderID, Customers.CustomerName 
FROM Orders 
JOIN Customers ON Orders.CustomerID = Customers.CustomerID 
WHERE Customers.CustomerName LIKE 'A%';

Question 5: Retrieve the list of customers, ordered by customer name, along with their order IDs. Include customers without orders.

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

Question 6: Retrieve the list of customer names for customers that have no orders using a left join.

SELECT Customers.CustomerName 
FROM Customers 
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
WHERE Orders.OrderID IS NULL;

Question 7: Retrieve the OrderID and ProductName of every order for a product with Tofu in the name.

SELECT Orders.OrderID, Products.ProductName 
FROM Orders 
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 
JOIN Products ON OrderDetails.ProductID = Products.ProductID 
WHERE Products.ProductName LIKE '%Tofu%';
⚠️ **GitHub.com Fallback** ⚠️