Oracle初探（三）

复杂查询，涉及子查询

--1. 多表查询
--1.1 外连接
--1.1.1 左连接
select *
from student s left join course c on s.cid = c.cid;

--1.1.2 右连接
select *
from student s right join course c on s.cid = c.cid;

--1.1.3 全外连接
select *
from student s full join course c on s.cid = c.cid;

--1.2 内连接
select * 
from student s inner join course c on s.cid = c.cid;

--1.3 等值连接
select * 
from student s，course c
where s.cid = c.cid;


--2 练习
--2.1	显示所有员工的姓名ename，部门号deptno和部门名称dname
select emp.ename,dept.deptno,dept.dname
from emp left join dept on emp.deptno = dept.deptno;

--2.2	查询20号部门员工的job和20号部门的loc
select e.ename,e.job,d.loc
from emp e right join dept d on e.deptno = d.deptno
where e.deptno = 20;

--2.3 选择所有有奖金comm的员工的ename , dname , loc
select e.ename,d.dname,d.loc
from emp e full join dept d on e.deptno = d.deptno
where e.comm is not null;

--2.4	选择在DALLAS工作的员工的ename , job , deptno, dname 
select e.ename,e.job,d.deptno,d.dname 
from emp e ,dept d 
where d.loc = 'DALLAS' and  e.deptno = d.deptno;

--2.5	选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno 
select e1.ename employees,e1.deptno Emp#,e2.ename maneger,e2.deptno Mgr# 
from emp e1,emp e2
where e1.mgr = e2.empno;

--2.6 查询各部门员工姓名和他们所在位置
select d.deptno,e.ename,d.loc 
from emp e left join dept d on e.deptno = d.deptno;

--2.7 列出每个部门工作的员工数量，平均工资和平均服务年限
select d.dname,count(e.deptno),round(avg(sal)),avg((months_between(sysdate,e.hiredate)/12))
from emp e,dept d
where e.deptno = d.deptno
group by d.dname;

--2.8 列出于“SCOTT”从事相同工作的所有员工及部门名称
select e.ename,d.dname
from emp e,dept d  
where e.deptno = d.deptno 
and job =(select job from emp where ename = 'SCOTT');

--2.9 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select e1.ename,d.dname,e2.ename,s.grade
from emp e1,emp e2,dept d,salgrade s
where e1.mgr = e2.empno and e1.deptno = d.deptno and e1.sal between s.losal and s.hisal and e1.sal>(select avg(sal)from emp);

--2.10 找出每一个员工自己的名字和经理的名字都找出来
select e1.ename,e2.ename 
from emp e1,emp e2
where e1.mgr = e2.empno;


--3 子查询
--3.1 查询部门名称为ACCOUNTING和RESEARCH的员工信息
select e.*
from emp e,dept d
where e.deptno = d.deptno
and d.dname in('ACCOUNTING','RESEARCH');

--3.2 查询不是经理的员工的信息
select *
from emp
where empno not in (select distinct(mgr) from emp where mgr is not null);

--3.3 查询出所有经理的姓名及所在部门编号
select distinct e1.ename,e1.deptno
from emp e1,emp e2
where e1.empno=e2.mgr
and e1.ename in(select distinct ename from emp);

--3.4 如何显示小于10号部门最低工资的员工
select *
from emp
where sal<all(select min(sal)
from emp
where deptno=10);

--3.5 列出与“SMITH”从事相同工作的所有员工及部门名称
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno
and job=(select job from emp where ename = 'SMITH')
and e.ename<>'SMITH';

--3.6 显示和KING同部门，工资低于FORD的雇员有哪些
select ename,sal
from emp 
where deptno=(select deptno from emp where ename='KING')
and sal<all(select sal from emp where ename='FORD');

--3.7 找出部门30中所有的经理（MANAGER）和部门20中所有办事员（CLERK）
select ename
from emp
where(deptno=30 and job='MANAGER')
or(deptno=20 and job='CLERK');

--3.8 显示每个部门的名称、地点、员工人数以及该部门所有员工的平均薪资.
select d.dname,d.loc,count(e.deptno),avg(e.sal)
from emp e,dept d
where e.deptno = d.deptno
group by d.dname,d.loc;

--3.9 列出薪金高于公司平均薪金的所有员工,薪资,所在部门名称,上级领导姓名,工资等级
select e1.ename,d.dname,e2.ename,s.grade
from emp e1,emp e2,dept d,salgrade s
where e1.mgr = e2.empno 
and e1.deptno = d.deptno 
and e1.sal between s.losal and s.hisal 
and e1.sal>(select avg(sal)from emp);

--3.10 查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资,及工资等级
select d.dname 部门名称,e2.countnum 部门员工人数,e2.avgsal 部门平均工资,e2.minsal 部门最低工资,e1.ename 姓名,s.grade 工资等级
from emp e1,dept d,salgrade s,(select deptno,avg(sal) avgsal,min(sal) minsal,count(*) countnum from emp group by deptno)e2
where e1.deptno = d.deptno
and e1.sal between s.losal and s.hisal
and e2.deptno = d.deptno
and e1.sal=e2.minsal;


--3.11 查询员工“SMITH”所在部门的员工，但不包含“FORD”的员工姓名、入职时间及部门编号
select ename,hiredate,deptno
from emp
where deptno=(select deptno from emp where ename='SMITH')
and ename<>'FORD';

--3.12 查询大于员工平均工资的所有员工，显示编号、姓名、工资，并按照升序排序
select empno,ename,sal
from emp
where sal>all(select avg(sal) from emp);

--3.13 查询员工姓名中包含“U”的员工所在部门编号下的所有员工的编号、姓名。
select empno,ename
from emp
where deptno=(select deptno from emp where ename like '%U%');

--3.14 查询经理“KING”下的所有员工的姓名、工资
select ename,sal
from emp
where mgr=(select empno from emp where ename='KING'); 

--3.15 查询属于“ACCOUNTING”部门下所有的员工的部门编号、员工姓名及工作
select ename,job,deptno
from emp
where deptno=(select deptno from dept where dname='ACCOUNTING');

--3.16 查询员工中包含“U”员工所在部门下的员工工资大于平均工资的员工，显示员工编号、姓名、及工资
select empno,ename,sal
from emp
where sal>all(select avg(sal) from emp)
and deptno=(select deptno from emp where ename like '%U%');