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, andemployees. - Schema Focus (
order_details):- Primary Key / Foreign Key attributes:
order_id,product_id. - Numeric attributes:
unit_price,quantity,discount.
- Primary Key / Foreign Key attributes:
- 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 likesmallintortinyintgenerally unnecessary in new schema designs in favor of standardintegeror 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 asnumericordecimalyields the same database type.
- SQL Arithmetic and Decimal Precision:
- Performing arithmetic across different numeric types (e.g., multiplying a fixed numeric
unit_priceby an integerquantity) 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 (), with exactly 2 digits designated after the decimal point (). This allows storing values up to approximately
- Performing arithmetic across different numeric types (e.g., multiplying a fixed numeric
- 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)
- ANSI SQL Standard (
- Rounding and Function Nesting (
ROUND):ROUND(number, decimal_places)requires anumericdata type input to accept a second precision argument.AVG()on certain numeric types returns adouble precisionfloating-point data type. Passingdouble precisiondirectly intoROUND(..., 2)triggers an explicit database error becauseROUNDcannot take a precision length argument for floating-point inputs.- To resolve this, the aggregated expression must be cast to
numericbefore being wrapped byROUND:sql ROUND(CAST(AVG(freight) AS numeric), 2) - If
numericis 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 precisionis 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 uppercaseNUMERICorDECIMALfor exact calculation integrity.
- Question: What is
SQL Aggregation and the GROUP BY Clause
- Definition: The
GROUP BYclause collapses multiple rows sharing identical categorical values into summary rows, allowing summary statistics (such asCOUNT,SUM,AVG,MIN,MAX) to be computed per category. - The Split-Apply-Combine Pattern:
- Split: The database engine divides the dataset into distinct subsets based on unique categorical values in the specified column(s).
- Apply: An aggregate transformation function (e.g.,
SUM,COUNT) is applied independently to each subset. - 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
SELECTlist must explicitly appear in theGROUP BYclause. 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 theSELECTlist and includes them in the grouping clause implicitly, eliminating repetitive column declarations (e.g., replacingGROUP BY a, b, c, d, ewithGROUP BY ALL).
- The Grouping Rule: Every non-aggregated column appearing in the
Filtering Grouped Data: WHERE vs. HAVING
- Execution Sequence and Logical SQL Pipeline:
FROM: Identifies and joins source tables.WHERE: Filters individual rows before grouping occurs.GROUP BY: Splits filtered rows into categorical groups.HAVING: Filters summarized groups after aggregation occurs.SELECT: Evaluates expressions and assigns column aliases.ORDER BY: Sorts the final output records.
- Key Differences Between
WHEREandHAVING:WHEREClause: Operates on raw tabular rows prior to aggregation. Aggregate functions (e.g.,WHERE COUNT(*) > 10) are strictly prohibited in theWHEREclause and will result in a database error (aggregate functions are not allowed in WHERE).HAVINGClause: Operates on aggregated results post-grouping. Whenever a filter condition evaluates a summary statistic (e.g.,HAVING COUNT(*) > 10), it must be placed in theHAVINGclause.
- Incorrect Syntax Example (
WHEREwith 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 BYoutput excludes summary rows for parent hierarchies or grand totals.ROLLUPandCUBEextensions automatically generate subtotal and grand total rows inside summary datasets.
- Standard
GROUP BY ROLLUP:- Generates hierarchical subtotals and a grand total.
- Operates based on order of declaration:
ROLLUP(a, b)calculates:
- Subtotals for each combination of
(a, b). - Higher-level subtotals for each unique value of
a(wherebis evaluated asNULL). - A grand total row where both
aandbevaluate toNULL.
GROUP BY CUBE:- Calculates every mathematically possible combination of subtotals across all declared dimensions.
CUBE(a, b)calculates:
- Subtotals for
(a, b)combinations. - Subtotals for
aalone (wherebisNULL). - Subtotals for
balone (whereaisNULL). - A grand total where both
aandbareNULL.
- Comparison:
GROUP BY CUBEalways yields a equal or larger result set thanGROUP BY ROLLUPbecause it incorporates cross-tabulations for non-hierarchical dimensions.
- Subtotal Labeling with
COALESCE:- Subtotal and grand total rows generated by
ROLLUPorCUBEdisplayNULLfor grouped column headers. - The
COALESCE(expression, replacement)function replaces theseNULLinstances with explicit labels (e.g.,'Subtotal'or'Grand Total').
- Subtotal and grand total rows generated by
Dialect Differences and Column Alias Scope
- Referencing Column Aliases in SQL Clauses:
- Standard SQL rules evaluate
SELECTaliases afterGROUP BYandHAVING, 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
SELECTlist directly within bothGROUP BYandORDER BYclauses. - 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;
- Standard SQL rules evaluate
Code Implementations and Practical Exercises
Setup and Environment Commands:
- Terminal download command:
python getLessons.py(fetchessql_queries_02_exercises.sqlinto theadat153_lessonsdirectory). - Terminal directory navigation:
cd ..(moves up one level in the directory hierarchy). - Database credentials must be stored in
database.txtto connect toNorthwind.
- Terminal download command:
Exercise 1.1: Explicit Type Casting with
CAST- Task: Return
order_id,product_id, and line total (unit_price * quantity) cast tonumeric(10,2)usingCASTfor the first 10 rows.
- Task: Return
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_dateand count the number of orders placed in each year.
- Task: Extract the year from
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*:
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
20rows (comprising 16 individual city detail rows, 3 country subtotal rows, and 1 final grand total row).- Exercise 3.2: Complete Dimension Permutations with
CUBE
- Exercise 3.2: Complete Dimension Permutations with
Task: Re-write Exercise 3.1 utilizing
CUBEto 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 aSELECTclause leaves an illegal trailing comma beforeFROM, 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;
- Trailing commas (