Sunday, October 30, 2011

Scalar Query in Oracle

 Scalar Query : [ 8.0 ] Select statement provided in place of column name  is  known as Scalar query.
 It is an Independent Query.
 Advantage : Used to retrieve Data Analysis reports

 * List the department details along with no.of             Employees and Total Salary .

 select deptno, dname, loc ,
 ( select count(*) from emp where deptno =      d.deptno ) ecount,
 ( select sum(sal) from emp where deptno =     d.deptno ) totsal  from dept d ;
    deptno   dname    loc   ecount    totsal

 Using 2 Queries :

 select job,ename,sal from emp order by job;

 select job ,min(sal) lopay , max(sal) hipay from     emp group by job order by job ;

 Using Scalar Query :

 select job,ename,sal,
 ( select min(sal) from emp where job = e.job )        lopay ,
 ( select max(sal) from emp where job = e.job )        hipay
   from emp e order by job ;
       job    ename   sal   lopay   hipay

  Select deptno,ename,sal,
  ( select min(sal) from emp
     where deptno = e.deptno) lopay ,
  ( select max(sal) from emp where deptno =            e.deptno ) hipay from emp e order by deptno ;
      deptno    ename   sal   lopay   hipay

 SELECT :

 SELECT - clauses
              ( where ,group by, having, order by)
               Joins
               Set operators
               Sub Queries
               Co-related sub query
               Scalar query  , Inline View
               Arithematic Expressions
               Built-in functions

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect