【MySQL】试题 -

【MySQL】试题 -

前言:以下试题中涉及的 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. 哪些人的薪水在部门的平均薪水之上
    先取出每个部门的平均薪水,再作为临时表与(薪水在其部门平均薪水上的部门名称,薪水表)连接
    select
        t.*,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.取得部门中所有人的平均薪水等级
    找到每个人的薪水等级(emp连接salgrade)
    select 
        e.ename,e.sal,e.deptno,s.grade 
    from 
        emp e 
    join 
        salgrade s 
    on 
        e.sal between s.losal and s.hisal;  
    基于以上结果继续按照deptno分组,求grade的平均值(直接两张表就行,不需要临时表)
    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.不准用组函数(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;
    select 
        deptno,avg(sal) as avgsal 
    from 
        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 
    from 
        salgrade 
    where 
        (select avg(sal) as avgsal,deptno from emp 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 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,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);
    找到高于(普通员工的最高薪水)的员工
    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));


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.取出每个薪水等级有多少个员工
    select 
        s.grade,count(*)
    from 
        emp e 
    join 
        salgrade s 
    on 
        e.sal between s.losal and s.hisal
    group by 
        s.grade;


12.列出所有员工及领导的名字
    select 
        a.ename "员工",b.ename "领导"
    from 
        emp a
    left join 
        emp b
    on 
        a.mgr = b.empno;


13.列出受雇日期早于直接上级的所有员工的姓名,受雇日期,直接上级的姓名,受雇日期,部门名称
    select 
        a.empno "员工",a.hiredate,b.ename "领导",b.hire    date,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;     


14.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
    select 
        e.*,d.dname 
    from 


        emp e 
    right join 
        dept d 
    on 
        e.deptno = d.deptno;


15.列出至少有5个员工的所有部门
    select
        deptno
    from
        emp
    group by
        deptno
    having 
        count(*) >= 5;


16.列出薪水比“SMITH”多的所有员工
    select 
        ename,sal
    from 
        emp
    where
        sal > (select sal from emp where ename = "SMITH");


17.列出最低薪水大于1500的各种工作及其从事此工作的全部雇员人数
    select 
        job,count(*)
    from
        emp
    group by
        job
    having 
        min(sal) > 1500;


18.列出在部门“SALES”<销售部>工作的员工的姓名,假定不知道销售部的部门编号 
    select 
        ename
    from 
        emp
    where
        deptno = (select deptno from dept where dname = "SALES");


19.列出薪水高于公司平均薪水的所有员工,所在部门,上级领导,雇员的工资等级
    select
        e.ename "员工",d.dname,l.ename "领导",s.grade    
    from 
        emp e
    join 
        dept d
    on
        e.empno = 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);


20.列出与“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.name <> "SCOTT";


21.列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
    select
        ename,sal
    from
        emp
    where
        sal in(select distinct sal from emp where deptno = 30) 
    and 
        deptno <> 30;


22.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水,部门名称
    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) ;


23.列出在每个部门工作的员工数量,平均工资和平均服务期限
    select
        d.deptno,
        count(e.ename) as ecount,
        ifnull(avg(e.sal),0) as avgsal,
        ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as avgtime
    from
        emp e
    right join
        dept d
    on
        e.deptno = d.deptno
    group by
        d.deptno;

    计算两个时间间隔的函数,语法为:

    timestampdiff(间隔类型,前一个日期,后一个日期)
    返回日期间的整数差。
    FRAC_SECOND   表示间隔是毫秒
    SECOND   秒
    MINUTE   分钟
    HOUR   小时
    DAY   天
    WEEK   星期
    MONTH   月
    QUARTER   季度
    YEAR   年


24.列出所有员工的姓名,部门名称,和薪水
    select
        e.ename,d.dname,e.sal
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno; 


25.列出所有部门的详细信息和人数
    select 
        d.deptno,d.dname,d.loc,count(e.ename)
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno
    group by
        d.deptno,d.name,d.loc;


26.列出各种工作的最低工资及从事此工作的雇员姓名
    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;


27.列出各个部门MANAGER(领导)的最低薪水
    select
        deptno,min(sal)
    from
        emp
    where
        job = "MANAGER"
    group by
        deptno; 


28.列出所有员工的年工资,按年薪从低到高排序
    select 
        ename,(sal + ifnull(comm,0))*12 as yearsal
    from 
        emp
    order by
        yearsal asc;


29.求出员工领导薪水超过3000的员工名称和领导
    select 
        a.ename "员工",b.ename "领导"
    from
        emp a
    join 
        emp b
    on 
        a.mgr = b.empno
    where
        b.sal > 3000;


30.求出部门名称带’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.name,d.loc;


31.给任职日期超过30年的员工加薪 10%
    update 
        emp 
    set 
        sal= sal*1.1 where timestampdiff(YEAR,hiredate,now())>30;


试题出处:https://www.bilibili.com/video/BV1fx411X7BD?p=1

ps:博主少写了3道哟!    ( •̀ ω •́ )✧