100 Oracle Interview Questions

Last updated - 8 August 2022

Q1. What is database?

A database is an organized collection of data.

Q2. What is DBMS?

Database management systems (DBMSs) are computer software applications that interact with the user, other applications, and the database itself to capture and analyze data.

Q3. What is the RDBMS?

A RDBMS is a database management system (DBMS) that is based on the relational model as invented by E. F. Codd.

Relationship among the data is maintained through tables. 

Q4. What is database table?

A table is a set of data values. A table has a specified number of columns, but can have any number of rows.

Q5. What is SQL?

SQL stands for Structured Query Language used to communicate Database.

Q6. What is PL / SQL?

PL/SQL stands for Procedural Language extension of SQL. It is a combination of SQL along with the procedural features of programming languages. 

Q7. What is Function?

Function is the group of the SQL / PL-SQL statements which performs the task and must return value/values to the calling environment.

 

Q8. What is a Stored Procedure?

Store procedure is PL/SQL block which performs one or more specific task. Stored procedure is precompiled SQL statements which will helpful to improve the performance.

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

SP can return multiple result set. Stored procedure may or may not return value.

Q9. What is the difference between SP and function?

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).

2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .

3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

4. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.

5. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

 6.The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.

Reference –

http://www.dotnet-tricks.com/Tutorial/sqlserver/7EDL150912-Difference-between-Stored-Procedure-and-Function-in-SQL-Server.html

Q10. What is Trigger?

A trigger is a pl/sql statements which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.

CREATE or REPLACE TRIGGER price_history_trigger 

BEFORE UPDATE OF unit_price 

ON product 

FOR EACH ROW 

BEGIN 

INSERT INTO product_price_history 

VALUES 

(:old.product_id, 

 :old.product_name, 

 :old.supplier_name, 

 :old.unit_price); 

END; 

Q11. What is Cursors?

A cursor is a temporary work area created in the memory when a SQL statement is executed. A cursor can hold more than one row, but can process only one row at a time.

The set of rows the cursor holds is called the active set.

There are two types of cursors in PL/SQL:

1.    Implicit cursors

Cursors internally created by the Oracle is called as implicit cursors.

2.    Explicit cursors

User created cursor is called as explicit cursor

Q12. What is View?

A view is a virtual table consisting result of select query, it contains no data.

Q13. What is Materialized view?

Every time we use a view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in, unlike a view it contains space and storage just like a regular table.

CREATE MATERIALIZED VIEW my_product

REFRESH FAST START WITH SYSDATE

   NEXT SYSDATE + 1

     AS SELECT * FROM product;

Q14. What is index and their types?

Index is the structure associated with the table for fast retrieval of the information.

1.    Clustered

Clustered index exists as sorted row on disk. 
Clustered index re-orders the table record. 
Clustered index contains record in the leaf level of the B-tree. 
There can be only one Clustered index possible in a table.

2.    Non-clustered

Non-clustered index is the index in which logical order doesn’t match with physical order of stored data on disk. 
Non-clustered index contains index key to the table records in the leaf level. 
There can be one or more Non-clustered indexes in a table.

Q15. What is Package?

A package is object that groups logically related PL/SQL types, items, and subprograms.

Q16. What is Sequence?

Sequences are database objects from which multiple users can generate unique integers. You can use sequences to automatically generate primary key values.

CREATE SEQUENCE product_seq

  MINVALUE 1

  START WITH 1

  INCREMENT BY 1

  CACHE 20;

ALTER SEQUENCE product_seq

INCREMENT BY 100;

If we use cache then that amount of numbers will be get cached in memory. This is useful to increase the performance but disadvantage is if the system failure occurs then all the values in memory will be lost. So, because of this there will be gap in the sequence numbers.

Q17. What is Synonym?

A synonym is an alias or alternative name for a database object.

CREATE PUBLIC SYNONYM s_product

FOR product;

Public means it is accessible by all the users.

Q18. What are Constraints? 

Constraints apply specific rules to data, ensuring the data is in required format. There are a number of different kinds of constraints -

·                     Primary key

·                     Unique

·                     Foreign Key

·                     Default

·                     Check

·                     Not NULL

Q19. What is Primary Key?

It is the constraint which is used to uniquely identify a row in table.

Primary key does not allows NULL.

It is not possible to change the length of the primary key column you have to drop primary key constraint and then you can modify the length.

Q20. What is Unique key?

A unique key in a table enforces uniqueness on set of columns so that no duplicate values are entered.

Unique key allows NULL.

Q21. What is Foreign key?

Primary key one table acts as foreign key in another table. Foreign keys are used to enforce referential integrity.

Q22. What is Default constraint?

This is used to add value in column when it is omitted.

Q23. What is Check Constraint?

It is used to limit the values that can be placed in column. It is used to enforce domain integrity.

Q24. What is NOT NULL constraint?

It is used to enforce that column should not accept NULL values. It is used to enforce domain integrity.

Q25. What are different joins?

Joins can be used to retrieve data from multiple table.

There are 4 different types of Oracle joins:

1.    INNER JOIN

INNER JOINS return all rows from multiple tables where the join condition is met.

2.    LEFT OUTER JOIN

This type of join returns all rows from the LEFT-hand table specified in the ON condition .

3.    RIGHT OUTER JOIN

This type of join returns all rows from the RIGHT-hand table specified in the ON condition.

4.    FULL OUTER JOIN

This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Q26. What is normalization?

It is the process of organizing data to reduce redundancy.

Normalization usually involves dividing the database into different tables and defining relationship between tables.

Objective is to isolate data so that Add Update Delete can be performed on one table and retrieve through whole table via defined relationships.

 Q27. What is first normal form?

A database is in first normal form if it satisfies the following conditions:

• Contains only atomic values
• There are no repeating groups

An atomic value is a value that cannot be divided. For example, a column [Color] contain value “green, red” can be divided into "red" and "green", hence [TABLE_PRODUCT] is not in 1NF.
A repeating group means that a table contains two or more columns that are closely related. For example, a table that records data on a book and its author(s) with the following columns: [Book ID], [Author 1], [Author 2], [Author 3] is not in 1NF because [Author 1], [Author 2], and [Author 3] are all repeating the same attribute.

Q28. What is second normal form?

A database is in second normal form if it satisfies the following conditions:

• It is in first normal form
• All non-key attributes are fully functional dependent on the primary key

Q29. What is third normal form?

A database is in third normal form if it satisfies the following conditions:

• It is in second normal form
• There is no transitive functional dependency

By transitive functional dependency, we mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B.

Q30. What is demoralization?

 Q31. What are ACID properties?

Q32. What is the difference between DELETE, TRUNCATE and DROP?

1.    DELETE is DML Command so need to COMMIT or ROLLBACK whereas TRUNCATE is DCL command

2.    Trigger can be fired after delete but after truncate trigger cannot fired

3.    Using delete you delete the specific or all records whereas truncate will delete all the records from the table

4.    Truncate is quite faster than delete as truncate doesn’t log anything

5.    The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. After drop you need to recreate the table

Q33. Write a query to find employee having nth highest salary?

There are different ways to achieve this in oracle and sql server. In oracle you can user ROWNUM and In SQL Server you can use top to achieve this.

Select TOP 1 salary from

(select top nth salary from employee order by salary DESC) as EMP

Order by salary