NNNNNNN

#单行子查询

#单行子查询的操作符；= != > < <= >= 

/*
 子查询编写技巧
 1 从里往外写
 
 2 从外往里写
*/

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

SELECT employee_id,last_name,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
							);
						
						
#返回公司工资最少的员工的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

#方式一
SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id =(
										SELECT manager_id
										FROM employees
										WHERE employee_id = 141
										)
AND department_id =(
										SELECT department_id
										FROM employees
										WHERE employee_id = 141
										)
AND employee_id != 141;										

#!= 与 <> 都是不等于

#方式二

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
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
								);