What is a derived table and why must it have an alias?

A derived table is a subquery in the FROM clause that acts as a temporary table. It must have an alias because the outer query needs a name to reference it. The alias is required by SQL standard even if you don't use it.

When should you use a derived table vs a CTE?

Use CTEs for better readability and when you need to reference the result multiple times. Use derived tables for older database compatibility. Performance is usually identical.

Write a query using a derived table to find departments with average salary above company average

SELECT dept_avg.department, dept_avg.avg_salary FROM (SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department) AS dept_avg WHERE dept_avg.avg_salary > (SELECT AVG(salary) FROM employees);

Derived table aggregates by department first, then outer query filters to above company average.