Answer / sriram

Correlated subquery runs once for each row selected by the
outer query. It contains a reference to a value from the
row selected by the outer query.

Nested subquery runs only once for the entire nesting
(outer) query. It does not contain any reference to the
outer query row.

For example

Correlated Subquery:

select e1.empname e1.basicsal e1.deptno from emp e1 where
e1.basicsal (select max(basicsal) from emp e2 where
e2.deptno e1.deptno)

Nested Subquery:

select empname basicsal deptno from emp where (deptno
basicsal) in (select deptno max(basicsal) from emp group by
deptno)



Please let me know if I am wrong...............

Answer / sriram

Correlated subquery runs once for each row selected by the
outer query. It contains a reference to a value from the
row selected by the outer query.

Nested subquery runs only once for the entire nesting
(outer) query. It does not contain any reference to the
outer query row.

For example

Correlated Subquery:

select e1.empname e1.basicsal e1.deptno from emp e1 where
e1.basicsal (select max(basicsal) from emp e2 where
e2.deptno e1.deptno)

Nested Subquery:

select empname basicsal deptno from emp where (deptno
basicsal) in (select deptno max(basicsal) from emp group by
deptno)



Please let me know if I am wrong...............

Answer / praveen

if we want to select 100th hig no.
in sub-query we will write 100 conditions.
to avoid this we go for correlated sub-query

what is check point and restart Logic ? why do we go for that ??

2 Answers   CGI, UST, Xansa,

---

When is the skeleton cursor table created?

1 Answers  

---

What happens to the PLAN if index used by it is dropped?

1 Answers  

---

Consider that a package is bound into 2 different collections and the PKLIST option specifies both the collections. If the collection id is not specified in the program while executing the SQL query, then when the DB2 system will search the package list, which collection will it pick up or will it give an error/abend?

1 Answers  

---

What is the syntax of SELECT statement when embedded in a COBOL program?

2 Answers  

---

Why do we use cursor?

0 Answers  

---

max number of columns in a db2 table?

6 Answers  

---

Suppose we have a query for update update table1 set col1 = 'val1' where col2=(select .... from ...) suppose the subquery does not return any record, what will happen to update?

1 Answers   IBM,

---

4. A DB2 application is bound with uncommitted Read isolation level.It issues a request that retrieves 20 rows out of 200000 in the table. Which of the following descrbes the rows that are locked as a result of this request? a. None of the rows are locked. B.The retrieved rows are locked. C.The last row of the result set is locked. D.The rows not previously updated by another application are locked.

3 Answers   Wipro,

---

What is a precompiler?

2 Answers  

---

What is clone table?

0 Answers  

---

How to check last update on table in db2?

0 Answers  

---