一、子查询:
1、查询工资比Abel高的员工的姓名、工资:
select last_name,salary
from employees
where salary > ( select salary
from employees
where last_name='Abel')
小括号里的是子查询语句(找出姓名为Abel的员工的工资),先算小括号里的。
2、查询员工名为Chen的manager的信息:
select last_name,salary
from employees
where employee_id = (select manager_id
from employees
where last_name='Chen')
二、单行子查询:
1、返回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
)
2、返回公司工资最少的员工的last_name,job_id和salary。
select last_name,job_id,salary
from employees
where salary=(
select min(salary)
from employees
)
3、如果子查询中涉及到了组函数,where应该改为having。
(1)、查询最低工资大于50号部门最低工资的部门id和其最低工资。
select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
select min(salary)
from employees
where department_id=50
)
三、多行子查询:
ANY操作符- -:
1、返回其他部门中比job_id为”IT_PROG ”部门任一工资低的员工的员工号、姓名、job_id以及salary。
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG' and salary < any(
select salary
from employees
where job_id='IT_PROG'
)
ALL操作符- -:
1、返回其他部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary。
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG' and salary < all(
select salary
from employees
where job_id='IT_PROG'
)