bjpowernode34道作业题

最新推荐文章于 2023-12-29 22:35:48 发布

chaozhouchao

最新推荐文章于 2023-12-29 22:35:48 发布

阅读量230

点赞数 1

文章标签：数据库 sql 后端

本文链接：https://blog.csdn.net/zc655545/article/details/127733656

版权

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

select t.*,emp.ename
	 from (select max(sal) as maxsal,deptno from emp group by deptno) t
	 join emp 
	on emp.sal=t.maxsal;

2.哪些人的薪水在部门的平均薪水之上
先计算部门的平均薪水
再将部门中各个员工的薪水与平均薪水进行比较，
输出大于部门平均薪水的员工

select emp.ename,emp.sal from emp
	 join (select avg(sal) as avgsal,deptno from emp group by deptno) t 
	on emp.sal>t.avgsal 
	and emp.deptno=t.deptno;

(表连接条件有两个)

3.取得部门中（所有人的）平均的薪水等级
可以先取得每个人的薪水等级
再按部门进行分组，求得部门平均薪水等级

SELECT AVG(grade),DEPTNO FROM
	(select e.deptno,e.sal,s.grade from emp e 
	join salgrade s on e.sal between s.losal and s.hisal) t
	GROUP BY deptno
	ORDER BY DEptno

;

4.不准用组函数（Max），取得最高薪水（给出两种解决方案）
（1）
先找出每个人的薪资等级，然后取薪资等级最高的那个人

select sal
	 from (select sal,grade from emp join salgrade on sal between losal and hisal) e 
	 where e.grade=5;

（2）
将员工工资按照大小进行降序排序，取第一个人的工资

SELECT sal 
	FROM emp
	ORDER BY SAL DESC
	LIMIT 1;

5.取得平均薪水最高的部门的部门编号（至少给出两种解决方案）
（1）
先计算各个部门的平均薪水
按照平均薪水降序排序
用limit关键字取第一行的部门号即为平均薪水最高的部门号

SELECT deptno 
	from emp 
	GROUP BY deptno 
	ORDER BY AVG(SAL) desc
	LIMIT 1

（2）

SELECT t.deptno
	FROM
	(SELECT e.deptno,AVG(e.SAL) as avg_sal
	from emp e
	GROUP BY deptno) t
	WHERE t.avg_sal=
			(SELECT MAX(avg_sal)
			FROM
			(SELECT e.deptno,AVG(e.SAL) as avg_sal
			from emp e
			GROUP BY deptno) t )

6.取得平均薪水最高的部门的部门名称
按照题5的结果进一步查询
先取得各个部门的平均薪水
再取得平均薪水最高的部门号
最后表连接的得到部门名称

SELECT d.dname
	FROM
	(select	t.deptno
	FROM
	(SELECT avg(sal) as avgsal,deptno
	FROM	emp
	GROUP BY DEPTNO
	order by avgsal desc) t
	LIMIT 1) s
	JOIN dept d
	on s.deptno=d.DEPTNO

7.求平均薪水的等级最低的部门的部门名称
先求每个部门的平均薪水
再求每个部门的薪水等级
最后取薪水等级最低得部门名称

 SELECT l.DNAME FROM		
		(
		//查询结果是每个部门的部门号，部门名称，平均薪资等级以及平均薪资
		SELECT t.*,s.grade,d.DNAME
				FROM
				(SELECT avg(sal) as deptno_avgsal,DEPTNO
				FROM emp
				GROUP BY DEPTNO) t
				JOIN salgrade s
				ON t.deptno_avgsal BETWEEN s.LOSAL and s.HISAL
				JOIN dept d
				ON t.DEPTNO=d.DEPTNO) l
		WHERE l.grade=
				//得到的是最低的平均薪资等级
				(SELECT MIN(m.grade)
				from
				(SELECT t.*,s.grade,d.DNAME
				FROM
				(SELECT avg(sal) as deptno_avgsal,DEPTNO
				FROM emp
				GROUP BY DEPTNO) t
				JOIN salgrade s
				ON t.deptno_avgsal BETWEEN s.LOSAL and s.HISAL
				JOIN dept d
				ON t.DEPTNO=d.DEPTNO) m)

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

SELECT DISTINCT e2.ename,e2.SAL
	FROM 
	emp e1
	JOIN emp e2
	ON e1.MGR=e2.EMPNO
	WHERE e2.SAL>
			(SELECT MAX(SAL)
			FROM 
			emp e
			WHERE e.EMPNO NOT IN
					(SELECT DISTINCT MGR 
					FROM emp
					WHERE MGR IS NOT NULL))

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

SELECT e.ename,e.sal
	FROM
	emp e
	ORDER BY
	SAL DESC
	LIMIT 0,5

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

SELECT e.ename,e.sal
	FROM
	emp e
	ORDER BY
	SAL DESC
	LIMIT 5,5

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

SELECT ENAME,HIREDATE 
	FROM
	emp
	ORDER BY
	HIREDATE DESC
	LIMIT 5

12.取得每个薪水等级有多少员工
先算出每个员工的薪水等级
再取每个薪水等级的数量

SELECT grade,count(grade)
	FROM
	(SELECT e.ename,e.sal,s.grade
	from emp e
	JOIN salgrade s
	ON e.sal BETWEEN s.LOSAL and s.HISAL) t
	GROUP BY
	grade
	ORDER BY
	grade

（1）找出没选过“黎明”老师的所有学生姓名

SELECT s.SNAME
	FROM s
	WHERE SNAME NOT IN
			(SELECT DISTINCT s.SNAME
			FROM s
			LEFT JOIN sc
			ON s.SNO=sc.SNO
			LEFT JOIN c
			ON sc.CNO=c.CNO
			WHERE c.CTEACHER='黎明')

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

SELECT t2.SNAME,t2.avg_grade
	FROM 
			(SELECT s.SNAME,s.SNO,AVG(sc.SCGRADE) as avg_grade
			FROM s
			JOIN sc
			ON s.SNO=sc.SNO
			GROUP BY
			s.SNO) t2
	JOIN
			(SELECT s.SNO,s.SNAME
			FROM s
			JOIN sc
			ON s.SNO=sc.SNO
			AND sc.SCGRADE<60
			GROUP BY
			s.SNO
			HAVING  count(s.SNAME)>2) t1
	ON t1.SNO=t2.SNO

（3）既学过 1 号课程又学过 2 号课所有学生的姓名

SELECT s.SNAME
	FROM s
	JOIN sc
	ON s.SNO=sc.SNO
	WHERE sc.CNO=1
	AND sc.SNO IN
			(SELECT SNO
				FROM sc
				WHERE CNO=2)

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

SELECT e1.ename,e2.ename as leader
	FROM
	emp e1
	LEFT JOIN emp e2
	on e1.MGR=e2.EMPNO

15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
先找出所有员工的编号，姓名，部门名称及其直接上级的受雇日期，
再与部门表进行表连接得到部门名称
最后选取受雇日期早于其直接上级员工信息

SELECT t.EMPNO,t.ename,d.dname
	FROM
	(SELECT e1.EMPNO,e1.ename,e1.HIREDATE,e1.DEPTNO,e2.ename as leader,e2.HIREDATE as leader_hiredata
	FROM
	emp e1
	LEFT JOIN emp e2
	on e1.MGR=e2.EMPNO) t
	JOIN dept d
	ON t.DEPTNO=d.deptno
	WHERE
	t.HIREDATE<t.leader_hiredata

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

SELECT e.*,d.DNAME FROM emp e
	RIGHT JOIN dept d
	on e.DEPTNO=d.DEPTNO

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

SELECT count(e.deptno) as deptno_count,d.dname
	FROM
	emp e
	JOIN dept d
	ON e.DEPTNO=d.deptno
	GROUP BY
	e.DEPTNO
	HAVING deptno_count>=5

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

SELECT *
	FROM
	emp
	WHERE SAL>
	(SELECT sal
	FROM emp
	where ename="smith")

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

SELECT e.ename,d.dname,t.deptno_count 
	FROM
	emp e
	JOIN dept d
	ON e.DEPTNO=d.deptno
	JOIN 
				(SELECT count(emp.deptno) as deptno_count,deptno
				FROM emp
				GROUP BY DEPTNO) t
	ON e.DEPTNO=t.deptno
	where job="clerk"

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

SELECT JOB,count(job)
	FROM
	emp
	where sal>1500
	GROUP BY
	JOB

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

SELECT e.ENAME
	FROM emp e
	JOIN dept d
	ON e.DEPTNO=d.DEPTNO
	WHERE d.DNAME='sales'

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

SELECT e1.ENAME,d.DNAME,e2.ENAME as leader,s.GRADE
	FROM emp e1
	JOIN dept d
	ON e1.DEPTNO=d.DEPTNO
	left JOIN emp e2
	ON e1.MGR=e2.EMPNO
	JOIN salgrade s
	ON e1.sal BETWEEN s.LOSAL and s.HISAL
	WHERE e1.sal>
					(select avg(sal)
						FROM
						emp)

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

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"

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

SELECT e.* FROM
	emp e
	where SAL IN
	(SELECT SAL
	FROM 
	emp
	where DEPTNO=30)
	AND e.DEPTNO<>30

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

SELECT e.ename,e.sal,d.DNAME
	from
	emp e
	JOIN dept d
	on e.DEPTNO=d.deptno
	where sal>
	(SELECT max(sal)
	FROM 
	emp
	where DEPTNO=30)

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

SELECT count(e.EMPNO) as emp_number,avg(e.sal),avg(2022-YEAR(HIREDATE)+1) as avg_hire_year,d.DNAME 
	FROM
	emp e
	JOIN dept d
	ON e.DEPTNO=d.DEPTNO
	GROUP BY e.DEPTNO

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

SELECT e.ename,e.sal,d.dname
	FROM 
	emp e
	JOIN dept d
	ON e.DEPTNO=d.DEPTNO

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

SELECT t.emp_num,d.DNAME,d.LOC,d.DEPTNO
	FROM dept d
	LEFT JOIN
			(SELECT e.DEPTNO,COUNT(e.DEPTNO) as emp_num
			FROM emp e
			GROUP BY DEPTNO) t
	ON d.DEPTNO=t.DEPTNO

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

SELECT *
	FROM
	emp e
	where e.SAL IN
			(SELECT MIN(SAL)
			FROM
			emp
			GROUP BY JOB)

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

SELECT e2.DEPTNO,MIN(e2.SAL) 
	FROM emp e1
	JOIN emp e2
	ON e1.MGR=e2.EMPNO
	GROUP BY e2.DEPTNO

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

SELECT e.ename,e.sal*12 as income
	FROM emp e
	ORDER BY income

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

SELECT  e1.ename,e2.ename as leader
	FROM emp e1
	JOIN emp e2
	ON e1.MGR=e2.EMPNO
	WHERE e2.sal>3000

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

SELECT d.DNAME,COUNT(e.DEPTNO),SUM(e.SAL)
	FROM 
	emp e
	RIGHT JOIN dept d
	ON e.DEPTNO=d.DEPTNO
	WHERE d.DNAME LIKE '%s%'
	GROUP BY e.DEPTNO

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

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

chaozhouchao

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
5
评论
bjpowernode34道作业题

bjpowernode34道作业题
复制链接

扫一扫

bjpowernode34道作业题

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