Capstone Part 1: Building the Foundation

Laying the Foundation

The SQL Foundation

Building a complex query is like building a house. Before we worry about the final interior design, we must lay the foundation and frame the walls. In SQL, this means establishing our data sources through JOINs and setting our boundaries with WHERE filters.

Welcome to the first part of your Capstone Project! Building a complex SQL query is exactly like building a house. Before you can pick out the paint colors or move in the furniture, you must first lay the foundation. In SQL, this means joining the correct tables and setting the boundaries with filters.

• Joins act as the foundation of a query.
• Filters define the boundaries of the data.
• The Capstone Project starts with a solid base.

The E-commerce Schema

Our Data Sources

We will work with three primary tables:

• Customers: Who is buying?
• Orders: What happened and when?
• Products: What was sold?

To answer our business questions, we will work with three primary tables. First, the Customers table contains identity and location data. Next, the Orders table is our central hub, linking people to products. Finally, the Products table provides the names and prices of our inventory.

• Customers (PK: customer_id)
• Orders (FK: customer_id, product_id)
• Products (PK: product_id)

Chaining Multiple JOINs

The Multi-Table Chain

When you need data from three places, you chain the JOIN statements. Every table needs an ON condition relating it to the previous tables.

SELECT ...
FROM Customers
JOIN Orders 
  ON Customers.id = Orders.id
JOIN Products 
  ON Orders.id = Products.id

When you need data from three different places, you simply chain the JOIN statements. Every time you add a table, you must provide an ON condition. This explains how the new table relates to the tables already in the query chain.

• Chain JOINs sequentially.
• Each JOIN requires an ON condition.
• Order matters: join the bridge table (Orders) to the others.

Applying the Timeframe

Setting Boundaries

Business requests are usually time-bound. Use the WHERE clause to isolate a specific timeframe using comparison operators or BETWEEN.

Business requests are almost always time-bound. We use the WHERE clause to isolate the specific timeframe. You can use standard comparison operators or the BETWEEN keyword to define your start and end dates.

• WHERE comes after all JOINs.
• Use '>' and '<' for date ranges.
• BETWEEN 'date1' AND 'date2' is a cleaner alternative.

Exercise 1: Basic Connection

The First Link

Write a query to retrieve the name from the Customers table and the order_date from the Orders table. Join them using customer_id.

Let's start small. Write a query to retrieve customer names and their order dates. You'll need to join the Customers and Orders tables. Click submit when you're ready for me to check your work.

• Select name and order_date.
• Join Customers and Orders.
• Match on customer_id.

Exercise 2: The Foundation Query

The Full Foundation

Construct a query that joins Customers, Orders, and Products. Select the customer name, the product_name, and the order_date. Filter for orders between '2023-06-01' and '2023-06-30'.

Now, let's build the full foundation. Join all three tables and filter the results for the month of June 2023. This is the exact base you'll need for the final capstone analysis!

• Join three tables.
• Select columns from different tables.
• Apply a date range filter.