SQL/ORACLE interview Questions

What is a Procedure ?

A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a specific problem or perform a set of related tasks.

What is difference between Procedures and Functions ?

A Function returns a value to the caller where as a Procedure does not.

Difference between procedure and function.?

Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed

What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL

construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

What is a outer join?

Outer Join--Its a join condition used where you can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.

What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

What is the difference between candidate key, unique key and primary key.

Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.

What is SAVE POINT ?

For long transactions that contain many SQL statements, intermediate markers or savepoints can be declared which can be used to divide a transaction into smaller parts. This allows the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction.