select * from scott.emp;
--1 列出emp表中各部门的部门号,最高工资,最低工资
select deptno,max(sal),min(sal) from scott.emp group by deptno;
2 列出emp表中各部门job为'CLERK'的员工的最低工资,最高工资
select min(sal),max(sal) from scott.emp where job='CLERK'
3 --对于emp中最低工资小于13000的部门,
--列出job为'CLERK'的员工的 (部门号),最低工资,最高工资
select min(sal),max(sal) from scott.emp where job='CLERK' and deptno in
(
select deptno from scott.emp group by deptno having min(sal)<'13000'
)
4 根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资
select ename,deptno,sal from scott.emp order by deptno desc,sal
5列出'张三'所在部门中每个员工的姓名与部门号
select ename,deptno from
scott.emp where deptno in
(
select deptno from scott.emp where ename='WARD'
)
6列出每个员工的姓名,工作,部门号,部门名
select ename,job,scott.emp.deptno ,scott.dept.dname
from scott.emp inner join scott.dept on scott.emp.deptno = scott.dept.deptno;
select ename,job,scott.emp.deptno ,scott.dept.dname
from scott.emp , scott.dept where
scott.emp.deptno = scott.dept.deptno;
7列出emp中工作为'CLERK'的员工的姓名,工作,部门号,部门名
select ename,job,scott.emp.deptno ,scott.dept.dname
from scott.emp , scott.dept
where
scott.emp.deptno = scott.dept.deptno and job='CLERK';
8 对于emp中有管理者的员工,列出姓名,管理者姓名(管理者外键为mgr)
同表自关联。适合于设计多级菜单,多级种类。
select s1.ename,s2.ename as 管理者
from
(select * from scott.emp where mgr is not null) s1,scott.emp s2
where s1.mgr = s2.empno;
9对于dept表中,列出所有部门名,部门号,
select * from
同时列出各部门工作为'CLERK'的员工名与工作
select d.*,e.ename,e.job from scott.dept d join
(select deptno,ename,job from scott.emp where job='CLERK') e
on d.deptno = e.deptno;
10 对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序
select deptno,ename,sal from scott.emp e
where sal>(select avg(sal) from scott.emp where deptno = e.deptno)
order by deptno;
select deptno,avg(sal) from scott.emp group by deptno
11 对于emp,列出各个部门中工资高于本部门平均水平的员工数和部门号,
按部门号排序
select count(1) 员工数,deptno from
( select deptno,ename,sal from scott.emp e
where sal>(select avg(sal) from scott.emp where deptno = e.deptno)
) group by deptno order by deptno;
12 对于emp中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序
select count(1) 员工数,deptno from
( select deptno,ename,sal from scott.emp e
where sal>(select avg(sal) from scott.emp where deptno = e.deptno)
) group by deptno having count(1)>1 order by deptno;
13 对于emp中低于自己工资至少5人的员工,列出其部门号,姓名,工资,
[以及工资少于自己的人数]
--找出有比自己工资少的人
select t.* from
(
select deptno,ename,sal,empno from scott.emp
where sal not in
(
select sal from (
select distinct(sal) ,rownum r from scott.emp order by sal )
where r<6
)
) t ,scott.emp where scott.emp.sal<t.sal;
select a.deptno,a.ename,a.sal,(select count(b.ename) from
emp as b where b.sal<a.sal) as 人数 from scott.emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>5