mysql练习题三

最新推荐文章于 2023-04-17 16:42:33 发布

半醉看夕阳

最新推荐文章于 2023-04-17 16:42:33 发布

阅读量2.6k

点赞数 2

分类专栏： Mysql 文章标签： MySQL练习题

本文链接：https://blog.csdn.net/bbb695480667/article/details/55805354

版权

Mysql 专栏收录该内容

16 篇文章 2 订阅

订阅专栏

12、取得每个薪水等级有多少员工

第一步：找出每个员工的薪水等级

mysql> SELECT
    ->  e.ename,
    ->  s.grade
    -> FROM
    ->  emp e
    -> JOIN
    ->  salgrade s
    -> ON
    ->  e.sal BETWEEN s.losal AND s.hisal;
+--------+-------+
| ename  | grade |
+--------+-------+
| SMITH  |     1 |
| JAMES  |     1 |
| ADAMS  |     1 |
| WARD   |     2 |
| MARTIN |     2 |
| MILLER |     2 |
| TURNER |     3 |
| ALLEN  |     3 |
| JONES  |     4 |
| BLAKE  |     4 |
| CLARK  |     4 |
| SCOTT  |     4 |
| FORD   |     4 |
| KING   |     5 |
+--------+-------+

第二步：在以上结果的基础上，按照grade分组计数

mysql> SELECT
    ->  s.grade,
    ->  count(*)
    -> FROM
    ->  emp e
    -> JOIN
    ->  salgrade s
    -> ON
    ->  e.sal BETWEEN s.losal AND s.hisal
    -> GROUP BY
    ->  s.grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13、列出所有员工及领导的姓名

emp a 员工表
emp b 领导表

进行表的自连接

mysql> SELECT
    ->  a.ename AS empname,
    ->  b.ename AS leadername
    -> FROM
    ->  emp a
    -> LEFT OUTER JOIN
    ->  emp b
    -> ON
    ->  a.mgr = b.empno;
+---------+------------+
| empname | leadername |
+---------+------------+
| SMITH   | FORD       |
| ALLEN   | BLAKE      |
| WARD    | BLAKE      |
| JONES   | KING       |
| MARTIN  | BLAKE      |
| BLAKE   | KING       |
| CLARK   | KING       |
| SCOTT   | JONES      |
| KING    | NULL       |
| TURNER  | BLAKE      |
| ADAMS   | SCOTT      |
| JAMES   | BLAKE      |
| FORD    | JONES      |
| MILLER  | CLARK      |
+---------+------------+

14、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

mysql> SELECT
    ->  a.empno AS empno,
    ->  a.ename empname,
    ->  a.hiredate,
    ->  b.empno AS leaderno,
    ->  b.ename AS leadername,
    ->  b.hiredate AS leaderhiredate,
    ->  d.dname
    -> FROM
    ->  emp a
    -> JOIN
    ->  emp b
    -> ON
    ->  a.mgr = b.EMPNO
    -> JOIN
    ->  dept d
    -> ON
    ->  d.deptno = a.deptno
    -> WHERE
    ->  a.HIREDATE < b.HIREDATE;
+-------+---------+------------+----------+------------+----------------+------------+
| empno | empname | hiredate   | leaderno | leadername | leaderhiredate | dname      |
+-------+---------+------------+----------+------------+----------------+------------+
|  7782 | CLARK   | 1981-06-09 |     7839 | KING       | 1981-11-17     | ACCOUNTING |
|  7369 | SMITH   | 1980-12-17 |     7902 | FORD       | 1981-12-03     | RESEARCH   |
|  7566 | JONES   | 1981-04-02 |     7839 | KING       | 1981-11-17     | RESEARCH   |
|  7876 | ADAMS   | 1981-05-23 |     7788 | SCOTT      | 1987-04-19     | RESEARCH   |
|  7499 | ALLEN   | 1981-02-20 |     7698 | BLAKE      | 1981-05-01     | SALES      |
|  7521 | WARD    | 1981-02-22 |     7698 | BLAKE      | 1981-05-01     | SALES      |
|  7698 | BLAKE   | 1981-05-01 |     7839 | KING       | 1981-11-17     | SALES      |
+-------+---------+------------+----------+------------+----------------+------------+

15、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

主要考察外连接。

mysql> SELECT
    ->  d.dname,
    ->  e.*
    -> FROM
    ->  dept d
    -> LEFT JOIN
    ->  emp e
    -> ON
    ->  d.deptno = e.deptno;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1981-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

16、列出至少有5个员工的所有部门

mysql> SELECT
    ->  deptno,
    ->  count(ename) AS empcount
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno
    -> HAVING
    ->  count(ename) >= 5;
+--------+----------+
| deptno | empcount |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+

17、列出薪金比"SMITH"多的所有员工信息.

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> WHERE
    ->  sal > (
    ->          SELECT
    ->                  sal
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  ename = upper('smith')
    ->  );
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1981-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

第一步：取得所有clerk的姓名及其部门名称

mysql> SELECT
    ->  e.ename,
    ->  d.dname
    -> FROM
    ->  dept d
    -> JOIN
    ->  emp e
    -> ON
    ->  d.deptno = e.deptno
    -> WHERE
    ->  e.job = 'clerk';
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
+--------+------------+

第二步：取得每个部门的员工数量

mysql> SELECT
    ->  deptno,
    ->  count(deptno) AS totalemp
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno;
+--------+----------+
| deptno | totalemp |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+

第三步：联合第一步和第二步的结果

mysql> SELECT
    ->  e.ename,
    ->  d.dname,
    ->  t.totalemp
    -> FROM
    ->  dept d
    -> JOIN
    ->  emp e
    -> ON
    ->  d.deptno = e.deptno
    -> JOIN (
    ->  SELECT
    ->          deptno,
    ->          count(deptno) AS totalemp
    ->  FROM
    ->          emp
    ->  GROUP BY
    ->          deptno
    -> ) t
    -> ON
    ->  t.deptno = e.deptno
    -> WHERE
    ->  e.job = 'clerk';
+--------+------------+----------+
| ename  | dname      | totalemp |
+--------+------------+----------+
| MILLER | ACCOUNTING |        3 |
| SMITH  | RESEARCH   |        5 |
| ADAMS  | RESEARCH   |        5 |
| JAMES  | SALES      |        6 |
+--------+------------+----------+