进阶7子查询

含义：

特点：1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询一般搭配着单行操作符使用 >,<>,<=,>=
列子查询：一般搭配着多行操作符使用 ,in any/some,all

标量子查询：

案例一：谁的工资比abel的高？
思路分析：
1.查询abel

select salary
from employees
where last_name ='abel';

2.查询员工的信息，满足salary>1

 select *
 from employees
 where salary>(
  select salary
  from employees
  where last_name ='abel'
  );

案例二：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
1.查询141号员工的job_id

select job_id
from employees
where employee_id=141;

2.查询143号员工的salary

select salary
from employees
where employee_id =143;

3.查询员工姓名，job_id 和工资，并且job_id=1,并且salary>2

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
思路：1.查询公司的最低工资

select min(salary)
from employees

查询last_name,job_id和salary =1

select last _name,job_id,salary
from employees
where salary=(
select min(salary)
from employees);            

案例四：
查询最低工资大于50号部门最低工资的部门Id和最低工资
1.查询50号部门的最低工资

  select min(salary)
  from employees
  where department_id =50

2.查询每个部门的最低工资

select min(salary),departmnet_id
from employees
group by department_id;

3.在2的基础上筛选，满足min(salary)>1

select min (salary),department_id
from employees
group by department_id
having min(salary)>(
select min(salary)
  from employees
  where department_id =50

);

子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果