【MySQL】試題 --- 31道鞏固 SQL 語句的練習題

1.取得每個部門最高薪水的人員名稱： 先取出每個部門的最高薪水，再作為臨時表與（對應最高薪水的人員名稱表）連接配接......

前言：以下試題中涉及的 table 均來自部落客前面發的随筆“【MySQL】筆記（1）--- MySQL 資料庫概述；常用 DOS指令，SQL指令（初步）；”

1.取得每個部門最高薪水的人員名稱

    先取出每個部門的最高薪水，再作為臨時表與（對應最高薪水的人員名稱表）連接配接

    select

        e.name,t.*

    from 

        emp e

    join

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

    on 

        t.deptno = e.deptno and t.maxsal = e.sal

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

    先取出每個部門的平均薪水，再作為臨時表與（薪水在其部門平均薪水上的部門名稱，薪水表）連接配接

        t.*,e.ename,e.sal

    from

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

    on

        e,deptno = t,deptno and e.sal > t.avgsal;

3.取得部門中所有人的平均薪水等級

    找到每個人的薪水等級（emp連接配接salgrade）

    select 

        e.ename,e.sal,e.deptno,s.grade 

        emp e 

    join 

        salgrade s 

        e.sal between s.losal and s.hisal;  

    基于以上結果繼續按照deptno分組，求grade的平均值（直接兩張表就行，不需要臨時表）

        e.deptno,avg(s.grade)

        salgrade s

        e.sal between s.losal and s.hisal

    group by

        e.deptno;

4.不準用組函數（Max），取得最高薪水（給出兩種解決方案）

    第一種，降序（limit）

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

    第二種方案，表的自連接配接

    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 order by avgsal desc limit 1;

    第二種解決方案：

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

        deptno,avg(sal) as avgsal 

        emp 

    group by 

        deptno 

    having 

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

6.求平均薪水的等級最低的部門的部門名稱

    找出最低平均薪水對應的等級

     select 

        grade 

        salgrade 

    where 

        (select avg(sal) as avgsal,deptno from emp order by avgsal asc limit 1) between losal and hisal;

    找出（等于最低平均薪水的等級對應的）部門名稱，平均薪水，等級

        t.*,s.grade 

        (select d.dname,avg(sal) as avgsal from emp e join dept on e.deptno = d.deptno group by d.dname) t 

        t.avgsal between s.losal and s.hisal;

        s.grade = (select grade from salgrade where (select avg(sal) as avgsal,deptno from emp order by avgsal asc limit 1)

            between losal and hisal);

7.取出比普通員工（員工代碼沒有在mgr字段出現的）的最高薪水還要高的上司人姓名

  注意：not in 在使用時，後面小括号裡記得排除 null

    找到普通員工的最高薪水

    select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

    找到高于（普通員工的最高薪水）的員工

        ename,sal 

        sal > (select max(sal) 

        empno not in(select distinct mgr from emp where mgr is not null));

8.取出薪水最高的前五名

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

9.取出薪水最高的第六到第十的員工

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

10.取出最後入職的5名員工;

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

11.取出每個薪水等級有多少個員工

        s.grade,count(*)

        s.grade;

12.列出所有員工及上司的名字

        a.ename '員工',b.ename '上司'

        emp a

    left join 

        emp b

        a.mgr = b.empno;

13.列出受雇日期早于直接上級的所有員工的姓名，受雇日期，直接上級的姓名，受雇日期,部門名稱

        a.empno '員工',a.hiredate,b.ename '上司',b.hire    date,d.dname    

        emp a 

        emp b 

        a.mgr = b.empno 

        dept d

        a.deptno = d.deptno

        a.hiredate < b.hiredate;     

14.列出部門名稱和這些部門的員工資訊，同時列出那些沒有員工的部門

        e.*,d.dname 

    right join 

        dept d 

        e.deptno = d.deptno;

15.列出至少有5個員工的所有部門

        deptno

        emp

        count(*) >= 5;

16.列出薪水比“SMITH”多的所有員工

        ename,sal

    where

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

17.列出最低薪水大于1500的各種工作及其從事此工作的全部雇員人數

        job,count(*)

        job

        min(sal) > 1500;

18.列出在部門“SALES”<銷售部>工作的員工的姓名，假定不知道銷售部的部門編号 

        ename

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

19.列出薪水高于公司平均薪水的所有員工，所在部門，上級上司，雇員的工資等級

        e.ename '員工',d.dname,l.ename '上司',s.grade    

        e.empno = d.deptno

        emp l

        e.mgr = l.empno

        e.sal > (select avg(sal) from emp);

20.列出與“SCOTT”從事相同工作的所有員工及其部門名稱

        e.ename,e.job,d.dname

        e.deptno = d.deptno

        e.job = (select job from emp where ename = 'SCOTT') 

    and e.name <> 'SCOTT';

21.列出薪水等于部門30中員工的薪水的其他員工的姓名和薪水

        sal in(select distinct sal from emp where deptno = 30) 

    and 

        deptno <> 30;

22.列出薪水高于在部門30工作的所有員工的薪水的員工姓名和薪水，部門名稱

        e.ename,e.sal,d.dname

        e.deptno = d,deptno

        e.sal > (select max(sal) from emp where deptno = 30) ;

23.列出在每個部門工作的員工數量，平均工資和平均服務期限

        d.deptno,

        count(e.ename) as ecount,

        ifnull(avg(e.sal),0) as avgsal,

        ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime

    right join

        d.deptno;

    計算兩個時間間隔的函數，文法為：

    timestampdiff(間隔類型,前一個日期,後一個日期)

    傳回日期間的整數差。

    FRAC_SECOND   表示間隔是毫秒

    SECOND   秒

    MINUTE   分鐘

    HOUR   小時

    DAY   天

    WEEK   星期

    MONTH   月

    QUARTER   季度

    YEAR   年

24.列出所有員工的姓名，部門名稱，和薪水

        e.ename,d.dname,e.sal

        e.deptno = d.deptno; 

25.列出所有部門的詳細資訊和人數

        d.deptno,d.dname,d.loc,count(e.ename)

        d.deptno,d.name,d.loc;

26.列出各種工作的最低工資及從事此工作的雇員姓名

        e.ename,t.*

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

        e.job = t.job and e.sal = t.,minsal;

27.列出各個部門MANAGER（上司）的最低薪水

        deptno,min(sal)

        job = 'MANAGER'

        deptno; 

28.列出所有員工的年工資，按年薪從低到高排序

        ename,(sal + ifnull(comm,0))*12 as yearsal

    order by

        yearsal asc;

29.求出員工上司薪水超過3000的員工名稱和上司

        a.mgr = b.empno

        b.sal > 3000;

30.求出部門名稱帶’S‘的部門員工的工資合計，部門人數

        d.deptno,d.dname,d.loc,count(e.ename),ifnull(sum(e.sal),0) as sumsal

        d.dname like '%S%'

31.給任職日期超過30年的員工加薪 10%

    update 

    set 

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

試題出處：https://www.bilibili.com/video/BV1fx411X7BD?p=1

ps：部落客少寫了3道喲！    ( •̀ ω •́ )✧