Oracle 自主学习笔记（三）

本篇博客主要练习的是数据库的一些多表查询

一、
  等值 select empno,emp.deptno,sal from emp,dept where emp.deptno = dept.deptno;

  非等值

  自然

二、
内连接：舍弃不匹配的元祖

左外连接：内连接+左边关系中失配的元组（缺少右边关系属性用null）

右外连接：内连接+右边关系中失配的元组（缺少的左边关系属性用null）

全连接：内连接+左边关系中失配的元组+右边关系中失配的元组

起别名   空格 别名

为了连接 n 个表，至少需要 n-1 个连接条件

三、

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

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

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

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

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

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

 

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

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

13、求出某个员工的领导，并要求这些员工的领导薪水高于或等于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 e1.ename from emp e1 left join emp e2 on e1.mgr = e2.empno
where e1.sal >= 3000;

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

15、列出所有职位为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'；

16、列出薪资高于公司平均薪资的所有员工，所在部门，上级领导，公司的工资等级
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;

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

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

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

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

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

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

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

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;

23、求出部门名称带字符'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%'； 

24、求出部门平均工资以及等级
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;

25、求平均工资的等级最低的部门名称
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));

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

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) from emp where empno in (select mgr from emp) group by deptno) t);