[打卡]jc子查询

#单行子查询

#单行子查询操作符：= ！= <> > >= < <=

#习题：查询工资大于149号员工工资的员工信息

SELECT last_name,employee_id,salary
from employees
WHERE salary>(
SELECT salary
FROM employees
WHERE employee_id=149
);

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

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
SELECT job_id
from employees
WHERE employee_id=141
)
AND salary>(
SELECT salary
FROM employees
WHERE employee_id=143
);

/*
子查询编写技巧 （步骤）
1.从里往外写
2.从外往里写
*/
#中英语互换

#习题：返回公司工资最少的last_name,job_id和salary

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

# 习题：查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id和department_id.
#方式1
SELECT employee_id,manager_id,department_id
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE employee_id=141
)
and manager_id=(
SELECT manager_id
FROM employees
WHERE employee_id=141
)
and employee_id != 141;

#方式2
SELECT employee_id,manager_id,department_id
FROM employees
WHERE (department_id,manager_id)=(
SELECT department_id,manager_id
FROM employees
WHERE employee_id = 141

)
AND employee_id !=141;

#习题：查询最低工资大与110号部门最低工资的部门id和其最低工资

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