oracle课堂笔记--第十三天

自连接：

empid ename mgrid

100 abc

101 def 100

102 xyz 100

 

emp: mgr:

empid ename mgrid empid mgrname

100 abc 100 abc

101 def 100

102 xyz 100

 

 

101 def 100 100 abc

102 xyz 100 100 abc

 

select emp.ename, mgr.mgrname

from emp, mgr

where emp.mgrid=mgr.empid

 

emp: mgr:

empid ename mgrid empid ename mgrid

100 abc 100 abc

101 def 100 101 def 100

102 xyz 100 102 xyz 100

select e.last_name, m.last_name

from employees e, employees m

where e.manager_id=m.employee_id;

 

有经理的员工数：106

SQL> select count(*) from employees where manager_id is not null;

没有经理的员工数：1

SQL> select count(*) from employees where manager_id is null;

练习：

显示所有员工姓名和经理姓名，没有经理的显示“无”。

select e.last_name, nvl(m.last_name, 'N/A')

from employees e, employees m

where e.manager_id=m.employee_id(+);

 

不等值连接：

conn scott/tiger

select e.ename, sg.grade

from emp e, salgrade sg

where e.sal between sg.losal and sg.hisal;

 

练习：

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

create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id  is not null group by department_id;

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

from employees e, avg_sal_dept asd

where e.department_id=asd.department_id

and e.salary>asd.avg_sal;

 

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;

子查询

第一写成子查询   第二写成主查询

单行子查询的思路：

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工作的所有员工姓名

在Seattle的部门1 Seattle的location id是多少  2 location id 下的部门信息

在这些部门中的员工 1 那些部门 2 这些部门的员工

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值的影响：

有员工的部门名称

 

select department_name from  departments where department_id in (select department_id from employees);

 

 

没有员工的部门名称select department_name from  departments where department_id not in (select department_id from employees where department_id is  not 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);

练习：

在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;

转载于:https://www.cnblogs.com/Matilda/p/7270039.html