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;  
--雇用日が直接の上司より早いすべての従業員をリストします.
select e.*,emgr.hiredate mdate from emp e left join emp emgr on e.mgr=emgr.empno where e.hiredate < emgr.hiredate;  
--従業員がいない部門を含め、部門名とその部門の従業員情報をリストします.
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);--各部門に勤務する従業員数、平均給与および平均サービス期間をリストします.

-- 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;  

--すべての部門の詳細と部門数をリストします.
    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;