SQL,PL/SQL Interview Questions part-2

What is Pseudo column?

Pseudo columns are database columns that are using for different purposes in oracle database like ROWNUM, ROWID, SYSDATE, UID, USER, ORA_ROWSCN, SYSTIMESTAMP,

What is Dual and where it can be use?

Dual is a database table owner is sys and reside in data dictionary. It have only one column name DUMMY and one data X. It is normally use for select the values of Pseudo columns.

Can we insert, update, delete, alter and drop DUAL table?

Yes we can Insert, update, delete alter and also drop Dual table in oracle.

If we create a table with name DUAL it can be work same as DUAL?

Yes we create table with same name of DUAL and also it will work like same of dual table.

What are group functions and how many types of group function?

Group functions return the single result on based on many rows. Like count, sum, min, max, avg first, last these functions return one row result based on many rows.

What are cursors and how many types of cursors?

Cursor is a pointer variable in a memory and use for DML operations. Cursor basically is a private SQL memory area it is also use to improve the performance of the database.

There are two types of cursors.

1- Implicit cursor

2- Explicit cursor

Implicit cursor use oracles to manipulate the DML opperations and programmer have no control on this type of cursor. We use sql%notfound and sql%rowcount in implicit cursor. Explicit cursors are created by the programmer and programmer can control it by using these keywords Fetch, Open and close.

Define Oracle cursor attributes.

There are five types of cursors attributes

1- %isopen (Verify whether this cursor is open or not)

2- %found (If cursor fetch the data then %found return true)

3- %notfound (If cursor fetches not data then %notfound return true)

4- %rowcount (It return no. of rows that are in cursor and also give position of record)

5- %bulk_rowcount (%bulk_rowcount is same like %rowcount but it is used in bulk)

How can we pass variable in cursors?

We can pass the variable in cursor by using parameter cursor. Just we give the variable in it like cursor C1 (my_variable number).

What is the CASE statement and where we can use it?

CASE statement is just like IT-THEN-ELSE condition it is normally use when we are using some type of if else condition. When condition is founded true the case statement return the result and no further evaluating of data.

What is INDEX, when and where we can use it?

Index is using for performance tuning and also give us faster data retrieval of record from tables. Index can create on any column in table by default oracle B-Tree index.

Which operator that are not coming in Indexing?

NOT IN operator is not coming in indexing.

How we can get limited rows in SQL?

We can get limited rows in oracle database by using ROWNUM or HAVING clause.

What is the difference between Sub query and Co-related query?

One query contains more than one sub-query. The inner most query run first then second outer then third outer and so on. If we will not use relation on inner query to outer query then this query is called sub-query if we use relation then this query called Correlated query.

Example of Sub Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT)

Example of Correlated Query:

SELECT EMP_NAME, DEPTNO FROM EMP

WHERE EMP_NAME IN (SELECT EMP_NAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO)

What is exception handling and how we can handle the exception in Oracle?

Exception is an error situation which arises during program execution. When an error occurs exception is raised normally execution is stopped and control transfers to exception handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally defined User-defined exception. In oracle we can handle exception by using these statements.

EXCEPTION WHEN

DUP_VAL_ON_INDEX

NOT_LOGGED_ON

NO_DATA_FOUND

TOO_MANY_ROWS

VALUE_ERROR

Can we define more than one exception in one block?

Yes we can define more than one exception in one block in oracle form builder like we will use DUP_VAL_ON_INDEX and NO_DATA_FOUND simultaneously