Oracle Interview Questions and Answers

Oracle is a product from Oracle Corporation, that provides a relational database management system. Oracle’s RDBMS supports any data model and has different product editions such as Standard Edition, Enterprise Edition, Express Edition, and Personal Edition, among which the user gets to choose the database system depending on their need. Oracle products are scalable and secure, with high-performance ability, compared to other databases available in the market. Here are some Oracle interview questions and answer list:

1. What is Oracle?

Its database is also known as simply Oracle also. It is a multi-model relational database management system, mainly designed for enterprise grid computing and data warehousing. It is one of the first choices for enterprises for cost-effective solutions for their applications and data management. It supports SQL as a query language to interact with the database.

Currently, its database comes in five different editions based on the features available.

  • Standard Edition One: It is suitable for single-server or highly branched business applications with limited features.
  • Standard Edition: It delivers all facilities provided in Standard Edition One. In addition, it provides larger machine support and Oracle Real Application clustering service.
  • Enterprise Edition: This edition is packed with features like security, performance, scalability, and availability, required for highly-critical applications in which online transaction processing is involved.
  • Express Edition: It is an entry-level edition that is free to download, install, manage, develop and deploy.
  • Personal Edition: It comes with the same Enterprise edition features except Oracle Real Application Clustering.

2. What are the Features of Oracle?

An Oracle database offers the following features to meet the requirements of powerful database management:

  • Application Development
  • Availability
  • Big Data and Data Warehousing Solutions
  • Database Overall
  • Diagnosability
  • Performance
  • RAC and Grid
  • Security

There are a lot more features you can check out on their official website.

3. Why do We Use Oracle?

It is a database management software product. A database contains an organized collection of information. A database management system is not only used for storing the data but to effectively manage it and provides high performance, authorized access and failure recovery features. It provides a software solution that is easy to use and manage database operations, from Personal to Enterprise level applications.

4. What are the components of the physical database structure of the Oracle database?

Components of physical database structure are given below.

  • One or more data files.
  • Two or more redo log files.
  • One or more control files.

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

Components of the logical database structure.

  • Tablespaces
  • Database’s schema objects

6. How will you differentiate between Varchar & Varchar2?

Both Varchar & Varchar2 are the Oracle data types that are used to store character strings of variable length. To point out the major differences between these,

VarcharVarchar2
Can store characters up to 2000 bytesCan store characters up to 4000 bytes.
It will hold the space for characters defined during
the declaration even if all of them are not used		It will release the unused space

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

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

  • Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.
  • Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user-specified.

8. What is a RAW datatype?

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

9. What is the use of the NVL function?

The NVL function is used to replace NULL values with another or given value. Example is –

NVL(Value, replace value)

10. Whether any commands used for Months calculation? If so, What are they?

In Oracle, the months_between function is used to find the number of months between the given dates. Example is –

Months_between(Date 1, Date 2)

11. What are nested tables?

A nested table is a data type in Oracle which is used to support columns containing multi-valued attributes. It also holds the entire sub-table.

12. What is a snapshot in the Oracle database?

A snapshot is a replica of a target master table from a single point in time. In simple words, you can say, a snapshot is a copy of a table on a remote database.

13. How many memory layers are in the Oracle shared pool?

Oracle shared pools contain two layers:

  1. library cache
  2. data dictionary cache

14. 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 virtual tables derived from one or more tables.
  • Indexes: This is a performance tuning method to process the records.
  • Synonyms: It is a name for tables.

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

A Join is used to compare and combine, this means literally joining and returning specific rows of data from two or more tables in a database.

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

  • Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key that references its own primary key.
  • Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
  • Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

Practical Questions

16. Find the error in the below code snippet if any?

SELECT student_id s_id, student_name name, birthdate date, student_number s_no FROM students;

Here, a reserved keyword ‘date’ has been used as an alias for the column birthdate. This action is illegal in Oracle SQL. In order to set a reserved keyword as an alias, we can use quotation marks.

SELECT student_id s_id, student_name name, birthdate “date”, student_number s_no FROM students;

17. Write a query to display a list of tables owned by the user.

The query can be written as:

SELECT tablespace_name, table_name FROM user_tables;

18. How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert the date to the character. You can also specify the format in which you want to output.

SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;  
or
SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;

19. What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.

Let’s see a procedure call that lists two actual parameters named empno and amt:

raise_sal(empno, amt);  

Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body. The following procedure declares two formal parameters named empid and amt:

PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;  

20. How to convert a string to a date in the Oracle database?

Syntax: to_date (string , format) Let us take an example :

to_date ('2022-12-12', 'YYYY/MM/DD')  

It will return on December 12, 2022.

Tags:

Leave a Reply