[打卡]子循环和习题

#单行子查询

#单行操作符 = =! <> > >= < <= 不等于

#习题：查询工资大于149号员工工资的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >(
SELECT salary
FROM employees
WHERE department_id=149
);

SELECT salary
FROM employees
WHERE department_id=149

#习题：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT job_id,last_name,salary
FROM employees
WHERE job_id =(
SELECT job_id
FROM employees
WHERE employee_id)=141;

SELECT job_id
FROM employees
WHERE employee_id=141

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = ()

#中英互换
#返回公司员工工资最少的last_name,job_id
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees;
)
SELECT MIN(salary)
FROM employees;

#子查询编写技巧（步骤）
#1 从里往外写
#2 从外往里写


/*习题，查询与141号员工的manager_id和departent_id
相同的其他员工的employee_id,manger_id,department_id
*/
SELECT manager_id,department_id
FROM employees
WHERE job_id =(
SELECT job_id
FROM employees
WHERE employee_id)=141;

SELECT manager_id
FROM employees
WHERE employee_id = 141;

SELECT department_id
FROM employees
WHERE employee_id = 141;


SELECT employee_id,manager_id,department_id
FROM employees
#方式1

#方式2

SELECT employee_id,manager_id,department_id
FROM employees
WHERE (manager_id,department_id) = (
SELECT manager_id,department_id
FROM employees
WHERE employee_id=141)
AND employee_id <> 141;

#查询最低工资大于110号部门最低工资的部门id和其最低工资

SELECT department_id,MIN(salary)
FROM employees
#WHERE department_id
GROUP BY department_id
HAVING MIN(salary) >(
SELECT MIN(salary)
FROM employees
WHERE department_id=110
);
#8条
AND department_id is not null;

#非法使用子查询
SELECT employee_id,last_name
FROM employees
WHERE salary = (
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);