--1. 给所有10部门的经理（MANAGER）和20部门的职员(CLERK)，增加薪金10% select * from emp update emp set SAL=SAL*1.1 where (deptno=10 and job='MANAGER') update emp set SAL=SAL*1.1 where (deptno=20 and job='CLERK')) --2. 给emp添加一条记录只有empno,ename,mgr,sal，deptno有值，其他列为空 INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7777,'ZHANGSAN',NULL,'7566',NULL,2000,NULL,20) --3. 要求查询在1981年雇佣的全部雇员信息 SELECT * FROM EMP WHERE HIREDATE >=TO_DATE('1981-1-1','yyyy-mm-dd') and HIREDATE <=TO_DATE('1981-12-31','yyyy-mm-dd') --4. 查询各部门的平均薪水及部门编号，要求只有员工姓名中包含 ‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水降序排列 select deptno, avg(sal) avgsal from emp where ename like '%A%' group by deptno having avg(sal)>1500 order by 2 desc --5. 列出所有员工及直接上级的姓名 select emp.ename,emp1.ename as mgrename from emp left join (select empno,ename from emp) emp1 on emp.mgr=emp1.empno --6. 列出至少有一个员工的所有部门 select dname from dept where deptno in (select deptno from emp group by deptno having count(deptno)>=1) --7. 取得每个部门最高薪水的人员名称（不考虑comm） select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno=e.deptno and t.maxsal=e.sal --8. 哪些人的薪水在部门的平均薪水之上（不考虑comm） select e.empno,e.ename,e.deptno,e.sal from emp e where e.sal>(select avg(e1.sal) from emp e1 where e.deptno=e1.deptno) --方法二 select e.ename,e.sal,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t on t.deptno=e.deptno and e.sal>t.avgsal --9. 取得薪水最高的第六到第十名员工 select ename, sal from (select ename, sal, rownum rn from (select ename, sal from emp order by sal desc)) where rn >= 6 and rn <= 10 --10. 查询薪金比“SMITH”或者“ALLEN”多的所有员工的编号、姓名、部门名称、领导姓名、部门人数、平均工资、最高及最低工资 select e.empno,e.ename,d.dname,m.ename,t.rn,t.avgsal,t.maxsal,t.minsal from emp e left join dept d on e.deptno=d.deptno left join emp m on e.mgr=m.empno left join (select e.deptno,count(e.deptno) rn,avg(e.sal) avgsal,max(e.sal) maxsal,min(e.sal) minsal from emp e group by e.deptno) t on e.deptno=t.deptno where e.sal>all(select sal from emp where ename in ('SMITH','ALLEN'))
