New PL SQL Interview Questions - Entry Level Interview Questions for Database Developers

SQL PL/SQL Question 1

How to display row number with records?

Select rownum, ename from emp;

SQL PL/SQL Question 2

How to view version information in Oracle?

Select banner from v$version;

SQL PL/SQL Question 3

How to find the second highest salary in emp table?

select min(sal) from emp a
where 1 = (select count(*) from emp b where a.sal <>

SQL PL/SQL Question 4

How to delete the duplicate rows from a table?

create table t1 ( col1 int, col2 int, col3 char(1) );
insert into t1 values(1,50, ‘a’);
insert into t1 values(1,50, ‘b’);
insert into t1 values(1,89, ‘x’);
insert into t1 values(1,89, ‘y’);
insert into t1 values(1,89, ‘z’);
select * from t1;

Col1	Col2	Col2
1	50	a
1	50	b
2	89	x
2	89	y
2	89	z

delete from T1
where rowid <> ( select max(rowid)
from t1 b
where b.col1 = t1.col1
and b.col2 = t1.col2 ) 3 rows deleted.

select * from t1;

Col1	Col2	Col2
1	50	a
2	89	z

will do it.

SQL PL/SQL Question 5

How to select a row using indexes?

You have to specify the indexed columns in the WHERE clause of query.

SQL PL/SQL Question 6

How to select the first 5 characters of FIRSTNAME column of EMP table?

select substr(firstname,1,5) from emp

SQL PL/SQL Question 7

How to concatenate the firstname and lastname from emp table?

select firstname ‘ ‘ lastname from emp

SQL PL/SQL Question 8

What's the difference between a primary key and a unique key?

Primary key does not allow nulls, Unique key allow nulls.

SQL PL/SQL Question 9

What is a self join?

A self join joins a table to itself.

Example

SELECT a.last_name Employee, b.last_name Manager
FROM employees a, employees b
WHERE b.employee_id = a.manager_id;

SQL PL/SQL Question 10

What is a transaction and ACID?

Transaction - A transaction is a logical unit of work. It must be commited or rolled back.
ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.

SQL PL/SQL Question 11

How to add a column to a table?

alter table t1 add sal number;
alter table t1 add middle_name varchar(20);

SQL PL/SQL Question 12

Is it possible for a table to have more than one foreign key ?
A table can have any number of foreign keys. It can have only one primary key .

SQl PL/SQL Question 13

How to display number value in words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

SQL PL/SQL Question 14

What is candidate key, alternate key, composite key.

Candidate Key A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.
Alternate KeyIf the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
Composite Key: - A key formed by combining at least two or more columns is called composite key.

SQL PL/SQL Question 15

What's the difference between DELETE TABLE and TRUNCATE TABLE commands? Explain drop command.

Both Delete and Truncate will leave the structure of the table. Drop will remove the structure also.

1. 
   

   Example

   If tablename is T1.
   To remove all the rows from a table t1.
   Delete t1
   Truncate table t1
   Drop table t1.
   
2. Truncate is fast as compared to Delete. DELETE will generate undo information, in case of rollback, but TRUNCATE will not.
   


3. Full Table scan and index fast scan read data blocks up to high water mark and truncate resets high water mark but delete does not.So full table scan after Delete will not improve but after truncate it will be fast.
   
4. Delete is DML. Because truncate is a DDL, it performs implicit commit. You cannot rollback a truncate. Any uncommitted DML changes will also be committed with the TRUNCATE.
   
5. You cannot specify a WHERE clause in the TRUNCATE statement, but you can specify that in Delete.
   
6. When you truncate a table the storage for the table and all the indexes can be reset back to its initial size,but a Delete will never shrink the size of the a table or its indexes.
   

About Dropping
Dropping a table removes the data and definition of the table. The indexes, constraints, triggers, and privileges on the table are also dropped. The action of dropping a table cannot be undone. The views, materialized views or other stored programs that reference the table are not dropped but they are marked as invalid.

SQL PL/SQL Question 16

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Procedures and functions are stored in compiled form in database.
Functions take zero or more parameters and return a value. Procedures take zero or more parameters and return no values.
Both functions and procedures can take or return zero or more values through their parameter lists.
Another difference between procedures and functions, other than the return value, is how they are called. Procedures are called as stand-alone executable statements:
my_procedure(parameter1,parameter2...);
Functions can be called anywhere in an valid expression :
e.g
1) IF (tell_salary(empno) < 500 ) THEN … 2) var1 := tell_salary(empno); 3) DECLARE var1 NUMBER DEFAULT tell_salary(empno); BEGIN …
Packages contain function , procedures and other data structures.
There are a number of differences between packaged and non-packaged PL/SQL programs. Package The data in package is persistent for the duration of the user’s session.The data in package thus exists across commits in the session.
If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package. You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.

SQL PL/SQL Question 17

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE associates a variable to an entire table row.
The %TYPE associates a variable with a single column type.

SQ PL/SQL Question 18

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the current database error number. These error numbers are all negative, except NO_DATA_FOUND, which returns +100.
SQLERRM returns the textual error message.. These are used in exception handling.

SQL PL/SQL Question 19

How can you find within a PL/SQL block, if a cursor is open?

By the Use of %ISOPEN cursor variable.

SQL PL/SQL Question 20

How do you debug output from PL/SQL?

By the use the DBMS_OUTPUT package.
By the use of SHOW ERROR command, but this only shows errors.
The package UTL_FILE can also be used.

SQL PL/SQL Question 21

What are the types of triggers?

Use Row and Statement Triggers

Use INSTEAD OF Triggers

SQL PL/SQL Question 22

Explain the usage of WHERE CURRENT OF clause in cursors ?

It refers to the latest row fetched from a cursor in an update and delete statement.

SQL PL/SQL Question 23

Name the tables where characteristics of Package, procedure and functions are stored ?

User_objects, User_Source and User_error.

SQL PL/SQL Question 24

What are two parts of package ?

They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package also contains a package body, which contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.

SQL PL/SQL Question 25

What are two virtual tables available during database trigger execution ?

The table columns are referred as OLD.column_name and NEW.column_name.
For INSERT only TRIGGERS NEW.column_name values ARE only available.
For UPDATE only TRIGERS OLD.column_name NEW.column_name values ARE only available.
For DELETE only TRIGGERS OLD.column_name values ARE only available.v

SQL PL/SQL Question 26

What is Overloading of procedures ?

REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.

SQL PL/SQL Question 27

What are the return values of functions SQLCODE and SQLERRM ?

SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

SQL PL/SQL Question 28

Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?

It is not possible.,because of the side effect to transactions. You can use them indirectly by calling procedures or functions .

SQL PL/SQL Question 29

What are the modes of parameters that can be passed to a procedure ?

IN, OUT, IN-OUT parameters.