Oracle:

SELECT

select::=
subquery [ for_update_clause ] ;
subquery::=
{ query_block
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery
[ { UNION [ALL] | INTERSECT | MINUS } subquery ]...
| ( subquery )
} [ order_by_clause ]
query_block::=
[ subquery_factoring_clause ]
SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ] ...
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
[ model_clause ]
subquery_factoring_clause::=
WITH
query_name ([c_alias [, c_alias]...]) AS (subquery) [search__clause] [cycle_clause]
[, query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...

Example

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;

SELECT last_name, job_id, salary, department_id
FROM employees
WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30)
ORDER BY last_name;