1. How is UNION different from UNION ALL in SQL?
Ans:
The UNION operator merges the results of two or more SELECT statements into a single result set while automatically removing duplicate rows, ensuring each row is unique. In contrast, UNION ALL combines results from multiple queries without eliminating duplicates. Since it skips the duplicate-checking process, UNION ALL usually performs faster and is more efficient for large datasets.
2. What are triggers in Oracle, and how do they differ from stored procedures?
Ans:
A trigger in Oracle is a PL/SQL block that executes automatically in response to specific database actions such as INSERT, UPDATE, or DELETE on a table or view. Triggers enforce business rules, maintain data consistency, or log database activity. Stored procedures, however, are PL/SQL programs that must be explicitly invoked and do not run automatically in response to events.
3. What does context switching mean in PL/SQL?
Ans:
Context switching occurs when Oracle alternates execution between the SQL engine, which handles database queries, and the PL/SQL engine, which executes procedural logic. Frequent switching can slow down performance because the system constantly moves between the two engines. Minimizing context switches improves program efficiency and ensures faster execution of PL/SQL code.
4. What types of joins exist in SQL?
Ans:
SQL supports different types of joins to combine data from multiple tables. INNER JOIN returns rows that have matching values in both tables. LEFT JOIN includes all rows from the left table plus matching rows from the right. RIGHT JOIN returns all rows from the right table with matches from the left. FULL OUTER JOIN returns all rows where there is a match in either table, including unmatched rows from both.
5. How does a function differ from a procedure in PL/SQL?
Ans:
A function in PL/SQL performs a specific calculation or operation and always returns a single value, which can be used in SQL queries. A procedure, on the other hand, is a PL/SQL code block that executes one or more tasks but may not return any value. Procedures are typically used to automate business logic or repetitive operations. Functions can be invoked within SQL, but procedures cannot.
6. What is the purpose of the EXPLAIN PLAN statement in Oracle?
Ans:
EXPLAIN PLAN provides a detailed roadmap of how Oracle will execute a SQL query. It shows the sequence of operations, the methods used to access data, and the estimated cost for each step. Developers use this information to identify bottlenecks, optimize queries, and enhance overall database performance.
7. What are analytical functions in Oracle? Can you give an example?
Ans:
Analytical functions perform calculations across a set of rows and return a result for each individual row, unlike aggregate functions which return a single summary value. For example, RANK() assigns a ranking to each row based on a specific column, while keeping all rows visible. These functions are useful for trend analysis, rankings, and advanced reporting.
8. How is SQL different from PL/SQL?
Ans:
SQL is a declarative language used for querying, updating, inserting, and deleting data, focusing on what data to manipulate. PL/SQL extends SQL with procedural constructs such as loops, conditions, and variables, enabling complex program logic. While SQL is ideal for data operations, PL/SQL is suitable for implementing business logic and automated workflows.
9. What is exception handling in PL/SQL?
Ans:
Exception handling in PL/SQL allows developers to detect and manage errors or unexpected conditions during program execution. Predefined exceptions like NO_DATA_FOUND or TOO_MANY_ROWS handle common issues, while custom exceptions allow developers to address specific scenarios. This ensures programs run smoothly without abrupt failures.
10. What are collections in PL/SQL?
Ans:
Collections are PL/SQL data structures that hold multiple elements in a single variable. They include associative arrays (indexed by numbers or strings), nested tables (dynamically sized and stored in tables), and VARRAYs (fixed-size arrays). Collections simplify handling and processing of related data, enabling efficient management of complex datasets.
Fees Starts From
