Oracle Interview Questions

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

SQL (Structured Query Language):

Primarily used for data definition, manipulation, and retrieval.

Focuses on declarative statements like SELECT, INSERT, UPDATE, DELETE.

PL/SQL (Procedural Language/SQL):

An extension of SQL with procedural programming constructs.

Includes control flow statements (IF-THEN-ELSE, loops), variables, functions, procedures, and triggers.

Offers more complex logic and improved performance for database operations.

2. Explain different data types in Oracle.

• Number: Represents numeric values (e.g., integers, decimals, floats).
• Char: Stores fixed-length character strings.
• Varchar2: Stores variable-length character strings.
• Date: Stores date and time values.
• Timestamp: Stores date and time with high precision.
• Blob: Stores large binary objects (e.g., images, audio).
• Clob: Stores large character objects.

3. What are constraints in Oracle?

Constraints are rules that enforce data integrity and ensure data accuracy. Common types include:

• Primary Key: Uniquely identifies each row in a table.
• Foreign Key: Establishes a relationship between two tables.
• Unique: Ensures that a column or set of columns has unique values.
• Not Null: Prevents null values from being inserted into a column.
• Check: Enforces a condition on the data in a column.

4. What are indexes in Oracle?

Indexes are data structures that improve the speed of data retrieval. They create a sorted pointer to the data, allowing Oracle to quickly locate specific rows.

5. Explain different types of joins in SQL.

• Inner Join: Returns rows where the join condition is met in both tables.
• Left Join: Returns all rows from the left table and matching rows from the right table.
• Right Join: Returns all rows from the right table and matching rows from the left table.
• Full Outer Join: Returns all rows from both tables, with unmatched rows filled with null values.
  
   
• Self Join: Joins a table to itself to compare rows within the same table.

6. What is a subquery?

A subquery is a nested SELECT statement within another SQL statement. It can be used to:

• Filter data based on conditions in another table.
• Retrieve data that meets specific criteria.
• Calculate summary values.

7. What is a view in Oracle?

A view is a virtual table that is created from a SELECT statement. It provides a simplified or customized view of the underlying data.

8. What is a trigger in Oracle?

A trigger is a stored program that is automatically executed in response to a database event (e.g., INSERT, UPDATE, DELETE).

9. What is a procedure in Oracle?

A procedure is a named block of PL/SQL code that can be executed by calling its name. It can accept input parameters and return output values.

10. What is a function in Oracle?

A function is a named PL/SQL block that returns a single value. It can accept input parameters and perform calculations or data retrieval.

11. Explain the concept of ACID properties in databases.

• Atomicity: All operations within a transaction are executed as a single unit.
• Consistency: Transactions maintain the integrity of the database.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Committed transactions are permanently recorded and survive system failures.

12. What is normalization in databases?

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

13. What are the different levels of data abstraction?

• Physical Level: How data is physically stored on the disk.
• Logical Level: How data is organized and structured.
• View Level: How individual users view and interact with the data.

14. What is a cursor in Oracle?

A cursor is a pointer that moves through a set of rows returned by a SQL statement. It allows you to process the results row by row.

15. What is partitioning in Oracle?

Partitioning divides a large table into smaller, more manageable segments. This can improve query performance, simplify data management, and facilitate parallel processing.

16. What is the difference between DDL and DML?

• DDL (Data Definition Language): Used to define the structure of database objects (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE).
• DML (Data Manipulation Language): Used to manipulate data within tables (e.g., INSERT, UPDATE, DELETE, SELECT).

17. What is the significance of the COMMIT and ROLLBACK statements?

• COMMIT: Permanently saves the changes made by a transaction.
• ROLLBACK: Undoes all changes made by a transaction since the last COMMIT or ROLLBACK.

18. What is the purpose of the WHERE clause in SQL?

The WHERE clause is used to filter rows based on specific conditions.

19. What is the purpose of the GROUP BY clause in SQL?

The GROUP BY clause is used to group rows based on one or more columns. It is often used with aggregate functions (e.g., SUM, AVG, COUNT).

20. What is the purpose of the HAVING clause in SQL?

The HAVING clause is used to filter groups of rows after they have been grouped by the GROUP BY clause.

21. Explain the concept of stored procedures and their advantages.

Stored procedures are pre-compiled blocks of SQL code that can be executed by calling their name. Advantages include:

• Improved performance: Pre-compiled code executes faster.
• Reduced network traffic: Only the procedure name needs to be transmitted.
• Increased security: Stored procedures can be granted specific privileges to users.
• Modularity: Encapsulates complex logic and improves code reusability.

22. What is the difference between a primary key and a unique key?

• Primary Key: Uniquely identifies each row in a table and cannot have null values.
• Unique Key: Ensures that all values in a column or set of columns are unique, but can have null values.

23. What is the purpose of the ORDER BY clause in SQL?

The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

24. What are the different types of indexes in Oracle?

• B-tree index: Most common type, suitable for equality and range searches.
• Bitmap index: Efficient for columns with a limited number of distinct values.
• Function-based index: Created on a function of one or more columns.
• Partition key index: Created on the partitioning key of a partitioned table.

25. What is data warehousing?

Data warehousing is the process of extracting, transforming, and loading (ETL) data from various sources into a central repository for analysis and reporting.

26. What is OLTP (On-Line Transaction Processing)?

OLTP systems are designed to handle a high volume of short transactions, such as online order entry or banking transactions.

27. What is OLAP (On-Line Analytical Processing)?

OLAP systems are designed for complex analysis and reporting on large volumes of historical data, such as sales trends or customer behavior.

28. What is a sequence in Oracle?

A sequence is an object that generates a series of unique numbers. It is often used to assign unique values to primary keys.

29. What is a synonym in Oracle?

A synonym is an alias for a table or view. It can simplify complex table names and improve code readability.

30. What is a materialized view in Oracle?

A materialized view is a pre-computed view that is stored in the database. It can improve query performance for frequently accessed data.

31. What is the difference between CHAR and VARCHAR2?

• CHAR: Stores fixed-length character strings.
• VARCHAR2: Stores variable-length character strings, which can be more efficient for storing data that varies in length.

32. What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword is used to eliminate duplicate rows from the result set.

33. What is the purpose of the UNION operator in SQL?

The UNION operator combines the results of two or more SELECT statements, eliminating duplicate rows.

34. What is the purpose of the UNION ALL operator in SQL?

The UNION ALL operator combines the results of two or more SELECT statements, including duplicate rows.

35. What is the purpose of the INTERSECT operator in SQL?

The INTERSECT operator returns rows that are common to two or more SELECT statements.

36. What is the purpose of the MINUS operator in SQL?

The MINUS operator returns rows that are present in the first SELECT statement but not in the second.

37. What is the purpose of the NVL function in Oracle?

The NVL function replaces null values with a specified value. For example, NVL(column_name, 0) replaces null values in the column_name with 0.

38. What is the purpose of the TO_CHAR function in Oracle?

The TO_CHAR function converts a date or number to a character string.

39. What is the purpose of the TO_DATE function in Oracle?

The TO_DATE function converts a character string to a date.

40. What is the purpose of the ROUND function in Oracle?

The ROUND function rounds a number to a specified decimal place.

41. What is the purpose of the TRUNC function in Oracle?

The TRUNC function truncates a number to a specified decimal place.

42. What is the purpose of the CASE statement in PL/SQL?

The CASE statement allows you to perform different actions based on different conditions.

43. What is the purpose of the DECODE function in Oracle?

The DECODE function is similar to the CASE statement, but it provides a more concise way to perform conditional checks.

44. What is the purpose of the FOR loop in PL/SQL?

The FOR loop iterates a specified number of times.

45. What is the purpose of the WHILE loop in PL/SQL?

The WHILE loop executes a block of code as long as a specified condition is true.

46. What is the purpose of the cursor FOR loop in PL/SQL?

The cursor FOR loop iterates through a set of rows returned by a cursor.

47. What is data integrity?

Data integrity refers to the accuracy, consistency, and validity of data.

48. What is referential integrity?

Referential integrity ensures that the data in related tables is consistent. For example, if a foreign key in one table references a primary key in another table, the foreign key must either match an existing primary key value or be null.

49. What is database security?

Database security involves protecting the confidentiality, integrity, and availability of data. This includes measures such as user authentication, access control, and encryption.

50. How can you improve the performance of SQL queries?

• Use indexes: Create indexes on frequently accessed columns.
• *Avoid using SELECT : Only select the necessary columns.
• Use bind variables: Reduce parsing overhead by using bind variables instead of literal values.
• Optimize SQL statements: Rewrite queries to improve their efficiency.
• Consider partitioning: Partition large tables to improve query performance.