Oracle Database Interview Questions & Answers Set-14

«« Previous
Next »»

1. What is a constraint? Tell me about its various levels.
Ans: Constraints are representators of a column to enforce data entity and consistency. There are two levels :
  1. column level constraint
  2. table level constraint

2. Write an SQL query to find names of employee start with 'A'?
Ans:  SELECT * FROM Employees WHERE EmpName like 'A%'.


3. Write an SQL query to get third maximum salary of an employee from a table named employee_table.
Ans: 
SELECT TOP 1 salary
FROM (
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC ) AS emp
ORDER BY salary ASC;    


4. What is the difference between DELETE and TRUNCATE statement in SQL?
Ans: The main differences between SQL DELETE and TRUNCATE statements are given below:

DELETE
TRUNCATE
DELETE is a DML command. TRUNCATE is a DDL command.
We can use WHERE clause in DELETE command. We cannot use WHERE clause with TRUNCATE
DELETE is slower than TRUNCATE statement. TRUNCATE statement is faster than DELETE statement.
You can rollback data after using DELETE statement. It is not possible to rollback after using TRUNCATE statement.

5. What is ACID property in database?
Ans: ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.


6. What is PL/SQL?
Ans: PL/SQL stands for procedural language extension to SQL. It supports procedural features of programming language and SQL both. It was developed by Oracle Corporation in early of 90's to enhance the capabilities of SQL.


7. What is PL/SQL table? Why it is used?
Ans: 
Objects of type tables are called PL/SQL tables that are modeled as database table. We can also say that PL/SQL tables are a way to providing arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.


8. What are the datatypes available in PL/SQL?
Ans: There are two types of datatypes in PL/SQL:
  1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
  2. Composite datatypes Example are RECORD, TABLE etc.

9. What is the basic structure of PL/SQL?
Ans: PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.

PL/SQL block contains 3 sections.
  1. The Declaration Section (optional)
  2. The Execution Section (mandatory)
  3. The Exception handling Section (optional)

10. What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
Ans
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.

Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.

Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.


11. What is exception? What are the types of exceptions?
Ans: Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.


12. How exception is different from error?
Ans: Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.


13. What is the main reason behind using an index?
Ans: Faster access of data blocks in the table.


14. What are PL/SQL exceptions? Tell me any three.
Ans: 
  1. Too_many_rows
  2. No_Data_Found
  3. Value_error
  4. Zero_error etc.

15. What is the maximum number of triggers, you can apply on a single table?
Ans: 12 triggers.

«« Previous
Next »»