1. What is the difference between UNION and UNION ALL in SQL?
Ans:
The UNION operator merges results from multiple SELECT queries while removing duplicates, ensuring unique rows in the output. UNION ALL also combines results but keeps duplicates, making it faster since no sorting or comparison is required.
2. What are triggers in Oracle and how are they different from stored procedures?
Ans:
Triggers in Oracle are PL/SQL blocks that execute automatically when specific events occur on a table or view, such as INSERT, UPDATE, or DELETE. They help enforce rules, audit data, and maintain integrity. Stored procedures, on the other hand, are named PL/SQL blocks that must be explicitly executed by users or applications to perform 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.
