Oracle PLSQL and SQL basic questions for 3 years experienced

SET 1

1. Syntax for Bitmap index. What is the purpose of Bitmap index. Which scenarios do we use it.
2. What is data dictionary. What are its classifications and privileges.
3. What is correlated subquery. Eg?
4. How do we migrate data from one table to another. How do we capture exceptions during that. (try to explain with bulk collect and saved expection cos thats what they look for)
5. If your query doesnt use indexes how do you tell the query to make use of the index you created. (here you need to explain about oracle hints)
6. what is correlated update.
7. Connect by Prior.
8. What are the uses of regular expressions. Learn few important functions of regular expressions. They may ask few real time questions on the spot to write a query using regular expressions.

SET 2

1.  What is cursor. types of cursor.  Why do we have referential cursor.

2.  What are the types of collections (Nested table, varray, associate array).  Difference between them.  Prepare some stored procedures for all these and execute them and check the output.

3.  What are saved exceptions.  Why should we use it.

4.  Difference between materialized view and normal view.

5.  Use of force view.

6.  Difference between stored procedure and function

7.  Difference between truncate and delete and drop.

8.  Prepare a stored procedure for bulk collect and give some sample values and execute.

10.  What are indexes.  types of it.  why do we use indexes.

11.  What are oracle hints why do we go for it.

12.  What is the use of NOCOPY passing parameter in the stored procedure.  

Oracle Database..

I learnt

First things first. What is ROWNUM?

ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers

follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful

when you want to do some filtering based on the number of rows, such as:  1 2 3 4 5 6

SQL> -- Rownum to limit result set

SQL> -- to three rows only

SQL> select empno, ename, sal, rownum

 from emp

 where rownum < 4;



     EMPNO ENAME        SAL     ROWNUM

---------- ---------- ----- ----------

      7369 SMITH        800          1

      7499 ALLEN       1600          2

      7521 WARD        1250          3

So out of those three rows if I want to select only rownum = 2, this should work. Right?

-- Query attempt to select row

-- with rownum = 2

select empno, ename, sal, rownum

from emp

where rownum = 2;

Run it on SQL.

no rows selected

What just happened?

Why did adding rownum = 2 return no results?

How ROWNUM Works

Here is the secret. Rownum values are not preassigned, they are determined on the fly, as the rows

are output. The common misconception is that every row in the table has a permanent ROWNUM. In

truth, rows in a table are not ordered or numbered – you cannot ask for row#5 from the table, there

is no such thing.

The pseudocode for a query using rownum is:

    rownum = 1

    for x in ( select * from query)

    loop

        if ( x satisfies the predicate )

        then

              output the row

              rownum = rownum + 1

        end if;

    end loop;

The first selected row is always assigned rownum = 1, and is tested against the predicate. When the

test is "< 4", rownum = 1 passes the test and the rownum is set to 2, and so the loop continues. The

first 3 rows pass the test and get printed out, till rownum becomes 4 and fails the test.

When the test is "= 2", the first row itself does not pass the test (since it is rownum = 1). The

increment never happens and no rows get printed.

All of which explains why the WHERE condition can only filter on what rownum is less than, not what

it is great than.

Summary

ROWNUM is a pseudocolumn that assigns a number to every row returned by a SQL query. It can be

of great use in filtering data based on the number of rows returned by the query.

ROWNUM gets its value as the query is executed, not before, and gets incremented only after the

query passes the WHERE clause. Therefore, your WHERE condition can filter data based on "rownum

< 2/3/4/." but not "rownum > 2/3/4.". The second filter will invariably return no rows selected.