SQL 经典语句

15题需要再分析。没弄懂 使用scott/tiger用户下的emp表和dept表完成下列练习，

表的结构说明如下 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资 ＝ 薪金 ＋ 佣金

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．列出所有员工的年工资,按年薪从低到高排序。

解答： 1．列出至少有一个员工的所有部门。(两个表联合查询，及group by...having的用法)

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

2．列出薪金比“SMITH”多的所有员工。(经典的自连接查询)

select ename from emp where sal>(select sal from emp where ename like'SMITH');

3．列出所有员工的姓名及其直接上级的姓名。(多次对自己查询,为表的取个别名，内部查询可以像对象一样引用外部的对象的字段，这里引用与编程中的作用域相似，即与{}类比)

select ename,(select ename from emp where empno in(a.mgr)) from emp a ;

4．列出受雇日期早于其直接上级的所有员工。(同上,日期可直接拿来比较)

select ename from emp a where HIREDATE<(select HIREDATE from emp where empno in(a.mgr));

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(以emp表为主，左连接查询)

select dname,emp.* from dept left join emp on dept.deptno=emp.deptno;

6．列出所有“CLERK”（办事员）的姓名及其部门名称。(域，注意())

select ename,(select dname from dept where deptno in(a.deptno)) as dname from emp a where JOB like'CLERK';

7．列出最低薪金大于1500的各种工作。 select job from emp where sal>1500;

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。(经典的两个表连接)

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

9．列出薪金高于公司平均薪金的所有员工。(反复查自己) select ename from emp where sal>( select avg( sal) from emp);

10．列出与“SCOTT”从事相同工作的所有员工。(排除自己)

select ename from emp where job in(select job from emp where ename like'SCOTT') and ename!='SCOTT' ;

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(any的用法，且排挤)

select ename,sal from emp where sal=any(select sal from emp wheredeptno=30) and deptno!=30;

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。(max的用法)

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

13．列出在每个(每个是关键字,对此group by)部门工作的员工数量、平均工资和平均服务期限。(经典的group by用法)

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

14．列出所有员工的姓名、部门名称和工资.(经典的两个表的连接查询，用具体的名称替换一个表中的主键的id (解决很多人在实际运用中会遇到的不能绑定多列的问题)，也可用where来查询 ,与题5比较)

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

15．列出所有部门的详细信息和部门人数。(因为是*，将显示dept和后面临时表b的全部字段(注意:不只是dept的字段,注意*号))

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

16．列出各种(与每个同义(参看题13))工作的最低工资。 select job,min(sal) from emp group by job ;

17．列出各个部门的MANAGER（经理,经理唯一，不用group by）的最低薪金。

select min(sal) from emp where job like'MANAGER';(因为MANAGER是值不是字段，所以不能用小写)

18．列出所有员工的年工资,按年薪从低到高排序。(nvl:空转化函数)

select ename,sal+nvl(comm,0) as sal from emp order by sal

 

注释：空转换函数，oracel:nvl；MySQL是：IFNULL(exp1,exp2)

 19. 查询每个部门的最高工资和最低工资，并且输出部门编号，部门名称

SELECT deptno,(SELECT dname from dept where deptno = e.deptno) as dname , max(sal) as '最高工资',min(sal) as '最低工资' FROM emp e GROUP BY deptno

 20. 查询每个部门职工的最高工资和最低工资

SELECT deptno,(select dname from dept where deptno = e.deptno) as dname,ename, max(sal) as '职工最高工资', min(sal) as '职工最低工资' from emp e where job = 'clerk' GROUP BY deptno;

21.对于emp中最低工资小于1000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资

select max(sal) as 最高工资,min(sal) as 最低工资,deptno as 部门号 from emp as b

where job ='CLERK' and 1000>(select min(sal) from emp as a where a.deptno = b.deptno) group by b.deptno

22.根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资

SELECT deptno,ename,sal from emp ORDER BY deptno DESC,sal ASC

23.列出张三所在部门的所有员工信息

SELECT * from emp where deptno = (SELECT deptno from emp where ename = 'SMITH')

24.列车部门名称、员工信息

select emp.*,dept.dname from emp, dept where emp.deptno = dept.deptno;

25.列出EMPLOYEES中工作为'CLERK'的员工的姓名，工作，部门号，部门名

select emp.*,dept.dname from emp, dept where emp.deptno = dept.deptno and emp.job='clerk';

26.对于emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为MANAGER）

1) select e.ename,e.mgr,(select ename from emp where empno = e.mgr)  from emp e where e.mgr > 0;

2) select a.ename,b.ename from emp as a,emp as b where a.mgr is not null and a.mgr = b.empno

27.对于DEPARTMENTS表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作

select dept.dname as 部门名,dept.deptno as 部门号,emp.ename as 员工名,emp.job as 工作 from dept,emp
where dept.deptno = emp.deptno and emp.job = 'CLERK'

28.对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序

select deptno,ename,sal from emp a where sal > (select AVG(b.sal) from emp b where b.deptno = a.deptno) ORDER BY deptno ASC

29.对于emp，列出各个部门中平均工资高于本部门平均水平的员工数和部门号，按部门号排序

select count(a.sal),deptno from emp a where sal > (select AVG(b.sal) from emp b where b.deptno = a.deptno) GROUP BY deptno ORDER BY deptno ASC

30.对于EMPLOYEES中工资高于本部门平均水平，且人数多与1人的，列出部门号，人数，按部门号排序

select count(a.empno) as 员工数,a.deptno as 部门号,avg(sal) as 平均工资 from emp as a

where (select count(c.empno) from emp as c where c.deptno = a.deptno and c.sal>(select avg(sal) from emp as b where c.deptno = b.deptno))>1

group by a.deptno order by a.deptno

 

31.对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数

select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal < a.sal) as 人数 from emp as a
where (select count(c.ename) from emp as c where c.sal < a.sal)>5