SQL Execution Order Internals: Why WHERE Fails on Aliases but ORDER BY Succeeds

Ever wondered why this query fails in SQL? SELECT department_id, COUNT(*) AS emp_count FROM...