Oracle Interview Question

Most Asking 50 Oracle interview questions and answers with examples 

1. What is Oracle Database?

Oracle Database is a relational database management system (RDBMS) that stores and manages data

2. What is the difference between SQL and PL/SQL?

SQL (Structured Query Language) is used to query and manipulate data, while PL/SQL (Procedural Language/SQL) is used to create stored procedures and functions.

3. Explain normalization in Oracle.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves breaking tables into smaller, related tables.

4. What is an Oracle tablespace?

An Oracle tablespace is a logical storage container that contains data files where database objects, like tables and indexes, are stored.

5. How do you create a new table in Oracle?

Example:

CREATE TABLE employees (

    employee_id NUMBER,

    first_name VARCHAR2(50),

    last_name VARCHAR2(50)

);

6. What is an Oracle index, and why is it used?

An Oracle index is a database object that improves the speed of data retrieval operations by providing a fast access path to rows in a table.

7. Explain the difference between UNION and UNION ALL in SQL.

UNION combines the result sets of two or more SELECT statements, removing duplicate rows. UNION ALL also combines result sets but includes duplicates.

8. How do you retrieve the Nth highest salary from a table?

Example:

SELECT DISTINCT salary

FROM (

    SELECT salary, RANK() OVER (ORDER BY salary DESC) AS ranking

    FROM employees

)

WHERE ranking = N;

9. What is a primary key in Oracle, and why is it important?

A primary key is a constraint that enforces uniqueness of values in a column. It's important because it ensures data integrity and provides a unique identifier for each row in a table.

10. How do you create a primary key constraint on a table?

- Example:

sql ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

11. Explain the ACID properties of a transaction in Oracle.

- ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable and maintain data integrity.

12. What is a stored procedure in Oracle?

- A stored procedure is a precompiled set of one or more SQL statements that can be executed as a single unit.

13. How do you declare and use a cursor in PL/SQL?

- Example:

plsql DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROM table_name; BEGIN OPEN cursor_name; FETCH cursor_name INTO variable1, variable2; CLOSE cursor_name; END;

14. What is the difference between a view and a table in Oracle?

- A table stores data physically, while a view is a virtual table that is defined by a query and does not store data itself.

15. How do you update data in a table in Oracle?

- Example:

sql UPDATE employees SET salary = 50000 WHERE employee_id = 101;

16. Explain the COMMIT and ROLLBACK statements in Oracle.

- COMMIT saves all pending changes in a transaction, while ROLLBACK undoes changes made in a transaction.

17. What is an Oracle sequence, and why is it used?

- An Oracle sequence is a database object used to generate unique sequential numbers. It is often used to create surrogate keys.

18. How do you create an Oracle sequence?

- Example:

sql CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1 MAXVALUE 1000;

19. What is a foreign key, and how is it used in Oracle?

- A foreign key is a constraint that enforces referential integrity by ensuring that values in one table's column correspond to values in another table's primary key column.

20. Explain the difference between a LEFT JOIN and INNER JOIN in SQL.

- A LEFT JOIN returns all records from the left table and the matched records from the right table. An INNER JOIN returns only the matched records from both tables.

21. How do you find the number of rows in a table in Oracle?

- Example:

sql SELECT COUNT(*) FROM table_name;

22. What is a subquery in SQL, and how is it different from a JOIN?

- A subquery is a query nested inside another query. It can be used to retrieve data for use in the main query. A JOIN is used to combine rows from two or more tables based on a related column.

23. How do you add a new column to an existing table in Oracle?

- Example:

sql ALTER TABLE table_name ADD new_column_name data_type;

24. What is the difference between a function and a procedure in Oracle?

- A function returns a value, while a procedure does not return a value but can perform actions within the database.

25. Explain the purpose of the Oracle Data Dictionary.

- The Oracle Data Dictionary is a set of tables and views that contain metadata about the database, such as information about tables, columns, indexes, and privileges.

26. How do you delete data from a table in Oracle?

- Example:

sql DELETE FROM employees WHERE employee_id = 101;

27. What is a synonym in Oracle, and why is it used?

- A synonym is an alternative name for a table, view, sequence, or other database object. It is used for security, abstraction, and simplifying object references.

28. Explain the concept of a deadlock in Oracle.

- A deadlock occurs when two or more transactions are unable to proceed because they are waiting for a resource held by another transaction. It can result in a standstill in the database.

29. How do you create a unique constraint on a table in Oracle?

- Example:

sql ALTER TABLE table_name ADD CONSTRAINT uc_column_name UNIQUE (column_name);

30. What is the purpose of the Oracle listener?

- The Oracle listener is a process that listens for incoming database connection requests and manages the communication between clients and the Oracle database.

31. Explain the difference between a full outer join and a cross join in SQL.

- A full outer join returns all records when there is a match in either the left or right table and fills in NULL values where no match is found. A cross join returns the Cartesian product of two tables.

32. How do you find the maximum value in a column in Oracle?

- Example:

```sql

SELECT MAX(column_name)