SQL TL;DR: Logic & Dates (DQL)

Logic, Dates & Casting

/tldr: Implementing conditional rules and temporal analysis.

Conditional Flow Data Transformation NULL Handling

1. Conditional Logic: CASE WHEN

CASE WHEN is SQL's equivalent of an IF/THEN/ELSE statement. It evaluates conditions and returns a corresponding value, making it essential for categorization, bucketing, and data translation.

Example: Categorizing Sales

SELECT
    product_name,
    sales_amount,
    -- Case Statement to create a new category column
    CASE
        WHEN sales_amount > 1000 THEN 'High Value'
        WHEN sales_amount BETWEEN 500 AND 1000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS sales_category,
    -- You can also use aggregate functions with CASE for pivots
    SUM(CASE WHEN region = 'East' THEN sales_amount ELSE 0 END) AS east_sales_sum
FROM
    sales_data
GROUP BY
    product_name, sales_amount; -- Note: GROUP BY is needed here for the aggregation example.

Tip: Every `CASE` must end with `END`. An optional `ELSE` defines the default result if no conditions are met.

2. Handling NULL Values

COALESCE(exp1, exp2, ...)

Returns the first non-NULL expression in a list of arguments. Great for providing fallback values.

SELECT COALESCE(email, phone, 'No Contact Info') AS contact_detail FROM users;

NULLIF(exp1, exp2)

Returns NULL if `exp1` equals `exp2`; otherwise, it returns `exp1`. Useful for avoiding division-by-zero errors.

-- If divisor is 0, the result is NULL instead of error
SELECT numerator / NULLIF(denominator, 0) FROM calculations;

3. Date & Time Manipulation

Dates and timestamps often require specific functions to be analyzed correctly. (Functions may vary slightly by database—e.g., PostgreSQL vs. MySQL vs. SQL Server).

Date Rounding/Grouping (DATE_TRUNC)

Truncates a timestamp to a specified unit (e.g., month, week, day).

-- Aggregates sales by the first day of the month
SELECT DATE_TRUNC('month', transaction_time) AS sale_month, SUM(revenue)
FROM transactions
GROUP BY 1;

Date Arithmetic (DATE_ADD / INTERVAL)

Adds or subtracts a time interval from a date/timestamp.

-- MySQL/PostgreSQL syntax (syntax varies)
SELECT order_date,
       order_date + INTERVAL '30 days' AS delivery_estimate,
       CURRENT_DATE() - order_date AS days_since_order
FROM orders;

Extracting Components (EXTRACT)

Pulls out a specific part of a date/time (e.g., year, hour, day of week).

SELECT EXTRACT(YEAR FROM transaction_time) AS sale_year,
       EXTRACT(HOUR FROM transaction_time) AS sale_hour
FROM transactions;

4. Data Type Conversion (CAST)

CAST is used to explicitly change the data type of an expression. This is crucial when concatenating strings, performing arithmetic, or ensuring correct filtering.

CAST and CONVERT

CAST(expression AS data_type) is the ANSI SQL standard. Some systems (like SQL Server) also support CONVERT.

SELECT
    CAST(revenue AS DECIMAL(10, 2)) AS formatted_revenue,
    CAST(transaction_date AS DATE) AS date_only,
    -- Converting a number to text for concatenation (syntax varies)
    'User ID: ' || CAST(user_id AS VARCHAR) AS user_identifier
FROM
    user_data;

Mastering logic means mastering data presentation.