s_dept d
where e.dept_id = d.id group by title,d.id,d.name order by 1 desc
例子54： 求出42部门的平均工资？（只显示平均工资） 例子55：求出42部门的平均工资？（显示部门号、平均 工资）
select dept_id,avg(salary) from s_emp where dept_id =42 GROUP by dept_id
如果select后有组函数，则select后只能出现group by后的字段
或者组函数。
例子56： 求出各部门的平均工资？
（要求显示：部门编号、部门名称（地区名称+部门名称）、平均工资） select d.id,r.name,d.name,avg(salary) from s_emp e, s_dept d,s_region r
where e.dept_id = d.id and d.region_id = r.id group by d.id,r.name,d.name
例子57： 求出平均工资高于2000的员工？
Select first_name,avg(salary) From s_emp
Group by first_name Having avg(salary) >= 2000 十五、
例子58：求出谁的工资最低？
Select first_name,salary from s_emp Where salary=(select min(salary) from S_emp)
子查询：1、放在括号里面的查询
2、先执行子查询，再执行主查询。 例子59： 求出谁的工资是750？ 例子60： 谁和'Smith'做同一个职位？ select first_name,title from s_emp
where title = (select title from s_emp where last_name = 'Smith')
例子61： 哪些人是员工？（子查询） 子查询
Select id,first_name From s_emp
Where id not in(select manager_id from s_emp Where manager_id is not null) Select m.id,m.first_name
From s_emp e right outer join s_emp m on e.manager_id = m.id where e.first_name is null 或select m.id,m.first_name from s_emp e,s_emp m Where e.first_name is null and e.manager_id(+) =m.id
外连接： 子查询：
--- 多列子查询：
例子62：哪些员工的工资和本部门的平均工资一致？ Select dept_id,first_name,salary from s_emp Where (dept_id,salary) in (select dept_id,avg(salary) From s_emp group by dept_id )
例子63：哪些部门的平均工资比32部门的平均工资高？
select dept_id, avg(salary)from s_emp group by dept_id
having avg(salary) > (select avg(salary) from s_emp where dept_id = 32 group by dept_id)
例子64：查询和42部门员工职位相同的所有员工的姓名？
select title,first_name from s_emp where title in (select title from s_emp where dept_id=42)
例子65：找出与Carmen同部门的员工信息
select id,first_name,salary,dept_id from s_emp where dept_id=(select dept_id from s_emp where first_name='Carmen') 例子66：
找出比ben早入职的员工
select first_name,start_date from s_emp
where start_date < (select start_date from s_emp where first_name='Ben')
使用子查询：找出哪些员工在亚洲工作，并打印出全名，部门名，薪资
Select r.name,d.name,e.first_name,
※TopN 查询 1. 工资前五名的员工
Select first_name,salary from (select *from s_emp Order by salary desc)
Where rownum (把符合条件的数据重新编号)<=5 分页 Select
first_name,salary
from
(select
rownum
mynu,first_name,salay from s_emp order by salary desc) Where mynu between 5 and 10