06、SQL--子查询（含习题）

一、子查询：

 

 

 

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'

                                          )