MySQL数据库-练习题34题

1. 取得每个部门最高薪水的人员名称

• 第一步：取得每个部门最高薪水
  select deptno,max(sal) as maxsal from emp group by deptno;

• 第二步：将以上结果当做临时表t, t表和emp e表进行连接，条件是: t.deptno = e.deptno and t.maxsal = e.sal

select
   e.ename ,t.*
from
   (select deptno ,max(sal) as maxsal from emp group by deptno) t
join
   emp e
on 
   t.deptno = e.deptno and t.maxsal = e.sal;

2. 哪些人的薪水在部门的平均薪水之上

• 第一步：找出每个部门的平均薪水
  select deptno,avg(sal) as avgsal from emp group by deptno;
  

• 第二步：将以上查询结果当做t表，t 和 emp表连接
  条件：部门编号相同，并且emp的sal大于t表的avgsal

select
   t.*, e.ename ,e.sal
from
   emp e
join
   (select deptno,avg(sal) as avgsal from emp group by deptno)t
on 
   e.deptno = t.deptno and e.sal > t.avgsal;

3. 取得部门中(所有人的)平均的薪水等级

平均的薪水等级:先计算每一个薪水的等级，然后找出薪水等级的平均值。
平均薪水的等级:先计算平均薪水，然后找出每个平均薪水的等级值。

• 第一步：找出每个人的薪水等级
  emp e 和salgrade s表连接
  连接条件：e.sal between s.losal and s.hisal

select
  e.enane ,e.sal ,e .deptno ,s.grade
from
  emp e
join
  salgrade s
on
  e.sal between s.losal and s.hisal order by deptno;

• 第二步：基于以上的结果继续按照deptno分组，求grade的平均值

select
  e.deptno , avg (s.grade)
from
  emp e
join
  salgrade s
on
  e.sal between s.1osal and s.hisal
group by
  e.deptno;

4.不准用组函数(Max)，取得最高薪水

第一种：降序，limit 1【按照降序排，取第一个】
select ename,sal from emp order by sal desc limit 1;

第二种：select max(sal) from emp;

第三种：表的自连接

select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal)

找出a表中比b表中值小的数据
select 
  distinct a.sal //去重
from 
  emp a 
join 
  emp b 
on 
  a.sal < b.sal

5.取得平均薪水最高的部门的部门编号

第一种：
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：降序选第一个
select deptno , avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

第二种：max
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

select 
  deptno ,avg(sal) as avgsal 
from 
  emp 
group by 
  deptno 
having 
  avgsal =(select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

6. 取得平均薪水最高的部门的部门名称【和第五题类似】

 select 
   d.dname , avg(e.sal) as avgsal 
 from 
   emp e
 join 
   dept d
 on
   e.deptno = d.deptno 
 group by 
   d.dname
 order by 
   avgsal desc 
 limit 
   1;

7. 求平均薪水的等级最低的部门的部门名称

第一步：找出每个部门的平均薪水
select deptno ,avg (sal) as avgsal from emp group by deptno;

第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件是：t.avgsal between s. losal and s.hisal

select
  t.* ,s.grade
from
  (select d.dname ,avg (sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
  salgrade s
on
  t.avgsal between s. losal and s.hisal ;

第三步：添加where语句

select
  t.* ,s.grade
from
  (select d.dname ,avg (sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
join
  salgrade s
on
  t.avgsal between s. losal and s.hisal ;
where
  s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

注意：薪水最低，等级一定最低；但是薪水不是最低，但可能等级也是最低的

8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

maysql> select distinct mgr from enp;

员工编号没有在以上范围内的都是普通员工

第一步：找出普通员工的最高薪水
not in 在使用的时候，后面小括号中记得排除NULL
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

注意：

select distinct mgr from emp where mgr is not null;

第二步：找出高于1600的
select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

注意：比普通员工最高工资还要高的肯定是领导

9. 取得薪水最高的前五名员工

select ename,sal from emp order by sal desc limt 5;

10.取得薪水最高的第六到第十名员工

select ename,sal from emp order by sal desc limt 5，5;

11.取得最后入职的5名员工

日期也可以降序、升序
select ename,hiredate from emp order by hiredate desc limit 5;

12.取得每个薪水等级有多少员工

分组count
第一步：找出每个员工的薪水等级

select 
  e.ename ,e. sal,s.grade 
from 
  emp e 
join 
  salgrade s 
on 
  e.sal between s.losal and s.hisal；

第二步：继续按照grade分组统计数量

select 
  s.grade,count(*)
from 
  emp e 
join 
  salgrade s 
on 
  e.sal between s.losal and s.hisal；
ground by
  s.grade;

13.面试题

有3个表s(学生表)，c(课程表), sc（学生选课表)
s (SNo, SNAME)代表(学号,姓名)
c (CNO,CNAME,CTEACHER)代表(课号,课名，教师)
sc ( SNo,CNo,SCGRADE)代表(学号，课号，成绩)
问题:
1,找出没选过”黎明”老师的所有学生姓名。
2,列出2门以上(含2门)不及格学生姓名及平均成绩。
3,即学过1号课程又学过2号课所有学生的姓名。

14.列出所有员工及领导的姓名

select
  a.enane'员工',b.enane '领导'
from
  emp a
left join
  emp b
on	
  a.mgr = b.empno;

15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select
  a.ename '员工',a.hiredate,b.ename '领导',b.hiredate，d.dname
from
  emp a
join
  emp b
on
  a.mgr = b.empno
join 
  dept d
on 
  a.deptno = d.deptno
where
  a.hiredate < b.hiredate

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

select
  e.* ,d.dname
from
  emp e
right join
  dept d
on
  e.deptno = d.deptno ;

17.列出至少有5个员工的所有部门

按照部门编号分组，计数，筛选出>=5

select
  deptno
from
  emp
group by
  deptno
having
  count(*) >= 5;

18.列出薪金比“SMITH”多的所有员工信息

select ename,sal from emp where sal > (select sal from emp where ename = ’ SMITH’);

19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数

第一步：列出所有“CLERK”（办事员）的姓名
select ename , job from emp where job = "CLERK’;

第二步：列出所有“CLERK”（办事员）的姓名及其部门名称

select
  e.enane ,e.job ,d.dname
from
  emp e
join
  dept d
on
  e.deptno = d.deptno
where
  e.job = "CLERK";

第三步：列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数

每个部门的人数：
select deptno,count(*) as deptcount from emp group by deptno;

将两张表进行表连接

select
	t1.*,t2.deptcount
from
	(select
	   e.enane ,e.job ,d.dname ,d.deptno
	from
	  emp e
	join
	  dept d
	on
	  e.deptno = d.deptno
	where
	  e.job = 'CLERK') t1
join
	(select deptno，count(*) as deptcount from emp group by deptno) t2
on
	t1.deptno = t2.deptno;

20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

按照工作岗位分组求最小值
select job , count(*) from emp group by job having min(sal) >1500;

21.列出在部门"SALES"<销售部>工作的员工的姓名，假定不知道销售部的部门编号.

select ename., from emp where deptno = (select deptno from dept where dname = ‘SALES’ );

22.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级.

select
	e.enane '员工' ,d.dnane ,l.enane '领导',s.grade
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
left join
	emp l
on
	e.mgr = l.empno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
where
	e.sal > (select avg (sal)from emp);

23.列出与"SCOTT"从事相同工作的所有员工及部门名称

第一步：列出"SCOTT"从事的工作
select job from emp where ename = ‘SCOTT’;

第二步：列出相同工作的所有员工及部门名称

select
	e.ename ,e.job ,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.job = (select job from emp where ename = 'SCOTT')
and
	e.ename ='SCOTT';

24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

第一步：列出部门30中员工的薪金
select distinct sal from emp where deptno = 30;

第二步：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

select 
	ename , sal 
from 
	emp 
where 
	sal in (select distinct sal from emp where deptno = 30) 
and 
	deptno <> 30 ;

Empty set (o.oo sec)

25.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

第一步：列出部门30中员工的最高薪金
select max (sal) from emp where deptno = 30 ;

第二步：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

select
	e.enane ,e.sal ,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.sal > (select max (sal) from emp where deptno = 30) ;

26.列出在每个部门工作的员工数量，平均工资和平均服务期限

没有员工的部门人数为0

select
	d.deptno , count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal,ifnull(avg (timestarapdiff (YEAR,hiredate,now ())),0) as avgservicetime
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

注：在mysql当中怎么计算两个日期的"年差”,差了多少年?
TimeStampDiff(间隔类型，前一个日期，后一个日期)【间隔类型可以是年、月、日、分、秒、天、星期、季度】
比如：timestampdiff(YEAR,hiredate, now()）

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

select
	e.ename,d.dname,e.sal
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

28.列出所有部门的详细信息和人数

select
	d.deptno,d.dnane,d.loc, count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno,d.dname, d.loc;

29.列出各种工作的最低工资及从事此工作的雇员姓名

第一步：列出各种工作的最低工资

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

第二步：emp e和以上t连接

select
	e.enane , t.*
from
	emp e
join
	(select
	  job,min (sal) as minsal
    from
	  emp
    group by
	  job)t
on
	e.job = t.job and e.sal = t.minsal ;

30.列出各个部门的MANAGER(领导)的最低薪金

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

31.列出所有员工的年工资，按年薪从低到高排序

select
	ename , (sal + ifnull (comm , 0))* 12 as yearsal
from
	emp
order by
	yearsal asc;

32.求出员工领导的薪水超过3000的员工名称与领导

select
	a.ename '员工',b.ename'领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno
where
	b.sal > 3000;

33.求出部门名称中，带’s’字符的部门员工的工资合计、部门人数

select
	d.deptno ,d.dname ,d.loc,count(e.ename),ifnull (sum(e.sal) ,0) as sumsal
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dnane like '%S%'
group by
	d.deptno,d.dnane,d.loc;

34.给任职日期超过30年的员工加薪10%

update 
	emp 
set 
	sal = sal * 1.1 
where 
	timestampdiff(YEAR，hiredate，now()) > 30;