Week 01 SQL Part 1

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