Oracle Interview Questions: Conquer Your Next Interview with This Comprehensive Guide

It is necessary to know SQL because people who are good at it are in high demand and are paid well in the market. Oracle is a very popular secured database that is widely used across multinational companies. That’s why this article on Oracle interview questions will help you prepare for the interview by going over the most common questions that are asked.

If you are new or have been in the field for a while, this is the right platform for you to start preparing.

Select rownum <fieldnames> from table;

Oracle Basic Interview Questions

Q1. How will you differentiate between Varchar & Varchar2?

The two Varchar To point out the major differences between these,.

Q2. What are the components of logical database structure in Oracle database?

The components of the logical database structure in Oracle database are:

• Tablespaces: A database’s main logical storage unit is its tablespaces. This tablespace is a set of related logical structures. To be more specific, logical structures and tablespace groups are linked.
• Database schema objects: A schema is a set of database objects that belong to a certain user. The objects include tables, indexes, views, stored procedures, etc. You are the account in Oracle, and the object is the schema. It is also possible for database platforms to have a schema that wasn’t chosen by a user.

Q3. Describe an Oracle table

A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.

The data types for columns 1 through n should be [NULL | NOT NULL], column 2 should also be [NULL | NOT NULL], and so on.

• name_of_table: This is the name of the table you want to make.
• column. In this case, it tells you how many columns to add to the table. There, each column needs to have a datatype, which should be “NULL” or “NOT NULL.” This means that if the value is blank, it is automatically taken to be “NULL.”

Q4. Explain the relationship among database, tablespace and data file?

An Oracle database possesses one or more logical storage units called tablespaces. Each tablespace in Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. When we talk about datafiles, they are the physical structure that tells the operating system which Oracle program is running.

Q5. What are the various Oracle database objects?

These are the Oracle Database Objects:

Tables: This is a set of elements organized in a vertical and horizontal manner. Tablespaces: It is a logical storage unit in Oracle. Views: Views are a virtual table derived from one or more tables. Indexes: This is a performance tuning method to process the records. Synonyms: It is a name for tables.

Q6. Explain about the ANALYZE command in Oracle?

This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

• The analyze command finds rows that have been moved or chained in a table or cluster.
• It is used to validate the structure of an object.
• This helps gather information about the object that the user is using, which is then saved in the data dictionary.
• It can also be used to get rid of statistics that an object needs from the data dictionary.

Q7. What types of joins are used in writing subqueries?

This means to literally join and return certain rows of data from two or more tables in a database. A join is used to compare and combine.

There are three types of joins in SQL that are used to write the subqueries.

• Some tables have foreign keys that point to their own primary keys, which is called a “self join.” This type of join joins two tables together.
• Outer Join: An outer join helps find data that matches and some data that doesn’t match from two tables.
• A join that has an equality operator in the join condition is called an equijoin. An equijoin only shows the rows where the values in the columns you choose are the same.

Q8. RAW datatype in Oracle

The RAW datatype in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table in 32767 bytes.

You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL doesn’t know what kind of data it is, so when RAW data is sent to different systems, it can’t be changed. This data type can only be queried or can be inserted in a table.

Q9. What is the use of Aggregate functions in Oracle?

When you use an Oracle aggregate function, the values of several rows or records are added together to make a single value. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations. Some common Aggregate functions are:

Q10. Explain Temporal data types in Oracle

Oracle mainly provides these following temporal data types:

• Date Data Type: Different formats of Dates.
• TimeStamp Data Type: Has different formats of Time Stamp.
• Interval Data Type: Interval between dates and time.

Q11. What is a View?

A view is a logical table based on one or more tables or views. People sometimes call a View a user-defined database object. It stores the results of an SQL query so that they can be searched again later. It’s possible to call a view a “logical table” because it doesn’t store data physically but as a virtual table. The corresponding tables upon which the views are signified are called Base Tables and this doesn’t contain data.

Q12. How to store pictures on to the database?

It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.

Q13. Where do you use DECODE and CASE Statements?

The if-then-else statement and these two statements, Decode and Case, will both work the same way. They are also the alternatives for each other. These functions are used in Oracle for data value transformation.

Example:

Select OrderNum, DECODE (Status,’O’, ‘Ordered’,’P’, ‘Packed,’ S’,’ Shipped’, ’A’,’Arrived’) FROM Orders;

SELECT OrderNum, Case(If Status = “O” Then “Ordered” If Status = “P” Then “Packed” If Status = “S” Then “Shipped” Otherwise “Arrived”); end FROM Orders;

Both these commands will display Order Numbers with their respective Statuses like this,

Status O= Ordered Status P= Packed Status S= Shipped Status A= Arrived

Q14. What do you mean by Merge in Oracle and how can you merge two tables?

Merge statement is used to merge the data from two tables subsequently. Based on the condition given in the query, it picks the data from the source table and then adds or changes it in the other table. It is also useful in data warehousing applications.

Q15. What is the data type of DUAL table?

The Dual table is basically a one-column table that is present in the Oracle database. This table has a single Varchar2(1) column called Dummy which has a value of ‘X’.

Q16. Explain about integrity constraint?

An integrity constraint is actually a declaration that is defined as a business rule for a table column. They are used to ensure accuracy and consistency of data in the database. It can also be called as a declarative way to define a business rule for a table’s column. There are a few types, namely:

• Domain Integrity
• Referential Integrity
• Domain Integrity

Q17. What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:

• Select: Data Retrieval
• Insert, Update, Delete, Merge: Data Manipulation Language (DML)
• Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)
• Commit, Rollback, Savepoint: Transaction Control Statements
• Grant, Revoke: Data Control Language (DCL)

Q18. Briefly explain what is Literal? Give an example where it can be used?

A literal is a string that has a character, a number, or a date in it and is in the Select list. It is not a column name or an alias for a column.

It’s also important to remember that date and character literals need to be enclosed in single quotes (‘ ‘), but number literals don’t.

For example: Select last_name||’is a’||job_id As “emp details” from employee;

In this case, “is a” is literal.

Q19. How to display row numbers with the records?

In order to display row numbers along with their records numbers you can do this:

This above query will display the row numbers and the field values from the given table.

This query will display row numbers and the field values from the given table.

Q20. What is the difference between SQL and iSQL*Plus?

Q21. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available. They are:

• Single-Row functions: These work on a single row and return a single result for each row.

Types of Single-Row functions are:

• Character
• Number
• Date
• Conversion
• General

• Functions that work on groups of rows to give one result per group of rows are called multiple-row functions.

Types of Multiple-Row functions:

• avg
• count
• max
• min
• sum
• stddev
• variance

Q22. Describe different types of General Function used in SQL?

General functions are of following types:

• NVL: Converts a null value to an actual value. NVL (exp1, exp2) . If exp1 is empty, the NVL function will return the value of exp2.
• It checks to see if exp1 is null and returns exp2 if it is. If it is null, it returns exp3 The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3).
• NULLIF checks if two expressions are equal and returns null if they are. If they are not equal, it returns the first expression. NULLIF (exp1, exp2).
• COALESCE: Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). One thing that makes the COALESCE function better than the NVL function is that it can take more than one value.
• Conditional expressions let you use IF-THEN-ELSE logic in an SQL statement. Example: CASE Expression and DECODE Function.

Q23. What is a Sub Query? Describe its Types?

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in where having and from clause.

Guidelines for using subqueries:

• You should enclose the sub-queries within parenthesis.
• They should be put on the right side of the comparison condition.
• Use Single-row operators with single-row subqueries.
• Use Multiple-row operators with multiple-row subqueries.

Types of subqueries:

• Single-Row Subquery: These are inner select queries that only return one row. Single-row comparison operators are: =, >, >=, <, <=, <>.
• Multiple-Row Subquery: These are queries that ask the inner Select statement to return more than one row. There are also subqueries with more than one column that return more than one column from the inner select statement. Operators include: IN, ANY, ALL.

Q24. What is the use of Double Ampersand (&&) in SQL Queries? Give an example

You can use && if you want to reuse the variable value without prompting the user each time.

For example: Select empno, ename, &&column_name from employee order by &column_name;

Q25. Describe VArray

VArray is an Oracle data type that can hold a bounded array of values and is used for columns with multivalued attributes. All Varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a Varray has an index associated with it. It has a maximum size (max_size) that can be changed dynamically.

Q26. What are the attributes of the Cursor?

In Oracle, each Cursor has a set of properties that let an application program check the Cursor’s state. You can use the attributes to see if the cursor is open or closed, if it is found or not found, and to find out how many rows there are.

Q27. Name the various constraints used in Oracle

These are the following constraints used:

• NULL: This is used to show that a certain column can have NULL values.
• NOT NULL: This is used to show that a certain column can’t have NULL values.
• CHECK: Make sure the values in the given column meet the requirements.
• DEFAULT: This is used to show that the value is set to a default value.

Q28. What is the fastest query method to fetch data from the table?

The fastest query method to fetch data from the table is by using the Row ID. A row can be fetched from a table by using RowID.

Q29. Difference between Cartesian Join and Cross Join?

There are no such differences between these Joins. Cartesian and Cross join are the same.

Cross join gives a cartesian product of two tables i. e. , the rows from the first table is multiplied with another table that is called cartesian product.

Cross join without the where clause gives a Cartesian product.

Q30. How does the ON-DELETE-CASCADE statement work?

When you delete a record from the parent table, this On Delete Cascade will delete the same record from the child table as well. This statement can be used with Foreign Keys as well.

You can add this On Delete Cascade option on an existing table.

Syntax:

Alter Table Child_T1 ADD Constraint Child_Parent_FK References Parent_T1(Column1) ON DELETE CASCADE;

Now let’s move on to the next part of this Oracle Interview Questions article.

Oracle PL/SQL Interview Questions

Q31. What is PL SQL?

PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle. It takes the power of SQL to change data and the processing power of procedural language to make super-powerful SQL queries. Telling the compiler what to do in SQL and how to do it in its own way is what PL SQL means.

Q32. Enlist the characteristics of PL/SQL?

There are a lot of characteristics of PL/SQL. Some notable ones among them are:

• PL/SQL is a block-structured language.
• It is portable to all environments that support Oracle.
• PL/SQL is integrated with the Oracle data dictionary.
• Stored procedures help better sharing of application.

Q33. What are the data types available in PL/SQL?

There are two data types available in PL/SQL. They are namely:

Example: Char, Varchar, Boolean, etc.

Example: Record, table etc.

Q34. What are the uses of a database trigger

Triggers are the programs which are automatically executed when some events occur:

• Implement complex security authorizations.
• Drive column values.
• Maintain duplicate tables.
• Implement complex business rules.
• Bring transparency in log events.

Q35. Show how functions and procedures are called in a PL SQL block

Some procedures have a return statement that sends control back to the calling block. However, these procedures cannot send any values back through the return statement. Calling them from a Select statement is not possible, but you can do it from another block or with the EXEC keyword.

The process can be called in three ways: a) from the CALL directive; b) from the calling environment; and c) from other processes, functions, or packages.

Functions can be called in the following ways a) Execute from calling environment. Always use a variable to get the return value. b) As part of an SQL/PL SQL Expression.

Q36. What are the two virtual tables available at the time of database trigger execution?

Columns are referred as Then.column_name and Now.column_name.

• INSERT related triggers, Now.column_name values are available only.
• DELETE related triggers, Then.column_name values are available only.
• UPDATE related triggers, both Table columns are available.

Q37. What are the differences between Primary Key and Unique Key?

Q38. Explain the purpose of %TYPE and %ROWTYPE data types with the example?

People who use PL/SQL can inherit the data types of a table that are set up in a database by using the ROWTYPE and 20%TYPE attributes. The main purpose of using these attributes in Oracle is to provide data independence and integrity. Do keep in mind that if any of the datatypes in the database change, the PL/SQL code automatically applies the new datatypes.

%TYPE: This tells the computer that a variable needs to have the same data type as a table column. %20ROWTYPE: This describes a full row of records with a structure similar to that of a table.

Q39. Explain the difference between Triggers and Constraints?

Triggers are very different from Constraints in the following ways:

Q40. Exception handling in PL/SQL

When an error occurs in PL/SQL, the corresponding exception is raised. This also means that error-handling code is built into the program to deal with situations where PL/SQL scripts end unexpectedly. In PL/SQL, all exception handling code is placed in the Exception section.

There are 3 types of Exceptions:

• Predefined Exceptions: Common errors with predefined names.
• Undefined Exceptions: Less common errors with no predefined names.
• User-defined exceptions don’t give runtime errors, but they do break business rules.

Top 50 Oracle Interview Questions and Answers | Questions for Freshers and Experienced | Edureka