Oracle 自主学习笔记（六）

本章主要是一些多表查询的练习

1、select * from emp where ename like '_A%' or ename  like 'A%';
   select * from emp where substr (ename,1,1) = 'A' or substr(ename,2,1) = 'A';  因为查询员工信息所以在 emp 表中检索
   select * from emp where instr(ename,'A') = 1 or instr(ename,'A') = 2;         知道 substr 和 instr 两个函数的用法
   //查询姓名首字母为 "A"或第二个字符为"A"的所有员工信息

2、select * from emp where job!='CLERK' and job!='SALESMAN' and deptno  in (20,30);       两个都不行所以用 and 并且 deptno
   //查询部门20和30中的、岗位不是"CLERK" 或 "SALESMAN"的所有员工信息                      20和30之间 用 in

3、select * from emp where to_char(hiredate, 'yyyy-mm-dd') like '1981%' and sal between 2500 and 3500 and comm is NULL;
   //查询工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息
首先先将 hhiredate 字符串化 用 to_char ，直接 comm is NULL

4、select * from emp where sal > (select avg(sal) from emp);   子查询 先算平均值 直接用 where进行比较   
   //查询比平均员工工资高的员工信息

5、select deptno,dname,loc from dept where deptno in (select deptno from emp group by deptno having avg(sal)> 2000);
   //查询平均工资高于2000的部门信息

查询部门信息，所以主表是部门dept表，用 group by 分组 然后再找出平均分 > 2000 的结果，用having 筛选，此时avg函数计算的
是每个部门的平均分，然后通过找出 deptno 用 in查询部门信息 

6、select loc from dept where deptno in (select deptno from emp where ename = 'WARD');
   select ename,dept.loc from dept,emp where emp.deptno = dept.deptno and ename = 'WARD';
   //查询出WARD的工作所在地

7、select ename,dname,loc from emp,dept where emp.deptno = dept.deptno and emp.sal > (select sal from emp where ename = 'ADAMS');
   //查询出工资比ADAMS高的所有人姓名、部门、所在地。

8、select * from emp where sal =(select min(sal) from (select * from emp order by sal desc )where rownum <= 7);
   //查询工资排名第7的员工信息

9、使用in查询 部门编号 10，20的所有员工
select * from emp where deptno in(10,20);

10、使用like查询所有名字中包括W的员工信息
select * from emp where ename like '%w%';

11、使用like查询所有员工名字中第二子字母为W的员工信息
select * from emp where ename like '_W%';

12、查询所有员工信息并按照部门编号和工资进行排序
select * from emp order by deptno,sal;

13、显示员工工资上浮20%的结果
select ename,sal 原工资,sal*1.2 上涨后的工资 from emp;

nvl(comm,0)函数  如果为空，返回0，如果不为空返回 comm

14、列出工资比smith多得员工
select ename from emp where sal > (select sal from emp where ename = 'SMITH')；

15、列出所有员工对应领导的姓名
select e1.ename,e2.ename 领导姓名 from emp e1 left join emp e2 on e1.mgr = e2.empno;

16、求出某个员工的领导，并要求这些员工的领导薪水高于或等于3000
select distinct emp.ename
  from (select e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno) a
  join emp on emp.ename = a.ename
 where emp.sal >= 3000;

select distinct e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno
where e2.sal >= 3000;

17、列出部门名称，和这些部门的员工信息
select dept.dname,dept.deptno,
emp.empno,emp.ename,emp.job,emp.mgr,emp.hiredate,emp.sal,emp.comm
from dept left join emp on emp.deptno = dept.deptno;

18、列出所有职位为clerk的员工姓名及其部门名称，部门的人数

select emp.ename,a.dname,a.人数 from emp join (select count(*) 人数,dept.dname,dept.depotno from emp join dept on emp.deptno = dept.deptno group by dept.dname,dept.deptno)
a on emp.deptno = a.deptno where emp.job = 'CLERK'；

19、列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，公司的工资等级
select e.ename,d.dname,e.job,b.ename "上级",e.hiredate,e.sal,s.grade
from emp e,emp b,dept d,salgrade s
where e.mgr = b.empno(+) 左外连接          上级领导
and e.deptno = d.deptno                    所在部门
and e.sal > (select avg(sal) from emp)     高于平均工资
and e.sal between s.losal and s.hisal;     工资等级

select e.ename,d.dname,e.job,b.ename "上级"，e.hiredate,e.sal,s.grade from
emp e,emp b,dept d,salgrade s
where e.mgr = b.eptno(+)              关联找出上级领导
and e.deptno = d.deptno               对应员工所在部门
and e.sal > (select avg(sal) from e)   
and e.sal between s.losal and s.hisal;

20、列出与scott从事相同工作的所有员工及部门名称
select ename,dname from emp left join dept on emp.deptno = dept.deptno 左外连接之后不要用还有条件不要用and
where job = (select job from emp where ename = 'SCOTT');

21、列出薪资大于部门30中的任意员工的薪资的所有员工的姓名和薪金
select ename,sal from emp where sal > any(select sal from emp where deptno = 30);30

22、列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称

select ename,dname,sal from emp,dept where emp.deptno = dept.deptno and
sal > (select max(sal) from emp where deptno = 30); 

23、列出每个部门的员工数量，平均工资
select count(*),avg(sal),deptno from emp group by deptno;

24、列出每个部门的员工数量，平均工资和平均服务期限（月）
select deptno,count(*) 员工数量,trunc(avg(sal+nvl(comm,0))) 平均工资,
trunc(avg(sysdate-hiredate)/30) 平均服务期限
from emp group by deptno;

25、列出各种工作的最低工资以及从事工资最低工作的雇员名称

select e.ename,a.* from (select job,min(sal) from emp group by job) a,emp e
where a.sal = e.sal and a.job = e.job;

26、求出部门名称带字符'S'的部门员工，工资合计，部门人数
select d.deptno,e.*,nvl2(comm, sal+comm, sal) 工资合计,c.count 部门人数 from emp e,dept d,
(select deptno, count(deptno) count from emp group by deptno) c  先按照你部门分组，计算各部门人数
where e.deptno(+) = d.deptno
and c.deptno(+) = e.deptno
and dname like '%S%'； 

27、求出部门平均工资以及等级
select d.deptno,d.dname,nvl(a.avg,0) 平均工资,s.grade from
(select deptno,avg(sal) avg from emp group by deptno) a,
dept d,
salgrade s
where a.deptno = d.deptno(+) 
and a.avg between s.losal and s.hisal;

28、求平均工资的等级最低的部门名称
select dname from dept join (select deptno,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t 
join salgrade on (t.avg_sal between salgrade.losal and salgrade.hisal)) t on dept.deptno = t.deptno
where t.grade = (select min(grade) from (select avg(sal) avg_sal from emp group by dept) t join salgrade on (avg_sal between 
salgrade.losal and salgrade.hisal));

29、部门经理人中平均工资最低的部门名称

select dname from (select deptno avg(sal) avg_sal from emp where empno in(select mgr from emp ) group by deptno) t

join dept on t.deptno = dept.deptno

where avg_sal = (select min(avg_sal) from (select avg(sal) avg_sal from emp where empno in (select mgr from emp) group by deptno) t);