Oracle Interview Questions and Answers for freshers

1. What is Oracle Database?

   • Answer: Oracle Database is a relational database management system (RDBMS) produced and marketed by Oracle Corporation. It's a powerful and widely used system for storing, managing, and retrieving data efficiently and securely. It's known for its scalability, reliability, and advanced features like data warehousing, transaction processing, and security.

2. What is SQL?

   • Answer: SQL (Structured Query Language) is a domain-specific language used for managing and manipulating databases. It's used to perform tasks like creating, modifying, and querying databases. Common SQL commands include SELECT, INSERT, UPDATE, and DELETE.

3. What is a table in Oracle?

   • Answer: A table in Oracle is a structured set of data organized into rows (records) and columns (fields). Each column represents a specific attribute, and each row represents a single record containing values for all columns.

4. Explain the difference between CHAR and VARCHAR2 datatypes.

   • Answer: CHAR stores fixed-length strings, padding with spaces if the input is shorter than the defined length. VARCHAR2 stores variable-length strings, only using the space needed for the actual data. VARCHAR2 is generally more efficient for storing text data.

5. What is a primary key?

   • Answer: A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It cannot contain NULL values and ensures data integrity.

6. What is a foreign key?

   • Answer: A foreign key is a column or set of columns in one table that refers to the primary key of another table. It establishes a link between the two tables, enforcing referential integrity.

7. What is a join in SQL?

   • Answer: A join is a SQL clause used to combine rows from two or more tables based on a related column between them. Different types of joins exist, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, each with its own way of handling unmatched rows.

8. Explain the difference between INNER JOIN and OUTER JOIN.

   • Answer: An INNER JOIN returns only the rows where the join condition is met in both tables. An OUTER JOIN (LEFT, RIGHT, or FULL) includes all rows from one or both tables, filling in NULL values where the join condition isn't met.

9. What is a view in Oracle?

   • Answer: A view is a virtual table based on the result-set of an SQL statement. It doesn't store data itself but provides a customized way to access data from one or more underlying tables.

10. What is an index in Oracle?

    • Answer: An index is a separate data structure that improves the speed of data retrieval operations on a table. It's like an index in a book; it helps the database quickly locate specific rows without scanning the entire table.

11. What is a sequence in Oracle?

    • Answer: A sequence is a database object that generates unique numerical values. They are often used to automatically assign unique IDs to rows in a table.

12. What is a synonym in Oracle?

    • Answer: A synonym is an alias for a table, view, sequence, or other database object. It simplifies referencing the object, especially if the object's name is long or complex.

13. What are constraints in Oracle?

    • Answer: Constraints are rules enforced on database tables to ensure data integrity. Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

14. What is DDL?

    • Answer: DDL (Data Definition Language) is a subset of SQL commands used to define the database structure, including creating, modifying, and deleting database objects like tables, indexes, and views.

15. What is DML?

    • Answer: DML (Data Manipulation Language) is a subset of SQL commands used to manipulate data within database tables. This includes inserting, updating, deleting, and retrieving data.

16. What is TCL?

    • Answer: TCL (Transaction Control Language) is a subset of SQL commands used to manage transactions in a database. Commands like COMMIT, ROLLBACK, and SAVEPOINT are used to control transaction integrity.

17. What is a transaction?

    • Answer: A transaction is a logical unit of work that consists of one or more SQL statements. Transactions ensure that data modifications are atomic (all-or-nothing) and consistent.

18. What is ACID properties of a transaction?

    • Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. These are properties that guarantee reliable database transactions.

19. Explain Atomicity.

    • Answer: Atomicity means that a transaction is treated as a single unit of work. Either all changes within the transaction are applied successfully, or none are. There's no partial completion.

20. Explain Consistency.

    • Answer: Consistency ensures that a transaction maintains the database's integrity constraints. The database must remain in a valid state after a transaction completes.

21. Explain Isolation.

    • Answer: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction operates as if it were the only transaction running.

22. Explain Durability.

    • Answer: Durability guarantees that once a transaction is committed, the changes are permanently stored in the database and will survive system failures.

23. What is a stored procedure?

    • Answer: A stored procedure is a pre-compiled SQL code block that can be stored in the database and executed repeatedly. They improve performance and code reusability.

24. What is a function in Oracle?

    • Answer: A function is similar to a stored procedure but always returns a single value. They are often used for calculations or data transformations within SQL queries.

25. What is a trigger in Oracle?

    • Answer: A trigger is a procedural code block that automatically executes in response to certain events on a particular table or view. They're used for enforcing business rules or performing actions based on data modifications.

26. What is PL/SQL?

    • Answer: PL/SQL (Procedural Language/SQL) is an extension of SQL that adds procedural programming capabilities to Oracle databases. It allows developers to write more complex and efficient database applications.

27. What are cursors in PL/SQL?

    • Answer: Cursors are used to process data retrieved from a SQL query in PL/SQL. They act as a pointer to a result set, allowing row-by-row processing.

28. Explain different types of cursors.

    • Answer: There are implicit and explicit cursors. Implicit cursors are automatically created by Oracle for single-row DML operations. Explicit cursors are declared and managed by the programmer for more complex queries.

29. What is exception handling in PL/SQL?

    • Answer: Exception handling is a mechanism to gracefully manage runtime errors in PL/SQL. Using `EXCEPTION` blocks, you can catch specific errors and handle them without causing the entire program to fail.

30. What are the different data types in PL/SQL?

    • Answer: PL/SQL supports various data types, including NUMBER, VARCHAR2, CHAR, DATE, BOOLEAN, and various other specialized types.

31. What is the use of %TYPE and %ROWTYPE attributes?

    • Answer: `%TYPE` allows you to declare variables with the same data type as a database column. `%ROWTYPE` lets you declare a record variable with the structure of a database table row.

32. What is a package in PL/SQL?

    • Answer: A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. It promotes modularity and reusability.

33. Explain the difference between a package specification and package body.

    • Answer: The package specification is the public interface, declaring what's available to other parts of the application. The package body contains the implementation details of the procedures and functions declared in the specification.

34. What is SQL*Plus?

    • Answer: SQL*Plus is a command-line tool for interacting with Oracle databases. It allows you to execute SQL and PL/SQL statements, manage database objects, and perform various administrative tasks.

35. What are some common SQL*Plus commands?

    • Answer: Common commands include `CONNECT`, `SQL`, `DESC`, `SPOOL`, `SET`, `EXIT`, etc.

36. How do you handle NULL values in SQL?

    • Answer: Use the `IS NULL` or `IS NOT NULL` operators to check for NULL values. Functions like `NVL` or `COALESCE` can replace NULLs with other values.

37. What is the difference between DELETE and TRUNCATE commands?

    • Answer: `DELETE` removes rows individually and can be rolled back. `TRUNCATE` removes all rows much faster, and it cannot be rolled back.

38. What is data integrity?

    • Answer: Data integrity refers to the accuracy, consistency, and reliability of data. It's crucial to maintain the validity and trustworthiness of information stored in a database.

39. What is normalization?

    • Answer: Normalization is a database design technique to organize data efficiently and reduce redundancy. It involves breaking down larger tables into smaller, more manageable tables and defining relationships between them.

40. Explain different normal forms (1NF, 2NF, 3NF).

    • Answer: 1NF eliminates repeating groups of data within a table. 2NF is 1NF plus eliminating redundant data that depends on only part of the primary key (in tables with composite keys). 3NF is 2NF plus eliminating transitive dependencies, where a non-key column depends on another non-key column.

41. What is an aggregate function?

    • Answer: Aggregate functions perform calculations on sets of values and return a single result. Examples include `SUM`, `AVG`, `COUNT`, `MIN`, `MAX`.

42. What is a subquery?

    • Answer: A subquery is a query nested inside another SQL query. It can be used to filter data, retrieve data based on conditions, or perform other complex operations.

43. What is the difference between a correlated and non-correlated subquery?

    • Answer: A correlated subquery is executed once for each row processed by the outer query. A non-correlated subquery is executed independently of the outer query.

44. What is a self-join?

    • Answer: A self-join is a join where a table is joined with itself, often to compare data within the same table.

45. How do you retrieve the top N records from a table?

    • Answer: Use the `ROWNUM` pseudocolumn in Oracle: `SELECT * FROM table_name WHERE ROWNUM <= N;`

46. How do you handle date and time data in Oracle?

    • Answer: Oracle uses the `DATE` datatype to store date and time information. Various functions like `SYSDATE`, `TO_DATE`, `TO_CHAR` are used for manipulating date/time values.

47. What are some common date functions in Oracle?

    • Answer: `SYSDATE`, `ADD_MONTHS`, `MONTHS_BETWEEN`, `TRUNC`, `EXTRACT`, `LAST_DAY` are some common date functions.

48. What is the role of a DBA (Database Administrator)?

    • Answer: A DBA is responsible for managing and maintaining the database system, ensuring its performance, security, and availability. Tasks include database design, installation, configuration, backups, recovery, performance tuning, and security administration.

49. What are some common performance tuning techniques in Oracle?

    • Answer: Techniques include creating appropriate indexes, optimizing SQL queries, using stored procedures, managing database resources, and using database partitioning.

50. What is a rollback segment?

    • Answer: (Older concept, mostly replaced by undo tablespaces) Rollback segments were used to store information needed to undo transactions. They were crucial for database recovery and transaction rollback.

51. What is an undo tablespace?

    • Answer: Undo tablespaces are used to store undo information, allowing for transaction rollbacks and database recovery. They are a more modern and efficient alternative to rollback segments.

52. What is a redo log?

    • Answer: The redo log records all changes made to the database. It's crucial for recovering the database in case of a crash or failure. It ensures that all committed transactions are permanently stored.

53. What is data warehousing?

    • Answer: Data warehousing is the process of collecting and storing data from various sources to support business intelligence and decision-making. It involves creating a central repository of historical data for analysis.

54. What is OLTP?

    • Answer: OLTP (Online Transaction Processing) systems are designed for handling a large number of short transactions efficiently. They focus on fast data entry and retrieval.

55. What is OLAP?

    • Answer: OLAP (Online Analytical Processing) systems are designed for analyzing large volumes of data to support complex queries and reporting. They focus on providing business insights.

56. What is a database trigger? Give an example of a trigger that prevents insertion of duplicate data.

    • Answer: A database trigger is a procedural code that automatically executes in response to certain events on a table or view. Example (preventing duplicate email addresses): ```sql CREATE OR REPLACE TRIGGER unique_email_check BEFORE INSERT ON users FOR EACH ROW DECLARE email_count NUMBER; BEGIN SELECT COUNT(*) INTO email_count FROM users WHERE email = :NEW.email; IF email_count > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Email address already exists!'); END IF; END; / ```

57. How can you improve the performance of a slow running query?

    • Answer: By using appropriate indexes, optimizing the query (rewriting inefficient parts), using hints, analyzing execution plans, ensuring sufficient database resources, and checking for table and data statistics.

58. Explain different ways to backup an Oracle database.

    • Answer: Methods include full backups, incremental backups, hot backups (without shutting down the database), and using RMAN (Recovery Manager) for a comprehensive backup and recovery strategy.

59. What is RMAN (Recovery Manager)?

    • Answer: RMAN is a command-line tool for managing and automating backup, recovery, and other database administration tasks in Oracle. It provides a robust and efficient way to protect and recover your database.

60. What is the difference between COMMIT and ROLLBACK?

    • Answer: `COMMIT` saves the changes made in a transaction permanently to the database. `ROLLBACK` undoes all changes made in the current transaction.

61. What is the use of the DUAL table in Oracle?

    • Answer: The DUAL table is a built-in single-row, single-column table used to execute queries that don't require data from any other table, such as those involving functions or expressions.

62. How to find the highest salary from a table?

    • Answer: `SELECT MAX(salary) FROM employees;`

63. How to find the second highest salary from a table?

    • Answer: Several methods exist, one common approach is using `ROW_NUMBER()` analytical function or subqueries with `MAX()` and filtering.

64. What is the difference between implicit and explicit cursors?

    • Answer: Implicit cursors are automatically handled by Oracle for single-row DML operations. Explicit cursors are declared and managed by the programmer for multi-row processing.

65. What is the purpose of the `%ROWTYPE` attribute in PL/SQL?

    • Answer: `%ROWTYPE` allows you to declare a record variable that has the same structure as a table row or a cursor's result set.

66. How can you improve the performance of a PL/SQL block?

    • Answer: Use appropriate data types, minimize cursor operations, use bulk collect to fetch data efficiently, avoid excessive context switching, and optimize loops.

67. What is the role of a database trigger in maintaining data integrity?

    • Answer: Triggers enforce business rules and constraints by automatically executing actions in response to events on tables, thereby maintaining the accuracy and consistency of data.

68. Explain the concept of database partitioning.

    • Answer: Database partitioning divides large tables into smaller, more manageable parts for better performance, manageability, and scalability. Different partitioning strategies exist (range, hash, list).

69. What is a materialized view?

    • Answer: A materialized view is a pre-computed result set of a query that is stored in the database. It improves query performance, especially for complex queries or reports run repeatedly.

70. What are some common security features in Oracle?

    • Answer: Features include user authentication, authorization, access controls, data encryption, auditing, and network security measures.

71. What is database replication?

    • Answer: Database replication creates copies of data on multiple servers to provide high availability, scalability, and disaster recovery capabilities.

72. Explain different types of database replication.

    • Answer: Types include synchronous (immediate data synchronization) and asynchronous (delayed synchronization), along with various methods like statement-based, row-based, and data-based replication.

73. What is an Oracle RAC (Real Application Clusters)?

    • Answer: Oracle RAC allows multiple database instances to run concurrently on multiple servers, providing high availability and scalability.

74. What is a sequence in Oracle and when would you use it?

    • Answer: A sequence is a database object that generates unique numeric values. Use sequences to auto-generate unique primary key values for tables.

75. Write a simple PL/SQL block to insert data into a table.

    • Answer: ```sql DECLARE v_name VARCHAR2(50) := 'John Doe'; v_age NUMBER := 30; BEGIN INSERT INTO employees (name, age) VALUES (v_name, v_age); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error inserting data: ' || SQLERRM); ROLLBACK; END; / ```

76. How do you manage transactions in PL/SQL?

    • Answer: Use `COMMIT` to save changes, `ROLLBACK` to undo changes, and `SAVEPOINT` to create checkpoints within a transaction.

77. What is the difference between a procedure and a function in PL/SQL?

    • Answer: Procedures perform actions and may or may not return values. Functions always return a single value.

78. What are the advantages of using stored procedures?

    • Answer: Advantages include improved performance, reduced network traffic, enhanced security, better code organization, and reusability.

79. How would you retrieve data from multiple tables using SQL?

    • Answer: Use `JOIN` clauses (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN) to combine data from multiple tables based on related columns.

Thank you for reading our blog post on 'Oracle Interview Questions and Answers for freshers'.We hope you found it informative and useful.Stay tuned for more insightful content!