Comprehensive Guide to SQL Data Types, Aggregations, and Grouping Operations

Database Environment and Data Types

  • Database Context: Queries are executed against the Northwind database, a fictional retail dataset containing the following tables: customers, orders, order_details, products, categories, and employees.
  • Schema Focus (order_details):
    • Primary Key / Foreign Key attributes: order_id, product_id.
    • Numeric attributes: unit_price, quantity, discount.
  • Numeric Data Types:
    • smallint: A 2-byte integer with lower and upper limits designed historically to conserve database storage. While standard in legacy databases from the 1980s and 1990s, modern hardware offers cheap storage and high compute power, rendering constrained integer types like smallint or tinyint generally unnecessary in new schema designs in favor of standard integer or higher.
    • integer: Standard 4-byte integer.
    • real: A floating-point data type that is inherently imprecise compared to fixed-point numeric types. It should generally be avoided in financial or exact data storage due to precision loss during arithmetic operations.
    • numeric / decimal: Fixed-point, exact numeric data types that are completely equivalent in SQL. Creating a column as numeric or decimal yields the same database type.
  • SQL Arithmetic and Decimal Precision:
    • Performing arithmetic across different numeric types (e.g., multiplying a fixed numeric unit_price by an integer quantity) can return floating-point numbers or unexpected decimal expansions.
    • Data Type Definitions:
    • Scale: Defined in this context as the total number of digits stored across the entire number.
    • Precision: Defined in this context as the total number of digits following the decimal point.
    • For example, a type defined as numeric(10, 2) allocates space for up to 10 total digits (scale=10\text{scale} = 10), with exactly 2 digits designated after the decimal point (precision=2\text{precision} = 2). This allows storing values up to approximately 99,999,999.9999,999,999.99
  • Explicit Type Casting Functions:
    • ANSI SQL Standard (CAST): Works uniformly across MySQL, PostgreSQL, Microsoft SQL Server, Oracle, and SQLite. sql CAST(expression AS numeric(10,2))     
    • PostgreSQL Shorthand Operator (::): Known informally as "bang bang" notation or double-colon casting. Syntactically cleaner shorthand specific to PostgreSQL. sql (expression)::numeric(10,2)     
  • Rounding and Function Nesting (ROUND):
    • ROUND(number, decimal_places) requires a numeric data type input to accept a second precision argument.
    • AVG() on certain numeric types returns a double precision floating-point data type. Passing double precision directly into ROUND(..., 2) triggers an explicit database error because ROUND cannot take a precision length argument for floating-point inputs.
    • To resolve this, the aggregated expression must be cast to numeric before being wrapped by ROUND: sql ROUND(CAST(AVG(freight) AS numeric), 2)     
    • If numeric is declared without explicit scale and precision parameters (e.g., CAST(val AS numeric)), PostgreSQL automatically assigns the most permissive numeric type based on the observed data.
  • Floating-Point Data Types (double precision):
    • Question: What is double precision?
    • Answer: double precision is an IEEE 754 floating-point data type that is inherently imprecise. It is a holdover from legacy computing architectures where storage space was severely constrained. Modern database design mandates using uppercase NUMERIC or DECIMAL for exact calculation integrity.

SQL Aggregation and the GROUP BY Clause

  • Definition: The GROUP BY clause collapses multiple rows sharing identical categorical values into summary rows, allowing summary statistics (such as COUNT, SUM, AVG, MIN, MAX) to be computed per category.
  • The Split-Apply-Combine Pattern:
    1. Split: The database engine divides the dataset into distinct subsets based on unique categorical values in the specified column(s).
    2. Apply: An aggregate transformation function (e.g., SUM, COUNT) is applied independently to each subset.
    3. Combine: The aggregated output from each subset is rolled up into a single output row per unique category.
  • Rules for Column Selection in Aggregations:
    • The Grouping Rule: Every non-aggregated column appearing in the SELECT list must explicitly appear in the GROUP BY clause. Selecting a un-aggregated column alongside an aggregate function without grouping causes a database execution failure (column must appear in the GROUP BY clause or be used in an aggregate function).
    • Modern Syntax Extension (GROUP BY ALL):
    • Recent SQL engine updates introduce GROUP BY ALL, which automatically identifies all non-aggregated columns in the SELECT list and includes them in the grouping clause implicitly, eliminating repetitive column declarations (e.g., replacing GROUP BY a, b, c, d, e with GROUP BY ALL).

Filtering Grouped Data: WHERE vs. HAVING

  • Execution Sequence and Logical SQL Pipeline:
    1. FROM: Identifies and joins source tables.
    2. WHERE: Filters individual rows before grouping occurs.
    3. GROUP BY: Splits filtered rows into categorical groups.
    4. HAVING: Filters summarized groups after aggregation occurs.
    5. SELECT: Evaluates expressions and assigns column aliases.
    6. ORDER BY: Sorts the final output records.
  • Key Differences Between WHERE and HAVING:
    • WHERE Clause: Operates on raw tabular rows prior to aggregation. Aggregate functions (e.g., WHERE COUNT(*) > 10) are strictly prohibited in the WHERE clause and will result in a database error (aggregate functions are not allowed in WHERE).
    • HAVING Clause: Operates on aggregated results post-grouping. Whenever a filter condition evaluates a summary statistic (e.g., HAVING COUNT(*) > 10), it must be placed in the HAVING clause.
  • Incorrect Syntax Example (WHERE with Aggregate):
  -- FAILS: Throws error "aggregate functions are not allowed in WHERE"
  SELECT category_id, COUNT(*) AS product_count
  FROM products
  WHERE COUNT(*) > 10
  GROUP BY category_id;
  ```
- **Correct Syntax Example (`HAVING` with Aggregate)**:

sql -- SUCCEEDS: Correctly filters after grouping SELECT category_id, COUNT() AS product_count FROM products GROUP BY category_id HAVING COUNT() > 10;   ```

Advanced Aggregation: ROLLUP and CUBE

  • Subtotals and Grand Totals:
    • Standard GROUP BY output excludes summary rows for parent hierarchies or grand totals. ROLLUP and CUBE extensions automatically generate subtotal and grand total rows inside summary datasets.
  • GROUP BY ROLLUP:
    • Generates hierarchical subtotals and a grand total.
    • Operates based on order of declaration: ROLLUP(a, b) calculates:
    1. Subtotals for each combination of (a, b).
    2. Higher-level subtotals for each unique value of a (where b is evaluated as NULL).
    3. A grand total row where both a and b evaluate to NULL.
  • GROUP BY CUBE:
    • Calculates every mathematically possible combination of subtotals across all declared dimensions.
    • CUBE(a, b) calculates:
    1. Subtotals for (a, b) combinations.
    2. Subtotals for a alone (where b is NULL).
    3. Subtotals for b alone (where a is NULL).
    4. A grand total where both a and b are NULL.
    • Comparison: GROUP BY CUBE always yields a equal or larger result set than GROUP BY ROLLUP because it incorporates cross-tabulations for non-hierarchical dimensions.
  • Subtotal Labeling with COALESCE:
    • Subtotal and grand total rows generated by ROLLUP or CUBE display NULL for grouped column headers.
    • The COALESCE(expression, replacement) function replaces these NULL instances with explicit labels (e.g., 'Subtotal' or 'Grand Total').

Dialect Differences and Column Alias Scope

  • Referencing Column Aliases in SQL Clauses:
    • Standard SQL rules evaluate SELECT aliases after GROUP BY and HAVING, meaning aliases cannot be referenced in those clauses in strictly compliant engines (e.g., Microsoft SQL Server).
    • PostgreSQL Extension: PostgreSQL (alongside MySQL and SQLite) permits referencing column aliases declared in the SELECT list directly within both GROUP BY and ORDER BY clauses.
    • ANSI Standard Syntax vs. PostgreSQL Shortcut:
    • Standard ANSI syntax: sql SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*) AS total_orders FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY year;       
    • PostgreSQL Alias Shortcut: sql SELECT EXTRACT(YEAR FROM order_date) AS year, COUNT(*) AS total_orders FROM orders GROUP BY year ORDER BY year;       

Code Implementations and Practical Exercises

  • Setup and Environment Commands:

    • Terminal download command: python getLessons.py (fetches sql_queries_02_exercises.sql into the adat153_lessons directory).
    • Terminal directory navigation: cd .. (moves up one level in the directory hierarchy).
    • Database credentials must be stored in database.txt to connect to Northwind.
  • Exercise 1.1: Explicit Type Casting with CAST

    • Task: Return order_id, product_id, and line total (unit_price * quantity) cast to numeric(10,2) using CAST for the first 10 rows.
  SELECT 
      order_id, 
      product_id, 
      CAST((unit_price * quantity) AS numeric(10,2)) AS line_total
  FROM order_details
  LIMIT 10;
  ```
  - *Key Verification Result*: Order ID `10248` returns a `line_total` of `$168.00` (formatted as `168.00`).

- **Exercise 1.2: PostgreSQL Shorthand Type Casting**
  - *Task*: Re-write Exercise 1.1 utilizing PostgreSQL double-colon notation (`::`).

sql SELECT order_id, product_id, (unit_price * quantity)::numeric(10,2) AS line_total FROM order_details LIMIT 10;   ```

  • Exercise 1.3: Date Extraction and Yearly Order Summaries
    • Task: Extract the year from order_date and count the number of orders placed in each year.
  SELECT 
      EXTRACT(YEAR FROM order_date) AS year, 
      COUNT(*) AS total_orders
  FROM orders
  GROUP BY EXTRACT(YEAR FROM order_date)
  ORDER BY year;
  ```
  - *Dataset Output Statistics*:
    - `2022`: 152 orders
    - `2023`: 408 orders
    - `2024`: 270 orders

- **Exercise 2.1: Simple Categorical Aggregation**
  - *Task*: Group products by `category_id`, count total products per category, and sort descending.

sql SELECT category_id, COUNT(*) AS product_count FROM products GROUP BY category_id ORDER BY product_count DESC;   ```

  • Exercise 2.2: Aggregated Filtering with HAVING
    • Task: Filter category counts to return only categories containing strictly more than 10 products.
  SELECT 
      category_id, 
      COUNT(*) AS product_count
  FROM products
  GROUP BY category_id
  HAVING COUNT(*) > 10
  ORDER BY product_count DESC;
  ```

- **Exercise 2.3: Multi-Layer Nested Aggregations with Discount Calculations**
  - *Task*: Compute total revenue per product accounting for discount rates, casting to numeric, and rounding to 2 decimal places.
  - *Revenue Formula Logic*: 
    Line Revenue=unit_price×quantity×(1−discount)\text{Line Revenue} = \text{unit\_price} \times \text{quantity} \times (1 - \text{discount})

sql SELECT product_id, ROUND(SUM(unit_price * quantity * (1 - discount))::numeric, 2) AS revenue FROM order_details GROUP BY product_id;   ```

  • Exercise 2.5: Distinct Aggregations Within Groups
    • Task: Calculate total orders and unique customers handled by each employee.
  SELECT 
      employee_id, 
      COUNT(DISTINCT customer_id) AS distinct_customers, 
      COUNT(*) AS total_orders
  FROM orders
  GROUP BY employee_id;
  ```
  - *Verification Fact*: Employee ID `4` processed `156` total orders spanning `75` distinct customers.

- **Exercise 3.1: Hierarchical Aggregation with `ROLLUP`**
  - *Task*: Compute order counts for cities within the USA, Canada, and Mexico, generating country subtotals and grand totals using `ROLLUP` and `COALESCE`.

sql SELECT COALESCE(ship_country, 'Grand Total') AS ship_country, COALESCE(ship_city, 'Subtotal') AS ship_city, COUNT(*) AS total_orders FROM orders WHERE ship_country IN ('USA', 'Canada', 'Mexico') GROUP BY ROLLUP(ship_country, ship_city) ORDER BY ship_country, ship_city;   ```

  • Output Detail: Returns exactly 20 rows (comprising 16 individual city detail rows, 3 country subtotal rows, and 1 final grand total row).

    • Exercise 3.2: Complete Dimension Permutations with CUBE
  • Task: Re-write Exercise 3.1 utilizing CUBE to cross-tabulate order counts across all country and city permutations. sql SELECT COALESCE(ship_country, 'Grand Total') AS ship_country, COALESCE(ship_city, 'Grand City Summary') AS ship_city, COUNT(*) AS total_orders FROM orders WHERE ship_country IN ('USA', 'Canada', 'Mexico') GROUP BY CUBE(ship_country, ship_city) ORDER BY ship_country, ship_city;   

    • SQL Formatting and Syntax Style Rules:
  • Leading vs. Trailing Commas:

    • Trailing commas (col1, col2,): Commenting out the last line in a SELECT clause leaves an illegal trailing comma before FROM, causing execution errors.
    • Leading commas (, col2): Enables seamless commenting out of individual selection lines without syntax failures. sql -- Safe formatting with leading commas: SELECT order_id , product_id -- , unit_price FROM order_details;