Answer Posted / sunil

A correlated Subquery runs for the rows selected from the outer query. It takes the value from the outer query
and execute the inner query for that value

example:

select * from emp e
where e.deptno in(select d.deptno from dept d
where e.deptno = d.deptno);

in this query emp table's deptno will be passed into the inner query(select deptno from dept d where e.deptno = d.deptno).
And the inner query will execute only for that value from the outer query.
That's why it is called correlated subquery

In Nested subquery the inner query runs only once and pass the result set to the outer query.

example

select * from emp e
where e.deptno in(select d.deptno from dept d);

Here the inner query (select d.deptno form dept d) will run first and fetches all the rows from the dept table
and the outer query will select only the records that has the matching deptno in the result set fetched by the
inner query. The outer query will act as a nesting query and that is why this is called nested subquery.

Here in correlated subquery, the outer query executes first and the inner query will execute second.

But in Nested subquery, the inner query executes first and the outer query executes second.

Hope this helps.
thanks to binosh who helped me to understand this concept before posting here...

What is a dynamic performance view in oracle?

1002

---

What are the types of trigger in oracle?

987

---

What are the attributes that are found in a cursor?

1084

---

How to assign a tablespace to a users in oracle?

1050

---

How to create a server parameter file?

1144

---

> CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 ) 2 RETURN NUMBER AS 3 new_str VARCHAR2(4000) := factstr||'*' ; 4 fact number := 1 ; 5 BEGIN 6 7 WHILE new_str IS NOT NULL 8 LOOP 9 fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1)); 10 new_str := substr( new_str,INSTR(new_str,'*')+1); 11 END LOOP; 12 13 RETURN fact; 14 15 END; explanation Above program?

2037

---

How translate command is different from replace?

1095

---

Explain integrity constraints?

1109

---

How many anonymous blocks can be defined?

1193

---

Differentiate between post-database commit and post-form commit?

1049

---

HI ALL, CAN ANYONE TELL ME THE DIFFERENCES BETWEEN SQL CLUSTURS,MSQL CLUSTERS,ORACLE CLUSTERS.......THANKS IN ADVANCE

2243

---

Can we write dml statement in function in oracle?

1091

---

You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

2018

---

What is an oracle wallet?

949

---

Describe the types of sub query?

1006

---