老杜34题

老杜34题

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

-- 1.先获得每个部门的最高薪水
-- select deptno, max(sal) from emp group by deptno
-- 2.连接员工表 e和先获得每个部门的最高薪水表 t 连接条件是 t.deptno = e.deptno and t.max(sal) = e.sal;
select e.ename, e.sal, e.deptno 
from emp e
join (select deptno, max(sal) as sal from emp group by deptno) t
on t.deptno = e.deptno and t.sal = e.sal;

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

-- 1.获取部门的平均薪水
select deptno, avg(sal) as avgsal from emp group by deptno
-- 2.连接t表和e表，条件：e.deptno = t.deptno and e.sal > t.avgsal
select 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、取得部门中（所有人的）平均的薪水等级，如下：

between and 左边放小数 右边放大数

-- 1.获取部门所有人的薪水等级emp e和salgrade s表连接 连接条件：e.sal between s.losal and s.hisal
-- select e.deptno, e.ename, s.grade
-- from emp e
-- join salgrade s
-- on e.sal between s.losal and s.hisal

-- 2.得到的平均薪水等级
select e.deptno, e.ename, avg(grade)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by deptno

4、不准用组函数（Max），取得最高薪水（给出两种解决方案）

-- 薪资逆序，取第一个
-- select sal from emp order by sal desc limit 1

-- 表的自连接， 先去重得到t表得到除了最大数的全部数，再用not in得到最大数
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);

5、取得平均薪水最高的部门的部门编号（至少给出两种解决方案）

-- 1.得到部门的平均薪资
-- select deptno, avg(sal) from emp GROUP BY deptno
-- 2.得到平均薪资最高的部门编号

select deptno from emp GROUP BY deptno order by avg(sal) desc limit 1

6、取得平均薪水最高的部门的部门名称

-- 获取每个部门的平均薪水,根据deptno 连接表
select d.dname, e.avgsal 
from (select deptno, avg(sal) as avgsal from emp group by deptno) e 
join dept d
on e.deptno = d.deptno

-- 获取平均薪资最高的部门
select d.dname, e.avgsal 
from (select deptno, avg(sal) as avgsal from emp group by deptno) e 
join dept d
on e.deptno = d.deptno
order by avgsal desc
limit 1

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

-- 得到去重的mgr编号
select distinct mgr from emp where mgr is not null
-- 得到员工中最高薪水 除了领导就是普通员工 null除外这是boss
select max(sal) from emp where empno not in (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、取得薪水最高的前五名员工

-- 利用limit 0开始
select ename, sal from emp order by sal desc limit 0, 5

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

-- 利用limit 5开始
select ename, sal from emp order by sal desc limit 5, 5

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

select ename, hiredate from emp order by hiredate desc limit 5

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

-- 获得每个员工的薪水等级
select e.empno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal 
-- 按照等级分组， 计数
select count(e.empno), s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal GROUP BY s.grade

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

-- emp表自连接 a.mgr = b.empno, ifnull用于select中
select a.ename '员工', ifnull(b.ename, '没有上级') '领导' from emp a left join emp b on a.mgr = b.empno 

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

-- 找到上级领导的受雇日期
select b.empno, b.hiredate from emp a join emp b on a.mgr = b.empno
-- 找到员工的受雇日期 早于 上级领导的受雇日期
select distinct e.empno, e.ename, d.dname
from emp e
join (select b.empno, b.hiredate from emp a join emp b on a.mgr = b.empno) t
on e.hiredate < t.hiredate and e.mgr = t.empno
join dept d
on e.deptno = d.deptno

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

-- 连接emp和dept显示所有的部门无论空
select d.dname, e.* from emp e right join dept d on e.deptno = d.deptno

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

-- 得到每个部门的员工数
select deptno, count(empno) from emp group by deptno
-- 得到大于5的部门
select dname, count(*) from emp join dept on emp.deptno = dept.deptno group by emp.deptno having count(*) >= 5

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

-- 子查询
select * from emp where sal > (select sal from emp where ename = 'smith')

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

-- 获取clerk的名字好部门名称和部门编号
select e.ename,e.job,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where job = 'clerk'
-- 获取每个部门的人数
select deptno, count(*) as deptcount from emp group by deptno

select t1.*, t2.deptcount
from (select e.ename,e.job,e.deptno,d.dname from emp e join dept d on e.deptno = d.deptno where 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、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等 级.

-- 1.得到公司的平均薪水
select avg(sal) from emp
-- 2.得到员工
select a.ename,d.dname,b.ename,s.grade
from emp a
left join emp b
on a.mgr = b.empno
join salgrade s
on a.sal between losal and hisal
join dept d
on a.deptno = d.deptno
where a.sal > (select avg(sal) from emp)

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

-- 找到scott的工作
select job from emp where ename = 'scott'
-- 寻找员工名字和部门
select ename,dname
from emp
join dept
on emp.deptno = dept.deptno
where emp.job = (select job from emp where ename = 'scott') and ename != 'scott'

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

-- 得到30部门最高薪水
select max(sal) from emp where deptno = 30
-- 得到员工
select ename,sal,dname
from emp 
join dept
on emp.deptno = dept.deptno
where emp.sal > (select max(sal) from emp where deptno = 30)

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

在mysql当中怎么计算两个日期的“年差”，差了多少年？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年

select 	d.dname,count(e.ename),ifnull(avg(e.sal),0),ifnull(avg(TIMESTAMPDIFF(YEAR,e.HIREDATE,now())), 0)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

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

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

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

-- 所有部门也包括没员工的
select d.*,count(e.empno)
from emp e
right join dept d
on e.deptno = d.deptno
group by e.deptno

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

-- 找到各种工作的最低工资
select min(sal) as sal from emp group by job
-- 得到最低工资的员工
select e.* 
from emp e
join (select job,min(sal) as sal from emp group by job) t
on e.job = t.job and e.sal = t.sal

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

-- 得到领导
select distinct b.deptno , min(b.sal)from emp a join emp b on a.mgr = b.empno group by deptno

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

select ename, sal*12 as income from emp order by sal 

33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.

-- like 用法 '_k' 第二个字是k '%k' 以k结尾 'k%' 以k开头 '%k% 含有k的
select d.*, count(e.ename), ifnull(sum(e.sal), 0)  sumsal
from dept d
left join emp e
on d.deptno = e.DEPTNO
where d.dname like '%S%'
group by d.deptno,d.dname,d.loc;

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

-- update加薪
update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;