Why can you use a column alias in ORDER BY but not in WHERE?

SQL has a logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Aliases are defined in the SELECT clause. WHERE executes before SELECT, so the alias doesn't exist yet. ORDER BY runs after SELECT, so it can see aliases. This is a fundamental SQL execution model question.

Draw the 9-step execution order on a whiteboard — interviewers love when candidates know this.

What is a non-SARGable predicate and why does it matter?

SARGable = Search ARGument Able. A non-SARGable predicate wraps an indexed column in a function or arithmetic operation, preventing the query engine from using the index. Example: WHERE YEAR(order_date) = 2024 forces a full scan. Fix: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'. This can be the difference between 45ms and 4200ms on a 5M-row table.

Always have a concrete before/after example with execution plan evidence.

How does NULL behave in arithmetic expressions and string concatenation?

NULL propagates through all standard arithmetic: NULL + 5 = NULL, NULL * 100 = NULL. In string concatenation, behavior varies: PostgreSQL/Oracle || propagates NULL (any NULL operand → NULL result). MySQL CONCAT() ignores NULL operands. SQL Server + propagates NULL. The universal fix is COALESCE(column, default_value) to substitute a safe value before the expression.

Mention that CONCAT() in MySQL treats NULL differently than || — this shows cross-engine knowledge.

Can you use a SELECT alias in GROUP BY?

It depends on the engine. MySQL allows GROUP BY annual_salary when annual_salary is a SELECT alias — this is a MySQL extension. PostgreSQL, SQL Server, and Oracle do not allow alias references in GROUP BY — you must repeat the full expression: GROUP BY salary * 12. The portable solution is always to repeat the expression.

This catches candidates who only know MySQL. PostgreSQL strict mode is the SQL standard.

What is operator precedence in SQL expressions and how do you avoid bugs?

SQL arithmetic operator precedence: (1) parentheses, (2) unary minus, (3) *, /, %, (4) +, -. Without parentheses, quantity * unit_price - discount_pct computes as (quantity * unit_price) - discount_pct which subtracts the raw decimal instead of applying a percentage discount. Always use explicit parentheses in multi-step formulas — they cost nothing and prevent subtle calculation bugs.

Give the classic discount formula bug as an example — it is immediately relatable.

What is the difference between a column alias and a table alias?

Column alias: renames a column in SELECT output — SELECT salary * 12 AS annual_salary. Available in ORDER BY, not in WHERE. Table alias: shortens table references in FROM/JOIN — FROM employees e. Required when joining tables with overlapping column names to disambiguate. Table aliases are compile-time only — zero runtime cost. Both use the AS keyword (optional for table aliases too).

Mention that table aliases are required (not optional) when the same table is self-joined.

Write a query that returns employee full name, annual salary, and their salary percentage relative to the department average. Only include employees earning more than the department average.

WITH dept_avg AS (

SELECT department_id, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id

)

SELECT

CONCAT(e.first_name, ' ', e.last_name) AS full_name,

e.salary * 12 AS annual_salary,

ROUND(e.salary / da.avg_salary * 100, 1) AS pct_of_dept_avg

FROM employees e

JOIN dept_avg da ON e.department_id = da.department_id

WHERE e.salary > da.avg_salary

ORDER BY pct_of_dept_avg DESC;

CTE computes department averages first. JOIN links each employee to their department average. CONCAT builds full_name. salary * 12 calculates annual salary with alias. ROUND formats the percentage. WHERE filters using the raw salary column — not the alias — because WHERE runs before SELECT.