11.查询练习
1)列出至少有一个员工的所有部门
第一步:求出所有部门的员工数量
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno;
第二步:求出员工数大于1的部门编号
SELECT deptno,COUNT(empno)
FROM emp
GROUP BY deptno HAVING COUNT(empno)>1;
第三步:通过部门表查出部门的信息
SELECT d.*,ed.cou
FROM dept d,(SELECT deptno,COUNT(empno) cou
FROM emp
GROUP BY deptno HAVING COUNT(empno)>1) ed
WHERE d.deptno=ed.deptno;
2)列出薪金比Smith高的所有员工
第一步:求出SMITH的工资
SELECT sal FROM emp WHERE ename='SMITH';
第二步:以上面的结果为条件,查询所有符合条件的结果
SELECT *
FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH');
3)列出所有员工姓名及其直接上级的姓名
自连接
4)列出所有受雇日期早于其直接上级的天涯吧覅部分的编号、姓名、部门名称
第一步:自关联,查找mgr=empno的同时还要比较hiredate
第二步,要加入部门名称,则肯定应该加入dept表
SELECT e.ename,m.ename,d.dname
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno;
5)列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
左右关联,显示没有雇员的40部门
6)列出所有"CLERK(办事员)"的姓名及其部门名称、部门人数
第一步:找出所有办事员的姓名及部门编号
第二步:部门名称,需要是要部门表
第三步:分组求人数
SELECT e.ename,d.dname,ed.cou
FROM emp e,dept d,(SELECT deptno,COUNT(empno) cou
FROM emp e
GROUP BY deptno) ed
WHERE job='CLERK' AND d.deptno=e.deptno AND ed.deptno=e.deptno;
7)列出最低薪金大于1500 的各种工作以及从事此工作的全部雇员的人数
第一步:按工资分组,分组条件最低工资大于1500
第二步:求出全部雇员人数
SELECT e.job,COUNT(e.empno)
FROM emp e
WHERE e.job IN(SELECT job
FROM emp
GROUP BY job HAVING MIN(sal)>1500)
GROUP BY e.job;
8)列出在"SALES(销售部)"工作的员工姓名,假定不知道销售部的部门号
第一步:通过dept表查询销售部的部门编号
第二步:将之前的作为子查询
9)列出薪金高于公司平均薪金的所有员工、所在部门、上级领导,工资等级
第一步:求出工资平均薪金
第二步:列出薪金高于平均工资的所有雇员信息
第三步:求出所在部门,要关联部门表
第四步:求出上级领导,要自关联
SELECT e.empno,e.ename,m.ename,d.deptno,d.dname,d.loc
FROM emp e,dept d,emp m
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno
AND e.mgr=m.empno(+);
第五步:求出雇员的工资等级
SELECT e.empno,e.ename,m.ename,d.deptno,d.dname,d.loc,s.grade
FROM emp e,dept d,emp m,salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno
AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal;
10)列出与SCOTT从事相同工作的所有员工及部门名称
第一步:找到SCOTT的工作
第二步:找出与其从事相同工作的人
以上结果中存在了SCOTT,实际上不应该
第三步:与部门表关联,查询部门名称
11)列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
第一步:求出30部门的员工的薪金
第二步:上一步作为子查询
部门编号不应该为30
12)列出薪金高于部门30中工作的所有员工的薪金的员工新明和薪金、部门名称
第一步:在之前的程序上进行修改,使用>ALL,比最大的还要大
第二步:与dept关联,求出部门名称
13)列出在每个部门工作的员工数量、平均工资和平均服务期限
第一步:求出每个部门的员工数量,求得时候可以求出部门名称
第二步:平均工资和服务期限
SELECT COUNT(e.ename),d.dname,AVG(e.sal),
AVG(MONTHS_BETWEEN(sysdate,e.hiredate)/12) 年
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
14)列出所有部门的详细信息和部门人数
第一步:列出所有部门的人数
第二步:列出部门的详细信息
SELECT d.*,ed.cou
FROM dept d,(SELECT deptno dno,COUNT(empno) cou
FROM emp
GROUP BY deptno) ed
WHERE d.deptno=ed.dno;
但是没有雇员的40部门也应该显示出来,人数应该是0
SELECT d.*,NVL(ed.cou,0)
FROM dept d,(SELECT deptno dno,COUNT(empno) cou
FROM emp
GROUP BY deptno) ed
WHERE d.deptno=ed.dno(+);
15)列出各种工作的最低工资及从事此工作的雇员姓名
第一步:按工作分组,用MIN求出最低工资
第二步:求出从事此工作的雇员姓名
16)列出各个部门的MANAGER的最低薪金
17)列出所有员工过的年工资,按年薪从低到高排序
在处理年薪的时候要注意奖金,奖金要使用NVL函数
18)查询出某个员工的上级主管,并要求出这些主管中的薪水超过3000
不要重复项
19)求出部门名称中带"S"字符的部门的员工、工资合计、部门人数
第一步:查询部门表的部门名称,使用模糊查询,来确定部门的编号
第二步:上面作为子查询
20)给任职日期超过10的人加薪10%
UPDATE emp SET sal=sal+(sal*0.1)
WHERE MONTHS_BETWEEN(sysdate,hiredate)/12>10;