mysql练习题五

26、列出所有员工的姓名、部门名称和工资。

mysql> 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 |
+--------+------------+---------+

27、列出所有部门的详细信息和人数

mysql> SELECT
    ->  d.*, count(e.ename) AS empcount
    -> FROM
    ->  dept d
    -> LEFT JOIN
    ->  emp e
    -> ON
    ->  d.deptno = e.deptno
    -> GROUP BY
    ->  d.deptno, d.dname, d.loc;
+--------+------------+----------+----------+
| DEPTNO | DNAME      | LOC      | empcount |
+--------+------------+----------+----------+
|     10 | ACCOUNTING | NEW YORK |        3 |
|     20 | RESEARCH   | DALLAS   |        5 |
|     30 | SALES      | CHICAGO  |        6 |
|     40 | OPERATIONS | BOSTON   |        0 |
+--------+------------+----------+----------+

28、列出各种工作的最低工资及从事此工作的雇员姓名

mysql> SELECT
    ->  e.ename,
    ->  e.sal
    -> FROM
    ->  emp e
    -> JOIN (
    ->  SELECT
    ->          min(sal) AS minsal,
    ->          job
    ->  FROM
    ->          emp
    ->  GROUP BY
    ->          job
    -> ) t
    -> ON
    ->  t.minsal = e.sal
    -> AND
    ->  t.job = e.job;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| SMITH  |  800.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+

29、列出各个部门的MANAGER(领导)的最低薪金

mysql> SELECT
    ->  deptno,
    ->  min(sal)
    -> FROM
    ->  emp
    -> WHERE
    ->  job = 'manager'
    -> GROUP BY
    ->  deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+

30、列出所有员工的年工资,按年薪从低到高排序

mysql> SELECT
    ->  ename,
    ->  (sal + ifnull(comm, 0))*12 AS yearsal
    -> FROM
    ->  emp
    -> ORDER BY
    ->  yearsal ASC;
+--------+----------+
| 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 |
+--------+----------+

31、求出员工领导的薪水超过3000的员工名称与领导名称

mysql> SELECT
    ->  a.ename AS empname,
    ->  b.ename AS leadername
    -> FROM
    ->  emp a
    -> JOIN
    ->  emp b
    -> ON
    ->  a.mgr = b.empno
    -> WHERE
    ->  b.sal > 3000;
+---------+------------+
| empname | leadername |
+---------+------------+
| JONES   | KING       |
| BLAKE   | KING       |
| CLARK   | KING       |
+---------+------------+

32、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.

mysql> SELECT
    ->  sum(sal),
    ->  count(ename)
    -> FROM
    ->  emp
    -> WHERE
    ->  ename LIKE '%S%'
    -> GROUP BY
    ->  deptno;
+----------+--------------+
| sum(sal) | count(ename) |
+----------+--------------+
|  7875.00 |            4 |
|   950.00 |            1 |
+----------+--------------+

33、给任职日期超过30年的员工加薪10%.

create table emp_bak as select * from emp;
update emp_bak set sal = sal*1.1 where (to_days(now()) - to_days(hiredate))/365 > 30;