0
0

[mysql]子查询覆盖范围很广的练习题

答案如下

1查询和Zlotkey相同部门的员工姓名和工资

这里建议使用IN

SELECT last_name,salary FROM employees

WHERE department_id IN (SELECT department_id FROM

                employees

                WHERE last_name='Zlotkey')

2查询工资比公司平均工资高的员工的员工号，姓名和工资

SELECT department_id,last_name,salary

FROM employees

WHERE salary>(SELECT AVG(salary)

          FROM employees)

#3..选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name, job_id, salary FROM employees

WHERE salary>ALL(SELECT salary FROM employees

WHERE job_id='SA_MAN')

#这里因为是使用大于所有,所以我们使用ALL关键词,而不是ANY关键词,因为ANY只要大于其中一个就可以.这里使用的是从外往里写的方法.

#4查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name FROM employees

WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE '%u%')

#这里要注意部门是多个,所以我们要使用IN,

#5.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id FROM employees

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id=1700)

#这里2个点要注意,因为只出现在两个表里的字段,所以只需要一次多表查询,而如果出现了3个表的字段,就需要2次子查询,或者2次多表连接.并且记得用IN

#6查询管理者是King的员工姓名和工资

SELECT last_name,salary FROM employees

WHERE manager_id=(SELECT manager_id FROM employees

              WHERE last_name='king')

#这里自连接也可以完成,并且效率比子查询高.

#7查询工资最低的员工信息: last_name, salary

SELECT last_name, salary FROM employees

WHERE salary=(SELECT MIN(salary) FROM employees)

#只是为了引出下面的题目的题目

#8查询平均工资最低的部门信息

#方法1用三层子查询

SELECT * FROM departments WHERE department_id=(

SELECT department_id FROM employees GROUP BY department_id

HAVING AVG(salary)=(SELECT MIN(A_S) FROM (SELECT AVG(salary) A_S FROM employees GROUP BY department_id) ls))

#方法2使用ALL

SELECT department_id, AVG(salary) FROM employees

GROUP BY department_id

HAVING AVG(salary)<=ALL(SELECT AVG(salary) A_S FROM employees GROUP BY department_id)

#方法3使用ORDER BY和LIMIT

SELECT *

FROM departments

WHERE department_id = (

        SELECT department_id

        FROM employees

        GROUP BY department_id

        HAVING AVG(salary) = (

                     SELECT AVG(salary) avg_sal

                     FROM employees

                     GROUP BY department_id

                     ORDER BY avg_sal

                     LIMIT 0,1

                    )          

        )

#方式四：使用多表连接

SELECT T1.* FROM departments T1 JOIN

(SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id) T2

ON T1.department_id=T2.department_id

ORDER BY avg_sal

LIMIT 0,1

#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT *,(SELECT AVG(salary) FROM employees e WHERE e.department_id=a.department_id ) A_G FROM departments a

WHERE department_id=(SELECT department_id FROM employees

GROUP BY department_id

HAVING AVG(salary)<=ALL(SELECT AVG(salary) FROM employees GROUP BY department_id))

#这题和上面的题目一样,只不过多个一列平均工资,要记得表名.*代表的就是查询全部字段

#10.查询平均工资最高的 job 信息

SELECT * FROM jobs

WHERE job_id=(SELECT job_id FROM employees GROUP BY job_id HAVING AVG(salary)>=ALL(SELECT AVG(salary) FROM employees GROUP BY job_id))

#方法2,不用ALL,使用ALL里面的表进行嵌套,查询这个字段的MAX平均工资.方法3,查询平均值表,对这个表进行降序排序,选择最大的平均工资.之后分流要么继续用子查询,要么用多表查询

#11.查询平均工资高于公司平均工资的部门有哪些

SELECT * FROM departments WHERE department_id IN

(SELECT department_id FROM employees GROUP BY department_id

HAVING AVG(salary)>(SELECT AVG(salary) FROM employees))

#这题因为是只要高于公司平均工资就可以,所以我们的最后一层子查询比较简单

#12.查询出公司中所有 manager 的详细信息

SELECT * FROM employees e

WHERE EXISTS(SELECT * FROM employees e2 WHERE e.employee_id=e2.manager_id)

#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

SELECT MAX(salary) M_s FROM employees

GROUP BY department_id

ORDER BY M_s

LIMIT 0,1

#这题不得不吐槽一下mysql没有Oracle好用,Oracle可以直接嵌套聚合函数),这里可以用临时表,也可以用LIMIT.或者多表连接查询

#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name, department_id, email, salary FROM employees

WHERE department_id IN (SELECT manager_id FROM employees

WHERE department_id=(SELECT department_id FROM employees

GROUP BY department_id

HAVING AVG(salary)=(SELECT AVG(salary) A_s FROM employees

GROUP BY department_id

ORDER BY A_s

LIMIT 0,1)))

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

SELECT department_id FROM departments

WHERE department_id NOT IN (SELECT department_id FROM employees

WHERE job_id <>'ST_CLERK' AND department_id IS NOT NULL)

#这里要记得NOT IN中不能出现子查询的空值,因为空值是未知的,如果出现未知,未知包含一切,不包含未知,就是未知.什么玄学.IN是可以出现NULL.

#16. 选择所有没有管理者的员工的last_name

SELECT last_name FROM employees

WHERE manager_id IS NULL

#注意这里是要用子查询

SELECT last_name

FROM employees e1

WHERE NOT EXISTS (

SELECT *

FROM employees e2

WHERE e1.manager_id = e2.employee_id

);

#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan

SELECT employee_id,last_name, department_id, hire_date, salary FROM employees

WHERE manager_id=(SELECT employee_id FROM employees WHERE last_name='DE Haan')

#注意这里就是普通的子查询咯,主要考察的是你的脑壳对自连接的思路,也就是说这个人的管理者名字查出管理者的id,然后这个人的管理者id等于管理者id.我们再写一个EXISTS,注意EXISTS可以有多个条件用AND连接

SELECT employee_id,last_name, department_id, hire_date, salary FROM employees T1

WHERE EXISTS(SELECT * FROM employees T2 WHERE T2.last_name='DE Haan'AND T1.manager_id=T2.employee_id)

18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资(难)

SELECT employee_id,last_name, salary

FROM employees T1

WHERE salary>

(SELECT AVG(salary) FROM employees T2

WHERE T1.department_id=T2.department_id

GROUP BY T2.department_id)

#这题标了一个难,我一会去看看为什么标难,还有方法2,那就是把每一行都加上部门的平均工资,然后直接自己和自己对比

SELECT employee_id,last_name, salary

FROM employees T1 JOIN

(SELECT department_id,AVG(salary) A_s FROM employees

WHERE department_id

GROUP BY department_id) table1 ON

T1.department_id=TABLE1.department_id

WHERE salary>A_s

#19.查询每个部门下的部门人数大于 5 的部门名称

SELECT department_name FROM departments T1

WHERE EXISTS ( SELECT 1 FROM employees T2

WHERE T1.department_id=T2.department_id

GROUP BY T2.department_id

HAVING COUNT(department_id)>5)

#脑子抽了一直想用EXISTS.不过也可行就是,每次都查询这个部门是否有大于5的情况.

SELECT department_name,department_id

FROM departments d

WHERE 5 < (

SELECT COUNT(*)

FROM employees e

WHERE d.`department_id` = e.`department_id`

);

#正解是这个,比较好理解,每次都查询该部门的人数,大于五就返回

#20查询每个国家下的部门个数大于 2 的国家编号

SELECT DISTINCT country_id FROM locations T1

WHERE 2<(SELECT count(*) FROM departments

WHERE location_id in (SELECT location_id FROM locations T2

WHERE T2.country_id=T1.country_id)

)

#我的思路是每个国家下面有多个地址,这些地址如果在部门表里有出现,错误的点是在哪呢,因为我以为每次只会运行一行,运行了第一行,比如第一行运行了,那么第一行的ID只会进去查找一个对应的部门.但是实际上是运行了第一行US,就会找到一个表里全部的部门,如果输入了一个国家比如US,那么这一个表US对应的几个部门id都会被拿去对比,所以我只要直接,令部门id相等就可以了

SELECT country_id

FROM locations l

WHERE 2 < (

SELECT COUNT(*)

FROM departments d

WHERE l.`location_id` = d.`location_id`

);

#这个才是正
标签： sql 数据库
本文转载自: https://blog.csdn.net/qq1021091799qq/article/details/143420213
版权归原作者 晴天qt01 所有， 如有侵权，请联系我们删除。
登录后发布评论

“[mysql]子查询覆盖范围很广的练习题”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

【AiPPT-注册/登录安全分析报告-无验证方式导致安全隐患】

Linux基础操作与安全实践指南

枚举与lambda表达式，枚举实现单例模式为什么是安全的，lambda表达式与函数式接口的小九九~

实用篇:Postman历史版本下载

selenium获取cookie的方法

大三学生实习面试经历（1）

如何使用Docker轻松搭建高颜值无广告音乐播放器SPlayer随时随地听歌

文章导航