MySQL第十二章練習問題

文書ディレクトリ

問題と答え

転送ゲート

テーマと答え
1.各部門の最高賃金を取得した人員名

select depno,max(sal) as maxsal from emp group by deptno;

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

2.どの人の給料が部門の平均給料の上にあるか

select deptno,avg(sal) as avgsal from emp group by deptno;

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

3.部門全体の平均給与等級を引き出す

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

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

4.最高賃金をもらう

select enmae,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)

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;

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 deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1
);

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;

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;

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;

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フィールドに表示されていない)の最高賃金よりも高いリーダー名を取得する

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
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.最も給料の高い上位5人の従業員

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

10.最も高い給料をもらった6～10人目の従業員

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.ename,e.sal,s.grade 
from emp e 
join salgrade s 
on e.sal between s.losal and s.hisal;

select s.grade ,count(*)
from emp e 
join salgrade s 
on e.sal between s.losal and s.hisal
group 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.ename '  ', b.ename '  '
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;

は、部門名およびこれらの部門の従業員情報をリストし、従業員がいない部門

をリストします.

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

17.少なくとも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」(事務員)の名前とその部門名、部門の人数をリストする

select ename,job from emp where job = 'CLERK';

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

select deptno, count(*) as deptcount from emp group by deptno;

select t1.*,t2.deptcount
from (select e.ename,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」<販売部>に勤務する従業員の名前を列挙し、販売部の部門番号が分からないと仮定する.

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

22.給与が会社の平均給与より高いすべての従業員、所属部門、上司、従業員の給与等級をリストする.

select e.ename '  ',d.dname,l.ename '  ',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」と同じ仕事をしているすべての従業員と部門名をリストする

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の従業員の給与に等しい他の従業員の名前と給与をリストする.

select distinct sal from emp where deptno = 30;

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

25.部門30で働いているすべての従業員の給与より高い給与の従業員の名前と給与をリストする.部門名

select max(sal) from emp where deptno = 3
select e.ename,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.各部門で働く従業員数、平均賃金、平均サービス期間のリスト

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

mysqlの中でどのように2つの日付の“年差”を計算して、何年差がありますか?TimeStampDiff(間隔タイプ、前の日付、後の日付)間隔タイプ:SECOND秒、MINUTE分、HOUR時間、DAY日、WEEK曜日、MONTH月、QUARTE四半期、YEAR年27.すべての従業員の名前、部門名、給与をリストします.

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

28.すべての部門の詳細と人数をリストする

select d.deptno,d.dname,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;

select e.ename,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.dname like '%S%'
group by d.deptno,d.dname,d.loc;

34.勤務日が30年を超えた従業員に10%の昇給を与える.

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

トランスファゲート
下一章:MySQL第十一章悲観ロックと楽観ロック