数据库连接与子查询习题

--每一个部门的平均薪水--
select deptno, avg(sal) from emp group by deptno




--取得平均薪水大于2000的部门--
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000




--薪水大于1200的雇员按照部门编号分组，分组之后的平均薪水大于1500，查询分组之内的平均工资并按照工资倒序排列--
select avg(sal)
from emp
where sal > 1200
group by deptno
having avg(sal) > 1500
order by deptno




--薪水最多的人的名字--
select ename from emp where sal = (select max(sal) from emp)




--每个部门薪水最高的人名，薪水和部门编号--
select ename, sal, emp.deptno from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.deptno = t.deptno and emp.sal = t.max_sal)




--每个雇员名字及其经理的名字--
select e1.ename, e2.ename 
from emp e1, emp e2
where e1.mgr = e2.empno
(上述方法将连接条件写在了过滤条件中，不合适，故采用SQL1999标准写法如下）
select e1.ename, e2.ename 
from emp e1 
join emp e2 
on (e1.mgr = e2.empno)
（此时会产生BOSS无法显示而只有13行数据的问题，用左外连接解决，同样的有右外连接和全连接）
select e1.ename, e2.ename 
from emp e1 
left join emp e2 
on (e1.mgr = e2.empno)




--每个雇员及其部门名--
select ename, dname from emp cross join dept
（交叉连接写法,得出笛卡尔积结果）
select ename, dname from emp join dept
on (emp.deptno = dept.deptno)
（等值连接）
select ename, dname from emp join dept using(deptno)
（等值连接第二种写法，不推荐）




--每个部门的平均薪水等级--
select deptno, avg_sal, grade from salgrade s
join (select avg(sal) avg_sal, deptno from emp group by deptno) t
on (t.avg_sal between s.losal and s.hisal)
（between小值and大值,否则会报未定义行）




--每个部门平均的薪水等级--
select deptno, avg(grade) from
(select grade, deptno from 
emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
group by deptno




--雇员中哪些是经理人--
select ename from emp where empno in (select distinct mgr from emp)




--不准用组函数，求薪水的最高值（面试题）--
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal))




--求平均薪水最高的部门的部门编号--
select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno) t
where avg_sal = 
(select max(avg_sal) from 
(select avg(sal) avg_sal, deptno from emp group by deptno)
)


select deptno, avg_sal from
(select avg(sal) avg_sal, deptno from emp group by deptno) t
where avg_sal = 
(select max(avg(sal） from emp group by deptno)
(组函数嵌套，最多只能嵌套两层）




--求平均薪水最高的部门的部门名称--
select dname from dept where deptno = 
（
select deptno from
(select avg(sal) avg_sal, deptno from emp group by deptno) t
where avg_sal = 
(select max(avg_sal) from 
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
）


--求平均薪水的等级最低的部门的部门名称--
select dname, t2.deptno, grade, avg_sal from
(
   select deptno, avg_sal, grade from 
      (select deptno, avg(sal) avg_sal from emp group by deptno) t
   join salgrade s on (t.avg_sal between losal and hisal)
) t2
join dept on (t2.deptno = dept.deptno)
where t2.grade =
(
   select min(grade) from 
   (
      select deptno, avg_sal, grade from 
         (select deptno, avg(sal) avg_sal from emp group by deptno) t
      join salgrade s on (t.avg_sal between losal and hisal)
   )
)




视图VIEW的写法
conn sys/tiger as sysbda
grant create table, create view to scott




create view v$_dept_avg_sal_info as
select deptno, avg_sal, grade from
   (select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between losal and hisal)






select dname, t2.deptno, grade, avg_sal from
   v$_dept_avg_sal_info t2
join dept on (t2.deptno = dept.deptno)
where t2.grade =
(
   select min(grade) from v$_dept_avg_sal_info
)




--求部门经理中平均薪水最低的部门名称(思考题）--
select dname 
from dept join
(
      select deptno, avg(sal) avg_sal from emp 
      where empno in (select distinct mgr from emp where mgr is not null)
      group by deptno
) t on (dept.deptno = t.deptno)
where t.avg_sal =  
(
   select min(avg_sal) from
   (
      select deptno, avg(sal) avg_sal from emp 
      where empno in (select distinct mgr from emp where mgr is not null)
      group by deptno
   ) t
)




--求比普通员工最高薪水还要高的经理名称--
select ename from emp 
where empno in (select distinct mgr from emp where mgr is not null)
and sal >
(
   select max(sal) from emp
   where empno not in (select distinct mgr from emp where mgr is not null)
)


--比较效率（面试题）--
select * from emp where deptno = 10 and ename like '%A%';
select * from emp where ename like '%A%' and deptno = 10;
（显然理论上第一个快，不解释）




--求薪水最高的前五名雇员--
select ename, sal
from (select ename, sal from emp order by sal desc) 
where rownum <= 5




--求薪水最高的第六到第十个雇员（必须掌握）--
select ename, sal from
(
   select ename, sal, rownum r 
      from（select ename, sal from emp order by sal desc）
) where r >= 6 and r <= 10
（注意，rownum只能写小于或者小于等于）