14、SQL--高级子查询（2）（含习题）

四、相关子查询：（有点难度）

 

 

1、相关子查询：

select employee_id,last_name

from employees e

order by (

          select department_name

          from departments d

          where e.department_id = d.department_id  --e是外层的表，d是内层的表；二者有相关性。

         )

 

2、非相关子查询：（返回公司中工资比Abel工资高的员工的信息）

select employee_id,salary

from employees

where salary > (

                select salary

                from employees

                where last_name = 'Abel'

               )

 

（1）、查询员工中工资大于本部门平均工资的员工的last_name，salary和其department_id。

select last_name,salary,department_id

from employees outer

where salary > (

                select avg(salary)

                from employees

                where department_id = outer.department_id

                )

 

（2）、

 

select * from job_history

select e.employee_id,last_name,e.job_id

from employees e

where 2 <= (

              select count(*)

              from job_history

              where employee_id = e.employee_id

            )

 

 

五、EXISTS与NOT EXISTS操作符：

 

1、EXISTS操作符：

 

 

 

（1）、代码实现：

select employee_id,last_name,job_id,department_id

from employees e1

where e1.employee_id in (

                        select manager_id

                        from employees e2

                        where e1.employee_id = e2.manager_id

                        )

 

（2）、代码实现：

select e1.employee_id,e1.last_name,e1.job_id,e1.department_id

from employees e1,employees e2

where e1.employee_id = e2.employee_id

 

2、NOT  EXISTS操作符：

 

 

（1）、代码：

select department_id,department_name

from departments d

where not exists(

                 select 'c'

                 from employees

                 where department_id = d.department_id

                 )

 

（2）、代码：只想看有哪些部门。

select department_id,department_name

from departments d

minus

select department_id,to_char(null)

from employees

 

六、相关更新：

 

1、相关更新：

 

 

 

2、相关删除：

 

 

 

七、WITH子句：

 

 

1、简单例子：

题目要求：查询公司中工资比Abel高的员工的信息

/*

select employee_id,salary

from employees

where salary > (

               select salary

               from employees

               where last_name = 'Abel'

               )

*/

with Abel_sal as(

                select salary

                from employees

                where last_name = 'Abel'

                )

               

select employee_id,salary

from employees

where salary > (

               select salary

               from Abel_sal

               )

 

2、

 

 

 

八、测验题一：

 

1、查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可

多列子查询

select last_name, department_id, salary

from employees

where (salary,department_id) in (

                                 select salary,department_id

                                 from employees

where commission_pct is not null

                                )

 

2、选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

select last_name, job_id, salary

from employees

where salary > all(

select salary

from employees

where job_id = 'SA_MAN'

)

 

3、选择所有没有管理者的员工的last_name

select last_name

from employees e1

where not exists (

select 'A'

from employees e2

where e1.manager_id = e2.employee_id

)