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')