[打卡]子

  #单行子查询
	
	#单行子查询的操作符：= ！= > < <=
	
/*
子查询的编写技巧
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
);