Oracle（九）老师

多表查询

等值连接
   where emp.deptno=dept.deptno
非等值连接
   where sal between losal and hisal
自连接
 from emp e,emp m
外部链接
 (+)

92 select
99 join

1 等值连接
emp+dept
  92 select
   select * from emp ,dept where emp.deptno=dept.deptno;
  99 join
   select * from emp natural join dept;
   select * from emp join dept using(deptno);
   select * from emp join dept on emp.deptno=dept.deptno;
2 非等值连接
emp+salgrade
  92 select
   select * from emp ,salgrade where sal between losal and hisal;
  99 join
   select * from emp  join  salgrade on sal between losal and hisal;
3自连接
emp+emp
  92 select
   select e.ename ,m.ename from emp e,emp m where e.mgr=m.empno;
select ename,loc,losal,hisal
from emp join dept on emp.deptno=dept.deptno
         join salgrade on sal between losal and hisal and grade=2;
  99 join
   select e.ename,m.ename from emp e join emp m on e.mgr=m.empno;
4 emp+dept+salgrade
 92 select
    select * from emp ,dept,salgrade where emp.deptno=dept.deptno and sal between losal and hisal;
 99 join
     select * from emp natural join dept join salgrade on sal between losal and hisal;
    select * from emp join dept on emp.deptno=dept.deptno join salgrade on sal between losal and hisal;

5外部连接
 left outer join
 right outer join
 full  outer join

 92 select
   select dname,ename  from emp,dept where emp.deptno(+)=dept.deptno;
 99 join
   select dname,ename from emp right outer join dept on emp.deptno=dept.deptno;

 练习：显示所有部门的名称和员工姓名和工资级别
 92
 select dname,ename,grade
 from emp,dept ,salgrade
 where emp.deptno(+)=dept.deptno and sal between losal(+) and hisal(+)

 

--------------------------------------------------------------练习----------------------------------------------------

 

1、返回拥有员工的部门名、部门号。
select dname,deptno
from dept where  deptno in (select deptno from emp)

select distinct dname,deptno
from emp natural join dept;

2、工资水平多于smith的员工信息。
select * from emp where sal>(select sal from emp where ename='SMITH');
3、返回员工和所属经理的姓名。
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno(+);

select e.ename,m.ename
from emp e left outer join emp m
on e.mgr=m.empno

4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
select e.ename,m.ename
from emp e,emp m
where e.mgr=m.empno and e.hiredate<m.hiredate

select e.ename,m.ename
from emp e join emp m
on e.mgr=m.empno
where e.hiredate<m.hiredate

 

5、返回员工姓名及其所在的部门名称。
select ename,dname
from emp ,dept where emp.deptno=dept.deptno;

select ename,dname
from emp natural join dept;

6、返回从事clerk工作的员工姓名和所在部门名称。
select ename,dname
from emp ,dept
where emp.deptno=dept.deptno and job='CLERK';

select ename,dname
from emp natural join dept
where job='CLERK'

7、返回部门号及其本部门的最低工资。
select deptno,min(sal)
from emp
group by deptno;

8、返回销售部(sales)所有员工的姓名。
select ename from emp ,dept where emp.deptno=dept.deptn and dname='SALES'
select ename from emp natural join dept where dname='SALES';

9、返回工资水平多于平均工资的员工。
select * from emp where sal>(select avg(sal) from emp);

10、返回与SCOTT从事相同工作的员工。
select * from emp where job=(select job from emp where ename='SCOTT') and ename<>'SCOTT';

 

11、返回与30部门员工工资水平相同的员工姓名与工资。
 select ename,sal
 from emp
 where sal in( select sal from emp where deptno=30);

12、返回工资高于30部门所有员工工资水平的员工信息。
 select ename,sal
 from emp
 where sal >all( select sal from emp where deptno=30);

13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。

14、返回员工的姓名、所在部门名及其工资。
select ename,dname,sal
from emp ,dept
where emp.deptno=dept.deptno;
select ename,dname,sal
from emp natural  join dept;

15、返回员工的详细信息。(包括部门名)
select * from emp,dept,salgrade where emp.deptno=dept.deptno and  sal between losal and hisal;

16、返回员工工作及其从事此工作的最低工资。
select job,min(sal)
from emp
group by job;

17、返回不同部门经理的最低工资。
select deptno,min(sal)
from emp where job='MANAGER' group by deptno;

18、计算出员工的年薪，并且以年薪排序。
select (sal+nvl(comm,0))*12 year from emp order by year;

19、返回工资处于第四级别的员工的姓名。
select ename
from emp,salgrade where grade=4 and sal between losal and hisal;

20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资

select ename,loc,losal,hisal
from emp,dept,salgrade
where emp.deptno=dept.deptno and sal between losal and hisal
 and grade=2;

select ename,loc,losal,hisal
from emp join dept on emp.deptno=dept.deptno
         join salgrade on sal between losal and hisal
where grade=2;

 

21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
 select ename,loc,(select min(sal) from emp ,salgrade where sal between losal and hisal and grad
 (select max(sal) from emp,salgrade where sal between losal and hisal and grade=2) maxsal
 from emp,salgrade,dept
 where emp.deptno=dept.deptno and sal between losal and hisal and grade=2

 

select e.ename,loc,max(m.sal),min(m.sal),grade from emp e,emp m,dept,salgrade where e.deptno=dept.deptno and e.sal between losal and hisal and m.sal between losal and hisal and grade=2 group by e.ename,loc,grade

22.工资等级多于smith的员工信息。

select * from emp ,salgrade where sal between losal and hisal and grade>  (select grade from emp,salgrade where sal between losal and hisal and ename='SMITH')