速写mysql练习题（包含面试题13）

于 2021-08-18 22:23:40 发布
阅读量201 收藏
点赞数
分类专栏： 数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_46096297/article/details/119756776
版权
本文介绍了多个SQL查询实例，涵盖了员工薪资、部门平均薪资、薪水等级、最高薪水获取、部门信息等多个方面。通过查询，展示了如何找出每个部门的最高薪水员工、哪些人的薪水高于部门平均薪资、不同薪水等级的员工数量，以及如何获取平均薪水最高和最低的部门等信息。此外，还探讨了如何获取特定职位员工、比普通员工薪水高的领导、高薪员工排名、入职日期倒序的员工等复杂查询技巧。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

题目来自动力节点
建议大家手动敲，遇到不会的再去看视频解说，越到后面越快！！！

1取得每个部门最高薪水的人员名称

mysql> select a.ename,a.sal,a.deptno from emp a join (select max(sal) 'salmax' from emp group by deptno) b on a.sal=b.salmax;
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

2哪些人的薪水在部门的平均薪水之上

mysql> select a.ename,a.sal from emp a join (select avg(sal) 'salavg',deptno from emp group by deptno) b on (a.sal>=b.salavg and a.deptno=b.deptno);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| ALLEN | 1600.00 |
| BLAKE | 2850.00 |
| KING  | 5000.00 |
+-------+---------+
6 rows in set (0.00 sec)

3取得部门中（所有人的）平均的薪水等级

这里我一开始求的是 平均薪水等级
所以首先先区分 部门平均的薪水等级 和 部门平均薪水的等级
前者 平均的薪水等级 意为 部门每个人的薪水都在一个范围之内，然后对这所有人对应的范围求平均。
后者 部门平均薪水的等级 是部门所有人的平均薪水，再对应求等级

部门平均的薪水等级

mysql>  select m.adeptno,avg(bgrade) from ( select a.deptno 'adeptno',a.ename 'aename',a.sal 'asal',b.grade 'bgrade' from emp a join salgrade b on a.sal between b.losal and b.hisal) m group by m.adeptno order by m.adeptno asc;
+---------+-------------+
| adeptno | avg(bgrade) |
+---------+-------------+
|      10 |      3.6667 |
|      20 |      2.8000 |
|      30 |      2.5000 |
+---------+-------------+
3 rows in set (0.00 sec)

部门平均薪水的等级

mysql>  select a.deptno,a.avgsal,b.grade from (select avg(sal) 'avgsal',deptno from emp group by deptno) a join salgrade b on a.avgsal between b.losal and b.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

4不准用组函数（Max），取得最高薪水

方法1 先排序 order by 再 取第一个

mysql>  select sal from emp order by sal desc limit 1;
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)

方法2，最大值，那么找出其余最小值 ——这点看了视频才会

mysql> select sal from emp where sal not in (select distinct(a.sal) 'minsal' from emp a join emp b on a.sal<b.sal);
+---------+
| sal     |
+---------+
| 5000.00 |
+---------+
1 row in set (0.01 sec)

巧妙！。

5取得平均薪水最高的部门的部门编号

这里前面一部分可在第三题找到
遇到的问题是ERROR 1248 (42000): Every derived table must have its own alias
每个派生表要有自己的别名。 解决方法是：给派生表的每一个列重新取名字

mysql> select max(x.asal) from (select a.deptno 'dep',a.avgsal 'asal' from (select avg(sal) 'avgsal',deptno from emp group by deptno) a join salgrade b on a.avgsal between b.losal and b.hisal) x;
+-------------+
| max(x.asal) |
+-------------+
| 2916.666667 |
+-------------+
1 row in set (0.00 sec)

6取得平均薪水最高的部门的部门名称

select d.dname,max(x.asal) from (select a.deptno 'dep',a.avgsal 'asal' from (select avg(sal) 'avgsal',deptno from emp group by deptno) a join salgrade b on a.avgsal between b.losal and b.hisal) x join dept d;
+------------+-------------+
| dname      | max(x.asal) |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.00 sec)

7求平均薪水的等级最低的部门的部门名称

上面改一下min

8取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

mysql> select ename,sal from emp where sal>( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

9取得薪水最高的前五名员工

mysql> select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

10取得薪水最高的第六到第十名员工

mysql> 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 |
| WARD   | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

11取得最后入职的5名员工

mysql>  select ename from emp order by hiredate desc limit 5;
+--------+
| ename  |
+--------+
| ADAMS  |
| SCOTT  |
| MILLER |
| JAMES  |
| FORD   |
+--------+
5 rows in set (0.00 sec)

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

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

面试题

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1， 找出没选过“黎明”老师的所有学生姓名。

mysql> select n.sname from s n where n.sno not in (select distinct sno from sc s1 
join c c1 on s1.cno=5);
+-------+
| sname |
+-------+
| 学生3 |
| 学生4 |
+-------+
2 rows in set (0.00 sec)

2，列出2门以上（含2门）不及格学生姓名及平均成绩。

mysql> select distinct(k.sno),f.avgs from (select sno 'xnum',count(sno) 
'logradednum' from sc where scgrade<60 group by sno) s left join sc k on 
k.sno=s.xnum join (select avg(scgrade) 'avgs',sno from sc group by sno) f on 
f.sno=k.sno where s.logradednum>=2;
+------+------+
| sno  | avgs |
+------+------+
| 1    |   46 |
+------+------+
1 row in set (0.00 sec)

3，即学过1号课程又学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。
这题一开始是想不出来的， 然后想到了单独寻找，但是如果数量特别多岂不是很麻烦？

mysql> select k.sno from (select sno,cno from sc where cno=1) k join ( select sno,cno from sc where cno=2) g on k.sno=g.sno;
+------+
| sno  |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

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

一开始没有加left 在遇到on后面的条件不符合时，就不会显示出来
而我们加上left 左边的表一定会全显示，哪怕关联的外表没有其也只是会显示null

mysql> select e.ename,ifnull(a.ename,'没有上级') from emp e left join emp a on e.mgr=a.empno;
+--------+----------------------------+
| ename  | ifnull(a.ename,'没有上级') |
+--------+----------------------------+
| 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 (0.00 sec)

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

mysql> select e.ename,e.empno,b.ename,b.empno from emp e left join emp b on e.mgr=b.empno where e.hiredate<b.hiredate;
+-------+-------+-------+-------+
| ename | empno | ename | empno |
+-------+-------+-------+-------+
| SMITH |  7369 | FORD  |  7902 |
| ALLEN |  7499 | BLAKE |  7698 |
| WARD  |  7521 | BLAKE |  7698 |
| JONES |  7566 | KING  |  7839 |
| BLAKE |  7698 | KING  |  7839 |
| CLARK |  7782 | KING  |  7839 |
+-------+-------+-------+-------+
6 rows in set (0.00 sec)

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

mysql> select d.dname,e.* from dept d left join emp e on e.deptno=d.deptno order by d.dname;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
| 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 | 1987-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 |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

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

mysql> select d.dname,h.num from dept d join (select deptno,count(*) 'num' from emp group by deptno having count(*)>=5) h on h.deptno=d.deptno;
+----------+-----+
| dname    | num |
+----------+-----+
| RESEARCH |   5 |
| SALES    |   6 |
+----------+-----+
2 rows in set (0.00 sec)

18列出薪金比"SMITH"多的所有员工信息

mysql> select f.* from emp e join emp f on f.sal>e.sal where e.ename='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 | 1987-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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

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

mysql> select m.ename,m.job,m.dname,n.num from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno=d.deptno where e.job='clerk') m join (select d.deptno, count(d.deptno) 'num' from emp e join dept d on e.deptno=d.deptno group by d.deptno) n on m.deptno=n.deptno;
+--------+-------+------------+-----+
| ename  | job   | dname      | num |
+--------+-------+------------+-----+
| SMITH  | CLERK | RESEARCH   |   5 |
| ADAMS  | CLERK | RESEARCH   |   5 |
| JAMES  | CLERK | SALES      |   6 |
| MILLER | CLERK | ACCOUNTING |   3 |
+--------+-------+------------+-----+
4 rows in set (0.00 sec)

20列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

mysql> select m.ename,m.job,(m.sal+ifnull(m.comm,0)),n.num '人数' from ( select e.* from emp e where (e.sal+ifnull(e.comm,0))>1500) m join ( select count(*) 'num',job from emp group by job) n on m.job=n.job;
+--------+-----------+--------------------------+------+
| ENAME  | JOB       | (m.sal+ifnull(m.comm,0)) | 人数 |
+--------+-----------+--------------------------+------+
| ALLEN  | SALESMAN  |                  1900.00 |    4 |
| WARD   | SALESMAN  |                  1750.00 |    4 |
| JONES  | MANAGER   |                  2975.00 |    3 |
| MARTIN | SALESMAN  |                  2650.00 |    4 |
| BLAKE  | MANAGER   |                  2850.00 |    3 |
| CLARK  | MANAGER   |                  2450.00 |    3 |
| SCOTT  | ANALYST   |                  3000.00 |    2 |
| KING   | PRESIDENT |                  5000.00 |    1 |
| FORD   | ANALYST   |                  3000.00 |    2 |
+--------+-----------+--------------------------+------+
9 rows in set (0.00 sec)

21列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

一开始还以为这个麻烦 还想着通过其他编号 然后排出它 后来答案是 通过名字 寻找 这步都一样吗。。。

mysql> select e.* from emp e where e.deptno=(select deptno from dept where dname='sales');
+-------+--------+----------+------+------------+---------+---------+--------+
| 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 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
6 rows in set (0.00 sec)

22列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

长到难以置信 一个表接一个表
然后看答案 才知道 join 可以重复使用

mysql> select k.ename '姓名',k.dname '部门名称',k.boss '上级领导',g.grade from (select f.ename,f.dname,f.sal,h.ename 'boss' from (select a.ename,b.dname,a.sal,a.mgr from (select e.ename,e.deptno,e.sal,e.mgr from emp e join ( select avg(sal) 'avgsal' from emp) m on e.sal>m.avgsal) a left join dept b on a.deptno=b.deptno) f left join emp h on f.mgr=h.empno) k join ( select e.ename,d.grade from emp e join salgrade d on e.sal between d.losal and d.hisal) g on k.ename=g.ename;
+-------+------------+----------+-------+
| 姓名  | 部门名称   | 上级领导 | grade |
+-------+------------+----------+-------+
| JONES | RESEARCH   | KING     |     4 |
| BLAKE | SALES      | KING     |     4 |
| CLARK | ACCOUNTING | KING     |     4 |
| SCOTT | RESEARCH   | JONES    |     4 |
| FORD  | RESEARCH   | JONES    |     4 |
| KING  | ACCOUNTING | NULL     |     5 |
+-------+------------+----------+-------+
6 rows in set (0.00 sec)

重复使用join 简易很多了 但是要注意 where 要在join语句后面
按照顺序来 第一个原则就是 高于平均水平 所以其肯定是覆盖所有的。

mysql> select e.ename '员工',d.dname '所在部门',l.ename '领导',s.grade '薪资等级'
    ->  from emp e join dept d on e.deptno=d.deptno left join emp l on e.mgr=l.empno
    -> join salgrade s on e.sal between s.losal and s.hisal
    -> where e.sal>(select avg(sal) from emp);
+-------+------------+-------+----------+
| 员工  | 所在部门   | 领导  | 薪资等级 |
+-------+------------+-------+----------+
| JONES | RESEARCH   | KING  |        4 |
| BLAKE | SALES      | KING  |        4 |
| CLARK | ACCOUNTING | KING  |        4 |
| SCOTT | RESEARCH   | JONES |        4 |
| FORD  | RESEARCH   | JONES |        4 |
| KING  | ACCOUNTING | NULL  |        5 |
+-------+------------+-------+----------+
6 rows in set (0.00 sec)

23列出与"SCOTT"从事相同工作的所有员工及部门名称

mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where job=(select job from emp where ename='scott') and ename!='scott';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

24列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

空

mysql> select e.ename,e.sal from (select *from emp where deptno!=30) e join (select *from emp where deptno=30) d on e.sal=d.sal;
Empty set (0.00 sec)

25列出薪金高于部门30中员工的薪金的其他员工的姓名和薪金

mysql> select e.ename,e.sal,f.dname from (select *from emp where deptno!=30) e join dept f on e.deptno=f.deptno join (select max(sal) 'msal' from emp where deptno=30) d on e.sal>=d.msal;
+-------+---------+------------+
| ENAME | SAL     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+
4 rows in set (0.01 sec)

26列出在每个部门工作的员工数量,平均工资和平均服务期限.

mysql>  select d.dname, avg(sal),count(*) from emp e join dept d on d.deptno=e.deptno group by e.deptno;
+------------+-------------+----------+
| dname      | avg(sal)    | count(*) |
+------------+-------------+----------+
| RESEARCH   | 2175.000000 |        5 |
| SALES      | 1566.666667 |        6 |
| ACCOUNTING | 2916.666667 |        3 |
+------------+-------------+----------+
3 rows in set (0.00 sec)

额 这个平均服务期限 蒙蔽 没有相关数据啊

下面是修改加上情况不存的时候 修改为0

mysql> select d.dname, ifnull(avg(sal),0),count(e.ename) from emp e right join dept d on d.deptno=e.deptno group by e.deptno;
+------------+--------------------+----------------+
| dname      | ifnull(avg(sal),0) | count(e.ename) |
+------------+--------------------+----------------+
| RESEARCH   |        2175.000000 |              5 |
| SALES      |        1566.666667 |              6 |
| ACCOUNTING |        2916.666667 |              3 |
| OPERATIONS |           0.000000 |              0 |
+------------+--------------------+----------------+
4 rows in set (0.00 sec)

加上一个求年差额函数 timestampdiff(格式，日期，日期)

mysql> select d.dname, ifnull(avg(sal),0),count(e.ename),avg(timestampdiff(year,hiredate,now())) from emp e right join dept d on d.deptno=e.deptno group by e.deptno;
+------------+--------------------+----------------+-----------------------------------------+
| dname      | ifnull(avg(sal),0) | count(e.ename) | avg(timestampdiff(year,hiredate,now())) |
+------------+--------------------+----------------+-----------------------------------------+
| RESEARCH   |        2175.000000 |              5 |                                 37.4000 |
| SALES      |        1566.666667 |              6 |                                 39.5000 |
| ACCOUNTING |        2916.666667 |              3 |                                 39.3333 |
| OPERATIONS |           0.000000 |              0 |                                    NULL |
+------------+--------------------+----------------+-----------------------------------------+
4 rows in set (0.00 sec)

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

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;

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

mysql> select d.*,e.num from (select count(*) 'num',deptno from emp group by deptno) e join dept d on e.deptno=d.deptno;
+--------+------------+----------+-----+
| DEPTNO | DNAME      | LOC      | num |
+--------+------------+----------+-----+
|     10 | ACCOUNTING | NEW YORK |   3 |
|     20 | RESEARCH   | DALLAS   |   5 |
|     30 | SALES      | CHICAGO  |   6 |
+--------+------------+----------+-----+
3 rows in set (0.00 sec)

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

mysql> select job,min(sal),ename from emp group by job;
+-----------+----------+-------+
| job       | min(sal) | ename |
+-----------+----------+-------+
| CLERK     |   800.00 | SMITH |
| SALESMAN  |  1250.00 | ALLEN |
| MANAGER   |  2450.00 | JONES |
| ANALYST   |  3000.00 | SCOTT |
| PRESIDENT |  5000.00 | KING  |
+-----------+----------+-------+
5 rows in set (0.00 sec)

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

mysql> select e.ename,min(e.sal),d.deptno from emp e join dept d on e.deptno =d.deptno  where job='MANAGER' group by e.deptno;
+-------+------------+--------+
| ename | min(e.sal) | deptno |
+-------+------------+--------+
| JONES |    2975.00 |     20 |
| BLAKE |    2850.00 |     30 |
| CLARK |    2450.00 |     10 |
+-------+------------+--------+
3 rows in set (0.00 sec)

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

mysql>  select ename,((sal+ifnull(comm,0))*12) from emp order by ((sal+ifnull(comm,0))*12) asc;
+--------+---------------------------+
| ename  | ((sal+ifnull(comm,0))*12) |
+--------+---------------------------+
| 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 |
| SCOTT  |                  36000.00 |
| FORD   |                  36000.00 |
| KING   |                  60000.00 |
+--------+---------------------------+

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

mysql> select e.ename,f.ename from emp e join emp f on e.mgr=f.empno where f.sal>3000;
+-------+-------+
| ename | ename |
+-------+-------+
| JONES | KING  |
| BLAKE | KING  |
| CLARK | KING  |
+-------+-------+
3 rows in set (0.00 sec)

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

mysql> select d.deptno,d.dname,count(e.deptno),sum(e.sal) from emp e right join dept d on e.deptno=d.deptno where d.loc like '%s%' group by d.deptno;
+--------+------------+-----------------+------------+
| deptno | dname      | count(e.deptno) | sum(e.sal) |
+--------+------------+-----------------+------------+
|     20 | RESEARCH   |               5 |   10875.00 |
|     40 | OPERATIONS |               0 |       NULL |
+--------+------------+-----------------+------------+
2 rows in set (0.01 sec)

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

update emp set sal=sal*1.1 where timestampdiff(year,hiredate,now())>30;

好家伙 这一改 我的原数据都变了

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值