oracle複雜查詢（二）

（一）函數

在實際應用中，經常使用執行複雜查詢的數理統計，經常需要你顯示多張表的資料，利用資料分組MAX  ,MIN  AVG,SUM,COUNT 

如何顯示所有員工中最高工資和最低工資？

SQL> select max(sal), min(sal) from emp ;

如何顯示最高工資的員工姓名？

select ename ，sal  from emp where sal=(select max(sal)from emp );

SQL> select ename ，sal  from emp where sal=(select max(sal) from emp ) or sal =

(select min(sal)  from emp);

顯示所有員工的平均工資和工資總和？

   select sal  "平均工資"，（sal+nvl(comm,0)）*12  "工資總和"      from emp；

計算有多少員工？

select  count (*) from emp;

顯示工資高于平均工資的的員工的資訊

select ename  ,sal,  deptno   from emp  where  sal >(select  avg(sal)  from  emp)   order by  sal;

（2）group by 和having子句

group  by 用于查詢的結果分組統計

having 子句用于限制分組顯示結果

如何顯示每個部門的平均工資和最高工資？

select     deptno，max（sal） "部門最高工資"，avg（sal） "部門平均工資"  from  emp  group by  deptno ；

顯示每個部門的每種崗位的平均工資和最低工資？

select  deptno，job，avg(sal) "部門平均工資",min(sal) "部門最低工資"  from   emp  group by  job，deptno;

顯示平均工資低于2000的部門号和它的平均工資

select  avg(sal) "部門平均工資"   deptno   from emp    group  by   deptno  having  avg（sal）<2000;

總結：對資料分組

1·分組函數隻能出現在選擇清單，having，order by 子句中

2、如果select語句中同時包含有group  by，having，order by，那他們的出場順序是group by ，having，order by 

3、在選擇列中如果有列，表達式和分組函數，那麼這些列和表達式必須有一個出現在group by 子句中，否則就會出錯  ！！！

    例如：select  avg(sal) "部門平均工資"   deptno   from emp    group  by   deptno  having  avg（sal）<2000;

三、多表查詢（多表查詢的條件 =不能少于表的個數—1）

多表查詢指的是基于兩個和兩個以上的表或是視圖的查詢，在實際中，查詢單個表不能滿足你的要求時（如顯示sales部門位置和其員工姓名），這種情況下需要使用到dept表和emp表

顯示雇員名，雇員工資及所在部門的名字[笛卡爾集]

select  emp.ename，emp.sal    ,dept.dname    from  emp a1 ，dpt a2    where

a1.deptno = a2.deptno；【笛卡爾集】

注意：如果沒有條件where a1.deptno = a2.deptno則會産56條記錄

如何顯示部門号為10的部門名，員工名和工資

select   dname，ename，sal  from  emp,dept  where  deptno=10;

顯示各個員工的姓名，工資及其工資的級别

SQL> select  a1.ename,a1.sal,a2.grade  from  emp a1 ,salgrade a2   where a1.sal

between  a2.losal and a2.hisal ;

顯示雇員名，雇員工資及所在部門的名字，并按部門排序。

SQL> select  a1.ename ，a1.sal, a2.dname   from   emp  a1 ,dept  a2   where a1.d

eptno = a2.deptno  order by  a2.deptno  ;

自連接配接：在同一張表的連接配接查詢。

顯示某個員工的上級上司的姓名？

SQL> select      a1.ename,  a2.empno from  emp a1, emp a2  where a1.mgr = a2.empno   and  a1.ename = 'FORD';

什麼是子查詢？

子查詢就是嵌入在其他sql語句中的select語句，也叫嵌套查詢

單行子查詢：指的是隻傳回一行資料的子查詢語句

如何顯示與SMITH同一個部門的所有員工？

select  ename  from  emp where deptno = (select  deptno from emp   where ename='SMITH');

多行子查詢：指的是傳回多行資料的子查詢  

如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門号？

select ename ,sal,deptno,job        from emp   where job in (select  job from emp where deptno='10');

多行子查詢中使用all操作符和any操作符

請思考：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門号

  SQL>select   ename，sal，deptno from  emp  where  sal> all(select  sal  from  emp   where deptno = 30);

或者如下：

SQL> select   ename，sal，deptno from  emp  where  sal> (select  max(sal)  from emp   where deptno = 30); 

如何顯示工資比部門30的任意一個員工的工資高的員工的姓名、工資和部門号

SQL>select   ename，sal，deptno from  emp  where  sal> any(select  sal  from  emp   where deptno = 30);

SQL>select   ename，sal，deptno  from  emp  where  sal> (select min（sal）   from  emp   where deptno = 30);

多列子查詢：

單行子查詢是指子查詢隻傳回單列、單行資料，多行子查詢是指傳回單列多行資料，都是針對單列而言的而多列子查詢則是指查詢傳回多個列的資料的子查詢語句

 請思考：如何查詢與SMITH的部門和崗位完全相同的所有雇員

select  * from  emp where (deptno ,job)= (select deptno ,job from emp  where ename='SMITH');

或者

SQL> select  * from  emp where (deptno ,job)= (select deptno ,job from emp  where ename='SMITH') ;

如何顯示高于自己部門的平均工資的員工的資訊？？？？？？

select  * from   emp , (select  deptno,avg(sal)    mysal  from emp  group  by   deptno )  a1   where emp.sal >a1.mysal  and emp.deptno = a1.deptno;