DAY 2: SQL - SELECT & Aggregates

Exercise 1: Your First SELECT Query - View All Data

Step-by-Step Instructions:

1. In the SQL query editor, type exactly: SELECT * FROM Orders;
2. Click the "Execute" button (▶️) or press F5
3. You should see ALL your data displayed
4. Now try: SELECT * FROM Orders LIMIT 20; to see only 20 rows

🔍 What does this mean?

• SELECT = "Show me..."
• * = "...all columns"
• FROM Orders = "...from the Orders table"
• LIMIT 20 = "...but only 20 rows"

💡 Tips:

• Always end SQL statements with a semicolon ;
• SQL keywords (SELECT, FROM) are case-insensitive, but UPPERCASE is conventional
• Use LIMIT when exploring large datasets to avoid overwhelming output

✅ Expected Result: A table showing all columns and rows (or 20 rows if you used LIMIT)

Exercise 2: SELECT Specific Columns

Step-by-Step Instructions:

1. Type this query:

   SELECT OrderID, CustomerName, Sales, Profit
   FROM Orders
   LIMIT 20;

2. Click Execute (▶️)
3. Notice how you only see the 4 columns you requested!

Now try selecting different columns:

SELECT ProductName, Quantity, Discount
FROM Orders
LIMIT 15;

💡 Tips:

• List column names separated by commas
• Order of columns in SELECT determines display order
• Use multi-line formatting for readability (as shown above)

✅ Expected Result: Only the columns you specified appear in the results

Exercise 3: Using DISTINCT to Find Unique Values

Step-by-Step Instructions:

1. First, see ALL regions (with duplicates):

   SELECT Region FROM Orders;

   This shows thousands of rows because each order repeats its region.
2. Now, get UNIQUE regions only:

   SELECT DISTINCT Region FROM Orders;

3. You should see exactly 4 unique regions: Central, East, South, West

Try finding other unique values:

SELECT DISTINCT Category FROM Orders;

SELECT DISTINCT ShipMode FROM Orders;

💡 Tips:

• DISTINCT removes duplicate values from results
• Very useful for finding all unique categories, regions, products, etc.
• Similar to Excel's "Remove Duplicates" feature

✅ Expected Result: A short list of unique values (4 regions, 3 categories, etc.)

Exercise 4: Aggregate Functions - SUM, AVG, COUNT, MAX, MIN

🎯 This is the SQL equivalent of Day 1's Excel functions!

Step-by-Step Instructions:

1. Calculate Total Sales (like Excel's =SUM()):

   SELECT SUM(Sales) AS TotalSales
   FROM Orders;

   The AS TotalSales renames the output column for clarity.
2. Find Average Sales (like Excel's =AVERAGE()):

   SELECT AVG(Sales) AS AverageSales
   FROM Orders;

3. Count Total Orders (like Excel's =COUNT()):

   SELECT COUNT(*) AS TotalOrders
   FROM Orders;

   COUNT(*) counts all rows. COUNT(ColumnName) counts non-null values.
4. Find Maximum Sale (like Excel's =MAX()):

   SELECT MAX(Sales) AS HighestSale
   FROM Orders;

5. Find Minimum Discount (like Excel's =MIN()):

   SELECT MIN(Discount) AS LowestDiscount
   FROM Orders;

💡 Pro Tip - Combine multiple aggregates in one query:

SELECT
    SUM(Sales) AS TotalSales,
    AVG(Sales) AS AvgSales,
    COUNT(*) AS TotalOrders,
    MAX(Sales) AS HighestSale,
    MIN(Sales) AS LowestSale
FROM Orders;

✅ Expected Result: Single row with calculated aggregate values (just like Excel formulas, but in one query!)

Exercise 5: ORDER BY - Sorting Your Results

Step-by-Step Instructions:

1. Sort by Sales (lowest to highest):

   SELECT ProductName, Sales
   FROM Orders
   ORDER BY Sales ASC
   LIMIT 10;

   ASC = Ascending (smallest first)
2. Sort by Sales (highest to lowest):

   SELECT ProductName, Sales
   FROM Orders
   ORDER BY Sales DESC
   LIMIT 10;

   DESC = Descending (largest first)
3. Sort by multiple columns:

   SELECT Region, Category, Sales
   FROM Orders
   ORDER BY Region ASC, Sales DESC;

   This sorts by Region first (A-Z), then within each region, sorts by Sales (high to low)

💡 Tips:

• ORDER BY always comes at the end of your query (before LIMIT)
• ASC is default - you can omit it: ORDER BY Sales = ORDER BY Sales ASC
• You can sort by columns not in your SELECT list

✅ Expected Result: Data sorted in the order you specified

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Write basic SELECT queries to retrieve data
• Select specific columns or all columns (*)
• Use DISTINCT to find unique values
• Apply aggregate functions: SUM, AVG, COUNT, MAX, MIN
• Sort results with ORDER BY (ASC/DESC)
• See the parallel between SQL and Excel functions

Compare with Day 1: Notice how SELECT SUM(Sales) does the same thing as Excel's =SUM(D:D)? SQL is just a different way to ask the same questions!

Take a 5-minute break, then move on to Independent Practice!