mysql-34道题目

1. 取得每个部门最高薪水的人员名称
  1. 取得每个部门最高薪水,按照部门编号进行分组。

    select deptno, max(sal) from emp group by deptno;
    
    +--------+----------+
    | deptno | max(sal) |
    +--------+----------+
    |     10 |  5000.00 |
    |     20 |  3000.00 |
    |     30 |  2850.00 |
    +--------+----------+
    3 rows in set (0.00 sec)
  2. 将第一步得到的表,作为临时表和emp表进行左连接,查出部门每个部门最高薪资的这几个员工。

    select   
     e.ename, t.* 
    from 
     (select deptno, max(sal) as msal from emp group by deptno) t 
    left join  
     emp e 
    on 
     t.deptno = e.deptno and t.msal = e.sal;
    +-------+--------+---------+
    | ename | deptno | msal    |
    +-------+--------+---------+
    | KING  |     10 | 5000.00 |
    | SCOTT |     20 | 3000.00 |
    | FORD  |     20 | 3000.00 |
    | BLAKE |     30 | 2850.00 |
    +-------+--------+---------+
    4 rows in set (0.00 sec)
2. 哪些人的薪水在部门的平均薪水之上
  1. 先求出每个部门的平均薪水

    select deptno, avg(sal) from emp group by deptno;
    
    +--------+-------------+
    | deptno | avg(sal)    |
    +--------+-------------+
    |     10 | 2916.666667 |
    |     20 | 2175.000000 |
    |     30 | 1566.666667 |
    +--------+-------------+
    3 rows in set (0.00 sec)
  2. 再找出每个部门那些高于本部门平均薪水的人

    select
     e.deptno, e.ename, e.sal, t.avgsal
    from
     (select deptno, avg(sal) as avgsal from emp group by deptno) t
    join 
     emp e
    on
     e.deptno = t.deptno and e.sal > t.avgsal
    order by e.deptno, e.sal;
    
    +--------+-------+---------+-------------+
    | deptno | ename | sal     | avgsal      |
    +--------+-------+---------+-------------+
    |     10 | KING  | 5000.00 | 2916.666667 |
    |     20 | JONES | 2975.00 | 2175.000000 |
    |     20 | FORD  | 3000.00 | 2175.000000 |
    |     20 | SCOTT | 3000.00 | 2175.000000 |
    |     30 | ALLEN | 1600.00 | 1566.666667 |
    |     30 | BLAKE | 2850.00 | 1566.666667 |
    +--------+-------+---------+-------------+
    6 rows in set (0.00 sec)
3. 取得部门中(所有人的)平均的薪水等级
  1. 先求出每个人的薪资等级

  2. 按照部门分组,对该组中所有人的薪资等级求取平均值

    select
     e.deptno, avg(grade)
    from
     emp e
    join
     salgrade s
    on 
     e.sal between s.losal and s.hisal
    group by
     e.deptno
    order by 
     e.deptno;
    
    +--------+------------+
    | deptno | avg(grade) |
    +--------+------------+
    |     10 |     3.6667 |
    |     20 |     2.8000 |
    |     30 |     2.5000 |
    +--------+------------+
    3 rows in set (0.00 sec)
4. 不准用组函数(Max),取得最高薪水
  1. 按照工资的降序排序找出工资最低的那个人,然后将表分页输出(limit)。

    mysql> select ename, sal from emp order by sal desc limit 0, 1;
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    +-------+---------+
    1 row in set (0.00 sec)
5. 取得平均薪水最高的部门的部门编号
  1. 先按照部门编号进行分组,求出每个部门的平均薪水

  2. 按照平均薪水进行倒序排序,输出表中第一行数据。

    select
     deptno, avg(sal) as avgsal
    from
     emp
    group by
     deptno
    order by
     avgsal desc
    limit 0, 1;
    
    +--------+-------------+
    | deptno | avgsal      |
    +--------+-------------+
    |     10 | 2916.666667 |
    +--------+-------------+
    1 row in set (0.00 sec)
6. 取得平均薪水最高的部门的部门名称
  1. 在第5题的基础上,将第五题得到的表与部门表进行连接,查出部门名称。

    select
     t.deptno, d.dname
    from
     (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 0, 1) t
    join
     dept d
    on 
     t.deptno = d.deptno;
    
    +--------+------------+
    | deptno | dname      |
    +--------+------------+
    |     10 | ACCOUNTING |
    +--------+------------+
    1 row in set (0.00 sec)
7. 求平均薪水的等级最低的部门的部门名称
  1. 按照部门分组,求各个部门的平均薪水

  2. 与工资等级表连接,求出各个组的部门等级

  3. 接着与部门表连接,找出部门名称,最后排序,使用limit显示第一个。

  4. 上面第3步是错误的,因为存在这种情况:多个部门的薪水等级均为最低等级。

  5. 接着第2步向下进行->所以先求出最低等级是多少,然后查询最低等级的有哪些部门。

    ------------------按照部门编号,进行分组,求出各个部门平均薪水的等级
    select
     t.deptno, t.avgsal, s.grade, d.dname
    from
     (select deptno, avg(sal) as avgsal from emp group by deptno) t
    join
     salgrade s
    on
     t.avgsal between s.losal and s.hisal
    join
     dept d
    on 
     t.deptno = d.deptno
    order by 
     s.grade;
    
    -------------------------求出最低等级
    select
     s.grade
    from
     (select deptno, avg(sal) as avgsal from emp group by deptno) t
    join
     salgrade s
    on
     t.avgsal between s.losal and s.hisal
    join
     dept d
    on 
     t.deptno = d.deptno
    order by 
     s.grade
    limit 1; 
    --------------------求出平均薪水的等级 等于最低等级的部门
    
    select
     t.avgsal, s.grade, d.dname
    from
     (select deptno, avg(sal) as avgsal from emp group by deptno) t
    join
     salgrade s
    on
     t.avgsal between s.losal and s.hisal
    join
     dept d
    on 
     t.deptno = d.deptno
    where
     s.grade = (
    
             select
                 s.grade
             from
                 (select deptno, avg(sal) as avgsal from emp group by deptno) t
             join
                 salgrade s
             on
                 t.avgsal between s.losal and s.hisal
             join
                 dept d
             on 
                 t.deptno = d.deptno
             order by 
                 s.grade
             limit 1
     );
    
    +-------------+-------+-------+
    | avgsal      | grade | dname |
    +-------------+-------+-------+
    | 1566.666667 |     3 | SALES |
    +-------------+-------+-------+
8. 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

(最开始没有思考出不需要中间过程、直接完成的sql语句。现在在第2步解决这个了问题,运用外连接,匹配不上的赋值为null。)

  1. 找出所有的领导编号

       select distinct mgr from emp where mgr is not null;
       +------+
       | mgr  |
       +------+
       | 7902 |
       | 7698 |
       | 7839 |
       | 7566 |
       | 7788 |
       | 7782 |
       +------+
       6 rows in set (0.00 sec)
  2. 将员工表与领导编号表左连接,员工编号与领导编号匹配相同的则是领导,匹配不上的则是员工,由于是外连接,匹配不上时,会赋值为null,通过判断是否为null来过滤出员工。找出普通员工的最高薪资。

       select 
         e.sal
       from
         emp e
       left join
         (select distinct mgr from emp where mgr is not null) l
       on 
         e.empno = l.mgr
       where
         l.mgr is null
       order by 
         e.sal desc
       limit 0, 1;
  3. 找出比普通最高薪资高的领导的名称。领导编号表与员工表连接,求出领导的姓名,where字句对领导工资与员工最高工资进行判断

     select
         e.ename, e.sal
       from
         (select distinct mgr from emp where mgr is not null) l
       join
         emp e
       on    
         l.mgr = e.empno
       where
         e.sal > (
             select 
                 e.sal
             from
                 emp e
             left join
                 (select distinct mgr from emp where mgr is not null) l
             on 
                 e.empno = l.mgr
             where
                 l.mgr is null
             order by 
                 e.sal desc
             limit 0, 1
         );
    
       +-------+---------+
       | ename | sal     |
       +-------+---------+
       | FORD  | 3000.00 |
       | BLAKE | 2850.00 |
       | KING  | 5000.00 |
       | JONES | 2975.00 |
       | SCOTT | 3000.00 |
       | CLARK | 2450.00 |
       +-------+---------+
       6 rows in set (0.00 sec)
9. 取得薪水最高的前五名员工
  1. 使用降序和分页函数

    select
     ename, sal
    from 
     emp
    order by
     sal desc
    limit 0, 5;
    
    +-------+---------+
    | ename | sal     |
    +-------+---------+
    | KING  | 5000.00 |
    | SCOTT | 3000.00 |
    | FORD  | 3000.00 |
    | JONES | 2975.00 |
    | BLAKE | 2850.00 |
    +-------+---------+
    5 rows in set (0.00 sec)
10. 取得薪水最高的第六到第十名员工
  1. 使用降序和分页函数

    select
     ename, sal
    from 
     emp
    order by
     sal desc
    limit 5, 5;
    
    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | CLARK  | 2450.00 |
    | ALLEN  | 1600.00 |
    | TURNER | 1500.00 |
    | MILLER | 1300.00 |
    | MARTIN | 1250.00 |
    +--------+---------+
11. 取得最后入职的5名员工
  1. 对入职日期字段进行排序

    mysql>  select ename,hiredate from emp order by hiredate desc limit 0, 5;
    +--------+------------+
    | ename  | hiredate   |
    +--------+------------+
    | ADAMS  | 1987-05-23 |
    | SCOTT  | 1987-04-19 |
    | MILLER | 1982-01-23 |
    | FORD   | 1981-12-03 |
    | JAMES  | 1981-12-03 |
    +--------+------------+
12. 取得每个薪水等级有多少员工
  1. 按薪水等级进行分组,分完组之后统计该组的数据数量

    select 
     s.grade, count(*)
    from
     emp e
    join
     salgrade s
    on
     e.sal between s.losal and s.hisal
    group by
     s.grade
    order by 
     s.grade;
    
    +-------+----------+
    | grade | count(*) |
    +-------+----------+
    |     1 |        3 |
    |     2 |        3 |
    |     3 |        2 |
    |     4 |        5 |
    |     5 |        1 |
    +-------+----------+
    5 rows in set (0.00 sec)
13. 面试题

首先创建表3个表,向表中插入数据。

drop table if exists sc;
create table sc(
    sno varchar(200),
    cno varchar(200),
    scgrade varchar(200)
);

create table s(
    sno varchar(200),
    sname varchar(200)
);

create table c(
    cno varchar(200),
    cname varchar(200),
    cteacher varchar(200)
);

insert into c (cno, cname, cteacher) values ('1', 'yuwen', 'zhang');
insert into c (cno, cname, cteacher) values ('2', 'zhengzhi', 'wang');
insert into c (cno, cname, cteacher) values ('3', 'yingyu', 'li');
insert into c (cno, cname, cteacher) values ('4', 'shuxue', 'zhao');
insert into c (cno, cname, cteacher) values ('5', 'wuli', 'liming');

INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'student 1');
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'student 2');
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'student 3');
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'student 4');

INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60');
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80');

mysql> select * from s;
+------+-----------+
| sno  | sname     |
+------+-----------+
| 1    | student 1 |
| 2    | student 2 |
| 3    | student 3 |
| 4    | student 4 |
+------+-----------+
4 rows in set (0.00 sec)

mysql> select * from c;
+------+----------+----------+
| cno  | cname    | cteacher |
+------+----------+----------+
| 1    | yuwen    | zhang    |
| 2    | zhengzhi | wang     |
| 3    | yingyu   | li       |
| 4    | shuxue   | zhao     |
| 5    | wuli     | liming   |
+------+----------+----------+
5 rows in set (0.00 sec)

mysql> select * from sc;
+------+------+---------+
| sno  | cno  | scgrade |
+------+------+---------+
| 1    | 1    | 40      |
| 1    | 2    | 30      |
| 1    | 3    | 20      |
| 1    | 4    | 80      |
| 1    | 5    | 60      |
| 2    | 1    | 60      |
| 2    | 2    | 60      |
| 2    | 3    | 60      |
| 2    | 4    | 60      |
| 2    | 5    | 40      |
| 3    | 1    | 60      |
| 3    | 3    | 80      |
+------+------+---------+
12 rows in set (0.00 sec)
  1. 问题1:找出没选过“黎明”老师的所有学生姓名。

    • 将学生选课表与课程表进行连接,筛选出选“黎明老师课的学生”,作为一个临时表。与学生表进行外连接。筛选出匹配后为null的学生,即是没选黎明老师课的学生。

      ----------选过黎明老师课的学生
      select 
         sc.*, c.*
      from
         sc 
      join
         c
      on 
         sc.cno = c.cno
      where
         sc.cno = '5';
      
      --------------将学生表与该表连接
      select
         s.sno, s.sname
      from
         s
      left join
         (
             select 
                 sc.sno, c.cteacher
             from
                 sc 
             join
                 c
             on 
                 sc.cno = c.cno
             where
                 sc.cno = '5'
         ) t
      on
         s.sno = t.sno
      where
         t.sno is null;
      
      +------+-----------+
      | sno  | sname     |
      +------+-----------+
      | 3    | student 3 |
      | 4    | student 4 |
      +------+-----------+
      2 rows in set (0.00 sec)
  2. 列出2门以上(含2门)不及格学生姓名及平均成绩

    • 利用学生选课表,先用where条件将学生选课表里高于60的成绩过滤掉,只剩下不及格的成绩。

    • 然后按照学号进行分组,计算出每个学生不及格成绩科目的数量。

    • 利用having对分组后的数据进行过滤,选出不及格科目数量>=2的学生。

    • 按学号分组,求出每个学生的平均成绩,作为临时表tscore。

    • 与学生表进行连接,找出该学生的名字,接着与临时表tscore进行连接,找出该学生的平均成绩。

      select
         s.sname, tscore.avgScgrade
      from
         s
      join
         (
             select
                 sno, count(*)
             from
                 sc
             where 
                 scgrade < 60
             group by
                 sno
             having 
                 count(*) >= 2
         ) t
      on
         s.sno = t.sno
      join
         (
             select
                 sno, count(*), avg(scgrade) as avgScgrade
             from
                 sc
             group by
                 sno 
         ) tscore
      on
         s.sno = tscore.sno;
      
      +-----------+------------+
      | sname     | avgScgrade |
      +-----------+------------+
      | student 1 |         46 |
      +-----------+------------+
  3. 既学过1号课程又学过2号课程的学生的姓名。

    • 通过sc表,选张一张临时表t1,选出选过1号课程的学生

    • 通过sc表,选张一张临时表t2,选出选过2号课程的学生

    • 将两张表进行连接,通过学号,选出既在t1表也在t2表中的学生,接着与学生表进行连接,查出学生姓名。

      select
         t1.sno, s.sname
      from   
         (select sno, cno from sc where cno = '1') t1
      join
         (select sno, cno from sc where cno = '2') t2
      on 
         t1.sno = t2.sno
      join
         s
      where
         t1.sno = s.sno;
      
      +------+-----------+
      | sno  | sname     |
      +------+-----------+
      | 1    | student 1 |
      | 2    | student 2 |
      +------+-----------+
      2 rows in set (0.00 sec)
14. 列出所有员工及领导的姓名
  1. 员工表进行自连接,找出各个员工对应的领导的姓名

  2. 最高领导的领导姓名为null,如果想对这个null值进行修改,需要将上述得到的表保存,然后使用update。

(做题的时候一直陷在误区里,不知道怎么改查询的结果。现在知道了,应该是在select字句中使用ifnull()函数进行对空值的修改)

   select
    e1.ename, ifnull(e2.ename, '没有上级') as leadername
   from
    emp e1
   left join
    emp e2
   on
    e1.mgr = e2.empno;  

   +--------+------------+
   | ename  | leadername |
   +--------+------------+
   | SMITH  | FORD       |
   | ALLEN  | BLAKE      |
   | WARD   | BLAKE      |
   | JONES  | KING       |
   | MARTIN | BLAKE      |
   | BLAKE  | KING       |
   | CLARK  | KING       |
   | SCOTT  | JONES      |
   | KING   | 没有上级   |
   | TURNER | BLAKE      |
   | ADAMS  | SCOTT      |
   | JAMES  | BLAKE      |
   | FORD   | JONES      |
   | MILLER | CLARK      |
   +--------+------------+
   14 rows in set, 1 warning (0.00 sec) 
15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
  1. 员工表与员工表进行连接,得到的数据表每一行信息为员工及其对应的领导,通过where选出员工入职日期早于对应的领导入职日期的员工。

  2. 接着与部门表进行连接,查出这些员工的部门名称。

    select
     e1.empno, e1.ename, d.dname
    from
     emp e1
    join
     emp e2
    on
     e1.mgr = e2.empno
    join
     dept d
    on   
     e1.deptno = d.deptno
    where
     e1.hiredate < e2.hiredate
    order by 
     e1.empno;
    +-------+-------+------------+
    | empno | ename | dname      |
    +-------+-------+------------+
    |  7369 | SMITH | RESEARCH   |
    |  7499 | ALLEN | SALES      |
    |  7521 | WARD  | SALES      |
    |  7566 | JONES | RESEARCH   |
    |  7698 | BLAKE | SALES      |
    |  7782 | CLARK | ACCOUNTING |
    +-------+-------+------------+
    6 rows in set (0.00 sec)
16. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
  • 将员工表与部门表进行外连接,部门表作为主表,这样部门下没有员工的部门也会显示出来

    select
      d.dname, e.*
    from
      dept d
    left join
      emp e
    on
      d.deptno = e.deptno;
    
    +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
    | deptno | dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
    +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
    |     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
    |     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
    |     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
    |     20 | RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
    |     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
    |     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
    |     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
    |     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
    |     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
    |     30 | SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
    |     30 | SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
    |     30 | SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
    |     30 | SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
    |     30 | SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
    |     40 | OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
    +--------+------------+-------+--------+-----------+------+------------+---------+---------+--------+
    15 rows in set (0.00 sec)
    
17. 列出至少有5个员工的所有部门
  • 在上一题目的基础上,进行按照部门名称进行分组,对每个部门的员工进行计数,计数的时候选用对员工编号字段进行计数,因为分组函数会自动忽略null。

    select
      d.dname, count(empno)
    from
      dept d
    left join
      emp e
    on
      d.deptno = e.deptno
    group by
      d.dname
    having
      count(empno) >= 5;
    
    +----------+--------------+
    | dname    | count(empno) |
    +----------+--------------+
    | RESEARCH |            5 |
    | SALES    |            6 |
    +----------+--------------+
    2 rows in set (0.00 sec)
18. 列出薪金比"SMITH"多的所有员工信息.
  • 一个简单的where字句嵌套

    select
      ename, sal
    from
      emp
    where
      sal > (select sal from emp where ename = 'SMITH');
19. 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
  • 通过将员工表与部门表进行连接,然后按照部门编号分组,获得每个部门的人数,作为临时表

  • 利用where字句筛选出工作名称为'CLERK'的员工,然后和部门表进行连接检索到部门名称,然后和部门人数表连接检索到该员工所属部门的员工人数。

    select
      e.ename, d.dname, t.num
    from
      emp e
    join 
      dept d
    on
      e.deptno = d.deptno
    join
      (
          select
              d.deptno, count(e.ename) as num
          from
              dept d
          join    
              emp e
          on
              d.deptno = e.deptno
          group by
              d.deptno
      ) t
    on 
      e.deptno = t.deptno
    where
      e.job = 'CLERK';
    
    +--------+------------+-----+
    | ename  | dname      | num |
    +--------+------------+-----+
    | SMITH  | RESEARCH   |   5 |
    | ADAMS  | RESEARCH   |   5 |
    | JAMES  | SALES      |   6 |
    | MILLER | ACCOUNTING |   3 |
    +--------+------------+-----+
    4 rows in set (0.00 sec)
20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
  1. 首先根据工作字段分组,找出各种工作的员工数量,作为临时表t

  2. 将员工表与临时表t连接,找出各个员工所从事的工种的人员数量,然后按照工作字段进行分组,对分组后的数据进行筛选,利用分组函数min(),选出最低薪资>1500的各种工作。

    select
     e.job, t.num
    from
     emp e
    join
     (
         select
             job, count(*) as num
         from 
             emp
         group by
             job
     ) t
    on 
     e.job = t.job
    group by
     job
    having
     min(sal) > 1500;
    
    +-----------+-----+
    | job       | num |
    +-----------+-----+
    | ANALYST   |   2 |
    | MANAGER   |   3 |
    | PRESIDENT |   1 |
    +-----------+-----+
    3 rows in set (0.00 sec)
21. 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
  • 利用where字句的嵌套使用,查出'SALES'部门的编号,然后对员工表进行对比

    select deptno from dept where dname = 'SALES';
    
    select
      ename
    from
      emp
    where
      deptno = (select deptno from dept where dname = 'SALES');
    
    +--------+
    | ename  |
    +--------+
    | ALLEN  |
    | WARD   |
    | MARTIN |
    | BLAKE  |
    | TURNER |
    | JAMES  |
    +--------+
    6 rows in set (0.00 sec)
22. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
  • 求出该公司的平均工资

  • 接下来就是4张表的连接。与部门表连接,检索出所在部门名称;接着与自身表连接,找出领导名字,注意这里的连接是外连接。接着与工资等级表连接,计算出各个员工的工资等级。

    select
      e.ename, d.dname, ifnull(e2.ename, '无') as leadername, s.grade
    from
      emp e
    join
      dept d
    on 
      e.deptno = d.deptno
    left join
      emp e2
    on 
      e.mgr = e2.empno
    join 
      salgrade s
    on    
      e.sal between s.losal and s.hisal
    where
      e.sal > (select avg(sal) from emp);
    
    +-------+------------+------------+-------+
    | ename | dname      | leadername | grade |
    +-------+------------+------------+-------+
    | JONES | RESEARCH   | KING       |     4 |
    | BLAKE | SALES      | KING       |     4 |
    | CLARK | ACCOUNTING | KING       |     4 |
    | SCOTT | RESEARCH   | JONES      |     4 |
    | KING  | ACCOUNTING | 无         |     5 |
    | FORD  | RESEARCH   | JONES      |     4 |
    +-------+------------+------------+-------+
    6 rows in set, 1 warning (0.00 sec)
23. 列出与"SCOTT"从事相同工作的所有员工及部门名称.
  • 一个表连接和一个where子句嵌套

    select
      e.ename, d.dname
    from
      emp e
    join
      dept d
    on
      e.deptno = d.deptno
    where
      e.ename != 'SCOTT' and e.job = (select job from emp where ename = 'SCOTT');
    
    +-------+----------+
    | ename | dname    |
    +-------+----------+
    | FORD  | RESEARCH |
    +-------+----------+
    1 row in set (0.00 sec)   
24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
  • 题目的意思是薪资等于30部门员工的工资但是不属于30部门的员工。求这些员工额的名字和薪资

    select
      e.ename, e.sal, t.sal
    from
      emp e
    join
      (select distinct e.sal from emp e where e.deptno = 30) t
    on
      e.deptno != 30 and e.sal = t.sal;
    Empty set (0.00 sec)
25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
  • 求出30部门的最高薪资,作为where字句判断条件。再同部门表做一个连接。

    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 e where e.deptno = 30)
    order by
      e.sal;
    +-------+---------+------------+
    | ename | sal     | dname      |
    +-------+---------+------------+
    | JONES | 2975.00 | RESEARCH   |
    | FORD  | 3000.00 | RESEARCH   |
    | SCOTT | 3000.00 | RESEARCH   |
    | KING  | 5000.00 | ACCOUNTING |
    +-------+---------+------------+
    4 rows in set (0.00 sec)
26. 列出在每个部门工作的员工数量,平均工资和平均服务期限.
  • 这里的重点在于对平均服务期限的求解,进行日期的计算。(听讲题时注意听这段要)

    select
      d.dname, count(*), avg(e.sal), avg( (to_days(now()) - to_days(hiredate))/365) as avgserveryear
    from
      emp e
    join
      dept d
    on
      e.deptno = d.deptno
    group by
      d.dname;
    
    +------------+----------+-------------+---------------+
    | dname      | count(*) | avg(e.sal)  | avgserveryear |
    +------------+----------+-------------+---------------+
    | ACCOUNTING |        3 | 2916.666667 |   38.74336667 |
    | RESEARCH   |        5 | 2175.000000 |   36.77040000 |
    | SALES      |        6 | 1566.666667 |   39.04201667 |
    +------------+----------+-------------+---------------+
    3 rows in set (0.01 sec)  
27. 列出所有员工的姓名、部门名称和工资
  • 一个简单的连接查询

    select 
      e.ename, d.dname, e.sal
    from  
      emp e
    join
      dept d
    on 
      e.deptno = d.deptno;
    +--------+------------+---------+
    | ename  | dname      | sal     |
    +--------+------------+---------+
    | CLARK  | ACCOUNTING | 2450.00 |
    | KING   | ACCOUNTING | 5000.00 |
    | MILLER | ACCOUNTING | 1300.00 |
    | SMITH  | RESEARCH   |  800.00 |
    | JONES  | RESEARCH   | 2975.00 |
    | SCOTT  | RESEARCH   | 3000.00 |
    | ADAMS  | RESEARCH   | 1100.00 |
    | FORD   | RESEARCH   | 3000.00 |
    | ALLEN  | SALES      | 1600.00 |
    | WARD   | SALES      | 1250.00 |
    | MARTIN | SALES      | 1250.00 |
    | BLAKE  | SALES      | 2850.00 |
    | TURNER | SALES      | 1500.00 |
    | JAMES  | SALES      |  950.00 |
    +--------+------------+---------+
    14 rows in set (0.00 sec) 
28. 列出所有部门的详细信息和人数
  • 先求出每个部门的人数

  • 再与部门进行连接

    select
      d.*, t.num
    from
      dept d
    join
      (
          select
              d.deptno, count(e.ename) as num
          from
              dept d
          left join
              emp e
          on  
              d.deptno = e.deptno
          group by
              d.deptno
      ) t
    on
      d.deptno = t.deptno;
    
    +--------+------------+----------+-----+
    | DEPTNO | DNAME      | LOC      | num |
    +--------+------------+----------+-----+
    |     10 | ACCOUNTING | NEW YORK |   3 |
    |     20 | RESEARCH   | DALLAS   |   5 |
    |     30 | SALES      | CHICAGO  |   6 |
    |     40 | OPERATIONS | BOSTON   |   0 |
    +--------+------------+----------+-----+
    4 rows in set (0.00 sec)  
29. 列出各种工作的最低工资及从事此工作的雇员姓名
  • 先求出每种工作的最低薪资,作为临时表,再与员工表进行连接

    select 
      e.job, e.ename, t.minsal
    from
      emp e
    join 
      (select e.job, min(e.sal) as minsal from emp e group by job) t
    on 
      e.job = t.job and e.sal = t.minsal
    order by
      e.job;
    
    +-----------+--------+---------+
    | job       | ename  | minsal  |
    +-----------+--------+---------+
    | ANALYST   | FORD   | 3000.00 |
    | ANALYST   | SCOTT  | 3000.00 |
    | CLERK     | SMITH  |  800.00 |
    | MANAGER   | CLARK  | 2450.00 |
    | PRESIDENT | KING   | 5000.00 |
    | SALESMAN  | MARTIN | 1250.00 |
    | SALESMAN  | WARD   | 1250.00 |
    +-----------+--------+---------+  
30. 列出各个部门的MANAGER(领导)的最低薪金
  • 找出job字段为'MANAGER'的员工,按照部门编号进行分组,找出每个部门‘MANGER’的最低薪资

     select
      deptno, min(sal) as minsal
     from
      emp
     where
      job = 'MANAGER'
     group by
      deptno;
    
     +--------+---------+
     | deptno | minsal  |
     +--------+---------+
     |     10 | 2450.00 |
     |     20 | 2975.00 |
     |     30 | 2850.00 |
     +--------+---------+
     3 rows in set (0.00 sec) 
31. 列出所有员工的年工资,按年薪从低到高排序
  • 年薪等于每个月的工资、津贴之和乘以12。

    select 
      ename, ((ifnull(comm, 0) + sal) * 12) as yearsal
    from
      emp
    order by
      yearsal;
    +--------+----------+
    | ename  | yearsal  |
    +--------+----------+
    | SMITH  |  9600.00 |
    | JAMES  | 11400.00 |
    | ADAMS  | 13200.00 |
    | MILLER | 15600.00 |
    | TURNER | 18000.00 |
    | WARD   | 21000.00 |
    | ALLEN  | 22800.00 |
    | CLARK  | 29400.00 |
    | MARTIN | 31800.00 |
    | BLAKE  | 34200.00 |
    | JONES  | 35700.00 |
    | FORD   | 36000.00 |
    | SCOTT  | 36000.00 |
    | KING   | 60000.00 |
    +--------+----------+
    14 rows in set (0.00 sec)
32. 求出员工领导的薪水超过3000的员工名称与领导名称
  • 题意是,找出员工的直接领导的工资大于3000的员工名称与领导名称

    select    
      e1.ename, e2.ename as leadername
    from
      emp e1
    join
      emp e2
    on
      e1.mgr = e2.empno
    where
      e2.sal > 3000;
    
    +-------+------------+
    | ename | leadername |
    +-------+------------+
    | JONES | KING       |
    | BLAKE | KING       |
    | CLARK | KING       |
    +-------+------------+
    3 rows in set (0.00 sec)
33. 求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
  • 首先找出部门名称中带有'S'字符的部门,利用通配字符

  • 再与员工表进行连接检索

    select
      t.dname, ifnull(sum(e.sal), ' ') as sumsal, count(e.ename)
    from
      emp e
    right join
      (select deptno, dname from dept where dname like '%S%') t
    on
      e.deptno = t.deptno
    group by
      t.deptno;
    
    +------------+----------+----------------+
    | dname      | sumsal   | count(e.ename) |
    +------------+----------+----------------+
    | RESEARCH   | 10875.00 |              5 |
    | SALES      | 9400.00  |              6 |
    | OPERATIONS |          |              0 |
    +------------+----------+----------------+
    3 rows in set (0.00 sec)
34. 给任职日期超过30年的员工加薪10%.
  • 首先查出年薪超过30年的员工

    select
      ename
    from
      emp
    where
      ((to_days(now()) - to_days(hiredate)) / 365) > 30;
Last modification:July 6th, 2020 at 01:10 am
有钱的捧个钱场,没带钱的捧个人场。

Leave a Comment