/*
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 |
+--------+------------+---------+
28.列出所有部门的详细信息和人数
select
d.*,count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
group by
d.deptno,d.dname,d.loc;
+--------+------------+----------+----------------+
| DEPTNO | DNAME | LOC | count(e.ename) |
+--------+------------+----------+----------------+
| 40 | OPERATIONS | BOSTON | 0 |
| 10 | ACCOUNTING | NEW YORK | 3 |
| 20 | RESEARCH | DALLAS | 5 |
| 30 | SALES | CHICAGO | 6 |
+--------+------------+----------+----------------+
29.列出各种工作的最低工资及从事此工作的雇员姓名。
select
job,min(sal) minsal
from
emp
group by
job;
+-----------+----------+
| job | minsal |
+-----------+----------+
| ANALYST | 3000.00 |
| CLERK | 800.00 |
| MANAGER | 2450.00 |
| PRESIDENT | 5000.00 |
| SALESMAN | 1250.00 |
+-----------+----------+
// 将以上表看成t表,emp表和t表进行表连接
select
e.ename,t.*
from
emp e
join
(select job,min(sal) minsal from emp group by job) t
on
e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| ename | job | minsal |
+--------+-----------+---------+
| SMITH | CLERK | 800.00 |
| WARD | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
| CLARK | MANAGER | 2450.00 |
| SCOTT | ANALYST | 3000.00 |
| KING | PRESIDENT | 5000.00 |
| FORD | ANALYST | 3000.00 |
+--------+-----------+---------+
30.列出各个部门的MANAGER(领导)的最低薪金
select
deptno,min(sal) minsal
from
emp
where
job = 'MANAGER'
group by
deptno;
+--------+---------+
| deptno | minsal |
+--------+---------+
| 10 | 2450.00 |
| 20 | 2975.00 |
| 30 | 2850.00 |
+--------+---------+
31.列出所有员工的年工资,按年薪从低到高排序。
select
ename,(sal + ifnull(comm,0))*12 as yearsal
from
emp
order by
yearsal asc;
32.求出员工领导的薪水超过3000的员工名称与领导名称。
select
e1.ename '员工',e2.ename '领导',e2.sal
from
emp e1
join
emp e2
on
e1.mgr = e2.empno
where
e2.sal > 3000;
+-------+------+---------+
| 员工 | 领导 | sal |
+-------+------+---------+
| JONES | KING | 5000.00 |
| BLAKE | KING | 5000.00 |
| CLARK | KING | 5000.00 |
+-------+------+---------+
33.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数。
// 加where条件,不要加having,效率更高
select d.dname,d.deptno,sum(ifnull(e.sal,0)),count(e.ename)
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
d.dname like '%S%'
group by
d.deptno,d.dname,d.loc;
+------------+--------+----------------------+----------------+
| dname | deptno | sum(ifnull(e.sal,0)) | count(e.ename) |
+------------+--------+----------------------+----------------+
| RESEARCH | 20 | 10875.00 | 5 |
| SALES | 30 | 9400.00 | 6 |
| OPERATIONS | 40 | 0.00 | 0 |
+------------+--------+----------------------+----------------+
34.给任职日期超过30年的员工加薪10%
update emp set sal = sal * 1.1 where
timestampdiff(YEAR,hiredate,now()) > 30;
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.50 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3300.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1210.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1430.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
*/
MySQL基础06 - 34道练习题
最新推荐文章于 2024-10-24 23:13:54 发布