6.7、子查询的课后练习
#1.查询和Zlotkey相同部门的员工姓名和工资
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'
);
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT department_id,last_name
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name RLIKE 'u'
);
#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id =1700
);
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'King'
);
#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary IN (
SELECT MIN(salary)
FROM employees
);
#8.查询平均工资最低的部门信息
#方式一：四层嵌套
SELECT *
FROM departments
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(avg_sal)
FROM(
SELECT AVG(salary) AS 'avg_sal'
FROM employees
GROUP BY department_id
)t_dept_avg_sal
)
);
#方式二：四层优化变为三层
SELECT *
FROM departments
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
)
);
#方式三：使用LIMIT进行优化
SELECT *
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT AVG(salary) AS 'AS_SAL'
FROM employees
GROUP BY department_id
ORDER BY AS_SAL
LIMIT 1
)
);
#方式四：在FROM中声明子查询，再次进行优化:
SELECT D.*
FROM departments D,(
SELECT department_id,AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal
LIMIT 1
)d_DEP_avg_sal
WHERE D.department_id = d_DEP_avg_sal.department_id;
#由此得出结论：根据实际场景去调整，再次优化，当然这个方法不算严谨，仅扩展思路
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT D.*,(SELECT AVG(salary)FROM employees WHERE D.department_id = employees.department_id)
FROM departments D
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) =(
SELECT MIN(AVG_SAL)
FROM(
SELECT AVG(salary) AS 'AVG_SAL'
FROM employees
GROUP BY department_id
)T_TEP_AVG_SAL
)
);
#10.查询平均工资最高的 job 信息
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) >=ALL (
SELECT AVG(salary) AS 'AVG_SAL'
FROM employees
GROUP BY job_id
)
);
#11.查询部门平均工资高于公司平均工资的部门有哪些?
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) >(
SELECT AVG(salary)
FROM employees
);
#12.查询出公司中所有 manager 的详细信息
#方式一：使用自连接
SELECT DISTINCT G.last_name,G.employee_id,G.last_name,G.employee_id
FROM employees E JOIN employees G
ON E.manager_id = G.employee_id;
#方式二：使用子查询
SELECT *
FROM employees
WHERE employee_id IN(
SELECT DISTINCT manager_id
FROM employees
);
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MIN(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) = (
SELECT MIN(MAX_SAL)
FROM(
SELECT MAX(salary) AS 'MAX_SAL'
FROM employees
GROUP BY department_id
)T_TMP_MAX_SAL
)
);
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT *
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MAX(DA)
FROM(
SELECT AVG(salary) AS 'DA'
FROM employees
GROUP BY department_id
)FAC
)
)
);
#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT DISTINCT department_id
FROM employees
WHERE department_id NOT IN(
SELECT department_id
FROM employees
WHERE job_id = 'ST_CLERK'
);
#16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees
WHERE manager_id IS NULL;
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id IN (
SELECT employee_id
FROM employees
WHERE last_name = 'De Haan'
);
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
SELECT last_name,department_id,salary
FROM employees E1
WHERE salary> (
SELECT AVG(salary)
FROM employees E2
WHERE E2.department_id = E1.department_id
);
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_name
FROM departments D
WHERE 5 < (
SELECT COUNT(*)
FROM employees E
WHERE D.department_id = E.department_id
);
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT country_id
FROM locations L
WHERE 2<(
SELECT COUNT(*)
FROM departments D
WHERE L.location_id = D.location_id
);
7、增删改的基本语法
7.1、增Insert
(特殊的如果id自动递增的话，就不需要插入id)
基本语法 INSERR INTO 表名
(列1，列2，列3，列4，...)
values(值，值，值)
例子INSERT INTO
student(name,sex,age)
values('张三'，18，'男');
7.2、删delete
基本语法 ：DELETE FROM 表名 WHERE 列 = 值
例子：
DELETE FROM student
WHERE id = 1；
DELETE from 表名 一行行删除整张表
TRUNCATE table 表名 就是清空表
7.3、改update
基本语法 UPDATE 表名 SET 列=值，列=值，.... WHERE...
列子 UPDATE student
SET name = '张三'
where id=1;
