[打卡]xgl子查询

#单行子查询
#习题
SELECT salary,employee_id,last_name
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和deparement_id相同的其他员工的employees_id,manager_id和deparement_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,department_id manager_id
from employees
where (manager_id,department_id)=(
SELECT manager_id,department_id
from employees
where employee_id=141
);
#查询最低工资大于110号部门最低工资的部门ID和其最低工资

SELECT MIN(salary),department_id
from employees
where department_id in NOT null
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary)
from employees
where department_id=50);

#非法使用子查询
SELECT employee_id.last_name
from employees
where salary=(
SELECT MIN(salary)
from employees
GROUP BY department_id
);