Oracle的完整练习(二)
ptno=(
SELECT deptno
FROM dept
WHERE dname='SALES');
34.列出薪金高于工资平均薪金的所有员工，所在部门，上级领导，公司的工资等级。
·求出平均工资：
SELECT AVG(sal) FROM emp;
·列出薪金高于平均工资的所有雇员信息
SELECT * FROM emp WHERE sal>(
SELECT AVG(sal) FROM emp
);
·和部门表关联，查询所在部门的信息(注意KING 是没有上级的 注意右连接)
SELECT e.*,d.dname,d.loc,m.ename
FROM emp e,dept d,emp m
WHERE
e.mgr=m.empno(+) AND
e.deptno=d.deptno AND
e.sal>(
SELECT AVG(sal) FROM emp
);
·求出雇员的工资等级
SELECT e.*,d.dname,d.loc,m.ename,s.grade
FROM emp e,dept d,emp m,salgrade s
WHERE
e.mgr=m.empno(+) AND
e.deptno=d.deptno AND
e.sal>(SELECT AVG(sal) FROM emp)
AND e.sal BETWEEN s.losal AND s.hisal
;
35.列出和“SCOTT”从事相同工作的所有员工及部门名称：
·SCOTT从事的工作
SELECT job FROM emp WHERE ename='SCOTT';
·做子查询
SELECT e.*,d.dname FROM emp e,dept d
WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT')
AND e.deptno=d.deptno;
·以上的结果存在SCOTT，应该去掉
SELECT e.*,d.dname FROM emp e,dept d
WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT')
AND e.deptno=d.deptno
AND e.ename!='SCOTT';
36.列出薪金等于部门30中员工薪金的所有员工的姓名和薪金
·求出部门30中的员工薪金
SELECT sal FROM emp WHERE deptno=30;
·子查询
SELECT ename,sal FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno=30)
AND deptno!=30;
37.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
·在之前的程序进行修改使用>ALL ，比最大还大
SELECT ename,sal FROM emp
WHERE sal >ALL(SELECT sal FROM emp WHERE deptno=30)
AND deptno!=30;
·再和dept关联，求出部门名称
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.sal >ALL(SELECT sal FROM emp WHERE deptno=30)
AND e.deptno!=30
AND e.deptno=d.deptno;
38.列出每个部门工作的员工数量、平均工资和平均服务期限
·每个部门工作的员工数量：
SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
·求出平均工资和服务年限
SELECT d.dname,COUNT(e.empno),AVG(sal),AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) 年
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
39.列出所有员工的姓名、部门和工资
SELECT e.ename,d.dname,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno;
40.列出所有部门的相信信息和部门人数
·列出所有部门的人数
SELECT deptno dno,COUNT(empno) cou
FROM emp
GROUP BY deptno;
·把上表当成临时表：【由于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(+);
41、列出各种工作的最低工资以及从事此工作的雇员姓名：
·按工作分组求出最低工资
SELECT MIN(sal) m FROM emp GROUP BY job;
·子查询
SELECT e.ename FROM emp e
WHERE e.sal IN(SELECT MIN(sal) m FROM emp GROUP BY job);
42、列出各个部门的MANAGER 的最低薪金：
·求出各个部门MANAGER的工资，按照部门分组
SELECT deptno,MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno;
43、列出所有员工的年工资，按照年薪从低到高排序：
·注意奖金，奖金要用NVL函数处理
SELECT ename,(sal+NVL(comm,0))*12 income FROM emp ORDER BY income
44、查询出某个员工的上级主管，并要求这些主管中的薪水超过3000
SELECT DISTINCT m.*
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.sal>
3000;
45、求出部门名称中带有’S‘字符的部门员工的工资合计，部门人数
·查询部门表中的部门名称，使用模糊查询，以确定部门编号
SELECT deptno FROM dept WHERE dname LIKE '%S%';
·再根据上面作为子查询，求出工资合计和部门人数
SELECT SUM(sal),COUNT(empno)
FROM emp e
WHERE e.deptno IN (SELECT deptno FROM dept WHERE dname LIKE '%S%')
GROUP BY deptno;
46、给任职日期超过10年的人加薪10%；
UPDATE emp SET sal=sal*1.1 WHERE MONTHS_BETWEEN(sysdate,hiredate)/12>10;