1. How do UNION and UNION ALL differ in SQL?
Ans:
UNION and UNION ALL are used to combine results from multiple SQL queries. UNION eliminates duplicate rows and returns only unique results, whereas UNION ALL keeps all rows including duplicates. Because UNION performs duplicate checks, it is slightly slower, while UNION ALL offers faster performance but may include repeated data. The choice depends on whether unique results or execution speed is more important.
2. What are Oracle triggers and how do they differ from procedures?
Ans:
Triggers are PL/SQL blocks that execute automatically when specific events occur in a table, such as INSERT, UPDATE, or DELETE operations. Procedures are also reusable PL/SQL blocks, but they only run when called manually. Triggers are ideal for enforcing business rules or logging changes, while procedures are used for performing controlled tasks within the database.
3. What is context switching in PL/SQL and why is it important?
Ans:
Context switching occurs when the system moves between SQL engine and the PL/SQL engine. Frequent switches consume additional time and resources, slowing down programs, especially when SQL statements are executed inside loops. Minimizing unnecessary context switches helps improve database performance and resource efficiency.
4. Can you explain different SQL join types and their uses?
Ans:
Rows from several tables are combined using SQL joins based on relevant columns. Only matching rows from both tables are returned by an INNER JOIN. All rows from the left table and corresponding rows from the right are included in the LEFT JOIN. While FULL OUTER JOIN returns all rows from both tables and fills in unmatched columns with NULLs, RIGHT JOIN includes all rows from the right table and matches from the left.
5. How do functions and procedures differ in PL/SQL?
Ans:
Functions and procedures are reusable PL/SQL blocks with distinct purposes. Functions return a single value and can be used within SQL statements. Procedures may or may not return values and are designed to perform tasks or actions. While functions always produce a single result, procedures can have numerous input and output arguments.
6. What is the role of EXPLAIN PLAN in Oracle?
Ans:
EXPLAIN PLAN provides insight into how Oracle executes a SQL query. It shows the steps, indexes, joins and the estimated cost involved in retrieving data. This helps developers and DBAs identify performance bottlenecks and optimize queries. It is a crucial tool for efficient SQL tuning and resource management.
7. What do analytical functions in Oracle do? Give an example.
Ans:
Analytical functions perform advanced calculations across a set of rows while still returning results for each row individually. They are useful for reporting and data analysis, such as calculating running totals, rankings, or averages. For example, the RANK() function does not collapse the data into a single summary row; instead, it assigns a rating to each row based on a particular column.
8. How are SQL and PL/SQL different?
Ans:
SQL is language for managing and manipulating data with commands like SELECT, INSERT, UPDATE and DELETE. PL/SQL extends SQL with programming constructs like loops, conditions and exception handling. While SQL handles direct data operations, PL/SQL allows creating complex programs to automate database tasks efficiently.
9. What is exception handling in PL/SQL and why is it necessary?
Ans:
Exception handling allows programs to catch and respond to runtime errors without crashing. It can handle built-in exceptions like NO_DATA_FOUND or custom exceptions defined by developers. Using exception handling ensures program stability, improves reliability and provides controlled error reporting or recovery actions during unexpected events.
10. What are collections in PL/SQL and why are they useful?
Ans:
Collections are data structures that store multiple values in a single variable. The main types include associative arrays, nested tables and VARRAYs. Collections simplify working with lists or related sets of data, support dynamic storage and access and enhance programming efficiency for managing groups of values within Oracle applications.
