Join Our Email Alerts-Subscribe
Important Note:Login & Check Your Email Inbox and Activate Confirmation Link

Enter Your Email :

Oracle Materials-Free Download

51. In what way we can display employee records who obtain more salary than the average one in the department?

This can be done by the following query:

1 Select * from employee where salary>(select avg(salary)) from dept, employee where

52. What is known as PL SQL?

PL SQL is considered to be a procedural language that has interactive SQL, as well as the procedural programming language which constructs such as iteration and conditional branching. 

53. Describe the difference between TYPE RECORD and % ROWTYPE

The TYPE RECORD has been used when a query gives back the column of various tables or views.

For e.g., TYPE r_emp is RECORD (snosmp.smpno%type,snamesmpsname %type)

e_recsmp %ROWTYPE

Cursor c1 is select smpno, dept from smp;

e_rec c1 %ROWTYPE

54. Describe the uses of the cursor

The cursor is said to be a named private area present in SQL from which the information can be accessed. They are necessary for functioning each row individually for the queries which return multiple rows. It is one of the important PL SQL Interview Questions. 

55. Display cursor code for loops

Cursor states %ROWTYPE as the loop index implicitly. Then it opens a cursor, obtains rows of values from active set in the fields of the record and shuts when all the records are being processed. 

For e.g., FOR smp_rec IN C1 LOOP



56. What are the uses of a database trigger?

A PL/SQL program unit has been associated with a specific database table that is known as a database trigger. Its uses are listed below 

1. Log events transparently

2. Maintaining replica tables

3. Enforce complex business rules

4. Executing complex security authorizations

5. Deriving column values

6. Auditing data modifications

7. Describe the two various kinds of exceptions

Error handling section of PL/SQL block is known as Exception. They contain two types such as predefined and user-defined.

58. Describe Raise_application_error.

It is considered to be a procedure of package DBMS_STANDARD which permits issuing of user_defined error messages from the database trigger or saved sub-program. 

It is considered to be the commonly asked Basic SQL Interview Questions. So prepare well to crack the interview in a successful way. 

59. Display how procedures and functions are known in a PL SQL block.

Function is known as the part of an expression.


Procedure is known as a statement in PL/SQL.


60. Describe two virtual tables that are available during database trigger execution.

Table columns are known as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.

61. Describe the rules to be applied to NULLs during comparisons.

1. NULL is never TRUE or FALSE

2. NULL cannot be unequal or equal to other values

3. If a value present in an expression is said to be NULL, then the expression itself estimates to NULL except for the concatenation operator (||) 

62. Explain the process of PL SQL compiled

The compilation process such as syntax check, bind and p-code generation processes.

The syntax checking examines the PL SQL codes for compilation errors. When all the errors are said to be corrected, a storage address has been assigned to the variables which hold data. It is known as binding. P-code is considered to be a list of instructions for the PL SQL engine. P-code has been saved in the database for the name blocks and has been used the next time it is implemented. 

63. Explain the difference between Syntax and runtime errors.

A syntax error can easily be detected by a PL/SQL compiler. For e.g., incorrect spelling.

A runtime error is managed with the help of exception-handling part in a PL/SQL block. For e.g., SELECT INTO statement, that does not provide back any rows. 

64. Describe Commit, Rollback, and Savepoint.

COMMIT statement

1. Other users may see the data changes made by the transaction

2. The locks accomplished by the transaction have been released

3. The work which was done by the transaction becomes permanent

ROLLBACK statement: it gets issued while the transaction ends. Hence the following is true.

1. The work done in a transaction has been undone as if it was never issued.

2. All locks accomplished by a transaction have been released.

It undoes all the work which was done by the user in a transaction. Using SAVEPOINT, only part of the transaction may be undone. 

65. What is ORDBMS? Can we say oracle as ORDBMS?

The full form of ORDBMS is object relational database management system. This form of database management system is called as a hybrid system as it is used for both objects based database and relational database. It helps to sort and locate files faster. It helps to filter and retrieve the files which share the same characteristics. Yes Oracle is an ORDBMS. Oracle is a vast subject with huge Oracle interview questions which requires constant learning. 

66. What is oracle index?

Oracle index is created with one or more column of the table which is used to have access to the row of the data base. They are used to speed up the queries which access to the small portion of the data base. Oracle allows much number of indexes in the tables to access the different types of queries. For understanding the database management system learn about Pl SQL Interview questions. 

67. Explain the term grid and cluster in Oracle?

A grid is the collection of server or collection of the cluster where the server is running the application server software. The grid infrastructure manages the demand and the supply. The grid consists of the application and the respective linked database server to manage the workload manageable. A Cluster is a single server used to create a grid. A Cluster is a place where the database or application resides and it is responsible for the high performance of the database system. The Cluster provides an alternative server in case of server failure. So many layers or components are used to make the application or the database available. Before attending the interview learn the SQL Server Interview questions to sharpen your knowledge. 

68. Explain how view option is useful in Oracle?

The View is very helpful in seeing the data without storing it in the object, View help to apply the control by hiding some of the columns, it helps to join two or more tables as per the requirement, and ensure the security by controlling the access to tables. A view is a logical table and it does not have data. Start your learning with the Basic SQL Interview questions then learns the advanced level questions for better understanding of the subject. 

69. Explain the different types of queries?

Normal queries, subqueries, co-related queries, nested queries, and compound queries are some of the types of queries. A query is used to execute the operation to one or more tables or views. A Query is a top-level select statement and subquery is nested query written for another SQL statement. The correlated query is the query that is followed by an outer query. When performing the compound query the control is obtained by using the parentheses matching record to be performed. SQL Interview Questions help you to understand the query language required for the database management. 

70. Differentiate clustered index and a non-clustered index?

An index contains the key required for the table or view. The keys are stored in a server to find the row or row associated with the key values. Cluster Index uses the key values to store the data. A non-cluster index stores the data in a logical order which does not match the order of the physically stored data. Learning Oracle DBA interview questions enhances the confidence level to attend the interview with confidence. 

71. What is tablespace?

A tablespace is a storage location where it specifies the database storage location logically. It is related to the logical database or database schema. Each object in the database may have different schema. A tablespace is used to group the different schema. Data is stored logically in tablespaces and physically in the data files. Oracle Interview questions with answers provide you the simple and detailed answers with relevant examples. Learning Oracle interview questions with answers would facilitate the knowledge required for the interview. 

72. What is material view? What is the difference between material view and standard view?

A material view is a database object which shows the results of a query. It can be a data or a subset of the row, column or summary, etc. The standard view uses a query to list the data, whereas material views are used to view the result of a query. The material view is used to reduce the network loads, reduce the computing problems, create an environment for deployment, and group the data. 

73. What is a user-Exit in Oracle?

A user exit is a C subroutine called from Oracle database to do a special process. You can use SQL statements and PL or SQL blocks in your user exit. A user exit is harder to write than SQL or PL/SQL and used for triggers. 

74. Define Roles in Oracle?

A role is used to grant privileges to the users. A role is used to save time and effort to the database system. Roles are helpful to give the right to access another user’s object to execute a particular type of SQL statement. 

75. Describe Oracle instances?

Database instance allocates memory area called as system global area {SGA} and it is used for the background process. It is used for maintaining the internal data which are subject to a processor thread for operations. Store the data blocks from the disk, buffer the redo data, and store the plans to be executed. The database instance can be configured by a single instance configuration and Oracle real application clusters configuration etc.

Source: Contents are provided by Technicalsymposium Google Group Members. 
Disclaimer: All the above contents are provided by Google Group members. 
Further, this content is not intended to be used for commercial purpose. is not liable/responsible for any copyright issues. All Technical Interview Materials