大连手机自适应网站建设,wordpress前台发视频图片,一流的龙岗网站建设,建设银行网站首页下载1、 列出至少有一个员工的所有部门编号、名称#xff0c;并统计出这些部门的平均工资、最低工资、最高工资。
1、确定所需要的数据表#xff1a;
dept表#xff1a;部门名称#xff1b;emp表#xff1a;统计信息#xff1b;
2、确定已知的关联字段#xff1a;
emp.de…1、 列出至少有一个员工的所有部门编号、名称并统计出这些部门的平均工资、最低工资、最高工资。
1、确定所需要的数据表
dept表部门名称emp表统计信息
2、确定已知的关联字段
emp.deptnodept.deptno
第一步找出至少有一个员工的部门编号
SELECT deptno
FROM emp
GROUP BY deptno
第二步找到部门名称肯定使用部门表因为现在的数据量较小所以可以将之前的emp表和dept表两个进行连接统一采用多字段分组的方式查询
SELECT d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptnod.deptno()
GROUP BY d.deptno,d.dname
第三步统计
SELECT d.deptno,d.dname,AVG(sal),MIN(sal),MAX(sal)
FROM emp e,dept d
WHERE e.deptnod.deptno()
GROUP BY d.deptno,d.dname2、 列出薪金比“SMITH”且“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
1、确定所需要的数据表
emp表查询出“SMITH”且“ALLEN”工资emp表最终的显示需要编号、姓名emp表领导的姓名自身关联dept表部门名称
2、确定已知的关联字段
雇员和领导emp.mgremp.empno雇员和部门emp.deptnodept.deptno
第一步找出“SMITH”且“ALLEN”的工资
SELECT sal FROM emp WHERE ename IN(SMITH,ALLEN);
第二步以上的查询返回的多行单列的记录按照子查询的要求在WHERE子句中写合适所以这个时候将上面的查询作为一个子查询出现继续查询符合此要求的员工的编号、姓名。
SELECT e.empno,e.ename
FROM emp e
WHERE e.salALL(SELECT salFROM empWHERE ename IN(SMITH,ALLEN));
第三步查询出部门的名称引入部门表同时增加消除笛卡尔积的条件
SELECT e.empno,e.ename,d.dname
FROM emp e,dept d
WHERE e.salALL(
SELECT sal
FROM emp
WHERE ename IN(SMITH,ALLEN))
AND e.deptnod.deptno;
第四步领导的信息需要emp表自身关联
SELECT e.empno,e.ename,d.dname,m.ename
FROM emp e,dept d,emp m
WHERE e.salALL(
SELECT sal
FROM emp
WHERE ename IN(SMITH,ALLEN))
AND e.deptnod.deptno
AND e.mgrm.empno();
3、 列出所有员工的编号、姓名及其直接上级的编号、姓名显示的结果按领导年工资的降序排列。
1、确定所需要的数据表
emp表员工的编号、姓名emp表领导的编号、姓名、计算年薪
2、确定已知的关联字段emp.mgrmemp.empno
SELECT e.empno,e.ename,m.empno,m.ename,(m.salNVL(m.comm,0))*12 income
FROM emp e,emp m
WHERE e.mgrm.empno()
ORDER BY income DESC;
4、列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。
1、确定所需要的数据表
emp表雇员的编号、姓名emp表求出领导的工作日期dept表部门名称、位置emp表统计部门人数
2、确定已知的关联字段
雇员和部门emp.deptnodept.deptno雇员和领导emp.mgrmemp.empno
第一步列出受雇日期早于其直接上级的所有员工的编号、姓名 —— 自身关联emp表。
SELECT e.empno,e.ename
FROM emp e,emp m
WHERE e.mgrm.empno() AND e.hiredatem.hiredate;
第二步加入部门信息继续引入部门表
SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e,emp m,dept d
WHERE e.mgrm.empno() AND e.hiredatem.hiredate
AND e.deptnod.deptno;
额外提问此时的笛卡尔积emp表的14条 * emp表的14条 * dept表的4条
第三步统计部门人数此时由于要使用统计函数而且以上的查询也无法再直接出现统计函数所以使用子查询完成
SELECT e.empno,e.ename,d.dname,d.loc,temp.count FROM emp e,emp m,dept d,( SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.mgrm.empno() AND e.hiredatem.hiredate AND e.deptnod.deptno AND e.deptnotemp.dno;
当查询显示的时候需要统计信息但是又不能直接使用统计函数查询的话通过子查询在FROM子句之后进行统计。
5、列出部门名称和这些部门的员工信息数量、平均工资同时列出那些没有员工的部门。
1、确定所需要的数据表
dept表部门的信息emp表求出所有的统计信息
2、确定已知的关联字段emp.deptnodept.deptno
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno()d.deptno
GROUP BY d.deptno,d.dname,d.loc;
6、列出所有“CLERK”办事员的姓名及其部门名称部门的人数工资等级。
1、确定所需要的数据表
emp表找到办事员的姓名dept表部门名称emp表统计求出部门的人数salgrade表查询工资等级
2、确定已知的关联字段
emp表和dept表emp.deptnodept.deptnoemp表和salgrade表emp.sal BETWEEN salgrade.losal AND salgrade.hisal
第一步找到所有办事员的姓名
SELECT e.ename
FROM emp e
WHERE jobCLERK;
第二步找到部门信息引入dept表同时增加消除笛卡尔积的条件
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE jobCLERK AND e.deptnod.deptno;
第三步部门人数需要额外的统计但是本程序的查询里面已经不可能继续使用COUNT()函数所以写子查询统计SELECT e.ename,d.dname,temp.count FROM emp e,dept d,( SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.jobCLERK AND e.deptnod.deptno AND d.deptnotemp.dno;
第四步雇员的工资等级继续引入salgrade表SELECT e.ename,d.dname,temp.count,s.grade FROM emp e,dept d,( SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp,salgrade s WHERE e.jobCLERK AND e.deptnod.deptno AND d.deptnotemp.dno AND e.sal BETWEEN s.losal AND s.hisal;
7、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资。
1、确定所需要的数据表
emp表最低薪金大于1500的工作肯定需要使用emp表统计求出以及可以求出雇员人数dept表求出些雇员所在的部门信息emp表统计求出部门的平均工资
2、确定已知的关联字段emp.deptnodept.deptno
第一步使用emp表按照job分组统计最低工资HAVING和人数
SELECT e.job,COUNT(e.empno)
FROM emp e
GROUP BY e.job
HAVING MIN(e.sal)1500;
第二步要查询出雇员所在的部门信息但是以上的查询能跟dept表有关联吗
以上的查询和dept表之间并没有关联字段那么如果没有关联字段一定会有笛卡尔积产生但是多表查询必须要消除笛卡尔积所以必须联系
以上的查询可以和emp表的job字段关联要引入的dept表也可以和emp表的deptno字段关联
SELECT temp.job,temp.count,d.dname,e.ename FROM dept d,( SELECT e.job job,COUNT(e.empno) count FROM emp e GROUP BY e.job HAVING MIN(e.sal)1500) temp, emp e WHERE e.deptnod.deptno AND e.jobtemp.job;
第三步求出一个部门的平均工资使用emp表在子查询中统计SELECT temp.job,temp.count,d.dname,e.ename,res.avg FROM dept d,( SELECT e.job job,COUNT(e.empno) count FROM emp e GROUP BY e.job HAVING MIN(e.sal)1500) temp, emp e,( SELECT deptno dno,AVG(sal) avg FROM emp GROUP BY deptno) res WHERE e.deptnod.deptno AND e.jobtemp.job AND e.deptnores.dno;
本题目之所以出的如此之复杂目的是训练大家寻找关联字段的能力但是本题目没有任何的意义知道就行了。
8、列出在部门“SALES”销售部工作的员工姓名、基本工资、雇佣日期、部门名称、假定不知道销售部的部门编号。
1、确定所需要的数据表
emp表员工姓名、基本工资、雇佣日期dept表找到销售部的部门编号、部门名称
2、确定已知的关联字段emp.deptnodept.deptno
SELECT e.ename,e.sal,e.hiredate,d.dname
FROM emp e,dept d
WHERE e.deptnod.deptno
AND d.dnameSALES;
9、列出薪金高于公司平均薪金的所有员工所在部门上级领导公司的工资等级。
1、确定所需要的数据表
emp表可以求出公司的平均薪金emp表员工的信息dept表部门的信息emp表领导的信息salgrade表工资等级
2、确定已知的关联字段
雇员和部门emp.deptnodept.deptno雇员和领导emp.mgrmemp.empno雇员和工资等级emp.sal BETWEEN salgrade.losal AND salgrade.hisal
第一步求出公司的平均薪金
SELECT AVG(sal) FROM emp;
第二步将以上的子查询放在WHERE子句之中作为一个查询条件求出满足此条件的雇员信息。
SELECT e.empno,e.ename,e.job,e.sal
FROM emp e
WHERE e.sal(SELECT AVG(sal) FROM emp);
第三步找到部门的名称
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc
FROM emp e,dept d
WHERE e.sal(SELECT AVG(sal) FROM emp)AND e.deptnod.deptno;
第四步找到领导的信息SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename FROM emp e,dept d,emp m WHERE e.sal( SELECT AVG(sal) FROM emp) AND e.deptnod.deptno AND e.mgrm.empno();
第五步找到工资等级SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename,s.grade FROM emp e,dept d,emp m,salgrade s WHERE e.sal( SELECT AVG(sal) FROM emp) AND e.deptnod.deptno AND e.mgrm.empno() AND e.sal BETWEEN s.losal AND s.hisal;
10、列出与“SCOTT”从事相同工作的所有员工及部门名称部门人数。
1、确定所需要的数据表
emp表找到SCOTT的工作emp表员工的信息dept表部门名称emp表部门人数
2、确定已知的关联字段emp.deptnodept.deptno
第一步找到SCOTT的工作
SELECT job FROM emp WHERE enameSCOTT;
第二步以上的子查询返回单行单列的数据所以可以在WHERE子句中出现以这个条件查找满足要求的雇员信息SELECT e.empno,e.ename,e.job FROM emp e WHERE e.job( SELECT job FROM emp WHERE enameSCOTT) AND e.enameSCOTT;
第三步找到部门名称SELECT e.empno,e.ename,e.job,d.dname FROM emp e,dept d WHERE e.job( SELECT job FROM emp WHERE enameSCOTT) AND e.enameSCOTT AND e.deptnod.deptno;
第四步找到部门人数需要统计所以在FROM子句之中编写SELECT e.empno,e.ename,e.job,d.dname,temp.count FROM emp e,dept d,( SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) temp WHERE e.job( SELECT job FROM emp WHERE enameSCOTT) AND e.enameSCOTT AND e.deptnod.deptno AND temp.dnoe.deptno;
11、列出公司各个工资等级雇员的数量、平均工资。
1、确定所需要的数据表
emp表统计出数据salgrade表得出工资等级
2、确定已知的关联字段emp.sal BETWEEN salgrade.losal AND s.hisal
本程序实际上就是一个多字段分组而已唯一不同的是将分组条件设置为salgrade表中的字段
SELECT s.grade,s.losal,s.hisal,COUNT(e.empno),AVG(e.sal)
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade,s.losal,s.hisal;
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
1、确定所需要的数据表
emp表找出所有在30部门工作的雇员的工资emp表最终显示的雇员姓名dept表找到部门名称
2、确定已知的关联字段emp.deptnodept.deptno
第一步找到30部门工作的雇员的工资
SELECT sal FROM emp WHERE deptno30;
第二步高于30部门使用ALL操作符引入emp表查询姓名和薪金
SELECT e.empno,e.ename
FROM emp e
WHERE salALL(SELECT sal FROM emp WHERE deptno30)AND e.deptnod.deptno;
13、列出在每个部门工作的员工数量、平均工资和平均服务期限。
1、确定所需要的数据表
dept表找到部门信息emp表统计出数量、平均工资、平均服务年限
2、确定已知的关联字段emp.deptnodept.deptno
直接将dept和emp表关联使用多字段分组即可但是对于服务年限需要一个计算过程。
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal),AVG(MONTHS_BWTWEEN(SYSDATE,e.hiredate)/12) year
FROM emp e,dept d
WHERE e.deptno()d.deptno
GROUP BY d.deptno,d.dname,d.loc;
14、列出所有员工的姓名、部门名称和工资。
1、确定所需要的数据表
emp表找到员工姓名dept表部门名称
2、确定已知的关联字段emp.deptnodept.deptno
SELECT e.ename,d.dname,e.sal
FROM emp e,dept d
WHERE e.deptnod.deptno;
15、列出所有部门的详细信息和部门人数。
1、确定所需要的数据表
emp表统计信息dept表查询部门信息
2、确定已知的关联字段emp.deptnodept.deptno
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno()d.deptno
GROUP BY d.deptno,d.dname,d.loc;
16、列出各种工作的最低工资及从事此工作的雇员姓名。
1、确定所需要的数据表
emp表统计出各个工作的最低工资emp表查找出雇员姓名
第一步按照职位统计各个职位的最低工资
SELECT job,MIN(sal) FROM emp
GROUP BY job;
第二步将以上的查询和emp表关联
SELECT e.ename,e.job,e.sal
FROM emp e,(SELECT job,MIN(sal) FROM empGROUP BY job) temp
WHERE e.jobtemp.job AND e.saltemp.min;
17、列出各个部门的MANAGER经理的最低薪金、姓名、部门名称、部门人数。
1、确定所需要的数据表
emp表找到经理的薪金、姓名dept表部门名称emp表统计部门人数
2、确定已知的关联字段emp.deptnodept.deptno
第一步找到所有部门的经理
SELECT deptno,MIN(sal)
FROM emp
WHERE jobMANAGER
GROUP BY deptno;
第二步找到姓名但是以上的子查询不能再出现其他的字段SELECT e.ename,e.sal FROM emp e,( SELECT deptno dno,MIN(sal) sal FROM emp WHERE job’MANAGER’ GROUP BY deptno) temp WHERE e.deptnotemp.dno AND e.saltemp.sal AND e.jobMANAGER;
第三步加入部门的名称信息SELECT e.ename,e.sal,d.dname FROM emp e,( SELECT deptno dno,MIN(sal) sal FROM emp WHERE jobMANAGER GROUP BY deptno) temp,dept d WHERE e.deptnotemp.dno AND e.saltemp.sal AND e.jobMANAGER AND e.deptnod.deptno;
第四步统计部门人数SELECT e.ename,e.sal,d.dname,res.count FROM emp e,( SELECT deptno dno,MIN(sal) sal FROM emp WHERE job’MANAGER’ GROUP BY deptno) temp,dept d,( SELECT deptno dno,COUNT(empno) count FROM emp GROUP BY deptno) res WHERE e.deptnotemp.dno AND e.saltemp.sal AND e.jobMANAGER AND e.deptnod.deptno AND res.dnod.deptno;18、列出所有员工的年工资所在部门名称按年薪从低到高排序。
1、确定所需要的数据表
emp表统计年工资dept表部门名称
2、确定已知的关联字段emp.deptnodept.deptno
SELECT e.ename,e.sal*12 income,d.dname
FROM emp e,dept d
WHERE e.deptnod.deptno
ORDER BY income;
19、查出某个员工的上级主管及所在部门名称并要求出这些主管中的薪水超过3000。
1、确定所需要的数据表
emp表员工的信息emp表领导的信息dept表部门名称
2、确定已知的关联字段
雇员和部门emp.deptnodept.deptno雇员和领导emp.mgrmemp.empno
SELECT DISTINCT m.ename,d.dname,m.sal
FROM emp e,emp m,dept d
WHERE e.mgrm.empno AND m.deptnod.deptno AND m.sal3000;
20、求出部门名称中带‘S’字符的部门员工的工资合计、部门人数。
1、确定所需要的数据表
emp表进行统计信息dept表部门名称
2、确定已知的关联字段emp.deptnodept.deptno
SELECT d.dname,SUM(e.sal),COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno()d.deptno AND d.dname LIKE ‘%S%’
GROUP BY d.dname;
21、给任职日期超过30年或者在87年雇佣的雇员加薪加薪原则10部门增长10%20部门增长20%30部门增长30%依次类推。
UPDATE emp SETsal(1 deptno/100)*sal
WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/1230OR TO_CHAR(hiredate,’yyyy’)1987; 以上内容取自复杂查询
