Exercise Question 2 of mysql

Keywords: MySQL

subject

5. Departmental Numbers of Departments with the Highest Average Salary (at least two solutions)

The first method is to get the maximum average salary in order, and then take the first one to get it.

Step 1: Take the average salary of each department.

mysql> select
    ->          deptno, avg(sal) as avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

Step 2: Get the maximum average salary.

mysql> select
    ->          avg(sal) avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno
    -> order by
    ->          avgsal desc
    -> limit
    ->          1;
+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

Step 3: Joint the first and second steps.

mysql> select
    ->          deptno
    -> from
    ->          emp e
    -> group by
    ->          deptno
    -> having
    ->          avg(sal) = (select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);
+--------+
| deptno |
+--------+
|     10 |
+--------+

The second method is to get the maximum average salary by using the grouping function max, and then take the first one.

mysql> select
    ->          deptno
    -> from
    ->          emp e
    -> group by
    ->          deptno
    -> having
    ->          avg(sal) = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t);
+--------+
| deptno |
+--------+
|     10 |
+--------+

6. The department name of the Department with the highest average salary.

Step 1: Get the average salary of each department.

mysql> select
    ->          deptno, avg(sal) avgsal
    -> from
    ->          emp
    -> group by
    ->          deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

Step 2: Get the highest average salary.

mysql> SELECT
    ->  avg(sal) AS avgsal
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno
    -> ORDER BY
    ->  avgsal DESC
    -> LIMIT 1;
+-------------+
| avgsal      |
+-------------+
| 2916.666667 |
+-------------+

Step 3: Joint the first and second steps.

mysql> SELECT
    ->  d.dname
    -> FROM
    ->  emp e
    -> JOIN dept d ON e.deptno = d.deptno
    -> GROUP BY
    ->  d.dname
    -> HAVING
    ->  avg(e.sal) = (
    ->          SELECT
    ->                  avg(sal) AS avgsal
    ->          FROM
    ->                  emp
    ->          GROUP BY
    ->                  deptno
    ->          ORDER BY
    ->                  avgsal DESC
    ->          LIMIT 1
    ->  );
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+

7. The name of the Department with the lowest average salary.

Step 1: Find the average salary of the department.

mysql> SELECT
    ->  deptno,
    ->  avg(sal) avgsal
    -> FROM
    ->  emp
    -> GROUP BY
    ->  deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

Step 2: Take the average salary of the department.

mysql> SELECT
    ->  t.deptno,
    ->  s.grade
    -> FROM
    ->  salgrade s
    -> JOIN (
    ->  SELECT
    ->          deptno,
    ->          avg(sal) avgsal
    ->  FROM
    ->          emp
    ->  GROUP BY
    ->          deptno
    -> ) t ON t.avgsal BETWEEN s.losal
    -> AND hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
|     30 |     3 |
|     10 |     4 |
|     20 |     4 |
+--------+-------+

Step 3: Get the average salary level of the department, and then take the department number of the lowest level.

mysql> SELECT
    ->  deptno,
    ->  min(m.grade) AS mingrade
    -> FROM
    ->  (
    ->          SELECT
    ->                  t.deptno AS deptno,
    ->                  s.grade AS grade
    ->          FROM
    ->                  salgrade s
    ->          JOIN (
    ->                  SELECT
    ->                          deptno,
    ->                          avg(sal) avgsal
    ->                  FROM
    ->                          emp
    ->                  GROUP BY
    ->                          deptno
    ->          ) t ON t.avgsal BETWEEN s.losal
    ->          AND hisal
    ->  ) m;
+--------+----------+
| deptno | mingrade |
+--------+----------+
|     30 |        3 |
+--------+----------+

Step 4: Connect the above results as temporary tables to dept tables, provided that deptno of dept tables is equal to deptno above.

mysql> SELECT
    ->  d.deptno,
    ->  d.dname
    -> FROM
    ->  dept d
    -> JOIN (
    ->  SELECT
    ->          deptno,
    ->          min(m.grade) AS mingrade
    ->  FROM
    ->          (
    ->                  SELECT
    ->                          t.deptno AS deptno,
    ->                          s.grade AS grade
    ->                  FROM
    ->                          salgrade s
    ->                  JOIN (
    ->                          SELECT
    ->                                  deptno,
    ->                                  avg(sal) avgsal
    ->                          FROM
    ->                                  emp
    ->                          GROUP BY
    ->                                  deptno
    ->                  ) t ON t.avgsal BETWEEN s.losal
    ->                  AND hisal
    ->          ) m
    -> ) n ON d.deptno = n.deptno;
+--------+-------+
| deptno | dname |
+--------+-------+
|     30 | SALES |
+--------+-------+

8. Get a leader's name with a higher salary than the average employee (whose code does not appear in the mgr field).

Step 1: Get the employee number, employee name and employee salary of ordinary employees.

mysql> SELECT
    ->  empno,
    ->  ename,
    ->  sal,
    ->  mgr
    -> FROM
    ->  emp
    -> WHERE
    ->  empno NOT IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  );
+-------+--------+---------+------+
| empno | ename  | sal     | mgr  |
+-------+--------+---------+------+
|  7369 | SMITH  |  800.00 | 7902 |
|  7499 | ALLEN  | 1600.00 | 7698 |
|  7521 | WARD   | 1250.00 | 7698 |
|  7654 | MARTIN | 1250.00 | 7698 |
|  7844 | TURNER | 1500.00 | 7698 |
|  7876 | ADAMS  | 1100.00 | 7788 |
|  7900 | JAMES  |  950.00 | 7698 |
|  7934 | MILLER | 1300.00 | 7782 |
+-------+--------+---------+------+

Note: not in does not automatically ignore null values (NULL), which need to be handled manually. In automatically ignores null values (NULL).

Step 2: Get the highest salary of the average employee.

mysql> SELECT
    ->  empno,
    ->  ename,
    ->  sal,
    ->  mgr
    -> FROM
    ->  emp
    -> WHERE
    ->  empno NOT IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  )
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 1;
+-------+-------+---------+------+
| empno | ename | sal     | mgr  |
+-------+-------+---------+------+
|  7499 | ALLEN | 1600.00 | 7698 |
+-------+-------+---------+------+

Step 3: Name the leader with the highest salary than the average employee.

mysql> SELECT
    ->  ename
    -> FROM
    ->  emp
    -> WHERE
    ->  empno IN (
    ->          SELECT DISTINCT
    ->                  mgr
    ->          FROM
    ->                  emp
    ->          WHERE
    ->                  mgr IS NOT NULL
    ->  )
    -> AND sal > (
    ->  SELECT
    ->          sal
    ->  FROM
    ->          emp
    ->  WHERE
    ->          empno NOT IN (
    ->                  SELECT DISTINCT
    ->                          mgr
    ->                  FROM
    ->                          emp
    ->                  WHERE
    ->                          mgr IS NOT NULL
    ->          )
    ->  ORDER BY
    ->          sal DESC
    ->  LIMIT 1
    -> );
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING  |
| FORD  |
+-------+

9. The top five employees with the highest salaries.

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 5;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+

10. The sixth to tenth highest-paid employees.

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  sal DESC
    -> LIMIT 5,
    ->  5;
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+

Note: Use of limit, start position of limit, number. If the starting position is omitted, the default number is 0 from the beginning.
11. Five employees who got the final job.

mysql> SELECT
    ->  *
    -> FROM
    ->  emp
    -> ORDER BY
    ->  hiredate DESC
    -> LIMIT 5;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

Posted by biopv on Tue, 02 Apr 2019 18:57:30 -0700

Programmer Group

Exercise Question 2 of mysql

Hot Keywords