TECH **SQL for Data Analysis: SELECT, FROM, WHERE, ORDER BY, LIMIT – Zero-Fluff Study Guide**

SQL for Data Analysis: SELECT, FROM, WHERE, ORDER BY, LIMIT – Zero-Fluff Study Guide

1. What This Is & Why It Matters

You’re a data analyst at a mid-sized e-commerce company. Your boss drops a 10GB CSV file of customer orders from the past year and says: "Find me the top 5 highest-spending customers in California who made purchases in the last 30 days. And I need it in 10 minutes."

If you don’t know SELECT, FROM, WHERE, ORDER BY, and LIMIT cold, you’re either: - Manually filtering rows in Excel (slow, error-prone, and impossible at scale).
- Writing a Python script (overkill for a one-off query).
- Panicking because you don’t know how to extract exactly what you need from the database.

These five clauses are the foundation of SQL for data analysis. They let you: ✅ Extract only the data you need (no more dumping entire tables).
✅ Filter rows to focus on relevant records (e.g., "only California customers").
✅ Sort results meaningfully (e.g., "highest spenders first").
✅ Limit output to avoid overwhelming dashboards or reports.

Real-world impact:
- Performance: A query with WHERE runs 10–100x faster than one without it (databases optimize filtered queries).
- Cost: In cloud databases (BigQuery, Snowflake, Redshift), you pay per byte scanned. SELECT * on a 1TB table could cost $50+ per query.
- Clarity: Stakeholders don’t want 1M rows—they want the top 10 outliers or trends for a specific segment.

2. Core Concepts & Components

SELECT – The "What" Clause

• Definition: Specifies which columns to retrieve from a table.
• Production insight: Always list columns explicitly (SELECT col1, col2) instead of SELECT *. This:
• Reduces query cost (cloud databases charge per byte scanned).
• Improves readability (future you/teammates won’t guess what’s in the output).
• Prevents errors when the table schema changes (e.g., a new column breaks your dashboard).

FROM – The "Where" Clause

• Definition: Identifies the table(s) to query.
• Production insight: In production, tables often have schema prefixes (e.g., analytics.orders instead of just orders). Always check:
• Are you querying the right environment (dev vs. prod)?
• Do you have permissions to access the table?

WHERE – The "Filter" Clause

• Definition: Filters rows based on conditions (e.g., WHERE state = 'CA').
• Production insight:
• Indexing matters: A WHERE clause on an indexed column (e.g., customer_id) runs in milliseconds; on a non-indexed column (e.g., notes), it can take minutes.
• Data quality traps: WHERE status = 'active' might miss rows where status is NULL or 'Active' (case-sensitive databases). Use WHERE status ILIKE 'active' or WHERE status IN ('active', 'Active') to catch variants.

ORDER BY – The "Sort" Clause

• Definition: Sorts results by one or more columns (ascending ASC or descending DESC).
• Production insight:
• Performance cost: Sorting large datasets is CPU-intensive. If you only need the top 10 rows, use LIMIT before ORDER BY (e.g., ORDER BY revenue DESC LIMIT 10).
• Default behavior: Without ASC/DESC, most databases default to ASC (ascending). Always specify to avoid surprises.

LIMIT – The "Cap" Clause

• Definition: Restricts the number of rows returned.
• Production insight:
• Pagination: Use LIMIT 10 OFFSET 20 to fetch rows 21–30 (critical for dashboards).
• Debugging: Always run SELECT * FROM table LIMIT 10 before writing complex queries to verify data structure.

3. Step-by-Step Hands-On: Querying Customer Orders

Prerequisites

• A SQL database (PostgreSQL, MySQL, BigQuery, Snowflake, etc.). For this guide, we’ll use PostgreSQL (free via ElephantSQL or Supabase).
• A table named orders with columns: sql order_id (INT), customer_id (INT), order_date (DATE), total_amount (DECIMAL), state (VARCHAR), status (VARCHAR)
• Sample data (run this to create a test table): ```sql CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2),
  state VARCHAR(2),
  status VARCHAR(20) );

INSERT INTO orders VALUES
(1, 101, '2023-10-01', 150.00, 'CA', 'completed'),
(2, 102, '2023-10-15', 200.00, 'NY', 'completed'),
(3, 101, '2023-11-01', 300.00, 'CA', 'completed'),
(4, 103, '2023-11-10', 50.00, 'CA', 'cancelled'),
(5, 104, '2023-11-20', 500.00, 'TX', 'completed'),
(6, 101, '2023-11-25', 100.00, 'CA', 'completed'); ```

Task: Find the Top 5 Highest-Spending Customers in California (Last 30 Days)

Step 1: Verify the table structure

SELECT * FROM orders LIMIT 5;

Expected output:

 order_id | customer_id | order_date | total_amount | state |  status
----------+-------------+------------+--------------+-------+-----------
        1 |         101 | 2023-10-01 |       150.00 | CA    | completed
        2 |         102 | 2023-10-15 |       200.00 | NY    | completed
        3 |         101 | 2023-11-01 |       300.00 | CA    | completed
        4 |         103 | 2023-11-10 |        50.00 | CA    | cancelled
        5 |         104 | 2023-11-20 |       500.00 | TX    | completed

Step 2: Filter for California customers (WHERE)

SELECT customer_id, total_amount
FROM orders
WHERE state = 'CA';

Output:

 customer_id | total_amount
-------------+--------------
         101 |       150.00
         101 |       300.00
         103 |        50.00

Step 3: Add date filtering (last 30 days)

SELECT customer_id, total_amount
FROM orders
WHERE state = 'CA'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days';

Output (assuming today is 2023-11-26):

 customer_id | total_amount
-------------+--------------
         101 |       300.00
         101 |       100.00
         103 |        50.00

Step 4: Aggregate spending per customer (GROUP BY + SUM)

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE state = 'CA'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;

Output:

 customer_id | total_spent
-------------+-------------
         101 |      400.00
         103 |       50.00

Step 5: Sort by highest spenders (ORDER BY)

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE state = 'CA'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY total_spent DESC;

Output:

 customer_id | total_spent
-------------+-------------
         101 |      400.00
         103 |       50.00

Step 6: Limit to top 5 (LIMIT)

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE state = 'CA'
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;

Final output:

 customer_id | total_spent
-------------+-------------
         101 |      400.00
         103 |       50.00

4. ? Production-Ready Best Practices

Performance

• Index WHERE columns: If you frequently filter by state or order_date, create an index: sql CREATE INDEX idx_orders_state ON orders(state); CREATE INDEX idx_orders_date ON orders(order_date);
• Avoid SELECT *: Always specify columns. Example: ```sql -- Bad (scans all columns, costs more in cloud DBs) SELECT * FROM orders WHERE state = 'CA';

-- Good (only scans needed columns) SELECT customer_id, total_amount FROM orders WHERE state = 'CA'; `` - UseLIMITearly: If you only need the top 10 rows, addLIMITbefore expensive operations likeORDER BYorJOIN`.

Readability & Maintainability

• Column aliases: Use AS for clarity: sql SELECT customer_id AS customer, SUM(total_amount) AS revenue
• Consistent formatting: Align clauses for readability: sql SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE state = 'CA'
AND order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY customer_id ORDER BY total_spent DESC LIMIT 5;
• Comments: Add context for future you/teammates: sql -- Top 5 CA customers by spend (last 30 days) SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE state = 'CA'
AND order_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY customer_id ORDER BY total_spent DESC LIMIT 5;

Data Quality

• Handle NULL values: Use IS NULL or IS NOT NULL: sql WHERE status IS NOT NULL
• Case sensitivity: Use ILIKE (PostgreSQL) or LOWER() for case-insensitive matching: sql WHERE LOWER(state) = 'ca'
• Date ranges: Use BETWEEN for inclusive ranges: sql WHERE order_date BETWEEN '2023-11-01' AND '2023-11-30'

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Basic filtering:
   "Write a query to find all orders from California in November 2023."

2. Answer:
   sql
SELECT * FROM orders
WHERE state = 'CA'
AND order_date BETWEEN '2023-11-01' AND '2023-11-30';

3. Sorting + limiting:
   "Find the 3 most recent orders."

4. Answer:
   sql
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 3;

5. Aggregation + filtering:
   "Calculate the total revenue from completed orders in Texas."

6. Answer:
   sql
SELECT SUM(total_amount) AS revenue
FROM orders
WHERE state = 'TX'
AND status = 'completed';

7. Trap: WHERE vs. HAVING:
   "Find customers who spent more than $100."

8. Wrong: WHERE SUM(total_amount) > 100 (can’t use aggregates in WHERE).
9. Right:
   sql
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 100;

Key Distinctions to Remember

• WHERE vs. HAVING:
• WHERE filters rows before aggregation.
• HAVING filters groups after aggregation.
• ORDER BY default: Ascending (ASC). Always specify DESC if you want descending.
• LIMIT vs. FETCH FIRST: LIMIT is PostgreSQL/MySQL; FETCH FIRST 5 ROWS ONLY is SQL standard (used in Oracle, SQL Server).

7. ? Hands-On Challenge

Challenge:
"Find the top 2 customers by total spend in each state. Return the state, customer ID, and their total spend."

Solution:

SELECT state, customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY state, customer_id
ORDER BY state, total_spent DESC;

Why it works:
- GROUP BY state, customer_id groups data by both state and customer.
- ORDER BY state, total_spent DESC sorts customers within each state by spend (highest first).
- To get only the top 2 per state, you’d need a window function (advanced topic), but this query gives you the raw data to verify.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. SQLZoo – Interactive Tutorial – Hands-on exercises for SELECT, WHERE, etc.
2. PostgreSQL Official Docs – SELECT – Deep dive into syntax.
3. Mode Analytics SQL Tutorial – Real-world examples and challenges.
4. Book: "SQL for Data Analysis" by O’Reilly – Covers advanced filtering and optimization.