【SQL查询系列】子查询经典案例

文章目录

- 前言
- - 数据库
  - 查询案例
  - - 1. 查询工资最低的员工信息: last_name, salary
    - 2. 查询平均工资最低的部门信息
    - 3. 查询平均工资最低的部门信息和该部门的平均工资
    - 4. 查询平均工资最高的 job 信息
    - 5. 查询平均工资高于公司平均工资的部门有哪些?
    - 6. 查询出公司中所有 manager 的详细信息.
    - 7. 各个部门中最高工资中最低的那个部门的最低工资是多少
    - 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
  - student数据库
  - 查询练习
  - - 一、查询每个专业的学生人数 SELECT majorid,COUNT(*)
    - 二、查询参加考试的学生中，每个学生的平均分、最高分
    - 三、查询姓张的每个学生的最低分大于60的学号、姓名
    - 四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
    - 五、查询每个专业的男生人数和女生人数分别是多少
    - 六、查询专业和张翠山一样的学生的最低分
    - 七、查询大于60分的学生的姓名、密码、专业名
    - 八、按邮箱位数分组，查询每组的学生个数
    - 九、查询学生名、专业名、分数
    - 十、查询哪个专业没有学生，分别用左连接和右连接实现
    - 十一、查询没有成绩的学生人数
  - 【学习资料】
  - - 【01学习笔记】
    - 【02课程资料】

前言

近期在补SQL的基础，特别整理了一下SQL的一些查询练习语句，边学边笔记，持续更新~

数据库

查询案例

1. 查询工资最低的员工信息: last_name, salary

#①查询最低的工资
SELECT MIN(salary)
FROM employees

#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

2. 查询平均工资最低的部门信息

#方式一：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep

#③查询哪个部门的平均工资=②

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep

);

#④查询部门信息

SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep

	)

);

#方式二：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

3. 查询平均工资最低的部门信息和该部门的平均工资

#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;
#③查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;

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

#①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1
#②查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

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

#①查询平均工资
SELECT AVG(salary)
FROM employees

#②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#③筛选②结果集，满足平均工资>①
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

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

#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

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

#①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1

#②查询①结果的那个部门的最低工资
SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

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

#①查询平均工资最高的部门编号
SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

#②将employees和departments连接查询，筛选条件是①
    SELECT 
        last_name, d.department_id, email, salary 
    FROM
        employees e 
        INNER JOIN departments d 
            ON d.manager_id = e.employee_id 
    WHERE d.department_id = 
        (SELECT 
            department_id 
        FROM
            employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1) ;

student数据库

查询练习

一、查询每个专业的学生人数 SELECT majorid,COUNT(*)

FROM student
GROUP BY majorid;

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT s.studentno,s.`studentname`,MIN(score)
FROM student s
JOIN result r
ON s.`studentno`=r.`studentno`
WHERE s.`studentname` LIKE '张%'
GROUP BY s.`studentno`
HAVING MIN(score)>60;

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

SELECT m.`majorname`,s.`studentname`
FROM student s
JOIN major m
ON m.`majorid`=s.`majorid`
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.`majorid`;

五、查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

六、查询专业和张翠山一样的学生的最低分

#①查询张翠山的专业编号
SELECT majorid
FROM student
WHERE studentname = '张翠山'

#②查询编号=①的所有学生编号
SELECT studentno
FROM student
WHERE majorid=(
	SELECT majorid
	FROM student
	WHERE studentname = '张翠山'

)
#②查询最低分
SELECT MIN(score)
FROM result
WHERE studentno IN(

	SELECT studentno
	FROM student
	WHERE majorid=(
		SELECT majorid
		FROM student
		WHERE studentname = '张翠山'

	)
)

七、查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

八、按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

九、查询学生名、专业名、分数

SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
LEFT JOIN result r ON s.studentno=r.studentno

十、查询哪个专业没有学生，分别用左连接和右连接实现

#左
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM major m
LEFT JOIN student s ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

#右
SELECT m.`majorid`,m.`majorname`,s.`studentno`
FROM student s
RIGHT JOIN  major m ON m.`majorid` = s.`majorid`
WHERE s.`studentno` IS NULL;

十一、查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.`studentno` = r.`studentno`
WHERE r.`id` IS NULL

【学习资料】

【01学习笔记】

【SQL数据库基础01】数据库和SQL概述
【SQL数据库基础02】MySQL最新版安装使用教程–（安装、卸载、登录、常用命令、语法规范、图形化界面SQLyog）
【SQL数据库基础03】数据处理之查询 (•过滤和排序数据 •分组函数 •分组查询 •多表查询 •分页查询 •联合查询)
【SQL查询系列】子查询经典案例
【SQL数据库基础04】数据操纵语言DML，即SQL的增删改查
【SQL数据库基础05】数据定义语言DDL-库表的管理、数据类型、常见的约束
【SQL数据库基础05-1】常见的数据类型–• 数值类型• 字符类型• 日期类型
【SQL数据库基础05-2】常见约束与标识列
【SQL数据库基础06】事务控制语言TCL–事务（概念、特性、隔离级别、案例演示）
【SQL数据库基础07】视图–定义、增删改查视图
【SQL数据库基础08】变量–系统变量：全局、会话、自定义变量
【SQL数据库基础09】存储过程
【SQL数据库基础10】函数
【SQL数据库基础11】流程控制结构

【02课程资料】

《数据库系统概论（第5版）》
计院学网盘复习资料–数据库
尚硅谷MySQL核心技术-婷姐

更多推荐

【SQL查询系列】子查询经典案例

【SQL查询系列】子查询经典案例

文章目录

前言

数据库

查询案例

1. 查询工资最低的员工信息: last_name, salary

2. 查询平均工资最低的部门信息

3. 查询平均工资最低的部门信息和该部门的平均工资

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

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

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

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

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

student数据库

查询练习

一、查询每个专业的学生人数 SELECT majorid,COUNT(*)

二、查询参加考试的学生中，每个学生的平均分、最高分

三、查询姓张的每个学生的最低分大于60的学号、姓名

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

五、查询每个专业的男生人数和女生人数分别是多少

六、查询专业和张翠山一样的学生的最低分

七、查询大于60分的学生的姓名、密码、专业名

八、按邮箱位数分组，查询每组的学生个数

九、查询学生名、专业名、分数

十、查询哪个专业没有学生，分别用左连接和右连接实现

十一、查询没有成绩的学生人数

【学习资料】

【01学习笔记】

【02课程资料】

发布评论取消回复

最近发表

热门文章

标签列表

【SQL查询系列】子查询经典案例

文章目录

前言

数据库

查询案例

1. 查询工资最低的员工信息: last_name, salary

2. 查询平均工资最低的部门信息

3. 查询平均工资最低的部门信息和该部门的平均工资

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

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

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

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

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

student数据库

查询练习

一、查询每个专业的学生人数 SELECT majorid,COUNT(*)

二、查询参加考试的学生中，每个学生的平均分、最高分

三、查询姓张的每个学生的最低分大于60的学号、姓名

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

五、查询每个专业的男生人数和女生人数分别是多少

六、查询专业和张翠山一样的学生的最低分

七、查询大于60分的学生的姓名、密码、专业名

八、按邮箱位数分组，查询每组的学生个数

九、查询学生名、专业名、分数

十、查询哪个专业没有学生，分别用左连接和右连接实现

十一、查询没有成绩的学生人数

【学习资料】

【01学习笔记】

【02课程资料】

相关文章

发布评论取消回复

最近发表

热门文章

标签列表

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