Answer / bindhu

Correlated subquery are used for row-by-row prcessing.Each
subquery is executed once for every row of the outer
query.It is one way of reading every row in a table and
camparing the values in each row againt the realted data.

Eg: select ename,sal,deptno from emp outer where sal >
(select avg(sal) from emp where deptno=outer.deptno);

Each time a row from the outer query is processed,the inner
query is evaluated.

Answer / guest

select empno,ename from emp where deptno in(
select deptno from dept where dept.deptno=emp.deptno)

when inner subquery has an reference to outer query then
this is know as Correlated sub-query.

Answer / his highness abdullah!!

Thats right , a co-related subquery is evaluated once for
every row processed by the parent statement.......

Answer / asnani_satish@yahoo.com

Difference between sub-query, correlated query and query as
table
1. Sub-query : the inner query is executed in entirety
before the outer query is executed
eg select * from emp where deptno in (select deptno from dept);

2. Correlated Query: For each record fetched in outer query
corresponding matching records are fetched in sub-query
because of join condition within inner sub-query. Answers
1,2,3 are correct

3. Query as Table: A query can behave as a table

select a.empno,a.sal,a.sal/b.avgsal*100 as percent_avg_sal
from emp a,(select deptno,sum(sal) avgsal from emp group by
deptno) b
where a.deptno=b.deptno;

Here the entire "(select deptno,avg(sal).....)" behaves as
table named "b". The query is behaving as a temporary table.

Answer / bis

A query which uses values from the outer query is called as a correlated sub query. The subquery is executed once and uses the results for all the evaluations in the outer query.

Here, the sub query references the employee_id in outer query. The value of the employee_id changes by row of the outer query, so the database must rerun the subquery for each row comparison. The outer query knows nothing about the inner query except its results.

select employee_id, appraisal_id, appraisal_amount From employee
where
appraisal_amount < (select max(appraisal_amount)
from employee e
where employee_id = e. employee_id);

Answer / raj

Correlated sub query is evaluated once per row processed by the parent statement.

Example:which employee earn salary greater than avg salary of their department.

Query: select emp_no,emp_name,job,sal,dept_no from emp a where sal>(select avg(sal) from emp where dept_no=a.dept_no)

Answer / asnani_satish@yahoo.com

Minor correction in above answer
Difference between sub-query, correlated query and query as
table
1. Sub-query : the inner query is executed in entirety
before the outer query is executed
eg select * from emp where deptno in (select deptno from dept);

2. Correlated Query: For each record fetched in outer query
corresponding matching records are fetched in sub-query
because of join condition within inner sub-query. Answers
1,2,3 are correct

3. Query as Table: A query can behave as a table

select a.empno,a.sal,a.sal/b.avgsal*100 as percent_avg_sal
from emp a,(select deptno,avg(sal) avgsal from emp group by
deptno) b
where a.deptno=b.deptno;

Here the entire "(select deptno,avg(sal).....)" behaves as
table named "b" containing dept wise average sal. The query
is behaving as a temporary table.

What is nested table in pl sql?

0 Answers  

define different types of trigger. : Sql dba

1 Answers  

in sql table following column r there i want find 1st paid ,2nd paid,3rd paid date for same |service_type|date |vehicle_no| |------------|------|_---------| |paid |23 jan|MH12H2007 | | | | | |paid |26 feb|MH12H2007 | | | | | | | | | |paid |28 mar|MH12H2007 | i want o/p like below vehicle no| 1st paid date | 2nd paid date|3rd paid |latest paid date| pls help me out

4 Answers  

Which join condition can be specified using on clause?

0 Answers  

Which is better varchar or nvarchar?

0 Answers  

how to retrive only second row from table?

31 Answers   IBM,

How can we implement rollback or commit statement in a trigger?

0 Answers  

What is a 'instead of trigger'?

3 Answers   Eenadu, TCS,

How to run pl/sql statements in sql*plus?

0 Answers  

Explain the working of primary key?

0 Answers  

Explain the uses of control file.

0 Answers  

Why schema is used in sql?

0 Answers