oracle里分组语句.docx

/*使用scott/tiger用户下的emp表和dept表完成下列练习，表的结构说明如下：emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)dept部门表(deptno部门编号/dname部门名称/loc地点)工资 ＝ 薪金 ＋ 佣金*/--1.列出在每个部门工作的员工数量、平均工资和平均服务期限。select deptno,count(*),trunc(avg(sal+nvl(comm,2)))as avgsal,to_char(to_date(0001-01-01,yyyy-mm-dd) + avg(sysdate-hiredate)-366-31,yy年mm月dd) avgday from emp group by deptno;--2.列出所有员工的姓名、部门名称和工资。select ename,dname,(nvl(comm,0)+sal)as 工资 from emp,dept where emp.deptno=dept.deptno;--3.列出从事同一种工作但属于不同部门的员工的一种组合。select e.ename,e.job, e.deptno ,d.job, d.deptno from emp e, emp d where e.job=d.job and e.deptnod.deptno;--4.列出所有部门的详细信息和部门人数。select d.*,(select count(*) from emp where deptno=d.deptno) as 人数 from dept d;--5. 列出各种工作的最低工资。select job, min(nvl(comm,0)+sal) from emp group by job;--6. 列出各个部门的MANAGER（经理）的最低薪金。select deptno,min(sal) from emp where job=MANAGER group by deptno;--7. 列出所有员工的年工资,按年薪从低到高排序。select empno,ename,(nvl(comm,0)+sal)*12 as 年薪 from emp order by 年薪；--8. 找出月薪相同的员工select e.empno,e.sal from emp e,emp a where (e.sal=a.sal);--9. 列出至少有一个员工的所有部门。 select * from dept where deptno in(select distinct deptno from emp);--10. 列出薪金比“SMITH”多的所有员工。select * from emp where sal(select sal from emp where ename=SMITH);--11. 列出所有员工的姓名及其直接上级的姓名。select ename,(select ename from emp where empno=e.mgr) as mgr from emp e;--12. 列出受雇日期早于其直接上级的所有员工。select * from emp e where hiredate (select hiredate from emp where empno=e.mgr);--13. 列出所有“CLERK”（办事员）的姓名及其部门名称。 select ename,dname from emp,dept where job=CLERK and emp.deptno=emp.deptno;--14. 列出最低薪金大于1500的各种工作。select job from emp group by job having min(sal)1500;--15. 列出在部门“SALES”（销售部）工作的员工的姓名， 假定不知道销售部的部门编号。select ename from emp where deptno=(select deptno from dept where dname=SALES); --16. 列出薪金高于公司平均薪金的所有员工。select * from emp where sal(select avg(sal) from emp);--17. 列出与“SCOTT”从事相同工作的所有员工。 select * from emp e where exists (select * from emp where job=e.job and ename=SCOTT);--18. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 select ename,sal