Answer Posted / 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 are pl/sql packages?

567

---

How do you explain an index?

784

---

What is the usage of the distinct keyword?

681

---

Can we call a function containing dml statements in a select query?

537

---

what happens when the column is set to auto increment and you reach the maximum value for that table? : Sql dba

563

---

---

Can cursors be part of a trigger body?

1110

---

what is foreign key? : Sql dba

579

---

Which data dictionary views have the information on the triggers that are available in the database?

750

---

what is innodb? : Sql dba

570

---

Is left join faster than inner join?

608

---

how to escape special characters in sql statements? : Sql dba

504

---

How are sql commands classified?

587

---

What is trigger explain with example?

547

---

Table A Table B 1 1 2 1 3 1. Union & union all --> A Union B , A Union all B 2. Minus , Intersect --> A minus B , B Minus A , A Intersect B 3. Joins A join B , A Left Join B A Right Join B , A full Join B 4. %Type - Uses & Benifit 5. Truncate & Delete 6. Pragma Autonomus Transaction 7. how to Perform DDL from function or procedure 8. Can we have DML inside Function 9. Rank & Dense Rank diffrence 10. Water Mark in Oracle 11. Index , Can we have index in all column of table if no then why ?

530

---

what are the advantages a stored procedure? : Sql dba

538

---