MySQL多表查询练习专题

表格创建数据及基础知识在 上一篇文章 SQL复习（三）-- 多表联合查询

 https://blog.csdn.net/wuyanwenyun/article/details/105511663

答案不唯一，只给出参考答案，查询结果一致即可，后期出优化篇，ctrl + A 查看全部答案

-- 【1】列出所有员工的年工资，按年薪从低到高排序。

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

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

select*from emp where sal>(select sal from emp where ename='smith');

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

select e.ename,emgr.ename mgrname from emp e left join emp emgr on e.mgr=emgr.empno;  

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

select e.*,emgr.hiredate mdate from emp e left join emp emgr on e.mgr=emgr.empno where e.hiredate < emgr.hiredate;  

-- 【5】列出部门名称和这些部门的员工信息，包括那些没有员工的部门。

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

-- 【6】列出所有job为“CLERK”（办事员）的姓名及其部门名称。

    select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno where e.job='clerk'; 

    select t.ename,t.job,d.dname from (select*from emp where job='CLERK') t left join dept d on t.deptno=d.deptno;

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

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

-- 【8】列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

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

    select e.ename from emp e left join dept d on e.deptno=d.deptno where d.dname='sales';

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

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

-- 【10】列出与“SCOTT”从事相同工作的所有员工。     

    select * from emp where job=(select job from emp where ename='scott');
-- 【11】列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

    select * from emp where job=(select job from emp where ename='scott');
-- >all, 要比子查询的所有结果都大, 大于最大值  
-- >any, 大于子查询结果的任意一个即可, 大于最小值

    select ename, sal from emp where sal >all (select sal from emp where deptno=30); 
-- 【12】列出在每个部门工作的员工数量,平均工资和平均服务期限。

-- datediff(date1, date2), 用于计算两个日期间的天数  
-- round(m, n), 对数字进行四舍五入, m表示数字, n表示小数位数  

select
	d.deptno,
	ifnull( t.cnt, 0 ) cnt,
	ifnull( t.avg, 0 ) avg,
	ifnull( t.year, 0 ) year 
from
	(
	select
		deptno,
		count(*) cnt,
		avg( sal ) avg,
		round( avg( datediff( now(), hiredate )/ 365 ), 2 ) year 
	from
		emp 
	group by
		deptno 
	) t
	right join dept d on t.deptno = d.deptno;

-- 【13】列出所有员工的姓名部门名称和工资。

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

-- 【14】列出从事同一种工作但属于不同部门的员工的一种组合。

select e1.ename en1, e1.job job1, e1.deptno d1, e2.ename en2, e2.job job2, e2.deptno d2  
from emp e1  
join emp e2  
on e1.job=e2.job and e1.deptno != e2.deptno  
where e1.ename>e2.ename;  

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

    select d.*,t.cnt from dept d left join (select deptno,count(*) cnt from emp group by deptno) t on d.deptno=t.deptno;

-- 【16】列出各种工作的最低工资。  

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

-- 【17】列出各个部门的MANAGER（经理）的最低薪金。

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