1. What is the difference between UNION and UNION ALL in SQL?
Ans:
The UNION operator eliminates duplicate rows and merges the output of two or more SELECT operations, ensuring each row is unique in the final result. In contrast UNION ALL combines the results without removing duplicates, making it faster as it does not require sorting or comparison. The choice between the two depends on whether duplicate rows are acceptable and performance considerations.
2. What are triggers in Oracle and how do they differ from stored procedures?
Ans:
Triggers in Oracle are stored PL/SQL blocks that automatically execute a specified event occurs on a table or view, such as an INSERT, UPDATE or DELETE operation. They are commonly used for enforcing business rules, auditing changes and maintaining data integrity. In contrast, stored procedures are named PL/SQL blocks that can be explicitly invoked by a user or application to perform specific tasks.
3. Explain the concept of context switching in PL/SQL.
Ans:
User research gathers insights about users’ behaviors, needs and motivations through methods like interviews, surveys, observation and usability testing. The findings help create accurate user personas and guide design decisions, ensuring products are relevant, practical and user-friendly.
4. What kinds of joins are there in SQL?
Ans:
SQL supports several types of joins, each with a distinct function in merging information from several tables. The main types include INNER JOIN, which returns only the rows have matching values in LEFT JOINING both tables yields all rows from the left table along with RIGHT JOIN, which yields all rows from the right table, matching rows from the right table, along with the left table's matching rows and FULL OUTER JOIN, which gives rows there is a match in one of the tables. Understanding these join types is crucial for querying relational databases effectively.
5. What is the difference between a function and a procedure in PL/SQL?
Ans:
In PL/SQL, both functions and procedures are named blocks of code that can be stored and executed, but they have distinct differences. A function is a PL/SQL block accomplishes a certain task and yields a single value and it can be used in SQL expressions.
6. What is the purpose of the explain plan statement in Oracle?
Ans:
The EXPLAIN PLAN statement in Oracle is used to display the execution plan for a SQL query. An execution plan shows how Oracle intends to execute a SQL statement, encompassing the sequence of events, the data access techniques, and the approximate cost of every operation.
7. What are analytical functions in Oracle? Provide an example.
Ans:
Analytical functions in Oracle are a category of functions use a collection of rows to calculate an aggregate value but return the result for each row in the group. These functions are used to carry out computations on a group of table rows associated with the current row.
8. What is the difference between SQL and PL/SQL?
Ans:
A query language called SQL is used to communicate with databases allowing users to query and manipulate data. PL/SQL (Procedural Language/SQL) is an extension of SQL that adds procedural capabilities, such as loops and conditions, enabling developers to write complex scripts and programs.
9. What is exception handling in PL/SQL?
Ans:
Exception handling in PL/SQL is a mechanism to handle errors or unexpected events in a program. It allows developers to define responses to different types of errors, ensuring that the program can continue executing or fail gracefully. Exceptions can be predefined, such as NO_DATA_FOUND or TOO_MANY_ROWS or user-defined and they are handled using the EXCEPTION block in PL/SQL.
10. What are collections in PL/SQL?
Ans:
Collections in PL/SQL are data structures that Permit developers to store and work with a variety of values as a single unit. Three categories of collections exist arrays that are associative (index-by tables), nested tables and varrays (variable-size arrays). Associative arrays are unbounded and indexed by numbers or strings, nested tables can store an arbitrary number of elements and can be stored in database tables and varrays have a fixed upper bound but can store an arbitrary number of elements.
