PLSQL interview questions part 1

Q:  What is PL/SQL?

A:	Procedural Language/Structured Query Language (PL/SQL) is a procedural language. It is the native database programming language within Oracle utilized by several Oracle development tools.

Q:  What is the difference between SQL and PL/SQL?

A:

SQL is a structured query language. It contains SELECT, INSERT, UPDATE, and DELETE statements. SQL is an ANSI standard tool and is widely used by relational databases such as Oracle, Informix, DB2, and so on. PL/SQL is a block-structured programming language, and the use of this tool is limited to the Oracle database and Oracle development tools. In SQL, statements are processed by the database one at a time, whereas in PL/SQL, multiple SQL statements can be included in a single block and processed together in the database at the same time. This reduces the frequency of database calls. By doing so, PL/SQL obviously improves its performance. PL/SQL also has additional features such as control statements, iterative statements, error handling, procedures, functions, and so on.

Q:  What is DDL?

A:	DDL stands for Data Definition Language. You can create or drop the objects using DDL. Examples include CREATE TABLE, DROP TABLE, RENAME TABLE, CREATE VIEW, and DROP VIEW.

Q:  What is DML?

A:	DML stands for Data Manipulation Language. You can manipulate the object's data. Examples include INSERT, SELECT, UPDATE, and DELETE.

Q:  What is DCL?

A:	DCL stands for Data Control Language. You can control access to the data or to the database. Examples include GRANT and REVOKE.

Q:  What is ROWID?

A:	ROWID, an Oracle-defined column, contains the data block address. It can be used to directly point to where the data physically resides on the disk. It can also improve the performance of SQL transactions when referenced in the SQL statement.

Q:  What does a PL/SQL block contain?

A:	A PL/SQL block contains three sections: the declaration section, the program code section, and the error-handling section. The following is a listing of the typical PL/SQL block structure:

Q:  What is a loop? How many types of loops are available in PL/SQL?

A:	A loop is an iterative statement and can be used to process the code within a block repeatedly until it satisfies the condition. A FOR loop repeats a specified number of times.

Q:  What is the control statement in PL/SQL?

A:	The IF...THEN...ELSE statement can be used to evaluate more than one condition. Here's an example: IF (x  10) THEN Y := TRUE; ELSE Y := FALSE; END IF;

Q:  What is a cursor?

A:	A cursor is a temporary work area (a context area) in memory where a database holds the current SQL statement.

Q:  What is the difference between implicit cursors and explicit cursors?

A:	Implicit cursors are defined by the Oracle database, while users define the explicit cursors that are used to process queries that return multiple data records. By declaring explicit cursors, you obviously get an improved performance.

Q:  How do you define an explicit cursor?

A:	It must be defined in a four-step process: declare the cursor, open the cursor, fetch the cursor, and close the cursor.

Q:  What is the difference between procedures and functions?

A:	Functions can return a value, whereas procedures cannot. The function value is returned through the use of the RETURN command. Functions can be used as part of an expression.

Q:  What are the advantages of stored procedures and functions?

A:	It provides consistency, security, easy maintenance, and better performance. It is centrally located in the database, views the source code through the data dictionary, and reduces network traffic.

Q:  What is a package?

A:

A package is a group of objects, such as procedures, functions, variables, cursors, and exceptions. A package usually consists of two components: a specification and a body. The specification component has the declaration of variables, cursors, procedures, functions, and exceptions. The body component has the definition of the declared elements and it implements the specification component.

Q:  What is an exception?

A:	An exception is an error that occurs during run time. When an error is encountered, the program control automatically goes to an error-handling section. This section always starts with an EXCEPTION reserved word and terminates with the END command.

Q:  What is a transaction?

A:	A transaction is a logical unit of work. In Oracle, two types of transactions exist: commit and rollback. Commit submits the transaction to the database, while rollback works like an undo command.

Q:  What is SAVEPOINT?

A:	SAVEPOINT is an intermediate point within a transaction to which you can rollback.

Q:   What is SQL*Plus?

A:  SQL*Plus, an Oracle tool, is an extension of SQL. SQL* Plus is used to connect to an Oracle database. The user can also use the tool to process SQL queries.

Q:   What is a trigger?

A:  A trigger is a procedure that is executed when a specific event occurs, such as when a table is inserted, updated, or deleted.

Q:   What is a view?

A:  A view is an overlay for tables. Views and tables are queried and accessed in the same way as a table. Views make it possible to hide the actual name of a table as well as fields that a user should not access.

Q:   What is a procedure?

A:  A procedure is a block of PL/SQL statements that is called by applications. A procedure allows the user to store frequently used commands for easy access later.

Q:   What is the purpose of an index? 

A:  An index is used to store data in a specific way in a table which will permit easy retrieval of data. An index to a database is similar to an index in a book; it allows the user to immediately access the information he or she is seeking without having to read every page. Indexes sort one or more fields in a database in ascending or descending order.

Q:   What is data normalization? 

A:  The goal of data normalization is to eliminate redundant data in tables. For example, in a payroll table where the hourly rate of $60 per hour is stored in a new field for each and every supervisor, a table can be created that is used to retrieve the hourly rate by the use of a join. This configuration will allow changes to be made once rather than in multiple locations for all supervisors in the table.

Q:   What is a package? 

A:  A package is used to store procedures and functions in one place.

Q:   What is a tablespace? 

A:  A tablespace is a logical division of a database. Each database must at least have a system tablespace.

Q:   What is a cluster? 

A:  A cluster is used to store tables that are frequently accessed together. Clusters enable better query performance.