SELECT-FROM-WHERE: The SQL Foundation

Core Concept

• Example: Use WHERE to filter the table down to only the rows you want (e.g. 9 rows → 5 rows). Then use SELECT to pick only the columns you need (e.g. from all columns → just 3 columns).

🌊 Remember: SQL FloWS Like Water!

FROM → WHERE → SELECT

Even though you write SELECT first, SQL executes in FloWS order:

1. FROM: Load the table

2. WHERE: Filter rows

3. SELECT: Choose columns and create aliases

💡 Key insight: Aliases (AS) are created in step 3, so you can't use them in step 2 (WHERE)!

Key Rules

1. Execution Order: FROM → WHERE → SELECT (FWS order, not SFW. FloWS like water)

2. Row Filtering: WHERE evaluates each row individually

3. Column Selection: SELECT determines output columns

Basic Query Structure: FloW...S (like water)

-- Get listens with high ratings (above 4.0)
SELECT user_id, song_id, rating    -- Step last: Choose only 3 columns for output
FROM Listens                       -- Step 1: Picks table with 9 rows
WHERE rating > 4.0                 -- Step 2: Filters 9 rows; keeps subset of rows

Why SQL FloWS Like This?

def high_ratings(Listens):
    output = []
    for row in Listens:              # FROM Listens
        if row.rating > 4.0:         # WHERE rating > 4.0  
            output += [row.user_id,  # SELECT user_id,
                       row.song_id,  #        song_id,
                       row.rating]   #        rating
    return output

SELECT * vs Specific Columns

-- Get all columns for user 1's listening history
SELECT * 
FROM Listens 
WHERE user_id = 1

-- Get only user_id and rating for user 1
SELECT user_id, rating 
FROM Listens 
WHERE user_id = 1

WHERE with Multiple Conditions

-- Get user 1's high-rated songs (rating > 4.0)
SELECT user_id, song_id, rating
FROM Listens
WHERE user_id = 1 AND rating > 4.0

-- Get all user 1's listens OR any listen rated > 4.5
SELECT user_id, song_id, rating
FROM Listens
WHERE user_id = 1 OR rating > 4.5

SELECT with Calculations

-- Calculate double rating and categorize by quality
SELECT 
    user_id,
    rating,
    rating * 2 AS double_rating,  -- AS means renaming columns
    CASE   -- CASE/WHEN: Creates conditional logic - like if-then-else in SQL
        WHEN rating >= 4.5 THEN 'Excellent'
        WHEN rating >= 4.0 THEN 'Good'
        ELSE 'Average'
    END AS category
FROM Listens
WHERE rating IS NOT NULL

bugfix: sept24. column header fixed to double_rating

Common Patterns

Pattern 1: Filter and Transform

-- Round ratings between 4.0 and 5.0
SELECT 
    user_id,
    ROUND(rating, 0) AS rounded_rating
FROM Listens
WHERE rating BETWEEN 4.0 AND 5.0

Execution Order Matters

-- FAILS: Alias 'double_rating' doesn't exist in WHERE
SELECT rating * 2 AS double_rating
FROM Listens
WHERE double_rating > 8  -- ERROR!

-- WORKS: Use original expression in WHERE
SELECT rating * 2 AS double_rating
FROM Listens
WHERE rating * 2 > 8

Why It Matters:

Understanding Aliases (AS Keyword)

What is an Alias?

An alias creates a new name for a column in your output using the AS keyword:

SELECT rating AS r           -- Creates alias 'r' for rating column
SELECT rating * 2 AS doubled  -- Creates alias 'doubled' for calculated value

Think of AS as a "rename" that happens at the very end of query execution.

🚨 The Alias Timing Problem

Why this fails:

-- ❌ ERROR: Can't use alias 'r' in WHERE
SELECT rating AS r 
FROM Listens 
WHERE r > 4.0  -- 'r' doesn't exist yet!

The execution timeline:

Step 1: FROM Listens     → TABLE loaded, columns: rating, user_id, song_id...
Step 2: WHERE r > 4.0    → Error! 'r' doesn't exist yet
Step 3: SELECT rating AS r → NOW 'r' alias IS created

The correct way:

-- ✅ WORKS: Use actual column name in WHERE
SELECT rating AS r 
FROM Listens 
WHERE rating > 4.0  -- Use original column name

More Alias Examples

-- ❌ WRONG: Using calculated alias in WHERE
SELECT rating * 2 AS double_rating
FROM Listens
WHERE double_rating > 8  -- Error! 'double_rating' created after WHERE

-- ✅ CORRECT: Repeat the calculation in WHERE
SELECT rating * 2 AS double_rating
FROM Listens
WHERE rating * 2 > 8     -- Calculate directly in WHERE

-- ✅ ALSO CORRECT: Use original column
SELECT rating * 2 AS double_rating
FROM Listens
WHERE rating > 4         -- Use base column for filtering

Quick Rule: Aliases live in SELECT, not WHERE

Common Mistakes Summary