Chapter 2 Quick Notes: SQL SELECT Expressions, GROUP BY, HAVING

Math expressions in SELECT

  • Basic arithmetic: use expressions like \text{quantity_on_hand} - \text{quantity_on_order} and alias the result, e.g., SELECT quantity_on_hand - quantity_on_order AS mine_diff

  • Combine with ordinary columns: SELECT order_number, quantity_on_hand - quantity_on_order AS mine_diff FROM inventory

  • Mixed expressions: e.g., quantity×price\text{quantity} \times \text{price} to compute extended price, then alias: SELECT order_number, quantity * price AS extended_price FROM order_item

  • Parentheses: use for readability; machine treats them the same, humans benefit from clarity

  • Rules summary: expressions can appear in SELECT and can appear in WHERE clauses as part of conditions

Operator precedence and math rules

  • Order of math operators (PEMDAS): Parentheses, Exponents, Division, Multiplication, Addition, Subtraction

  • SQL big-picture processing order: math first, then comparison operators, then logical operators, then range operators

String expressions

  • String concatenation example: \text{SKU_description} + \text{ 'hello' } to append text

  • Common string helpers: TRIM, RTRIM, LTRIM (trim spaces from ends)

  • The + operator is the typical choice for concatenation in this context

Grouping data with GROUP BY

  • Problem: mixing non-aggregated columns with aggregates requires grouping those columns

  • Example, group by warehouse to sum quantity on hand per warehouse:

    • SELECT warehouse_id, SUM(quantity_on_hand) AS total_qty FROM inventory GROUP BY warehouse_id

  • Important rule: ordinary (non-aggregated) columns in SELECT must be covered by GROUP BY

  • Another example: group by department and count items per department:

    • SELECT department, COUNT(SKU) AS number_of_items FROM catalog_2021 GROUP BY department

  • How many output rows determine grouping needs: if output is one row, no GROUP BY; if output is fewer rows than input, use GROUP BY

HAVING: filtering after aggregation

  • HAVING filters the aggregated results (not the input rows):

    • Example: HAVING COUNT(SKU) > 2

  • WHERE vs HAVING: WHERE filters input before aggregation; HAVING filters after aggregation

  • Aliases in HAVING: you must repeat the full expression (aliases often aren’t recognized there): e.g., HAVING COUNT(SKU) > 2 rather than HAVING number_of_items > 2

Ordering of SQL statements (mnemonic)

  • Order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

  • Memory aid: "Sweaty feet will generate horrible odors" for quick recall

Quick recap and chapter note

  • This covers SELECT with a single table: arithmetic, string expressions, grouping, HAVING

  • Next section extends to multiple tables

  • Use cheat sheets as needed to remember syntax and rules for rapid review