Oracle实验三、四答案

上机练习三∙选择部门30中的所有员工Select * from emp where deptno=30∙列出所有办事员的姓名、编号和部门编号Select empno,ename,deptno from emp where job='CLERK';∙找出佣金高于薪金的员工Select * from emp where comm>sal;∙找出佣金高于薪金60%的员工Select * from emp where comm>sal*1.6;∙找出部门10中所有经理和部门20中所有办事员的详细资料Select * from empWhere (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');∙找出部门10中所有经理、部门20中所有办事员、既不是经理又不是办事员但薪金大于或等于2000的所有员工的详细资料Select * from empWhere (deptno=10 and job=‟MANAGER‟) or (deptno=20 and job=‟CLERK‟)Or (job not in(‟MANAGER‟ ,‟CLERK‟) and sal>=2000)∙找出收取佣金的员工的不同工作Select distinct job from empWhere comm is not null;∙找出不收取佣金或收取的佣金低于100的员工Select * from empWhere comm is null or comm<100;∙找出各月倒数第三天受雇的所有员工。

Select * from empWhere last_day(hiredate)-2=hiredate;∙找出早于12年前受雇的员工Select * from empWhere months_between(sysdate,hiredate)/12>12;∙以首字母大写的方式显示所有员工的姓名Select initcap(ename) from emp;∙显示正好为5个字符的员工姓名Select ename from empWhere length(ename)=5;∙显示不带有R的员工姓名Select * from empWhere ename not like '%R%';∙显示所有员工的前三个字符Select substr(ename,0,3) from emp;∙显示所有员工的姓名，用a替换所有ASelect replace(ename, 'A', 'a') from emp;∙显示满10年服务年限的员工的姓名和受雇日期Select ename,hiredate from empWhere months_between(sysdate,hiredate)/12>10;∙显示员工的详细资料，按姓名排序Select * from emp order by ename;∙显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面Select ename,hiredate from empOrder by hiredate;∙显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同按薪金排序Select ename,job,sal from empOrder by job desc,sal;∙显示所有员工姓名、加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工排在最前面。

Select ename,to_char(hiredate, 'yyyy') year, to_char(hiredate, 'mm') monthsFrom empOrder by months,year;∙显示在一个月为30天的情况下所有员工的日薪金，忽略余数Select ename,sal,trunc(sal/30) from emp;∙找出在任何年份的2月受聘的所有员工Select * from emp where to_char(hiredate, 'mm')= 2;∙对于每个员工显示其加入公司的天数Select ename,trunc(sysdate-hiredate) from emp;∙以年月日的方式显示所有员工的服务年限Select ename,hiredate,trunc(months_between(sysdate,hiredate)/12) year,mod(trunc(months_between(sysdate,hiredate)),12) months,trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate))) day from emp;实验四∙列出所有员工的姓名、部门名称和工资Select e.ename,d.dname,e.salFrom emp e,dept dWhere e.deptno=d.deptno(+);∙列出所有部门的详细信息和部门人数Select d.deptno,d.dname, d.loc,count(e.empno)From emp e,dept dWhere e. deptno(+)=d.deptnoGroup by d.deptno,d.dname,d.loc;∙列出所有员工的年工资、所在部门名称，按年薪升序排序Select e.ename,e.sal*12 income,d.dnameFrom emp e,dept dWhere e.deptno=d.deptnoOrder by income;∙查出每个员工的上级主管及所在部门名称，并要求这些主管的薪水超过3000 Select distinct m.ename,d.dname,m.salFrom emp e,emp m,dept dWhere e.mgr=m.empno and m.deptno=d.deptno and m.sal>3000;∙求出部门名称中带…S‟字符的部门员工的工资合计、部门人数。

Select d.dname,sum(nvl(e.sal,0)),count(e.empno)From emp e,dept dWhere e.deptno(+)=d.deptno and d.dname like '%S%'Group by d.dname;∙列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。

Select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)From emp e,dept dWhere e.deptno(+)=d.deptnoGroup by d.deptno, d.dname,d.loc;∙列出在部门“SALES”工作的员工姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。

Select e.ename,e.sal,e.hiredate,d.dnameFrom emp e,dept dWhere e.deptno=d.deptno and d.dname='SALES';∙列出公司各个工资等级雇员的数量、平均工资Select s.grade,s.losal,s.hisal,count(e.empno),avg(e.sal)From emp e,salgrade sWhere e.sal between s.losal and s.hisalGroup by s.grade,s.losal,s.hisal;∙列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称Select e.empno,e.ename,d.dname,e.salFrom emp e,dept dWhere sal>all(Select sal from emp where deptno=30)And e.deptno=d.deptno;∙列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。

Select e.empno,e.ename,d.dname,d.loc,temp.countFrom emp e,emp m,dept d,(Select deptno dno,count(empno) countFrom empGroup by deptno) tempWhere e.mgr=m.empno(+) and e.hiredate<m.hiredateAnd e.deptno=d.deptno and e.deptno=temp.dno;∙列出所有“clerk”的姓名及其部门名称、部门人数、工资等级。

Select e.ename,d.dname,e.job,temp.count,s.gradeFrom emp e,dept d,(Select deptno dno,count(empno) countFrom empGroup by deptno) temp,salgrade sWhere e.job='CLERK' and e.deptno=d.deptnoAnd d.deptno=temp.dno and e.sal between s.losal and s.hisal;∙列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资。

Select temp.job,temp.count,d.dname,e.ename,res.avgFrom dept d,(Select e.job job,count(e.empno) countFrom emp eGroup by e.jobHaving min(e.sal)>1500) temp,emp e,(Select deptno dno,avg(sal) avgFrom empGroup by deptno) resWhere e.deptno=d.deptno and e.job=temp.jobAnd e.deptno=res.dno;∙列出薪金高于公司平均薪金的所有员工、所在部门、上级领导、公司的工资等级。

Select e.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename,s.gradeFrom emp e,dept d,emp m,salgrade sWhere e.sal>(Select avg(sal) from emp)And e.deptno=d.deptno and e.mgr=m.empnoAnd e.sal between s.losal and s.hisal;∙列出与SCOTT从事相同工作的所有员工及部门名称、部门人数Select e.empno,e.ename,e.job,d.dname,temp.countFrom emp e,dept d,(Select deptno dno,count(empno) countFrom empGroup by deptno) tempWhere e.job=(Select jobFrom empWhere ename='SCOTT') and e.ename<>'SCOTT'And e.deptno=d.deptno and temp.dno= e.deptno;∙列出在每个部门工作的员工数量、平均工资和平均服务期限Selectd.deptno,d.dname,d.loc,count(e.empno),avg(sal),avg(months_between(sysdate,e.hiredate)/12) yearFrom emp e,dept dWhere e. deptno(+)=d.deptnoGroup by d.deptno,d.dname,d.loc;∙列出各种工作的最低工资及此雇员姓名Select e.ename,e.job,e.salFrom emp e,(Select job,min(sal) min from empGroup by job) tempWhere e.job=temp.job and e.sal=temp.min;∙列出各个部门的MANAGER的最低薪金、姓名、部门名称、部门人数Select e.ename,e.sal,d.dname,res.countFrom emp e,(Select deptno dno,min(sal) salFrom empWhere job='MANAGER'Group by deptno) temp, dept d,(Select deptno dno,count(empno) countFrom empGroup by deptno) resWhere e.deptno=temp.dno and e.sal=temp.sal and e.job='MANAGER'And e.deptno=d.deptno and res.dno=d.deptno;给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%。