MySQL-sql99-经典查询+作业讲解

案例：查询工资最低的员工信息：last_name,salary

# 案例：查询工资最低的员工信息：last_name,salary

SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary`=(
	SELECT MIN(`salary`)
	FROM `employees`);

案例：查询平均工资最低的部门信息

# 案例：查询平均工资最低的部门信息
# 一、查询各部门的平均工资
SELECT AVG(`salary`) ag,`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 `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`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`;

# 二、在一的结果上查询平均工资最低的部门编号

SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) ASC
LIMIT 1


# 三、查询部门信息
SELECT d.*
FROM `departments` d
WHERE `department_id`=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(`salary`) ASC
	LIMIT 1
)

同样的效果

案例：查询平均工资最低的部门信息和该部门的平均工资

# 案例：查询平均工资最低的部门信息和该部门的平均工资
# 一、查询各部门的平均工资
SELECT AVG(`salary`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`;

# 二、在一的结果上查询平均工资最低的部门编号

SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) ASC
LIMIT 1

# 三、查询部门信息
SELECT d.*,ag_dep.ag
FROM `departments` d
JOIN (
	SELECT AVG(`salary`) ag,`department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(`salary`) ASC
	LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.`department_id`

上面使用的是连接查询

案例：查询平均工资最高的job信息

SELECT j.*
FROM `jobs` j
JOIN (
	SELECT `job_id`
	FROM `employees`
	GROUP BY `job_id`
	ORDER BY AVG(`salary`) DESC
	LIMIT 1
) ag_dep
ON j.`job_id`=ag_dep.`job_id`

案例：查询平均工资高于公司平均工资的部门有哪些？

SELECT `department_id`,AVG(`salary`) 
FROM `employees`
GROUP BY `department_id`
HAVING AVG(`salary`) >(
	SELECT AVG(`salary`) 
	FROM `employees`
)

案例：查询公司中所有manager的详细信息

SELECT e.*
FROM `employees` e
JOIN (
	SELECT DISTINCT `manager_id`
	FROM `employees`
) mag
ON e.`employee_id`=mag.manager_id

或者：

SELECT e.*
FROM `employees` e
WHERE `employee_id` IN (
	SELECT DISTINCT `manager_id`
	FROM `employees`
)

或者

SELECT e.*
FROM `employees` e
WHERE `employee_id` = ANY(
	SELECT DISTINCT `manager_id`
	FROM `employees`
)

案例：各个部门中 最高工资中最低的那个部门的 最低工资是多少

SELECT MIN(`salary`),`department_id`
FROM `employees`
WHERE`department_id`=(
	SELECT DISTINCT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY MAX(`salary`)
	LIMIT 1
)

案例：查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary

# 案例：查询平均工资最高的部门的manager的详细信息：last_name，department_id，email，salary
# 1
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1

# 2
SELECT manager_id
FROM employees
WHERE department_id=(
	SELECT `department_id`
	FROM `employees`
	GROUP BY `department_id`
	ORDER BY AVG(`salary`) DESC
	LIMIT 1
)

# 3
SELECT last_name,department_id,email,salary
FROM employees
WHERE `employee_id`=ANY(
	SELECT manager_id
	FROM employees
	WHERE department_id=(
		SELECT `department_id`
		FROM `employees`
		GROUP BY `department_id`
		ORDER BY AVG(`salary`) DESC
		LIMIT 1
)
)