[打卡]ns

#返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 
select e1.last_name,e1.salary,e1.employee_id
from employees e1,employees e2
where e1.salary>e2.salary
and e2.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 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;
#备注：<>可看成不等号!=。

#方式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
);
#非法使用子查询
select employee_id,last_name
from employees
where salary=(
select min(salary)
from employees
group by department_id
);