单行子查询的思路：

SQL> select salary from employees where last_name=‘Feeney‘;

SQL> select last_name from employees where salary>3000;

SQL> select last_name from employees where salary>(select salary from employees where last_name=‘Feeney‘);

多行子查询的思路：

SQL> select distinct department_id from employees where department_id is not null;

SQL> select department_name from departments where department_id in (10, 20,30);

SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);

用多表连接改写：

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id

for dept in 1..27

  for emp in 1..107

   查看emp中是否出现deptid

练习：

工资大于全公司平均工资的员工姓名。

SQL> select last_name from employees where salary>(select avg(salary) from employees);

和Feeney同年入职的员工姓名

select last_name, hire_date

from employees

where extract(year from hire_date)=

(select extract(year from hire_date) from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

select last_name, hire_date

from employees

where hire_date between

(select to_date(to_char(hire_date, ‘yyyy‘)||‘0101‘, ‘yyyymmdd‘) from employees where last_name=‘Feeney‘)

and

(select to_date(to_char(hire_date, ‘yyyy‘)||‘1231‘, ‘yyyymmdd‘) from employees where last_name=‘Feeney‘)

在Seattle工作的所有员工姓名

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city=‘Seattle‘));

查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高

select last_name from employees

where department_id=

(select department_id from employees where last_name=‘Abel‘)

and salary >

(select salary from employees where last_name=‘Olson‘);

配对子查询：

和Feeney在同一个部门、做同一职位的员工姓名：

select last_name, department_id, job_id

from employees

where department_id=

(select department_id from employees where last_name=‘Feeney‘)

and job_id=

(select job_id from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

select last_name, department_id, job_id

from employees

where (department_id, job_id)=

(select department_id, job_id from employees where last_name=‘Feeney‘)

and last_name != ‘Feeney‘;

in和not in受null值的影响：

所有管理者的姓名：

SQL> select last_name from employees where employee_id in (select manager_id from employees);

所有普通员工的姓名：

SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);

关联子查询：

工资大于所在部门平均工资的员工姓名。

for i in 1..107所有员工

{

  select avg(salary) from employees where department_id=i.department_id

  if i.salary > i所在部门的平均工资

  保留此记录

}

select last_name

from employees outer

where salary >

(select avg(salary) from employees

 where department_id = outer.department_id);

select e.last_name, e.salary, asd.avg_sal

from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

exists/not exists查询：

for i in 1..27所有部门

{

  for j in 1..107所有员工

{

  if i.department_id = j.department_id

  保留此记录

  break

}

}

select department_name

from departments outer

where exists

(select 1 from employees where department_id=outer.department_id);

select department_name

from departments outer

where not exists

(select 1 from employees where department_id=outer.department_id);

练习：

有员工的部门名称

没有员工的部门

所有普通员工的姓名

所有管理者的姓名：

for i in 1..107所有员工

{

  for j in 1..107所有员工

{

  if i.employee_id = j.manager_id

  保留此记录

  break

}

}

select last_name

from employees outer

where exists

(select 1 from employees where manager_id=outer.employee_id);

所有普通员工的姓名：

select last_name

from employees outer

where not exists

(select 1 from employees where manager_id=outer.employee_id);

子查询和多表连接的转换：

有员工的部门的名称

select department_name

from departments

where department_id in

(select department_id from employees);

select department_name

from departments outer

where exists

(select 1 from employees where department_id=outer.department_id);

select distinct d.department_name

from employees e, departments d

where e.department_id=d.department_id;

练习：

在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）

select last_name

from employees

where department_id in

(select department_id from departments

where location_id=

(select location_id from locations where city=‘Seattle‘));

select e.last_name

from employees e, departments d, locations l

where e.department_id=d.department_id

and d.location_id=l.location_id

and l.city=‘Seattle‘;

最大值查询：

SQL> select last_name from employees where salary=(select max(salary) from employees);

top-N查询：

SQL> select last_name, salary from employees where rownum<=3 order by salary desc;

SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;

分页查询：

SQL> select * from

(select * from

(select * from

(select last_name, salary from employees order by salary desc)

where rownum<=6)

order by salary)

where rownum<=3

order by salary desc;

SQL> select last_name, salary

 from (select rownum row_num, v1.*

             from

               (select last_name, salary from employees order by salary desc) v1

         ) v2

 where row_num between 4 and 6;

 select last_name, salary

 from (select rownum row_num, v1.*

             from

               (select last_name, salary from employees order by salary desc) v1

             where rownum<=6

         ) v2

 where row_num >= 4;

子查询