oracle练习题目

1.习题：列出至少有一个员工的所有部门
select distinct d.deptno,d.dname from emp e join dept d on e.deptno=d.deptno;
select distinct deptno from emp having count(empno)>=1 group by deptno;
2.习题：列出工资比“Smith”多的所有雇员。

select ename,sal from emp where sal>(select sal from emp where ename='SMITH');
3.习题：列出所有员工的姓名和其直接上司的姓名①
select e.ename,m.ename from emp e left join emp m on
e.mgr=m.empno;②select
emp.ename,mg.ename from emp join(select e.ename,e.empno from emp e join(select distinct mgr from emp where mgr is not null) m on empno=m.mgr) mg on emp.mgr=mg.empno;
4.习题：列出雇佣日期早于其直接上级的所有雇员的编号、姓名和部门名称
select empno,ename,dname from(select emp.empno,emp.ename,deptno dno from emp join(select
e.ename,e.empno,e.hiredate from emp e join(select distinct
mgr from emp where mgr is not null) m on empno=m.mgr) mg on emp.mgr=mg.empno and emp.hiredate<mg.hiredate)q join dept
d on dno=d.deptno;
5.习题：列出部门名称和这些部门的雇员信息，同时列出那些没有雇员的部门
select dname,ename,empno,sal,job from dept d left join emp e on d.deptno=e.deptno order by dname;
6.习题：列出所有“Cl e rk”的姓名和其部门名称，部门的人数
select ename,dname,(select count(*)from emp e,(select deptno from emp where ename ='CLARK')s where
e.deptno=s.deptno)from emp,dept where ename ='CLARK'and
emp.deptno=dept.deptno;
7.习题：列出最低工资大于1500 的各种工作以及从事此工作的全部雇员人数
select job,newsal,c from(select job,min(sal)as newsal,count(job)as c from emp group by job)where newsal>1500;
8.习题：列出在部门“Sales”工作的员工姓名，假定不知道销售部的部门编号
select ename from emp e,(select deptno from dept where dname='SALES') d where e.deptno=d.deptno;
习题：列出工资高于公司平均工资的所有雇员，所在部门，上级领导，公司的工资等级select ename,job,sal,grade,mname from(select
ename,job,sal,grade from(select ename,job,sal from
emp ,(select avg(sal)as asal from emp)where sal>asal)
join salgrade on sal between losal and hisal),(select e.ename aname,m.ename mname from emp e left join emp m on e.mgr=m.empno)
9.where ename =aname;
10.习题：列出与scott从事相同工作的所有雇员及部门名称
select ename,deptno dno from emp,(select job j from emp where ename='SCOTT')where job=j;
11.习题：列出工资等于部门30 中雇员的工资的所有雇员姓名和工资
select ename,sal from emp,(select sal s from emp where deptno=30)where sal in s;
12.习题：列出工资高于在30 部门工作的所有雇员的工资的雇员姓名和工资，部门名称
select ename,sal,dname from(select ename,sal,deptno dno from emp join(select max(sal)s from emp where deptno=30) on sal>s),dept where dno=deptno;
13.习题：列出在每个部门工作的雇员数量，平均工资和平均工龄
select
dname,count(empno),avg(sal),avg(sysdate-hiredate)from emp e right join dept d on e.deptno=d.deptno group by dname;
14.习题：列出所有雇员的姓名、部门名称和工资
select ename,dname,sal from emp e,dept d where
e.deptno=d.deptno;
15.习题：列出所有部门的详细信息和部门的人数
select deptno,dname,loc,s from dept left join(select deptno dno,count(empno) s from emp group by deptno)on deptno=dno order by deptno;
16.习题：列出各种工作的最低工资及从事此工作的雇员姓名
select ename,dsal,job from emp,(select job j,min(sal) dsal from emp group by job)where job=j;
17.习题：列出各个部门的manager的最低工资
select dname,msal from dept left join(select deptno dno,min(sal) msal from emp where job='MANAGER'group by deptno)
on deptno=dno;
18.习题：列出所有雇员的年薪，按照年薪从低到高排序
select ename,(sal*12)+(nvl(comm,0)*12)from emp order
by(sal*12)+(nvl(comm,0)*12);
19.习题：查询某个雇员的上级主管，并求出这些主管中的薪水超过3000 的
select e.ename,e.empno,e.sal from emp e join(select distinct mgr from emp where mgr is not null)m on empno=m.mgr and sal>3000;
20.习题：求出部门名称中带“S”的部门雇员的工资合计和部门人数
select sum(sal),count(empno)from emp,(select deptno dno from dept where dname like'%S%')where deptno=dno group by dno;。