What is correlated sub-query?




Answers were Sorted based on User's Feedback



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.

Is This Answer Correct ?    102 Yes 17 No

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.

Is This Answer Correct ?    106 Yes 23 No

Answer / his highness abdullah!!

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

Is This Answer Correct ?    32 Yes 8 No




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.

Is This Answer Correct ?    15 Yes 3 No

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

Is This Answer Correct ?    2 Yes 0 No

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)

Is This Answer Correct ?    0 Yes 0 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.

Is This Answer Correct ?    6 Yes 9 No

Post New Answer



More SQL PLSQL Interview Questions

what is global table

5 Answers   iFlex,


how to create a database in oracle?please gve anser with example

1 Answers  


I have a procedure in a procedure. The inner procedure contains out parameter. How I can call the inner procedure in the out procedure and send the inner procedure parameter value(out parameter value) into out procedure?

2 Answers  


25.67,-1 Trunc = 20 Round= 30 HOW????

2 Answers   Karrox,


If I have a table T with 4 rows & 2 columns A & B. A has values 1,2,3,4. and B has 10,20,30,40. Write an Update SQL query which can Swap the values of A & B for all records. (Do not use a sub-query)

5 Answers  


function can return multiple value?how give give sample coding

2 Answers   CTS, UHG, 3i Infotech, Excelity Global,


Give an example of Full Outer Join?

1 Answers   IBM,


How did you export data from database to excel file.

1 Answers   TCS,


define a variable representing the expression used to calculate on emps total annual remuneration.use the variable in a statement which finds all emps who can earn 30000 a year or more.

4 Answers  


display records from 5 to 9 using rowid or rownum

4 Answers   Agile Software,


There is a sequence with min value 100. I want to alter this sequence to min value as 101. If the table has already data in the sequence column as 100,101,102... Is it possible to do so ?

4 Answers   IBM,


what is meant by databases

4 Answers  




Categories