Oracle SQL 练习题 2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- 1.	列出所有雇员的姓名及其直接上级的姓名
select e1.ename, e2.ename superiorname
from emp e1 left join emp e2 
on e1.mgr = e2.empno;

-- 2.	列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门
select dept.dname, emp.ename
from emp right join dept
on emp.deptno = dept.deptno;

-- 3.	显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
select emp.ename
from emp
where deptno in (select deptno
                  from dept
                  where loc = 'NEW YORK');

-- 4.	显示员工"SMITH"的姓名，部门名称
select emp.ename, dept.dname
from emp, dept
where lower(emp.ename) = 'smith' and emp.deptno = dept.deptno;

-- 5.	显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
select emp.ename, dept.dname, emp.sal, salgrade.grade
from emp left join dept on emp.deptno = dept.deptno, salgrade
where emp.sal between salgrade.losal and salgrade.hisal and salgrade.grade>4;

-- 6.	显示员工"KING"和"FORD"管理的员工姓名及其经理姓名
select e1.ename "NAME", e2.ename substaffname, emp.ename managername
from (emp e1 left join emp e2 on e1.empno = e2.mgr) left join emp on e1.mgr = emp.empno
where lower(e1.ename) = 'king' or lower(e1.ename) = 'ford';

-- 7.	显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早
select e1.ename, e1.hiredate, e2.ename managername, e2.hiredate managerhiredate
from emp e1 left join emp e2 on e1.mgr = e2.empno
where e1.hiredate < e2.hiredate;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

-- 8.	显示平均工资为>2000的职位
select "JOB"
from emp 
group by "JOB"
having avg(sal) > 2000;

-- 9.	计算工资在2000以上，各种职位的平均工资大于3000的职位及平均工资
select "JOB", avg(sal) avgsal
from emp
group by "JOB"
having avg(sal) > 3000 and min(sal) > 2000;

-- 10.	找每个部门的最高和最低的工资
select deptno, max(sal), min(sal)
from emp
group by deptno;

-- 11.	找每个部门中每种职位的最高和最低的工资
select deptno, "JOB", max(sal), min(sal)
from emp 
group by deptno, "JOB";

-- 12.	显示出工作名称(job)中包含"MAN"的员工平均工资，最高工资，最低工资及工资的和
select avg(sal) avgsal, max(sal) maxsal, min(sal) minsal, sum(sal) sumsal
from emp
where "JOB" like '%MAN%';

-- 13.	显示出20号部门的员工人数
select count(*) 
from emp
where deptno = 20;

-- 14.	显示出平均工资大于2000的部门名称及平均工资
select d1.dname, d2.avgsal
from dept d1, (select deptno, avg(sal) avgsal
               from emp
               group by deptno
               having avg(sal) > 2000) d2
where d1.deptno = d2.deptno;

-- 15.	显示每个部门每种工作平均工资大于2500的部门及工作
select deptno, "JOB"
from emp
group by deptno,"JOB"
having avg(sal)>2500;

-- 16.	显示出工作名称中包含"MAN"，并且平均工资大于1000的工作名称及平均工资
select "JOB", avg(sal) avgsal
from emp
where "JOB" like '%MAN%'
group by "JOB"
having avg(sal)>1000;

-- 17.	显示出平均工资最高的的部门平均工资
select max(avgsal)
from (select avg(sal) avgsal
      from emp
      group by deptno);

-- 18.	列出最低工资大于1500的各种工作
select "JOB"
from emp
group by "JOB"
having min(sal) > 1500;

-- 19.	列出各部门的员工数量及平均工作年限
select deptno, count(*), avg((sysdate-hiredate)/365)
from emp
group by deptno;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- 20.	查询和ford相同部门的员工姓名和雇用日期
select ename, hiredate
from emp
where deptno = (select deptno
                from emp
                where lower(ename) = 'ford');

-- 21.	查询工资比公司平均工资高的员工的员工号，姓名和工资。
select empno, ename, sal
from emp
where sal > (select avg(sal)
              from emp);

-- 22.	查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select empno,ename
from emp
where deptno in (select deptno
                  from emp
                  where ename like '%U%');

-- 23.	查询在CHICAGO工作的员工的员工号，job
select empno,"JOB"
from emp
where deptno in (select deptno
                  from dept
                  where loc = 'CHICAGO');

-- 24.	查询管理者是king的员工姓名和工资
select ename, sal
from emp 
where mgr = (select empno
              from emp
              where lower(ename) = 'king');