'查询每个部门中大于本部门平均工资的员工名单及本部门的平均工资'
SQL> select a.last_name,a.salary,a.department_id,b.salavg
2 from employees a,(select department_id,avg(salary) salavg
3 from employees
4 group by department_id) b
5 where a.department_id=b.department_id
6 and a.salary>b.salavg;

LAST_NAME SALARY DEPARTMENT_ID SALAVG
---------- ---------- ------------- ----------
Hartstein 13000 20 9500
Mourgos 5800 50 3500
Lorentz 42000 60 19000
Zlotkey 10500 80 10033.3333
Abel 11000 80 10033.3333
King 24000 90 19333.3333
Higgins 12000 110 10150

7 rows selected.

'在order by子句中使用子查询的应用'
'雇员表中有部门ID，没有部门名称，现在要按部门名称对雇员进行排序'
SQL>
1 select employee_id,last_name
2 from employees e
3 order by (select department_name from departments d
4 where e.department_id=d.department_id)

EMPLOYEE_ID LAST_NAME
----------- ----------
205 Higgins
206 Gietz
200 Whalen
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
107 Lorentz
201 Hartstein
202 Fay
149 Zlotkey
176 Taylor
174 Abel
124 Mourgos
141 Rajs
142 Davies
143 Matos
144 Vargas
178 Grant

20 rows selected.