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

i have a table emp and columns ename,empno,mgr_id,i need ename,manager name as result i.e employee respective manager.. example empno ename mgr_id 1 john 3 2 paul 3 3 smith 1 4 kevin 1 5 stewart 2 result has to look like this ename manager john smith paul smith smith john kevin john stewart paul can u plz help me out in this.....

7 Answers  


What is meant by Join? What are the different types of Joins available? Explain.

5 Answers   Cap Gemini,


i have a customer table. trans_id trans_date trans_amt debit_credit_indicator 001 01-JAN-13 1099 cr 001 12-JAN-13 500 db 002 24-FEB-13 400 db 002 23-MAR-13 345 cr 001 18-APR-13 800 cr 002 15-MAR-13 600 db 001 12-FEB-13 200 cr i want like this output. trans_id trans_amt debit_credit_indicator i want get highest credit amount and lowest credit amount and highest debit amount and lowest debit amount for each trans_id. pls give me answer. i want urgent

3 Answers  


I need to write a simple query,which one is better select statement or stored procedure?and why?

2 Answers   iGate,


How to count the no of records of a table without using COUNT function?

11 Answers   TCS, Tesco,


Difference between DBMS and RDBMS...CODD's rules

4 Answers   Polaris,


Give the structure of the function ?

2 Answers  


Give the structure of the procedure ?

1 Answers  


query to retrive the employees whose sal is greater than avg sal

10 Answers   TCS,


we have a package and we have grants to execute that package inside of that we have table, here we don't have privileges to this table? whether this package will execute or not?

3 Answers   TCS,


in table there r so many rows write a query which two rows r updated last two day befor?

3 Answers   Exilant,


write a procedure to print a statement or number not using "dbms_output.put_line" package.write a procedure instead of it using procdure name as "print" ex:- declare a number:=2; begin print(a); end; /* when U type above procedure 2 have to should be printed*/

1 Answers   iFlex,




Categories