FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / Oracle Interview Questions / Oracle PL/SQL and overall SQL Interview Questions

Oracle SQL & PL/SQL Interview Questions

Admin — Wed, 03 Dec 2008 15:18:54 GMT

How to display row number with records?

Select rownum, ename from emp;

How to view version information in Oracle?

Select banner from v$version;

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 < b.sal) ;

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.

How to select a row using indexes?

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

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

select substr(firstname,1,5) from emp

How to concatenate the firstname and lastname from emp table?

select firstname ‘ ‘ lastname from emp

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

Primary key does not allow nulls,

Unique key allow nulls.

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;

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.

How to add a column to a table?

alter table t1 add sal number;

alter table t1 add middle_name varchar(20);

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 .

How to display number value in words?

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

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.

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.

Example
If tablename is T1.
To remove all the rows from a table t1.
Delete t1
Truncate table t1
Drop table t1.
Truncate is fast as compared to Delete. DELETE will generate undo information, in case of rollback, but TRUNCATE will not.
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.
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.
You cannot specify a WHERE clause in the TRUNCATE statement, but you can specify that in Delete.
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.

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.

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.

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.

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

By the Use of %ISOPEN cursor variable.

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.

What are the types of triggers?

Use Row and Statement Triggers

Use INSTEAD OF Triggers

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.

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

User_objects, User_Source and User_error.

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.

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

What is Overloading of procedures ?

REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.

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.

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 .

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

IN, OUT, IN-OUT parameters.

Basic SQL Interview Questions

Admin — Wed, 03 Dec 2008 15:29:39 GMT

What's SQL

SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.

What's SQL*Plus

SQL*Plus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.

What's NVL

NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.

What's Date Functions

Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.

What's Character Functions

Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.

What's TTITLE & BTITLE

TTITLE & BTITLE are commands to control report headings & footers.

What's COLUMN

COLUMN command define column headings & format data values.

What's BREAK

BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.

What's COMPUTE

command control computations on subsets created by the BREAK command.

What's SET

SET command changes the system variables affecting the report environment.

What's SPOOL

SPOOL command creates a print file of the report.

What's JOIN

JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause. Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table. Self Join joins a table to itself as though it were two separate tables.

What's Union

Union is the product of two or more tables.

What's Intersect

Intersect is the product of two tables listing only the matching rows.

What's Minus

Minus is the product of two tables listing only the non-matching rows.

What is a Correlated Subquery ?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

What are Multiple columns ?

Multiple columns can be returned from a Nested Subquery.

What are Sequences ?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

What are Synonyms ?

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience. Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.