1. How do UNION and UNION ALL differ in SQL?
Ans:
Both UNION and UNION ALL combine results from multiple SQL queries. The main distinction is that UNION ALL contains all rows, including duplicates whereas UNION eliminates duplicate rows and only gives unique results. UNION checks for duplicates, making it slightly slower, whereas UNION ALL is faster but may repeat data. Choose based on whether you need distinct results or performance.
2. What are triggers in Oracle and how do they differ from procedures?
Ans:
Triggers are automatic PL/SQL blocks that execute when specific events occur in a table, like insert, update or delete. You don’t call them they run automatically. Procedures, however, are manually executed PL/SQL blocks used to perform specific tasks. Triggers enforce rules or log changes, while procedures perform controlled actions on demand.
3. What is context switching in PL/SQL and why is it important?
Ans:
Context switching is the process by which the system alternates between the PL/SQL and SQL engines. Excessive switching consumes time and resources, slowing performance. For instance, using SQL statements inside loops in PL/SQL causes frequent context switches. Reducing unnecessary switches improves program efficiency.
4. What are the main types of SQL joins and their purposes?
Ans:
Two or more tables' rows are combined using SQL joins. Only rows that match from both tables are returned by an INNER JOIN. All of the rows from the left table and the matched rows from the right are retrieved by the LEFT JOIN. Right joints do the opposite. With NULLs in unmatched fields, FULL OUTER JOIN returns every row from both tables.
5. How is a function different from a procedure in PL/SQL?
Ans:
Functions are PL/SQL blocks that always return a single value and can be used in SQL queries. Procedures may or may not return values and are used to execute tasks or operations. Procedures can have multiple input/output parameters, while functions return a single result. Use functions for values and procedures for actions.
6. What is the role of EXPLAIN PLAN in Oracle?
Ans:
EXPLAIN PLAN shows how Oracle executes a SQL query, including the steps, indexes used, join order and estimated cost. It helps developers and DBAs identify performance issues and optimize queries. This tool is essential for tuning SQL statements and ensuring efficient data retrieval.
7. What are analytical functions in Oracle? Can you give an example?
Ans:
Analytical functions perform advanced calculations over a set of rows while returning results for each individual row. They are useful for reporting, rankings, running totals and averages. For example, the RANK() function assigns a rank to each row based on a column value without grouping the data.
8. How do SQL and PL/SQL differ?
Ans:
The SQL programming language is used to select, insert, update and delete as well as query and manipulate database data. PL/SQL extends SQL with programming features like loops, conditions and error handling. PL/SQL allows automation and complex database programs, whereas SQL focuses on data operations.
9. What is exception handling in PL/SQL and why is it important?
Ans:
Exception handling is a way to manage runtime errors in PL/SQL programs. Instead of the program failing, you can define actions like displaying a message, skipping operations or logging the error. Oracle provides predefined exceptions like NO_DATA_FOUND and allows custom exceptions. This ensures programs remain stable and manageable.
10. What are the collections in PL/SQL and how are they used?
Ans:
Collections are data structures that store multiple values in a single variable. Types include associative arrays, nested tables and varrays. Associative arrays use keys, nested tables can grow dynamically and varrays have fixed sizes. Collections simplify handling lists or sets of related data within PL/SQL programs.
