【Oracle篇】常用查询与SQL92笔记（一）

-- 在scott.emp表中，输出工资大于本部门平均工资的人员信息（需要使用Oracle优先的查询类型 还要使用 SQL92标准查询） -- 方法一 select max(salnvl(comm,0)),e.empno from emp e group by empno; select avg(salnvl(comm,0)),e.empno from emp e group by

-- 在scott.emp表中，输出工资大于本部门平均工资的人员信息（需要使用Oracle优先的查询类型 还要使用 SQL92标准查询）
-- 方法一
select max(sal+nvl(comm,0)),e.empno from emp e group by empno;

select avg(sal+nvl(comm,0)),e.empno from emp e group by empno;

select distinct e.*
from emp e,emp e2
where  e.deptno=e2.deptno and (select max(sal+nvl(comm,0))from emp e3)>(select avg(sal+nvl(comm,0)) from emp e4);

-- 方法二 
select distinct e.*
from emp e join emp e2
on (e.deptno=e2.deptno and (select max(sal+nvl(comm,0))from emp e3)>(select avg(sal+nvl(comm,0)) from emp e4));


-- 外连接：输出20部门对应员工信息，及其他部门信息
-- 要求：使用Oracle外连接符号， 还要使用 SQL92标准 outer join;

-- 方法一
select e.*,d.*
from dept d,emp e
where d.deptno(+)=e.deptno and d.deptno(+)=20
union
select e2.*,d2.*
from dept d2,emp e2
where d2.deptno=e2.deptno(+) and e2.deptno(+)=20;

-- 方法二 
select e.*,d.*
from dept d full join emp e
on (d.deptno=e.deptno and d.deptno=20);

-- --3、获取在98年10月15日加入项目的所有职员的部门编号、姓名、员工编号、部门名称
-- 方法一
select e.*,d.dept_name,w.*
from employee e,department d,works_on w
where e.dept_no=d.dept_no and e.emp_no=w.emp_no and w.enter_date=(to_date('1998-10-15','yyyy-mm-dd'));

select * from works_on;

-- 方法二
select e.*,d.dept_name
from employee e join department d on(e.dept_no=d.dept_no) join works_on w
on (e.emp_no=w.emp_no and w.enter_date=(to_date('1998-10-15','yyyy-mm-dd')));


--4、获取会计部门(ACCounting)中的职员所工作的项目名称
--方法一 
select p.project_name,e.*
from employee e,department d,works_on w,eproject p
where e.dept_no=d.dept_no and e.emp_no=w.emp_no and w.project_no=p.project_no and lower(trim(d.dept_name))=lower('ACCounting');

--方法二
select p.project_name,e.*
from employee e join department d on(e.dept_no=d.dept_no and lower(trim(d.dept_name))=lower('ACCounting')) join works_on w on(e.emp_no=w.emp_no) join eproject p 
on (w.project_no=p.project_no );

--5、获取与至少一个其他部门拥有相同所在地的所有部门的全部细节信息（自连接）
-- 方法一
select d.*,d2.*
from department d,department d2
where d.location=d2.location;

-- 方法二
select d.*,d2.*
from department d join department d2
on (d.location=d2.location);

select * from department;

--6、获取与至少一位其他职员工作在同一部门且居住在同一城市的每一名职员编号、姓名
--   居住地（使用employee_enh)
-- 方法一
select en.*
from employee_enh en,department d
where en.dept_no=d.dept_no and en.emp_address=en.emp_address;

--方法二
select en.*
from employee_enh en join department d
on(en.dept_no=d.dept_no and en.emp_address=en.emp_address);

--7、获取为项目编号为p3工作的所有职员姓名
-- 方法一
select e.*
from eproject p,employee e,works_on w
where p.project_no=w.project_no and w.emp_no=e.emp_no and p.project_no='p3';

-- 方法二
select e.*
from eproject p join works_on w on(p.project_no=w.project_no)
join employee e on (w.emp_no=e.emp_no and p.project_no='p3');

--10、获取为项目p1工作的所有职员姓名
-- 方法一
select e.*
from eproject p,employee e,works_on w
where p.project_no=w.project_no and w.emp_no=e.emp_no and p.project_no='p1';

-- 方法二
select e.*
from eproject p join works_on w on(p.project_no=w.project_no)
join employee e on( w.emp_no=e.emp_no and p.project_no='p1');

--11、获取工作部门不再Seattle的所有职员的姓名
-- 方法一
select e.*
from employee e,department d
where not exists (select e2.* from employee e2,department d2 where e2.dept_no=d2.dept_no and lower(trim(d2.location))=lower('Seattle'));

-- 方法二
select e.*
from employee e join department d 
on(e.dept_no=d.dept_no and d.location! ='Seattle');

--方法三
select e.*
from department d,employee e
where e.dept_no=d.dept_no and d.location! ='Seattle';

--12、获取工作部门所在地和员工居住地的相同的员工信息
-- 方法一
select en.*,d.*
from department d , employee_enh en
where (d.dept_no=en.dept_no and d.location=en.emp_address);

-- 方法二
select en.*
from department d , employee_enh en
union
select en2.* from department d2 join employee_enh en2 on(d2.dept_no=en2.dept_no and  d2.location=en2.emp_address);

--13、获取工作部门所在地和员工居住地的不同的员工信息
-- 方法一
select en.*
from department d,employee_enh en
where  exists(select en2.* from  department d2,employee_enh en2 where d2.location!=en2.emp_address);

--方法二
select en.*
from department d ,employee_enh en
union
select en2.* from  department d2 join employee_enh en2 on (en2.dept_no=d2.dept_no and d2.location !=en2.emp_address);

还未结束，下次继续更新。。。