老杜 | Mysql的34道作业题

最新推荐文章于 2024-01-16 16:05:19 发布

小七rrrrr

最新推荐文章于 2024-01-16 16:05:19 发布

阅读量739

点赞数 1

分类专栏： java 数据库 Mysql 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_43167873/article/details/121134891

版权

java 同时被 3 个专栏收录

90 篇文章 30 订阅

订阅专栏

Mysql

4 篇文章 1 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

MySQL34道作业题

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

mysql> select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal;
+-------+--------+---------+
| ename | deptno | maxsal  |
+-------+--------+---------+
| BLAKE |     30 | 2850.00 |
| SCOTT |     20 | 3000.00 |
| KING  |     10 | 5000.00 |
| FORD  |     20 | 3000.00 |
+-------+--------+---------+

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

mysql> select e.ename,t.* from emp e join (select deptno,avg(sal) as avgsal from emp group by deptno) t
    -> on e.deptno = t.deptno and e.sal > t.avgsal;
+-------+--------+-------------+
| ename | deptno | avgsal      |
+-------+--------+-------------+
| ALLEN |     30 | 1566.666667 |
| JONES |     20 | 2175.000000 |
| BLAKE |     30 | 1566.666667 |
| SCOTT |     20 | 2175.000000 |
| KING  |     10 | 2916.666667 |
| FORD  |     20 | 2175.000000 |
+-------+--------+-------------+

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

求的是部门薪资水平等级的平均值不是薪资水平平均值的等级

mysql> select t .*, avg(t.grade) as avggrade from (select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno
    -> ;
+-------+---------+--------+-------+----------+
| ename | sal     | deptno | grade | avggrade |
+-------+---------+--------+-------+----------+
| CLARK | 2450.00 |     10 |     4 |   3.6667 |
| SMITH |  800.00 |     20 |     1 |   2.8000 |
| ALLEN | 1600.00 |     30 |     3 |   2.5000 |
+-------+---------+--------+-------+----------+
3 rows in set (0.00 sec)

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

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

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

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

mysql> select t.deptno,max(t.avgsal) from ( select avg(sal) as avgsal,deptno from emp group by deptno) t;
+--------+---------------+
| deptno | max(t.avgsal) |
+--------+---------------+
|     10 |   2916.666667 |
+--------+---------------+
1 row in set (0.00 sec)

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

mysql> select d.dname,max(t.avgsal) from dept d join (select avg(sal) as avgsal,deptno from emp group by deptno) t
    -> on d.deptno = t.deptno;
+------------+---------------+
| dname      | max(t.avgsal) |
+------------+---------------+
| ACCOUNTING |   2916.666667 |
+------------+---------------+
1 row in set (0.00 sec)

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

mysql> select
    -> t.*,s.grade
    -> from
    -> (select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
    -> join
    -> salgrade s
    -> on
    -> t.avgsal between s.losal and s.hisal
    -> where
    -> s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);
+-------+-------------+-------+
| dname | avgsal      | grade |
+-------+-------------+-------+
| SALES | 1566.666667 |     3 |
+-------+-------------+-------+
1 row in set (0.00 sec)

mysql> select d.*,t.deptno from dept d join (select deptno, avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) t on d.deptno = t. deptno;
+--------+-------+---------+--------+
| DEPTNO | DNAME | LOC     | deptno |
+--------+-------+---------+--------+
|     30 | SALES | CHICAGO |     30 |
+--------+-------+---------+--------+
1 row in set (0.00 sec)

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

mysql> select ename from emp where sal >( select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
+-------+
| ename |
+-------+
| JONES |
| BLAKE |
| SCOTT |
| KING  |
| FORD  |
+-------+
5 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     |
+--------+---------+
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
+--------+---------+
5 rows in set (0.00 sec)

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

日期也可以降序，升序。

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 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
+--------+------------+
5 rows in set (0.00 sec)slq

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

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

13、面试题：

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

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

mysql> select e.ename,b.ename from emp e join emp b on e.empno = b.empno;
+--------+--------+
| ename  | ename  |
+--------+--------+
| ADAMS  | ADAMS  |
| ALLEN  | ALLEN  |
| BLAKE  | BLAKE  |
| CLARK  | CLARK  |
| FORD   | FORD   |
| JAMES  | JAMES  |
| JONES  | JONES  |
| KING   | KING   |
| MARTIN | MARTIN |
| MILLER | MILLER |
| SCOTT  | SCOTT  |
| SMITH  | SMITH  |
| TURNER | TURNER |
| WARD   | WARD   |
+--------+--------+
14 rows in set (0.00 sec)

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

mysql>  select e.ename 'employess' ,e.hiredate,b.ename 'employers',b.hiredate,d.dname from emp e join emp b on e.mgr = b.empno join dept d on e.deptno = d.deptno where e.hiredate < b.hiredate;
+-----------+------------+-----------+------------+------------+
| employess | hiredate   | employers | hiredate   | dname      |
+-----------+------------+-----------+------------+------------+
| CLARK     | 1981-06-09 | KING      | 1981-11-17 | ACCOUNTING |
| SMITH     | 1980-12-17 | FORD      | 1981-12-03 | RESEARCH   |
| JONES     | 1981-04-02 | KING      | 1981-11-17 | RESEARCH   |
| ALLEN     | 1981-02-20 | BLAKE     | 1981-05-01 | SALES      |
| WARD      | 1981-02-22 | BLAKE     | 1981-05-01 | SALES      |
| BLAKE     | 1981-05-01 | KING      | 1981-11-17 | SALES      |
+-----------+------------+-------

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

mysql> select e.*,d.dname from emp e join dept d on e.deptno = d.deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 1000.00 |    NULL |     10 | ACCOUNTING |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1000.00 |    NULL |     10 | ACCOUNTING |
|  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 | RESEARCH   |
|  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 | SALES      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
14 rows in set (0.00 sec)

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

mysql> select t.SUMCOUNT,t.dname from ( select count(*) as 'SUMCOUNT',d.dname from emp e join dept d on e.deptno = d.deptno group by d.deptno) t where t.SUMCOUNT >4;
+----------+----------+
| SUMCOUNT | dname    |
+----------+----------+
|        5 | RESEARCH |
|        6 | SALES    |
+----------+----------+

mysql> select count(*) from emp group by deptno having count(*) >= 5;
+----------+
| count(*) |
+----------+
|        5 |
|        6 |
+----------+
2 rows in set (0.00 sec)

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

mysql> select e.ename,e.sal from emp e join (select ename,sal from emp where ename = 'SMITH') t on e.sal > t.sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 1000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1000.00 |
+--------+---------+
13 rows in set (0.00 sec)

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

mysql> select
    -> t1.*,t2.deptcount
    -> 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') t1
    -> join
    -> (select deptno, count(*) as deptcount from emp group by deptno) t2
    -> on
    -> t1.deptno = t2.deptno;
+--------+-------+------------+--------+-----------+
| ename  | job   | dname      | deptno | deptcount |
+--------+-------+------------+--------+-----------+
| MILLER | CLERK | ACCOUNTING |     10 |         3 |
| SMITH  | CLERK | RESEARCH   |     20 |         5 |
| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
| JAMES  | CLERK | SALES      |     30 |         6 |
+--------+-------+------------+--------+-----------+
4 rows in set (0.00 sec)

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

mysql> select job,count(*) from emp group by job having min(sal) > 1500;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| PRESIDENT |        1 |
+-----------+----------+
2 rows in set (0.00 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.00 sec)

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

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);
+-------+------------+-------+-------+
| 员工     | dname      | 领导    | grade |
+-------+------------+-------+-------+
| JONES | RESEARCH   | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| SCOTT | RESEARCH   | JONES |     4 |
| KING  | ACCOUNTING | NULL  |     5 |
| FORD  | RESEARCH   | JONES |     4 |
+-------+------------+-------+-------+
5 rows in set (0.00 sec)

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

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

mysql>  select t.* from ( select a.job, a.ename,d.dname from emp a join dept d where a.deptno = d.deptno) t where t.job = (select job from emp where ename = 'SCOTT');
+---------+-------+----------+
| job     | ename | dname    |
+---------+-------+----------+
| ANALYST | SCOTT | RESEARCH |
| ANALYST | FORD  | RESEARCH |
+---------+-------+----------+

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

mysql> select ename ,sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;
Empty set (0.00 sec)

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

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

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

select 
	d.deptno, count(e.ename) ecount,ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

+--------+--------+-------------+----------------+
| deptno | ecount | avgsal      | avgservicetime |
+--------+--------+-------------+----------------+
|     10 |      3 | 2333.333333 |        39.3333 |
|     20 |      5 | 2175.000000 |        37.4000 |
|     30 |      6 | 1566.666667 |        39.6667 |
|     40 |      0 |    0.000000 |         0.0000 |
+--------+--------+-------------+----------------+
4 rows in set (0.00 sec)

在mysql当中怎么计算两个日期的“年差”，差了多少年？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年

mysql>  select d.deptno,count(ename) count,ifnull(avg(sal),0) avgsal,ifnull(avg(timestampdiff(YEAR,hiredate,now())),0) hiredate from emp e right join dept d on e.deptno = d.deptno group by d.deptno;
+--------+-------+-------------+----------+
| deptno | count | avgsal      | hiredate |
+--------+-------+-------------+----------+
|     10 |     3 | 2333.333333 |  39.3333 |
|     20 |     5 | 2175.000000 |  37.4000 |
|     30 |     6 | 1566.666667 |  39.6667 |
|     40 |     0 |    0.000000 |   0.0000 |
+--------+-------+-------------+----------+
4 rows in set (0.00 sec)

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

mysql> select e.ename,d.dname,e.sal from emp e left 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 | 1000.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 | 1000.00 |
+--------+------------+---------+
14 rows in set (0.00 sec)

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

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

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

mysql> select e.sal, e.ename,e.job from emp e join  (select job, min(sal) minsal from emp group by job) t on e.sal = t.minsal and e.job = t.job;
+---------+--------+-----------+
| sal     | ename  | job       |
+---------+--------+-----------+
| 3000.00 | SCOTT  | ANALYST   |
| 3000.00 | FORD   | ANALYST   |
|  800.00 | SMITH  | CLERK     |
| 1000.00 | CLARK  | MANAGER   |
| 5000.00 | KING   | PRESIDENT |
| 1250.00 | WARD   | SALESMAN  |
| 1250.00 | MARTIN | SALESMAN  |
+---------+--------+-----------+
7 rows in set (0.00 sec)

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

mysql> select deptno,min(sal) from emp where job = 'MANAGER' group by deptno;
+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  1000.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

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

mysql> select ename,(sal + ifnull(comm,0))*12 as yearsal from emp order by yearsal asc;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| MILLER | 12000.00 |
| CLARK  | 12000.00 |
| ADAMS  | 13200.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

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

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

mysql> select count(e.ename) count,d.dname,ifnull(sum(e.sal),0) sumsal from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.deptno;
+-------+------------+----------+
| count | dname      | sumsal   |
+-------+------------+----------+
|     5 | RESEARCH   | 10875.00 |
|     6 | SALES      |  9400.00 |
|     0 | OPERATIONS |     0.00 |
+-------+------------+----------+
3 rows in set (0.00 sec)

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

mysql> update emp set sal = sal*1.1 where timestampdiff(YEAR,hiredate,now())> 30;
Query OK, 14 rows affected (0.01 sec)
Rows matched: 14  Changed: 14  Warnings: 0

小七rrrrr

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
2
评论
老杜 | Mysql的34道作业题

MySQL34道作业题1、取得每个部门最高薪水的人员名称mysql> select e.ename,t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal;+-------+--------+---------+| ename | deptno | maxsal |+-------+-------
复制链接

扫一扫