SELECT-FROM-WHERE: The SQL Foundation

Basic Query Structure: Execution Order

-- Get listens with high ratings (above 4.0)
SELECT user_id, song_id, rating    -- choose only 3 columns for output
FROM Listens                       -- picks the table (9 rows)
WHERE rating > 4.0                 -- keeps rows where the test is TRUE

1. Execution Order: FROM → WHERE → SELECT. Execution runs FROM, then WHERE, then SELECT.

2. Row Filtering: WHERE evaluates each row individually

3. Column Selection: SELECT determines output columns

Imagine a python function for why: you start with the input, then you write the logic, then finally the return (output). That's the execution order. It's the same idea in SQL. FROM (input), then logic, then SELECT (output).

WHERE with AND and OR

-- AND: both conditions must hold
SELECT user_id, rating
FROM Listens
WHERE user_id = 1 AND rating > 4.0

-- OR: either condition is enough
SELECT user_id, rating
FROM Listens
WHERE user_id = 1 OR rating > 4.0

Understanding Aliases (AS Keyword)

An alias gives a column a new name in the output with AS. But because WHERE runs before SELECT, the alias does not exist yet when WHERE is evaluated, so WHERE cannot use it.

SELECT with Calculations

SELECT can also compute new columns. rating * 2 AS double_rating doubles a value; CASE ... WHEN is SQL's if-then-else.

SELECT
    user_id,
    rating,
    rating * 2 AS double_rating,
    CASE
        WHEN rating >= 4.5 THEN 'Excellent'
        WHEN rating >= 4.0 THEN 'Good'
        ELSE 'Average'
    END AS category
FROM Listens
WHERE rating IS NOT NULL