Column aliases make query results readable for applications and reports. Expressions calculate derived values like total_price = quantity * unit_price, full_name = first_name || last_name, or discount_amount = price * 0.15. Used in every API response, dashboard, and data export.

API Response Formatting

A React dashboard expects specific JSON field names: customerId, fullName, annualSalary, departmentName. The database columns are customer_id, first_name, last_name, salary, dept_name. Aliases bridge the gap.

SELECT

c.customer_id AS "customerId",

CONCAT(c.first_name, ' ', c.last_name) AS "fullName",

e.salary * 12 AS "annualSalary",

d.name AS "departmentName"

FROM customers c

JOIN employees e ON c.account_manager_id = e.id

JOIN departments d ON e.department_id = d.id

WHERE c.status = 'active';

Frontend applications consume exact field names. Aliases let SQL match any naming convention (camelCase, PascalCase, snake_case) without renaming database columns or adding application-layer transformation code.

All

Financial Reporting ETL Pipeline

Monthly finance report requires line item totals, discount amounts, tax, and commission — all calculated from raw order data with proper rounding and audit fields.

SELECT

oi.order_id,

CONCAT(c.first_name, ' ', c.last_name) AS customer_name,

p.product_name,

oi.quantity,

oi.unit_price,

ROUND(oi.quantity * oi.unit_price, 2) AS subtotal,

ROUND(oi.quantity * oi.unit_price * oi.discount_pct, 2) AS discount_amount,

ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct), 2) AS net_amount,

ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct) * p.tax_rate, 2) AS tax_amount,

ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct) * (1 + p.tax_rate), 2) AS final_total

FROM order_items oi

JOIN orders o ON oi.order_id = o.id

JOIN customers c ON o.customer_id = c.id

JOIN products p ON oi.product_id = p.id

WHERE o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01';

Each calculation step is separately aliased for auditability — finance teams can verify subtotal, discount, tax, and final columns independently. ROUND prevents floating-point errors in financial totals. SARGable date range uses index on order_date.

All

Performance Monitoring — SARGable Rewrite

Production dashboard query running on 10M-row events table was taking 8 seconds. Root cause: YEAR() function wrapped the indexed timestamp column. SARGable rewrite cut it to 40ms.

-- Before (8 seconds — full table scan)

SELECT

user_id,

COUNT(*) AS event_count,