Have you ever written a query that had a redundant subquery?

You can predefine inline views and then just use the alias in your sql statement. The WITH clause precedes a SELECT statement and looks like this:

WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;