Oracle常规SQL练习
--1.查询属于领导的人员信息:
select * from emp a where exists (select 1 from emp b where b.mgr = a.empno);
--2.查询不存在员工的部门信息:
select* from dept d where not exists (select 1 from emp e where e.deptno = d.deptno);
--3.查询薪水前三名:
select * from (select *from emp order by sal desc)where rownum<=3;
--4.查询前小于5的记录:
select *from emp where rownum <5;
--5.查询大于5的记录数:
select* from (select e.*,rownum as rn from emp e) where rn >5;
注意:使用rownum需要注意:使用<可以查询结果,使用'>'没有结果,必须使用别名的形式查询'>'才能有结果.
--11.列出工资比smith多得员工
select * from emp a where a.sal>(select b.sal from emp b where b.ename='SMITH')
--12.列出所有员工对应领导的姓名
--(错误)
select a.empno,a.ename,a.mgr,b.ename from emp a,emp b where a.mgr=b.empno;
--正确
select a.empno,a.ename,a.mgr,b.ename from emp a left join emp b on（a.mgr=b.empno）;
--13.求出某个员工的领导，并要求这些员工的领导薪水高于或等于3000
--(错误)
select a.empno,a.ename,a.mgr,b.ename from emp a,emp b where a.mgr=b.empno and a.sal>=3000;
--正确
select a.empno,a.ename,a.mgr,b.ename from emp a left join emp b on（a.mgr=b.empno） where a.sal>=3000;
--14.列出部门名称，和这些部门的员工信息;
select d.deptno,d.dname,e.empno,e.ename from dept d left join emp e on d.deptno=e.deptno;
--哪边有（+）哪边就允许为空
select d.deptno,d.dname,e.empno,e.ename from dept d,emp e where e.deptno(+)=d.deptno;
--15.列出所有职位为clerk的员工姓名及其部门名称，部门的人数
select e.ename,d.dname,(select count(*) from emp a where a.deptno=e.deptno) from emp e left join dept d on e.deptno=d.deptno where e.job='CLERK';
--16.查询各个部门的人数
select d.deptno,d.dname,count(e.deptno) from dept d left join emp e on e.deptno=d.deptno group by d.deptno,d.dname;
--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;
--17列出与SMITH从事相同工作的所有员工及部门名称
select e.*,d.dname from emp e ,dept d where job=(select job from emp where ename='SMITH') and e.deptno=d.deptno(+);
--18列出薪资大于部门30中的任意员工的薪资的所有员工的姓名和薪金
select e.ename,e.sal from emp e where e.sal>（select min(sal) from emp where deptno='30'）;
--19列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称
select e.ename,e.sal from emp e where e.sal>（select max(sal) from emp where deptno='30'）;
--20.列出每个部门的员工数量，平均工资
select count(*),avg(sal),d.deptno,d.dname from emp e,dept d where d.deptno=e.deptno（+） group by d.deptno,d.dname;
--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);
6.4.merge into的用法:
merge into emp a
using (select 33 empno, 'kht' ename from dual) b
on (a.empno = b.empno)
when matched then
update set ename = 'khtt'
when not matched then
insert (empno, ename, job) values ('33', '33', '33');
7.Oracle递归查询start with connect by prior
递归查询7369员工的上级领导:
select *from emp start with empno=7369 connect by prior mgr=empno;
8.分析函数:
8.1:select deptno,
ename,
sal,
sum(sal) over(order by ename) as 连续求和,
sum(sal) over() as 总和,
100 * round(sal / sum(sal) over(), 5) as "份额(%)"
from emp;
8.2:select deptno,
ename,
sal,
sum(sal) over(partition by deptno order by ename) 部门连续求和,
sum(sal) over(partition by deptno) 部门总和,
100 * round(sal / sum(sal) over(partition by deptno), 4) "部门份额(%)",
sum(sal) over(order by deptno, ename) 连续求和,
sum(sal) over() 总和,
100 * round(sal / sum(sal) over(), 4) "总份额(%)"
from emp;
8.3:根据部门分组,显示每个部门的工资等级
select employee.*,Row_Number () OVER (partition by deptno ORDER bY sal desc)as "排名"from employee;
8.4:rollup函数,cube函数,grouping函数,rank,dense_rank,row_number等