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;