子查询经典案例讲解

#1.查询工资最低的员工信息：`last_name`，`salary`
#(1)查询工资最低的工资
SELECT MIN(`salary`)
FROM  `employees`;
#查询满足(1)的员工信息
SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary`=(
SELECT MIN(`salary`)
FROM  `employees`
);
#2.查询平均工资最低的部门信息
#(1)查询各部门平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;
 #(2)查询（1）结果上的最低平均工资
SELECT MIN(ag) FROM(
SELECT AVG(`salary`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`
)  ag_dep;
 
#(3)查询哪个部门的平均工资=（2）
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
);
#(4)查询部门信息
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
)
);
#(3)查询哪个部门的平均工资=（2）
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
);
#(4)查询部门信息和平均工资
SELECT d.*,AVG(`salary`)
FROM `departments` d,`employees` e
INNER JOIN 
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
)
);

#做法二
#（1）各部门的平均工资
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`;
#(2)求出最低平均工资的部门编号
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1;
#(3)查询部门信息
SELECT *
FROM `departments`
WHERE `department_id`=(
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1
);





#3.查询平均工资最低的部门信息和该部门的平均工资
#(1)查询平均工资按部门编号分组
SELECT  AVG(`salary`)
FROM `employees`
GROUP BY `department_id`;
#(2)求出最低平均工资的部门编号
SELECT AVG(`salary`),`department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`)
LIMIT 1;
 #(3)部门信息
 SELECT d.*,ag
 FROM `departments` d
 INNER 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信息
#(1)查询每个job的平均工资,并查出最高工资
SELECT AVG(`salary`)，job_id
FROM `employees`
GROUP BY `job_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1;
#(2)查询满足条件（1）的job信息
SELECT j.*
FROM `jobs` j
WHERE `job_id`=(
SELECT `job_id`
FROM `employees`
GROUP BY `job_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1
);
#5.查询平均工资高于公司平均工资的部门有哪些
#(1)查询平均工资
SELECT AVG(`salary`)
FROM `employees`;
#(2)查询每个部门的平均工资
SELECT AVG(`salary`)
FROM `employees`
GROUP BY `department_id`;
#(3)筛选结果（2），满足平均工资》（1）
SELECT AVG(`salary`) ag,`department_id`
FROM `employees`
GROUP BY `department_id`
HAVING  ag>(
SELECT AVG(`salary`)
FROM `employees`
);
#6.查询出公司中所有manager的详细信息
#(1)查询所有manager的员工编号
SELECT DISTINCT `manager_id`
FROM `employees`;
#(2)查询详细信息，满足`employee_id`=（1）
SELECT *
FROM `employees`
WHERE `employee_id`IN(
SELECT DISTINCT `manager_id`
FROM `employees`
);

#7.各个部门中，最高工资中最低的那个部门的最低工资是多少
#(1)查询最高工资部门编号
SELECT `department_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY MAX(`salary`)
LIMIT 1;
#(2)查询满足条件（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
#(1)查询平均工资最高的员工编号
SELECT `employee_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1;
#（2）查询满足条件（1）的manager的信息
SELECT `last_name`,`department_id`,`email`,`salary`
FROM `employees`
WHERE `manager_id`=(
SELECT `employee_id`
FROM `employees`
GROUP BY `department_id`
ORDER BY AVG(`salary`) DESC
LIMIT 1
);

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

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