连接查询

5. select from where join、 on、 group、order、top、having的混合使用

--输出输出姓名中不包含A的所有员工的工资最高的前三名每个员工姓名工资工资等级部门名称
select top 3 "E".ename "员工姓名", "E".sal "员工工资", "S".grade "工资等级", "D".dname "部门名称"
	from emp "E"
	join dept "D"
	on "E".deptno="D".deptno
	join salgrade "S"
	on "E".sal >="S".losal and "E".sal <="S".hisal
	where "E".ename not like '%A%'
	order by "E".sal desc

一般查询格式：
select 
from A
join B
on ...
join C
on ...
where ...
group by ...
having ...
order by .....

6. 习题

1> 求每个员工的姓名 部门编号 薪水 和 薪水等级

		select "E".ename "员工姓名", "E".deptno "部门编号", "E".sal "薪水", "S".grade "薪水等级"
							from emp "E"
							join salgrade "S"
							on "E".sal >="S".losal and "E".sal <="S".hisal;

2> 查找每个部门的编号 该部门所有员工的平均工资 平均工资等级

select "T".deptno, "T".avg_sal, "S".grade
	from
	(
	select "E".deptno, avg(sal) "avg_sal"
		from emp "E"
		group by deptno
	) "T"
	join salgrade "S"
	on "T".avg_sal >="S".losal and "T".avg_sal <="S".hisal; 
--查找每个部门的编号部门名称该部门所有员工的平均工资平均工资等级
select "T".deptno, "D".dname, "T".avg_sal, "S".grade
	from
	(
	select "E".deptno, avg(sal) "avg_sal"
		from emp "E"
		group by deptno
	) "T"
	join salgrade "S"
	on "T".avg_sal >="S".losal and "T".avg_sal <="S".hisal
	join dept "D"
	on "D".deptno="T".deptno

3> 求出emp表中所有领导的姓名

select "E".ename
	from emp "E"
	where empno in (select mgr from emp);
--求出emp表中所有非领导的姓名(oracle中not in 使用null的问题)
select "E".ename
	from emp "E"
	where empno not in (select mgr from emp
				where emp.mgr != null);

4> 求出平均薪水最高的部门的编号和部门的平均工资

                                              select top 1 deptno "部门编号", avg(sal) "部门平均工资"
							from emp 
							group by deptno
							order by avg(sal) desc		

5> 把工资大于所有员工中工资最低的前三个人的姓名 工资 部门编号 部门名称 工资等级输出

select top 3 "T".ename "姓名", "T".sal "工资","T".empno "部门编号","D".dname "部门名称","S".grade "工资等级"
	from (
			select *
				from emp "E"
				where sal> (select min(sal) from emp)
		) "T" 
	join dept "D"
    on "T".deptno = "D".deptno
	join salgrade "S"
	on "T".sal between "S".losal and "S".hisal
	order by T.sal asc

6> 把工资大于的所有员工按部门分组，把部门平均工资大于的最高的前两个部门的部门编号、部门名称、部门平均工资、平均工资等级输出

select "T".deptno "部门编号","D".dname "部门名称","T"."avg_sal" "部门平均工资","S"."grade" "工资等级"
	from dept "D"
	join (
			select top 2 "E".deptno,avg(sal) "avg_sal"
				from emp "E"
				join dept "D"
				on "E".deptno = "D".deptno
				join salgrade "S"
				on "E".sal between "S".losal and "S".hisal
				where "E".sal >1500
				group by "E".deptno
				having avg("E".sal)>2000
				order by avg("E".sal) desc
			) "T"
	on "D".deptno="T".deptno
	join salgrade "S"
	on "T"."avg_sal" between "S".losal and "S".hisal