MySQL8.0做老杜34道作业题。有的未必是按照他的思路，未完结。暂停，学新课，回头接着补，毕竟也要复习

想起啥就学啥

已于 2022-01-21 16:30:37 修改

阅读量993

点赞数

分类专栏： JAVA MySQL 文章标签： sql 数据库 mysql java database

于 2022-01-14 11:08:50 首次发布

本文链接：https://blog.csdn.net/pface/article/details/122486700

版权

JAVA 同时被 2 个专栏收录

27 篇文章 0 订阅

订阅专栏

MySQL

14 篇文章 0 订阅

订阅专栏

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

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

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

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

8、取得比普通员工的最高薪水还要高的领导人姓名

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

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

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

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

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

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值。

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

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

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

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

每条sql语句都是根据第一反应写出来的，并没有深究性能等需要仔细考虑的东西。

做完一道题我只会对答案，答案一样我就会做下一道题，并没有研究老杜的思路。

目前为止除了第八题有点难度，别的还好

前期emp，dept，salary三张表准备

CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	)
	;

CREATE TABLE SALGRADE
      ( GRADE INT,
	LOSAL INT,
	HISAL INT );




INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
, 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
, 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
, 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
, 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
, 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
, 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
, 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
, 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
, 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
, 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
, 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
, 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
, 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
5, 3001, 9999); 
commit;

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

第一步：获取emp表中每个部门的最高薪水

select deptno,max(sal) as maxsal from emp group by deptno;

第二步：将上述表作为临时表，和emp表通过detpno连接，获取每个部门最高工资的人员信息和部门编号，并确定筛选条件：emp的工资 = 第一张临时表.最高工资

select ename '姓名',e.maxsal '最高工资' from emp 
	inner join (select deptno,max(sal) as maxsal from emp group by deptno) e
		on emp.deptno = e.deptno
        where emp.sal = e.maxsal;

第三步：将上述结果集作为临时表和dept表，通过deptno连接获取部门名称

select ename '姓名',dept.dname '部门' ,e.maxsal '最高工资' from emp 
	inner join (select deptno,max(sal) as maxsal from emp group by deptno) e
		on emp.deptno = e.deptno
	inner join dept on e.deptno = dept.deptno
		where emp.sal = e.maxsal
        order by dept.dname;

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

第一步：查询出emp表中每个部门的平均薪水

select deptno,avg(sal) avgsal from emp group by deptno;

第二步：把上述表作为临时表连接emp表，查询出该部门编号下员工薪水>avgsal的员工信息

select emp.ename,emp.sal,emp.deptno from emp 
	inner join (select deptno,avg(sal) avgsal from emp group by deptno) e
	on emp.DEPTNO = e.deptno
	where emp.sal >= e.avgsal;

第三步：把上述表作为临时表连接dept表，查询出部门名称

select emp.ename '姓名',emp.sal '工资',dept.dname '部门' from emp 
	inner join (select deptno,avg(sal) avgsal from emp group by deptno) e
	    on emp.DEPTNO = e.deptno
	inner join dept 
	    on emp.deptno = dept.deptno
	        where emp.sal >= e.avgsal
	        order by dept.dname;

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

第一种情况：获取部门的薪水等级

第一步：获取每个部门的平均薪水

SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno;

第二步：把上述表作为临时表连接salary表，查询出部门的平均薪水等级

select salgrade.GRADE ' 级别',e.deptno as 'deptno',e.avgsal as 'avgsal' from
		(SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno) e 
		inner join salgrade
		on e.avgsal between salgrade.LOSAL and salgrade.HISAL;
		-- 或者
select salgrade.GRADE ' 级别',e.deptno as 'deptno',e.avgsal as 'avgsal' from
		(SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno) e,salgrade
		where e.avgsal between salgrade.LOSAL and salgrade.HISAL;

第三步：把上述表作为临时表连接dept表，查询出部门名称

select e2.grade,dept.dname ,e2.avgsal from 
		(select salgrade.GRADE 'grade',e.deptno as 'deptno',e.avgsal as 'avgsal' from
			(SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno) e 
				inner join salgrade
				on e.avgsal between salgrade.LOSAL and salgrade.HISAL) e2
		inner join dept 
			on dept.deptno = e2.deptno;

第二种情况，获取每个人的薪水等级

第一步：emp和dept表连接，查询出每个部门的员工薪水

select dept.dname deptname ,e.ename empname,e.sal empsal
	from dept
	inner join emp e
	on dept.deptno = e.deptno;

第二步：把上述表作为临时表连接salary表，查询出每个员工的薪水等级

select s.grade,e2.deptname,e2.deptno,e2.empname,e2.empsal
	from salgrade s
	inner join (
		select dept.dname deptname,dept.deptno deptno ,e.ename empname,e.sal empsal
			from dept
			    inner join emp e
			    on dept.deptno = e.deptno) e2
		on e2.empsal between s.LOSAL and s.HISAL;

第三步：算出部门平均薪水等级

select e3.deptnos '部门编号', e3.deptnames '部门名称',avg(e3.grades) '平均等级' 
	from (select s.grade grades,e2.deptname deptnames,e2.deptno deptnos,e2.empname enames,e2.empsal esals
		from salgrade s
			inner join (
				select dept.dname deptname,dept.deptno deptno ,e.ename empname,e.sal empsal
					from dept
					inner join emp e
						on dept.deptno = e.deptno) e2
			on e2.empsal between s.LOSAL and s.HISAL) e3
group by e3.deptnos , e3.deptnames;

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

select ename,sal from emp order by sal desc limit 0,1;

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

SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno order by avgsal desc limit 0,1;

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

第一步：获取每个部门的平均薪水

select deptno, avg(sal) as avgsal from emp group by deptno

第二步：把上述表作为临时表连接dept表，然后根据平均薪资排序，最后获取第一条数据

select dept.* ,e.avgsal from dept inner join 
	(select deptno, avg(sal) as avgsal from emp group by deptno) e
	on dept.deptno = e.deptno
	order by e.avgsal desc limit 0,1;

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

第一步：获取每个部门的平均薪水，并根据平均薪水进行升序排列

SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno order by avgsal asc

第二步：把上述表作为临时表连接salary表，确定三个部门的薪水等级后，取第一条数据

select s.grade grades,e.avgsal avgsals,e.deptno deptnos
		from salgrade s
		inner join 
			(SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno order by avgsal asc) e
			on e.avgsal between s.LOSAL and s.HISAL
		limit 0,1

第三步：把上述表作为临时表连接dept表，获取部门名称

select d.DNAME,e2.grades,e2.avgsals
from dept d
	inner join 
		(select s.grade grades,e.avgsal avgsals,e.deptno deptnos
		from salgrade s
		inner join 
			(SELECT deptno, avg( sal ) avgsal FROM emp GROUP BY deptno order by avgsal asc) e
			on e.avgsal between s.LOSAL and s.HISAL
		limit 0,1) e2
		on d.DEPTNO = e2.deptnos;

8、取得比普通员工的最高薪水还要高的领导人姓名

第一步：获取领导的编号

select distinct mgr from emp where mgr is not null

第二步：获取普通员工的最高薪水（普通员工的empno没在上面的结果集里）

select max(sal) from emp where emp.empno not in
 (select distinct mgr from emp where mgr is not null)

第三步：查询领导们薪水比第二步的结果高的领导信息

select emp.ename,emp.sal from emp 
    where sal> (select max(sal) from emp where emp.empno not in
        (select distinct mgr from emp where mgr is not null));

下面是我自己写的，写完的一瞬间忘了自己为什么这么写了，逻辑想不通了

select distinct emp.ename,emp.sal from emp 
    inner join 
        (select empno,ename,sal,mgr from emp) e
        on emp.empno = e.mgr

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

select * from emp order by sal desc limit 0,5;

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

select * from emp order by sal desc limit 5,5;

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

select * from emp order by hiredate desc  limit 0,5;

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

第一步：查出每个员工的薪水等级

select s.GRADE grade from emp 
		inner join salgrade s
		on emp.SAL between s.LOSAL and s.HISAL

第二步：把上述表作为临时表查询出每个级别的数量

select e.grade, count(e.grade) from 
	(select s.GRADE grade from emp 
		inner join salgrade s
		on emp.SAL between s.LOSAL and s.HISAL) e
group by e.grade;

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

select e1.ENAME,e2.ENAME
	from emp e1 
	left join emp e2
	on e1.MGR = e2.EMPNO

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

第一步：emp自连接查询，查询受雇日期早于其直接上级的所有员工

select e.EMPNO,e.ENAME,d.DNAME from emp e 
	inner join emp e2 
		on e.MGR = e2.EMPNO 
	where e.HIREDATE < e2.HIREDATE ;

第二步：连接dept，查询部门名称

select e.EMPNO,e.ENAME,d.DNAME from emp e 
	inner join emp e2 
		on e.MGR = e2.EMPNO 
	inner join dept d 
		on e.DEPTNO = d.DEPTNO 
	where e.HIREDATE < e2.HIREDATE ;

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

select dept.DNAME,emp.*
	from dept 
	left join emp 
	on emp.DEPTNO = dept.DEPTNO

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

第一步：查询出每个部门的人数

select DEPTNO,count(emp.EMPNO) count from emp group by DEPTNO

第二步：把上述表作为临时表连接dept表，获取部门名称，并确定人数>=5

select dept.DEPTNO,dept.DNAME,e.count
	from dept inner join 
	(select DEPTNO,count(emp.EMPNO) count from emp group by DEPTNO) e
	on dept.DEPTNO = e.DEPTNO
where e.count >= 5;

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

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');

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

第一步：获取到办事员的员工信息

select ename,job,DEPTNO from emp where job = 'CLERK'

第二步：把上述表作为临时表连接dept表，查询部门名称

select e.ename,dept.DNAME
	from (select ename,job,DEPTNO from emp where job = 'CLERK') e
	inner join dept 
	on e.DEPTNO = dept.DEPTNO

第三步：把上述表作为临时表连接emp表，查询出该部门下的员工数量

select e.ename,count(emp.DEPTNO),dept.DNAME
	from (select ename,job,DEPTNO from emp where job = 'CLERK') e
	inner join dept 
	on e.DEPTNO = dept.DEPTNO
	inner join emp 
	on e.DEPTNO = emp.DEPTNO
	group by dept.DNAME,e.ename;

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值。

select job,count(job) from emp group by job having min(sal) > 1500;

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

select emp.ENAME from emp where emp.DEPTNO = (select dept.DEPTNO from dept where dept.DNAME = 'SALES');

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

第一步：查询出薪水>平均工资的员工

select emp.MGR emgr,emp.ENAME name,emp.DEPTNO dno,emp.SAL salary
from emp where  emp.sal > (select avg(emp.sal) from emp)

第二步：把上述表作为临时表连接emp表，查询出他们的上司

select emp.ENAME 'leader',e.*
from 
    (select emp.MGR emgr,emp.ENAME name,emp.DEPTNO dno,emp.SAL salary
        from emp where  emp.sal > (select avg(emp.sal) from emp)) e 
left join 
    emp
    on emp.EMPNO = e.emgr

第三步：把上述表作为临时表连接salgrade，查询出薪水等级

select emp.ENAME 'leader',e.*,s.GRADE '级别'
from 
	(select emp.MGR emgr,emp.ENAME name,emp.DEPTNO dno,emp.SAL salary
		from emp where  emp.sal > (select avg(emp.sal) from emp)) e 
left join 
emp
on emp.EMPNO = e.emgr
inner join salgrade s
on e.salary between s.LOSAL and s.HISAL

第四步：把上述表作为临时表连接dept，查询部门名称

select emp.ENAME 'leader',e.*,s.GRADE '级别',dept.DNAME '部门名称'
from 
	(select emp.MGR emgr,emp.ENAME name,emp.DEPTNO dno,emp.SAL salary
		from emp where  emp.sal > (select avg(emp.sal) from emp)) e 
left join 
emp
    on emp.EMPNO = e.emgr
inner join salgrade s
    on e.salary between s.LOSAL and s.HISAL
inner join dept 
    on dept.DEPTNO = e.dno;

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

select emp.ENAME,emp.JOB,dept.DNAME from emp
inner join dept 
on emp.DEPTNO = dept.DEPTNO
where job = (select job from emp where ENAME='SCOTT');

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

select ename,emp.sal from emp
where emp.sal in (select distinct sal from emp where deptno = 30)
and emp.DEPTNO not in (30);

想起啥就学啥

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL8.0做老杜34道作业题。有的未必是按照他的思路，未完结。暂停，学新课，回头接着补，毕竟也要复习

老杜带你学_mysql入门基础（mysql基础视频+数据库实战)_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Vy4y1z7EX?p=132&spm_id_from=pageDriver
复制链接

扫一扫