oracle练习题-emp表

–用SQL完成以下问题列表：

–1.列出至少有一个员工的所有部门。

–2.列出薪金比“SMITH”多的所有员工。

–3.列出所有员工的姓名及其直接上级的姓名。

–4.列出受雇日期早于其直接上级的所有员工。

–5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

–6.列出所有“CLERK”(办事员)的姓名及其部门名称。

–7.列出最低薪金大于1500的各种工作。

–8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

–9.列出薪金高于公司平均薪金的所有员工。

–10.列出与“SCOTT”从事相同工作的所有员工。

–11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

–12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

–13.列出在每个部门工作的员工数量、平均工资和平均服务期限。

–14.列出所有员工的姓名、部门名称和工资。

–15.列出所有部门的详细信息和部门人数。

–16.列出各种工作的最低工资。

–17.列出各个部门的MANAGER(经理)的最低薪金。

–18.列出所有员工的年工资,按年薪从低到高排序。

–19 查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句

–20 查询各部门的最高薪水、最低薪水、平均薪水….

1、列出至少有一个员工的所有部门。

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);

执行结果如下：

2、列出薪金比“SMITH”多的所有员工。

select * from emp where sal > (select sal from emp where ename = 'SMITH');
--首先将姓名为小写得部分变为大写
update emp set ename=replace(ename,substr (ename,length(ename),length(ename)),upper(substr(ename,length(ename),length(ename))));
--查询是否变更；
select * from emp;
--最后进行查询
select * from emp where sal > (select sal from emp where ename = 'SMITH');

*由于之前练习将ename中最后一个字母变为小写，所以查不到SMITH，因此得先将小写变为大写后，在进行查询
执行结果如下：


3、列出所有员工的姓名及其直接上级的姓名。

select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;

执行结果如下：

4、列出受雇日期早于其直接上级的所有员工。

select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);

执行结果如下：

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left join emp b on a.deptno=b.deptno;

执行结果如下：

6、列出所有“CLERK”(办事员)的姓名及其部门名称。

select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 

执行结果如下：

7、列出最低薪金大于1500的各种工作。

select distinct job as HighSalJob from emp group by job having min(sal)>1500;

执行结果如下：

8、列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。

select ename from emp where deptno=(select deptno from dept where dname='SALES');

执行结果如下：

9、列出薪金高于公司平均薪金的所有员工。

select ename from emp where sal>(select avg(sal) from emp);

执行结果如下：

10、列出与“SMITH”从事相同工作的所有员工。

select ename from emp where job=(select job from emp where ename='SMITH');

执行结果如下：

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

select ename,sal from empwhere sal in (select sal from emp where deptno=30);

执行结果如下：

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

执行结果如下：

13、列出在每个部门工作的员工数量、平均工资和平均服务期限。

select deptno,count(*),avg(a.sal),avg(sysdate-HIREDATE) from emp a group by deptno;

执行结果如下：

14、列出所有员工的姓名、部门名称和工资。

select ename,(select dname from dept a where a.deptno=b.deptno)as dname,sal from emp b;

执行结果如下：

15、列出所有部门的详细信息和部门人数。

select * from dept a left join (select deptno,count(*) from emp group by deptno) b on a.deptno=b.deptno;

执行结果如下：

16、列出各种工作的最低工资。

select job,min(sal) from emp group by job;

执行结果如下：

17、列出各个部门的MANAGER(经理)的最低薪金。

select deptno,min(sal) from emp where job='MANAGER' group by deptno;

执行结果如下：

18、列出所有员工的年工资,按年薪从低到高排序。

select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal; 

执行结果如下：

19、查询雇员表中，姓名为SMITH的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句。

select round((sysdate-hiredate)/7) as "Weeks" from emp where ename='SMITH';

执行结果如下：

20、查询各部门的最高薪水、最低薪水、平均薪水…

select deptno,max(sal),min(sal),avg(sal) from emp group by deptno;

执行结果如下：