34道练习题

最新推荐文章于 2024-04-19 03:50:26 发布
最新推荐文章于 2024-04-19 03:50:26 发布
阅读量588 收藏 1
点赞数
分类专栏： Mysql学习 文章标签： mysql 数据库 dba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43761240/article/details/125276772
版权

34道练习题

表数据

dept表数据
  部门编号	  部门名字		地理位置
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
emp表数据
 员工编号  员工姓名    工作岗位   上级编号   入职日期       工资      补助    部门编号
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL    |     20 |
|  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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
salgrade表数据
 工资等级  最低工资	最高工资
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

习题练习

1.取得每个部门最高薪水的员工名称
//该写法错误，可能存在其他部门员工的工资和该工资相等
mysql> select e.ename,t.maxsal from emp e join (select max(sal) as maxsal from emp group by deptno) t on e.sal=t.maxsal;
+-------+---------+
| ename | maxsal  |
+-------+---------+
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| BLAKE | 2850.00 |
| KING  | 5000.00 |
+-------+---------+
4 rows in set (0.04 sec)
//正确写法
mysql> select e.ename,e.deptno,t.maxsal from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on e.sal=t.maxsal and t.deptno=e.deptno;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| SCOTT |     20 | 3000.00 |
| FORD  |     20 | 3000.00 |
| BLAKE |     30 | 2850.00 |
| KING  |     10 | 5000.00 |
+-------+--------+---------+
4 rows in set (0.04 sec)
2.哪些人的薪水在部门的平均薪水之上
mysql> select e.ename,e.sal,t.maxsal,t.deptno from emp e join (select deptno,avg(sal) as maxsal from emp group by deptno) t on e.deptno=t.deptno and e.sal>t.maxsal;
+-------+---------+-------------+--------+
| ename | sal     | maxsal      | deptno |
+-------+---------+-------------+--------+
| ALLEN | 1600.00 | 1566.666667 |     30 |
| JONES | 2975.00 | 2175.000000 |     20 |
| BLAKE | 2850.00 | 1566.666667 |     30 |
| SCOTT | 3000.00 | 2175.000000 |     20 |
| KING  | 5000.00 | 2916.666667 |     10 |
| FORD  | 3000.00 | 2175.000000 |     20 |
+-------+---------+-------------+--------+
6 rows in set (0.04 sec)
3.取得部门中(所有人的)平均的薪水等级
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 |
| CLARK  |     4 |
| BLAKE  |     4 |
| JONES  |     4 |
| SCOTT  |     4 |
| FORD   |     4 |
| KING   |     5 |
+--------+-------+
14 rows in set (0.16 sec)
4.不准用组函数(MAX)，取得最高薪水
//方案一  sal降序，limit分页
mysql> select ename,sal from emp order by sal desc limit 1;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.04 sec)
//方案二  表的自连接
mysql> select ename,sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.15 sec)
5.取得平均薪水最高的部门的部门编号
//第一种  排序 
mysql> select deptno,avgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) as a order by avgsal desc limit 1;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.20 sec)
//第二种  
mysql> select deptno,avg(sal) as avgsal from emp group by deptno having avgsal=(select max(avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) as depts);
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.04 sec)
6.取得平均薪水最高的部门的部门名称
mysql> select dname from dept where deptno=(select deptno from emp group by deptno having avg(sal)=(select max(avgsal) from (select deptno,avg(sal) as avgsal from emp group by deptno) as depts));
+------------+
| dname      |
+------------+
| ACCOUNTING |
+------------+
1 row in set (0.04 sec)

mysql> select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname order by avgsal desc limit 1;
+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+
1 row in set (0.04 sec)
7.求平均薪水的等级最高的部门名称
//该写法未考虑同时有两个部门平均薪水等级最高
mysql> select d.deptno,de.dname,s.grade,d.avgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) d join salgrade s on  d.avgsal between s.losal and s.hisal join dept de on de.deptno=d.deptno order by grade desc limit 1;
+--------+----------+-------+-------------+
| deptno | dname    | grade | avgsal      |
+--------+----------+-------+-------------+
|     20 | RESEARCH |     4 | 2175.000000 |
+--------+----------+-------+-------------+
1 row in set (0.05 sec)
//正确写法一
mysql> select d.deptno,de.dname,s.grade,d.avgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) d join salgrade s on  d.avgsal between s.losal and s.hisal join dept de on de.deptno=d.deptno  where s.grade=(select g.grade from (select deptno,avg(sal) as avgsal from emp group by deptno) f join salgrade g on  f.avgsal between g.losal and g.hisal order by g.grade desc limit 1);
+--------+------------+-------+-------------+
| deptno | dname      | grade | avgsal      |
+--------+------------+-------+-------------+
|     10 | ACCOUNTING |     4 | 2916.666667 |
|     20 | RESEARCH   |     4 | 2175.000000 |
+--------+------------+-------+-------------+
2 rows in set (0.05 sec)
//正确写法二
mysql> select x.deptno,x.dname,x.grade,x.avgsal from (select d.deptno,de.dname,s.grade,d.avgsal from (select deptno,avg(sal) as avgsal from emp group by deptno) d join salgrade s on  d.avgsal between s.losal and s.hisal join dept de on de.deptno=d.deptno order by grade desc) as x group by x.deptno,x.grade having x.grade=(select max(s.grade) from (select deptno,avg(sal) as avgsal from emp group by deptno) d join salgrade s on  d.avgsal between s.losal and s.hisal);
+--------+------------+-------+-------------+
| deptno | dname      | grade | avgsal      |
+--------+------------+-------+-------------+
|     20 | RESEARCH   |     4 | 2175.000000 |
|     10 | ACCOUNTING |     4 | 2916.666667 |
+--------+------------+-------+-------------+
2 rows in set (0.06 sec)
8.取得比普通员工的最高薪水还要高的领导人姓名
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     |
+-------+---------+
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| KING  | 5000.00 |
+-------+---------+
6 rows in set (0.04 sec)
9.取出薪水最高的前五名普通员工
mysql> select ename,sal from emp where empno not in(select distinct mgr from emp where mgr is not null) order by sal desc limit 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.04 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.04 sec)
11.取得最后入职的五名员工
mysql> select ename,hiredate from emp order by hiredate desc limit 5;
+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| JAMES  | 1981-12-03 |
| FORD   | 1981-12-03 |
+--------+------------+
5 rows in set (0.04 sec)
12.取得每个薪水等级有多少员工
mysql> select grade,count(*) from (select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and hisal) as g group by grade;
+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+
5 rows in set (0.04 sec)
13.
14.列出所有员工及领导的名字
mysql> select a.ename as '员工', ifnull(b.ename,'没有上级') as'领导' from emp a left join emp b on a.mgr =b.empno;
+--------+----------+
| 员工   | 领导     |
+--------+----------+
| 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.05 sec)
15.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
mysql> select x.ename,x.empno,y.dname from (select a.empno,a.ename,a.deptno,a.hiredate from emp a where a.hiredate <(select hiredate from emp b where b.empno =a.mgr)) as x join dept as y on x.deptno=y.deptno;
+-------+-------+------------+
| ename | empno | dname      |
+-------+-------+------------+
| SMITH |  7369 | RESEARCH   |
| ALLEN |  7499 | SALES      |
| WARD  |  7521 | SALES      |
| JONES |  7566 | RESEARCH   |
| BLAKE |  7698 | SALES      |
| CLARK |  7782 | ACCOUNTING |
+-------+-------+------------+
6 rows in set (0.04 sec)
16.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
mysql> select d.dname,e.ename from dept d left join emp e on e.deptno=d.deptno order by d.dname;
+------------+--------+
| dname      | ename  |
+------------+--------+
| ACCOUNTING | MILLER |
| ACCOUNTING | KING   |
| ACCOUNTING | CLARK  |
| OPERATIONS | NULL   |
| RESEARCH   | FORD   |
| RESEARCH   | ADAMS  |
| RESEARCH   | SCOTT  |
| RESEARCH   | JONES  |
| RESEARCH   | SMITH  |
| SALES      | JAMES  |
| SALES      | TURNER |
| SALES      | BLAKE  |
| SALES      | MARTIN |
| SALES      | WARD   |
| SALES      | ALLEN  |
+------------+--------+
15 rows in set (0.04 sec)
17.列出至少5个员工的所有部门
mysql> select deptno,count(*) from emp group by deptno having count(*)>=5;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     20 |        5 |
|     30 |        6 |
+--------+----------+
2 rows in set (0.03 sec)
18.列出薪水比“SMITH”多的所有员工信息
mysql> select ename,sal from emp where sal>(select sal from emp where ename='SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.04 sec)
19.列出所有做“CLERK”工作(办事员)的姓名及其部门名称，部门的人数
mysql> select x.ename,x.dname,y.co from (select  e.ename,d.dname,d.deptno from (select ename,deptno from emp where job='CLERK') as e join dept as d on e.deptno=d.deptno) as x join (select  deptno,count(*) as co from emp group by deptno) as  y on x.deptno=y.deptno;
+--------+------------+----+
| ename  | dname      | co |
+--------+------------+----+
| SMITH  | RESEARCH   |  5 |
| JAMES  | SALES      |  6 |
| ADAMS  | RESEARCH   |  5 |
| MILLER | ACCOUNTING |  3 |
+--------+------------+----+
4 rows in set (0.04 sec)
20.列出最低薪资大于1500的各种工作及其从事次工作的全部雇佣人员
mysql> select a.job,e.ename,a.minsal from (select job,min(sal) as minsal from emp group by job having min(sal)>1500) a join emp e on a.job =e.job;
+-----------+-------+---------+
| job       | ename | minsal  |
+-----------+-------+---------+
| ANALYST   | SCOTT | 3000.00 |
| ANALYST   | FORD  | 3000.00 |
| MANAGER   | CLARK | 2450.00 |
| MANAGER   | BLAKE | 2450.00 |
| MANAGER   | JONES | 2450.00 |
| PRESIDENT | KING  | 5000.00 |
+-----------+-------+---------+
6 rows in set (0.04 sec)
21.列出在部门“SALES”<销售部>工作的员工的姓名，假定不知道销售部的部门编号
mysql> select ename from emp where deptno=(select deptno from dept where dname='SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.04 sec)
22.列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，雇员的工资等级
mysql> select a.ename,c.dname,d.ename,b.grade from (select ename,deptno,mgr,sal from emp where sal>(select avg(sal) from emp)) a join salgrade b on a.sal between b.losal and b.hisal join dept c on a.deptno =c.deptno left join emp d on a.mgr=d.empno;
+-------+------------+-------+-------+
| ename | dname      | ename | grade |
+-------+------------+-------+-------+
| FORD  | RESEARCH   | JONES |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
+-------+------------+-------+-------+
6 rows in set (0.05 sec)
23.列出与“SCOTT”从事相同工作的所有员工及部门名称
mysql> select a.ename,b.dname from (select ename,deptno from emp where job=(select job from emp where ename='SCOTT')) a join dept b on a.deptno=b.deptno where ename!='SCOTT';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.04 sec)
24.列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
mysql> select e.ename,e.sal from emp e join (select distinct sal from emp where deptno=30) b on e.sal=b.sal and e.deptno!=30;
Empty set
25.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水，部门名称
mysql> select e.ename,e.sal,d.dname from emp e join dept d on d.deptno=e.deptno where e.sal>(select max(sal) from emp where deptno=30);
+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
| KING  | 5000.00 | ACCOUNTING |
+-------+---------+------------+
4 rows in set (0.03 sec)
26.列出在每个部门工作的员工数量，平均工资和平均服务期限。

mysql>  select d.dname,ifnull(avg(sal),0),count(ename),ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) as year from emp e right join dept d on e.deptno=d.deptno group by d.deptno;
+------------+--------------------+--------------+---------+
| dname      | ifnull(avg(sal),0) | count(ename) | year    |
+------------+--------------------+--------------+---------+
| ACCOUNTING |        2916.666667 |            3 | 40.3333 |
| RESEARCH   |        2175.000000 |            5 | 38.4000 |
| SALES      |        1566.666667 |            6 | 40.5000 |
| OPERATIONS |           0.000000 |            0 | 0.0000  |
+------------+--------------------+--------------+---------+
4 rows in set (0.05 sec)
27.列出所有员工的姓名，部门名称和工资
mysql> select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;
+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| SMITH  | RESEARCH   |  800.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| JONES  | RESEARCH   | 2975.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| CLARK  | ACCOUNTING | 2450.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| KING   | ACCOUNTING | 5000.00 |
| TURNER | SALES      | 1500.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| JAMES  | SALES      |  950.00 |
| FORD   | RESEARCH   | 3000.00 |
| MILLER | ACCOUNTING | 1300.00 |
+--------+------------+---------+
14 rows in set (0.04 sec)
28.列出所有部门的详细信息和人数
mysql> select d.dname,d.deptno,d.loc,ifnull(e.co,0) from dept d left join (select deptno,count(*) as co from emp group by deptno) e  on d.deptno=e.deptno;
+------------+--------+----------+----------------+
| dname      | deptno | loc      | ifnull(e.co,0) |
+------------+--------+----------+----------------+
| ACCOUNTING |     10 | NEW YORK |              3 |
| RESEARCH   |     20 | DALLAS   |              5 |
| SALES      |     30 | CHICAGO  |              6 |
| OPERATIONS |     40 | BOSTON   |              0 |
+------------+--------+----------+----------------+
4 rows in set (0.04 sec)
29.列出各种工资的最低工资及从事此工作的雇员姓名
mysql> select b.job, b.minsal,e.ename from (select job,min(sal) as minsal from emp group by job) b join emp e on b.job=e.job and b.minsal =e.sal;
+-----------+---------+--------+
| job       | minsal  | ename  |
+-----------+---------+--------+
| ANALYST   | 3000.00 | SCOTT  |
| ANALYST   | 3000.00 | FORD   |
| CLERK     |  800.00 | SMITH  |
| MANAGER   | 2450.00 | CLARK  |
| PRESIDENT | 5000.00 | KING   |
| SALESMAN  | 1250.00 | WARD   |
| SALESMAN  | 1250.00 | MARTIN |
+-----------+---------+--------+
7 rows in set (0.04 sec)
30.列出各个部门的MANAGER(领导)的最低薪水
mysql> select distinct deptno,min(sal) from emp where job='MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     30 |  2850.00 |
|     20 |  2975.00 |
+--------+----------+
3 rows in set (0.05 sec)
31.列出所有员工的年工资，按年薪从低到高排序
mysql> select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal;
+--------+----------+
| 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 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.05 sec)
32.求出员工领导的薪水超过3000的员工名称与领导名称
mysql> select x.ename as '员工',y.ename as '领导' from emp x join (select distinct a.mgr,b.ename from emp a join emp b on a.mgr=b.empno and b.sal>3000) y on x.mgr=y.mgr;
+-------+------+
| 员工  | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+
3 rows in set (0.05 sec)
33.求出部门名称中，带‘S’字符的部门员工的工资合计，部门人数
mysql> select b.deptno ,b.dname,ifnull(sum(a.sal),0),count(a.ename) from emp a right join (select deptno,dname from dept where dname like '%S%') b on a.deptno=b.deptno group by deptno,dname;
+--------+------------+----------------------+----------------+
| deptno | dname      | ifnull(sum(a.sal),0) | count(a.ename) |
+--------+------------+----------------------+----------------+
|     20 | RESEARCH   |             10875.00 |              5 |
|     30 | SALES      |              9400.00 |              6 |
|     40 | OPERATIONS |                 0.00 |              0 |
+--------+------------+----------------------+----------------+
3 rows in set (0.05 sec)
34.给任职日期超过30年的员工加薪10%；
mysql> update emp set sal=sal*1.1 where ifnull(timestampdiff(YEAR,hiredate,now()),0)>30;
Query OK, 14 rows affected (0.39 sec)
Rows matched: 14  Changed: 14  Warnings: 0

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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.05 sec)
沐雨~橙风
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL练习题（34道）
09-12
此练习题可以配套本人MySQL讲解专栏使用，在本人文件分享中，有nanxi.sql文件，该文件为此练习题配套文件，文档中所有题目均来源于该文件. 适合人群： 初学mysql的任何人群，已经配套专栏系统学习过mysql的任何人群...
MySQL--找出每个部门薪水最高的员工 方法总结
weixin_52012241的博客
05-08 6206
首先给出本文所需的表格： emp表： mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+--------
MySqL速成教程笔记系列六
weixin_54248223的博客
10-23 1817
分组查询
【每日SQL打卡】DAY 1丨部门工资最高的员工【难度中等】_sql查询工资前10名的员工姓名和工资情况
最新发布
2401_83946509的博客
04-19 324
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。对于从来没有接触过网络安全的同学，我们帮你准备了详细的学习成长路线图。可以说是最科学最系统的学习路线，大家跟着这个大的方向学习准没问题。，朋友们如果有需要全套《网络安全入门+黑客进阶学习资源包》，可以扫描下方二维码领取（如遇扫码问题，可以在评论区留言领取哦）~表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
第三章 MySQL数据库
weixin_44530052的博客
10-23 3276
MySQL的练习和一些SQL语句
数据库练习
weixin_56680764的博客
08-13 1354
数据库练习
蓝凌EKP_sysUiComponent_任意文件上传漏洞（含批量验证poc）
2401_83946070的博客
04-16 273
蓝凌EKP，是蓝凌旗下的协同办公平台，其定位在平台型产品，它是结合蓝凌知识管理及移动互联网理念设计，综合企业管理支撑系统发展而来，并将协同办公、知识共享、文化管理、战略管理等融合为一体，通过企业门户统一战线，满足用户个性化办公要求的一体化工作平台。蓝凌OA sysUiComponent接口处存在任意文件上传漏洞，未经过身份认证的攻击者可通过构造压缩文件上传恶意后门文件，远程命令执行，获取服务器权限。可以说是最科学最系统的学习路线，大家跟着这个大的方向学习准没问题。还有大家最喜欢的黑客技术。
MYSQL练习题34道.zip
06-16
MySQL数据库基础练习题34道，有题有答案，包括练习用.sql文件，适合新手练习。 基础的增删改查。
34道SQL习题详解
10-13
数据库方面的习题讲解具备牢固的技术基础，一般就可以满足项目中普通程序员的要求了，但如果你需要找的是一个资深程序员，那么你还需要对面试者的技术深度进行考察。我们现在做项目时都会大量使用框架，这能使我们的...
34道作业题.txt
12-28
动力节点 sql练习题
MySQL34道作业题.md
12-13
适合巩固mysql知识
mysql查询工资大于某个员工_MySQL（二）
weixin_40005373的博客
02-02 3871
简单查询语法格式：select 字段名1，字段名2，... from 表名；提示：1.任何一条sql语句以“；”结尾2.sql语句不区分大小写查询员工年薪：select ename.sal * 12 from emp;image.png给查询结果的列重命名：select ename.sal * 12 as yearsal from emp;(as关键字可以省略)image.png当起的别名有中文时...
mysql 查询部门工资前三高的所有员工,分组查询前几的问题
HeroNet2010的博客
08-30 2571
mysql查询部门工资前三的所有员工,分组查询前几的问题
MySQL部门工资最高的员工
wgchen
08-05 5148
MySQL部门工资最高的员工SQL架构题目描述题解方法：使用 JOIN 和 IN 语句方法2方法： GROUP BY 语句知识点exists 和 in1.原理2.分析3.总结4.效率5.举例说明 SQL架构 Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int); Create table If Not Exists Department (Id int, Name varch
MySQL34道练习题
heliuerya的博客
01-19 3436
MySQL34道练习题
MySQL-06练习：部门最高工资的员工
qq_41502322的博客
03-31 1398
MySQL-06练习： 部门最高工资的员工 题目 表：Employee +--------------+---------+ | 列名 | 类型 | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id是此表的主
mysql查询每个部门工资最高的员工信息_SQL简单面试题：查询每个部门工资最高的前10位员工...
weixin_35160938的博客
02-19 5826
前段时间同事突然甩了一个SQL面试题给我，题目是需要查询每个部门工资最高的前10位员工。想着查每个部门的最高还好，用max语句就可以搞定，这是要取前十。。。。快速构建了一下思路，查了一下有没有类似的语法可以解决。思路：查询每个部门工资最高的员工这样的员工取每个部门的前10个按照部门、工资排序准备数据:CREATE DATABASE company;CREATE TABLE `employee`(`...
mysql查询工资前十名_MYSQL查询以找到薪水第n高的所有员工
weixin_30229479的博客
01-18 1085
The two tables are salary_employee and employeeemployee_salarysalary_id emp_id salaryEmployeeemp_id | first_name | last_name | gender | email | mobile | dept_id | is_activeQuery to get the ...
mysql查询部门工资前三的信息
Mint6的博客
05-27 8990
两个表，一个部门表Department 一个Employee表 第一种，（如果有多名并列第三，只展示前二） SELECT emp.id , emp.name ,emp.salary, dep.id as depId , dep.name as depName FROM Employee emp ,Employee emp1, Department d...
生成 100道 mysql 练习题
05-12
34. 查询“students”表中男性学生的姓名和成绩。 35. 查询“students”表中女性学生的姓名和成绩。 36. 查询“students”表中年龄最大的学生的姓名和年龄。 37. 查询“students”表中年龄最小的学生的姓名和年龄。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值