2020-11-22

最新推荐文章于 2024-09-11 10:18:51 发布
最新推荐文章于 2024-09-11 10:18:51 发布
阅读量170 收藏
点赞数 2
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_46131402/article/details/109966736
版权

MySQL学习周报——习题练习

1、取得每个部门最高薪水的人员名称
第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
mysql> select deptno,max(sal) as maxsal from emp group by deptno;
±-------±--------+
| deptno | maxsal |
±-------±--------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
±-------±--------+
第二步：将以上的查询结果当做一张临时表t，
t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal
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、哪些人的薪水在部门的平均薪水之上
第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+
第二步：将以上查询结果当做t表，t和emp表连接
条件：部门编号相同，并且emp的sal大于t表的avgsal
select
t.*, e.ename, e.sal
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;
±-------±------------±------±--------+
| deptno | avgsal | ename | sal |
±-------±------------±------±--------+
| 30 | 1566.666667 | ALLEN | 1600.00 |
| 20 | 2175.000000 | JONES | 2975.00 |
| 30 | 1566.666667 | BLAKE | 2850.00 |
| 20 | 2175.000000 | SCOTT | 3000.00 |
| 10 | 2916.666667 | KING | 5000.00 |
| 20 | 2175.000000 | FORD | 3000.00 |
±-------±------------±------±--------+

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

平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。

平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值。

第一步：找出每个人的薪水等级
emp e和salgrade s表连接。
连接条件：e.sal between s.losal and s.hisal

select 
	e.ename,e.sal,e.deptno,s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal;

+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| CLARK  | 2450.00 |     10 |     4 |
| KING   | 5000.00 |     10 |     5 |
| MILLER | 1300.00 |     10 |     2 |

| SMITH  |  800.00 |     20 |     1 |
| ADAMS  | 1100.00 |     20 |     1 |
| SCOTT  | 3000.00 |     20 |     4 |
| FORD   | 3000.00 |     20 |     4 |
| JONES  | 2975.00 |     20 |     4 |

| MARTIN | 1250.00 |     30 |     2 |
| TURNER | 1500.00 |     30 |     3 |
| BLAKE  | 2850.00 |     30 |     4 |
| ALLEN  | 1600.00 |     30 |     3 |
| JAMES  |  950.00 |     30 |     1 |
| WARD   | 1250.00 |     30 |     2 |
+--------+---------+--------+-------+

第二步：基于以上的结果继续按照deptno分组，求grade的平均值。
select 
	e.deptno,avg(s.grade)
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
group by
	e.deptno;

+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+

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

第一种：sal降序，limit 1
select ename,sal from emp order by sal desc limit 1;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
±------±--------+

第二种方案：select max(sal) from emp;

第三种方案：表的自连接
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 |
±--------+

select
distinct a.sal
from
emp a
join
emp b
on
a.sal < b.sal

a表
±--------+
| sal |
±--------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
±--------+

b表
±--------+
| sal |
±--------+
| 800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00 |
| 3000.00 |
| 1300.00 |
±--------+

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

第一种方案：降序取第一个。

第一步：找出每个部门的平均薪水
	select deptno,avg(sal) as avgsal from emp group by deptno;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
第二步：降序选第一个。
	select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	+--------+-------------+

第二种方案：max

第一步：找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;
+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：找出以上结果中avgsal最大的值。
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
+---------------+
| max(t.avgsal) |
+---------------+
|   2916.666667 |
+---------------+

第三步：
select 
	deptno,avg(sal) as avgsal 
from 
	emp 
group by 
	deptno
having
	avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+

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

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

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

平均薪水是800
平均薪水是900
那么他俩都是1级别。

第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
±-------±------------+
| deptno | avgsal |
±-------±------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
±-------±------------+

第二步：找出每个部门的平均薪水的等级
以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal

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;

±-----------±------------±------+
| dname | avgsal | grade |
±-----------±------------±------+
| SALES | 1566.666667 | 3 |
| ACCOUNTING | 2916.666667 | 4 |
| RESEARCH | 2175.000000 | 4 |
±-----------±------------±------+

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

抛开之前的，最低等级你怎么着？
平均薪水最低的对应的等级一定是最低的.
select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
±------------+
| avgsal |
±------------+
| 1566.666667 |
±------------+

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;
+-------+
| grade |
+-------+
|     3 |
+-------+

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

比“普通员工的最高薪水”还要高的一定是领导！
	没毛病！！！！

mysql> select distinct mgr from emp where mgr is not null;
±-----+
| mgr |
±-----+
| 7902 |
| 7698 |
| 7839 |
| 7566 |
| 7788 |
| 7782 |
±-----+
员工编号没有在以上范围内的都是普通员工。

第一步：找出普通员工的最高薪水！
not in在使用的时候，后面小括号中记得排除NULL。
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
±---------+
| max(sal) |
±---------+
| 1600.00 |
±---------+

第二步：找出高于1600的
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 |
±------±--------+

9、取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 5;
±------±--------+
| ename | sal |
±------±--------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
±------±--------+

10、取得薪水最高的第六到第十名员工
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 |
| MARTIN | 1250.00 |
±-------±--------+

11、取得最后入职的 5 名员工
日期也可以降序，升序。

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

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

第一步：找出每个员工的薪水等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
±-------±--------±------+
| ename | sal | grade |
±-------±--------±------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±-------±--------±------+

第二步：继续按照grade分组统计数量
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 | 3 |
| 2 | 3 |
| 3 | 2 |
| 4 | 5 |
| 5 | 1 |
±------±---------+

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

14、列出所有员工及领导的姓名
select
a.ename ‘员工’, b.ename ‘领导’
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 | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
±-------±------+

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
emp a 员工表
emp b 领导表
a.mgr = b.empno and a.hiredate < b.hiredate

select
a.ename ‘员工’, a.hiredate, b.ename ‘领导’, b.hiredate, d.dname
from
emp a
join
emp b
on
a.mgr = b.empno
join
dept d
on
a.deptno = d.deptno
where
a.hiredate < b.hiredate;

±------±-----------±------±-----------±-----------+
| 员工 | hiredate | 领导 | 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 |
±------±-----------±------±-----------±-----------+

程程爱干饭
关注
专栏目录
mysql计算员工的日薪_mysql 综合练习（一）
weixin_35802555的博客
02-19 2381
-- 创建部门表DROP TABLE IF EXISTS dept;CREATE TABLE dept(-- 部门编号deptno int PRIMARY KEY,-- 部门名称dname VARCHAR(14),-- 部门所在地loc VARCHAR(13));-- 向部门表插入数据INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');INSE...
SQL数据分析题
airgzne的博客
12-24 1603
sql必看内容
Oracle（二）
ningmeng666_c的博客
08-26 1585
三、SQL语言介绍 分类 命令 DDL ，Data Definition Language数据定义语言 create:创建；drop:删除；alter:修改；rename:重命名；truncate:截断 DML，Data Management Language数据管理语言 insert:插入；delete:删除；update:更新；select:查询 DCL，Data Control Language数据库控制语言 grant:授权；revoke:回收权力；commit:提交事务；r
查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级
热门推荐
蜗牛学习笔记
06-05 3万+
如题：查询出每个雇员的姓名，工资，部门名称，工资在公司的等级及其领导的姓名，领导的工资，以及领导所对应的等级 这是oracle的默认Scott用户下面的emp，dept 和 salgrade 表间的一道思考题。 分析:先明确工资等级表(salgrade)中的内容；再查询查询出每个雇员的姓名，工资，部门名称，工资在公司的等级(salgrade)；在此上基础上增加查找上司姓名；最后确定雇员的经理姓名,经理的工资,以及经理工资所对应的等级。从这个例子可以看出，对于复杂的多表查询，要一步一步地实现，不要急于求成。
Oracle-数据库所有查询命令
春风十里不如你
03-17 3379
1.简单查询 1. 消除重复的姓 select distinct e.last_name as 姓氏 from employees e 2. 计算员工的月收入（工资+佣金） select salary,salary*(nvl(commission_pct,0)+1) as 工资佣金 from employees –3. 查询在 1998 年入职的员工 select * from employees e where to_char(e.hire_date,‘yyyy’)=‘1998’ –4. 查询姓以 B 开
gmall2020-mock-log-2020-04-01.jar
12-30
用户行为日志生成脚本
湖北武汉新洲区2020-2020学年七年级上期末试卷--数学.doc
10-11
这份文档是湖北武汉新洲区2020-2020学年七年级上学期的数学期末试卷，主要涵盖了一年级初中生需要掌握的基础数学知识。试卷包括选择题、填空题和解答题，涉及了数学术语、运算、几何、代数等多个方面。 1. 选择题：...
Magento-CE-2.3.4_sample_data-2020-01-22-11-11-58.tar.gz
03-09
Magento-CE-2.3.4_sample_data-2020-01-22-11-11-58.tar.gz源自官网
第九章第三节脚本.pdf-2020-10-05-11-44-22-408.docx
12-03
第九章第三节脚本.pdf-2020-10-05-11-44-22-408.docx
22-09-2020-no2
02-15
很抱歉，根据您提供的信息，标题"22-09-2020-no2"和描述"22-09-2020-no2"并没有提供具体的知识点或主题，标签为空，且压缩包子文件的文件名称列表只包含了一个条目"22-09-2020-no2-master"，这同样没有给出足够的上...
Oracle数据库的学习笔记
LiangGaGa的博客
10-13 738
Oracle数据库的学习笔记 一、限制条件的查询的练习 1.显示所有没有主管经理的雇员名字和工作岗位 SQL&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; select ename ,job 2 from emp 3 where mgr is null; ENAME JOB KING PRESIDENT 2.显示所有有佣金的雇员名字，薪水和佣金的降序排序 select ename,sal,com
数据库练习
weixin_56680764的博客
08-13 1597
数据库练习
mysql作业_MySQL-34道作业题（1）
weixin_34766614的博客
01-25 276
1、取得每个部门最高薪水的人员的名称：第一步：取得每个部门最高薪水【按照部门分组求最大值】select deptno ,max(sal) as maxsal from emp group by deptno;第二步：将上面的查询结果当作临时表题，T表和emp e表进行表连接，条件：t.deptno=e.deptno and t.maxsal=e.sal;selectt.*,e.namefrom e...
MYSQL34道练习题
yaoqinghao的博客
02-08 378
学习课程来源：http://www.bjpowernode.com/javavideo/111.html 1、取得每个部门最高薪水的人员名称 //第一步：找出每个部门的最高薪水 select deptno,max(sal) as maxsal from emp group by deptno; +----------+--------+ | max(sal) | deptno | +----------+--------+ | 3000.00 | 20 | | 2850.00 | 30
MySQL练习题
xiaosa的博客
10-23 412
1、取得每个部门最高薪水的人员名称 第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值) 第二步：将以上的查询结果当做一张临时表t，条件：t.deptno = e.deptno and t.maxsal = e.sal 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
数据库运维实操优质文章文档分享（含Oracle、MySQL等） | 2024年8月刊
Era666的博客
09-10 1901
本文为大家整理了墨天轮数据社区2024年8月发布的优质技术文章、文档，主题涵盖Oracle、MySQL、PostgreSQL等主流数据库系统以及国产数据库的技术实操！
MySQL 大量 IN 的查询优化
xchenhao 的博客
09-09 599
MySQL 大量 IN 的查询优化
【mysql】mysql之优化
最新发布
zerotoall的博客
09-11 2701
数据库优化 　 一、数据库硬件优化（选型） 　　 1.一般数据库选择 　　 2.数据库类型 　　 3.硬件选型 　　 4.操作系统优化 　　 5.应用端优化 　 二、创建数据库 　　 1.创建一个库一个表，并插入100万数据 　　 2.查看数据可用性 　　 3.进行压力测试 　 三、数据库参数优化 　　 1.Max_connections 　　 2.back_log 　　 3.wait_timeout和interactive_timeout 　　
基于SpringBoot+Vue+MySQL的校园生活服务平台
程序员大金的博客
09-10 959
基于SpringBoot+Vue+MySQL的校园生活服务平台，附源码。
根据时间段【2020-11-20 09:09:09,2021-10-09 10:10:10】先转换为【2020-11-20 00:00:00,2021-10-09 59:59:59】 再根据转换时间把里面的每一天的开始时间和结束时间获取出来 java
05-24
Day 2020-11-22 starts at 2020-11-22T00:00 and ends at 2020-11-22T23:59:59.999999999 ... Day 2021-10-07 starts at 2021-10-07T00:00 and ends at 2021-10-07T23:59:59.999999999 Day 2021-10-08 starts at ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值