Oracle Interview questions

• This article contains the Top 49 Oracle Interview questions and answers: Basics, Oracle SQL asked in various interviews.
• These questions cover a wide range of topics relevant to Oracle SQL, including table clusters, SQL commands, data types, functions, joins, and more. Each question is followed by a concise answer, providing a valuable resource for individuals preparing for Oracle SQL interviews.
• I hope it will help in interviews

Question 1.  What is a table Cluster?
Answer: A table Cluster is a group of related tables that share common columns are store related data in the same block.

Question 2. What is EXISTS usage in SQL?

Answer “exists” statement helps us to do the job of the If statement. The main query will get executed if the subquery returns at least one row. So we can consider the subquery as “If condition” and the main query as “code block” inside the If condition. We can use any SQL commands (Joins, Group By, having, etc) in a subquery. This command will be useful in queries that need to detect an event and do some activity

SELECT *
FROM emp
WHERE EXISTS (SELECT *
FROM dept
WHERE emp.dept_id = dept.dept_id);

Question 3. How will you convert a string to a date in the Oracle database?

Answer: We can use the to_date function to convert a string into a date

to_date('2015/11/12', 'yyyy/mm/dd')

Related link

Oracle Date Functions

Question 4. What are Save Points in the Oracle database?

Answer: SAVE POINTS are used to divide a transaction into smaller parts. It enables rolling back part of a transaction. A maximum of five save points are allowed. Whenever we encounter an error we can roll back from the point where we set our SAVEPOINT. This is useful for multistage transactions and conditional transactions where commit and rollback depend on certain conditions.

Question 5. How do you find the current date and time in Oracle?

Answer: we can find using the sysdate function

SQL> SELECT SYSDATE FROM dual;

SYSDATE
---------
2-Nov-16

 

SQL> SELECT
SYSDATE Today, SYSDATE - 1 Yesterday,
SYSDATE + 1 Tomorrow
FROM
dual;

TODAY YESTERDAY TOMORROW
--------- --------- ---------
2-Nov-16 1-Nov-16 3-Nov-16

Question 6. How does a Query execute in Oracle? Can you list the steps involved?
Answer:
The Following steps are involved in parsing SQL queries:
1) parsing
2) binding
3) execute
4) fetch

Question 7. How do you find MONTH and YEAR from a date in Oracle?
Answer: By using the EXTRACT function, you can use it to get MONTH and YEAR from a DATE object.

SELECT EXTRACT(YEAR FROM DATE '2016-01-12')  Year_date FROM DUAL;
Year_date
---------------------------------
2016

Question 8. What is an Oracle view?
Answer: A oracle view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns as tables do. It has the following advantages
1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of the columns in the tables.
3. Join two or more tables and show them as one object to the user.
4. Restrict the access of a table so that nobody can insert the rows into the table.

Question 9. What is the Oracle table?

Answer: Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns

Question 10. Difference between VARCHAR and VARCHAR2 oracle datatypes?

Answer: VARCHAR can store up to 2000 bytes and varchar2 can store up to 4000 bytes. VARCHAR will occupy space for NULL values and VARCHAR2 will not occupy any space. Both are different with respect to space.

Question 11. What is the difference between CHAR and VARCHAR datatype
Answer:
CHAR Data Type is a Fixed Length Data Type. For example, if you declare a variable/column of CHAR (5) data type, then it will always take 5 bytes irrespective of whether you are storing 1 character or 5 characters in this variable or column. And in this example, as we have declared this variable/column as CHAR(5), so we can store a max of 5 characters in this column.

VARCHAR is a variable-length Data Type. For example, if you declare a variable/column of VARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR (10), so we can store a max of 10 characters in this column

Question 12 What is the use of NVL function?

Answer: The NVL function is used to replace NULL values with another or given value.

Example is –

NVL(Value, replace value)

Question 13 What is DML?

Answer: Data Manipulation Language (DML) is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.

Question 14 What is DDL?

Answer: DDL is an abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in the database. it does implicit commit

Question 15. What is the difference between TRANSLATE and REPLACE?

Answer: Translate is used for character by character substitution and Replace is used to substitute a single character with a word.

Question 16. How do we display rows from the table without duplicates?

Answer: Duplicate rows can be removed by using the keyword DISTINCT in the select statement.

Related link

delete duplicate rows in oracle

Question 17. What is a NULL value in Oracle?

Answer: The NULL value represents missing or unknown data. This is used as a placeholder or represented as a default entry to indicate that there is no actual data present.

Question 18. What is the usage of the Merge Statement?

Answer: A merge statement is used to select rows from one or more data sources for updating and insertion into a table or a view. It is used to combine multiple operations.

Question 19. What are the various constraints used in Oracle?

Answer: The Following are constraints used in Oracle:

NULL – It is to indicate that a particular column can contain NULL values
NOT NULL – It is to indicate that a particular column cannot contain NULL values
CHECK – Validate that values in the given column meet the specific criteria
DEFAULT – It is to indicate the value is assigned to the default value

Question 20. What are the different Oracle Database objects?

Answer: There are different data objects in Oracle –

Tables – set of elements organized in vertical and horizontal
Views – Virtual table derived from one or more tables
Oracle Indexes – Performance tuning method for processing the records
Synonyms – Alias name for tables
Oracle Sequences – Multiple users generate unique numbers
Oracle Tablespace – Logical storage unit in Oracle

Question 21. What are materialized views?
Answer A materialized view is a database object that contains the results of a query. It is different from a simple view. These materialized views have data stored and when you query the materialized view, it returns data from the data stored. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

Check below link for Details

Oracle materialized view and Materialized View Log

Question 22. What is a decode statement?

Answer

Oracle  decode is the method in Oracle database to transform data values from one value to another which is better to understand. Oracle Decode transforms data values at retrieval time.

Question 23. What is the difference between TRUNCATE & DELETE?

Answer: Truncate resets the high water mark of the table while delete does not reclaim any space

Question 24 What is a join? Explain the different types of joins?
Answer: An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.
INNER JOIN operation
LEFT OUTER JOIN operation
RIGHT OUTER JOIN operation
CROSS JOIN operation
NATURAL JOIN operation

Various Joins Method in Oracle
Hash join in Oracle
Nested Loop Join in Oracle

Question 25. You have just compiled a PL/SQL package but got errors, how would you view the errors?

Answer dba_errors or show errs

Question 26. What is a transaction?
Answer:

When a session update /delete/insert  ,then a transaction is started. It is completed when the commit or rollback happened. A transaction is identified by a transaction identifier(XID).  The transaction identifies consists of three-part

1. Rollback or undo segment number
2. Transaction table Slot number
3. Sequence or wrap no

What is transaction

Question 27. How can you enable a trace for a session?

Answer :

Normal trace

execute dbms_system.set_sql_trace_in_session (‘sid’,’serial’,true);  — To put tracing on

execute dbms_system.set_sql_trace_in_session (‘sid’,’serial’,true);   — To put tracing off

Full level with wait event And bind trace

execute dbms_system.set_ev(‘sid’,’serial’,10046,12,’’);

To put trace off

execute dbms_system.set_ev(‘sid’,’serial’,10046,0,’’);

How to turn on the trace in database

Question 28. What is the difference between Cartesian Join and Cross Join?

Answer: There are no differences between the join. Cartesian and Cross join are the same. Cross join gives the Cartesian product of two tables – Rows from the first table are multiplied with another table which is called the Cartesian product.

Cross join without where clause gives the Cartesian product.

Question 29.  What is the data type of the DUAL table?

Answer: The DUAL table is a one-column table present in the oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

Question 30. How to deal with duplicate records in the table?

Answer:

create table my_table1 as select distinct * from my_table;
drop my_table;
rename my_table1 to my_table;

Delete from my_table where rowid not in (
select max(rowid) from my_table group by my_col_name );

delete duplicate records in oracle

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

Answer: Row can be fetched from the table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.

Question 32. What are privileges and Grants?

Answer: Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.

Oracle Create User , System Privileges and Oracle Object Privileges

Question 33. What is an integrity constraint?

Answer: An integrity constraint is a declaration to define a business rule for a table column. Integrity constraints are used to ensure the accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity, and Domain Integrity.

Question 34. what is referential integrity?
Answer: A referential integrity rule is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value).

Question 35. What is VARRAY?

Answer: VARRAY  is an oracle data type used to have columns containing multivalued attributes and it can hold a bounded array of values.

Question 36. How do we get field details of a table?

Answer: Describe <Table_Name> is used to get the field details of a specified table.

Question 37.  What is the use of Aggregate functions in Oracle?

Answer: Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –

Average
Count
Sum

Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

Aggregating Data Using Group Functions

Question 38. What do you mean by GROUP BY Clause?
Answer:
A GROUP BY clause can be used in the select statements where it will collect data across multiple records and group the results by one or more columns.

Question 39. What is the difference between rename and alias?
Answer: Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but a replacement of a name and Alias is an alternate name of the table or column.

Question 40. What is COALESCE function?

Answer: COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.

Coalesce(value1, value2,value3,…)

Question 41. What is the difference between PRIMARY KEY, UNIQUE KEY, FOREIGN KEY?
Answer

ORACLE PRIMARY KEY is a column or a combination of columns of a table that can be used to uniquely identify a row of the table. PRIMARY KEY cannot be null.
Oracle UNIQUE KEY is a column or a combination of columns of a table, which can be used to identify a row of the table uniquely. UNIQUE KEY can be null.
FOREIGN KEY in oracle is a column or a combination of columns which is the primary key or unique key of the referenced table. FOREIGN KEY can be null.

Question 42. How can you fetch the first 5 characters of the column in the table?

Answer

This can be done using the Substr function

select substr(ename,1, 5) from emp;

Question 43. What is replace function?

Answer
A replace function is used to replace a string with another string

update EXAM_TAB set col_option_value=replace(col_option_value,'TECH','TEST') where col_option_value like '%TECH%';

Question 44. How do we analyze statistics on the table?

Answer

We generate stats using dbms_stats.gather_table_stats procedure

Question 45. What is Optimizer?

Answer

Optimizer is the engine in the Oracle database which determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query

The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides cost-based (CBO) and rule-based (RBO) optimization. In general, use the cost-based approach. Oracle Corporation is continually improving the CBO and new features require CBO.

Question 46. What are hints in Oracle?

Answer
Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the specific criteria.

SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;

Question 47. What is explain plan?

Answer

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement’s execution plan is the sequence of operations Oracle performs to run the statement. The row source tree is the core of the execution plan.

Question 48. How to change the date format?

Answer

alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Question 49. How to compile the package body and package?

Answer

Alter package <package name > compile;
Alter package <package name > compile body;

Few more questions  on Oracle  interview questions to increase your skills

1. Which two statements about views are true? (Choose two.)
A. A view can be created as read-only.
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.

Answer: A, BA view can be created as a read-only object. However, it is possible to change data in the
underlying table(s) with some restrictions. A view also can be created as a join on two or more
tables. This type of view is called a complex view. Complex views provide complicated data
models where many base tables are drawn together into one virtual table.

2. Which data dictionary table should you query to view the object privileges granted to
the user on specific columns?
A. USER_TAB_PRIVS_MADE
B. USER_TAB_PRIVS
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS
Answer: D

The USER_COL_PRIVS data dictionary view will show the object privileges granted to the
a user on specific columns.

3. For which two constraints does the Oracle Server implicitly create a unique index?
(Choose two.)
A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
D. CHECK
E. UNIQUE
Answer: B, E

Indexes are created automatically by Oracle to support integrity constraints that enforce
uniqueness. The two types of integrity constraints that enforce uniqueness are PRIMARY
KEY and UNIQUE constraints. When the primary key or UNIQUE constraint is declared, a
unique index to support the column’s uniqueness is also created, and all values in all columns
that were defined as part of the primary key or UNIQUE constraint are placed into the index

4. Which two are true about aggregate functions? (Choose two.)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer: C, D

It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also, it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.

5. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause
placed in the SELECT statement?
A. Immediately after the SELECT clause
B. Before the WHERE clause
C. Before the FROM clause
D. After the ORDER BY clause
E. After the WHERE clause
Answer: E

The GROUP BY clause can be placed only after the WHERE clause, or after FROM clause if
there is no WHERE clause in the statement.

6. Which two tasks can you perform using only the TO_CHAR function?
A. convert 10 to ‘TEN’
B. convert ’10’ to 10
C. convert ’10’ to ’10’
D. convert ‘TEN’ to 10
E. convert a date to a character expression
F. convert a character expression to a date
Answer: C, E

7. In which case would you use a FULL OUTER JOIN?
A. Both tables have NULL values.
B. You want all unmatched data from one table.
C. You want all matched data from both tables.
D. You want all unmatched data from both tables.
E. One of the tables has more data than the other.
F. You want all matched and unmatched data from only one table.
Answer: D

8. You are granted the CREATE VIEW privilege. What does this allow you to do?
A. Create a table view.
B. Create a view in any schema.
C. Create a view in your schema.
D. Create a sequence view in any schema.
E. Create a view that is accessible to everyone.
F. Create a view only if it is based on tables that you created.
Answer: C

9. Which constraint can be defined only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Answer: B

10. Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY
Answer: B

Related Articles
Oracle PlSQL interview questions : 25 Oracle PLSQL interview questions with detailed explanations and answer for the success in an interview
Oracle dba interview questions and answers : oracle dba interview questions and answers to succeed in any oracle database interviews
Oracle apps interview questions and answer : 19 oracle apps technical interview questions and answers to succeed in your career

You can try the SQL statement by installing the developer. Download the below SQL developer guide for it

I hope you like this stuff on Oracle interview questions with answers. This is useful for freshers and experienced person both. Please do provide the feedback

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning