[打卡]打卡d

# 单行子查询

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

/*

子查询的编写技巧

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	



# 方式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 (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)
and department_id is not null;

# 非法使用子查询

select employee_id,last_name
from employees
where salary =(
select min(salary)
from employees
GROUP BY department_id
);