1. How are UNION and UNION ALL different in SQL?
Ans:
When combining the output of two or more SQL queries, UNION and UNION ALL are utilized. The main difference is that UNION removes any duplicate rows and only returns unique results, while UNION ALL keeps all the rows, including duplicates. Because UNION has to check for duplicates, it is a bit slower. UNION ALL is faster but may show repeated data. You can choose based on whether you need unique results or better performance.
2. What are triggers in Oracle and how are they not the same as procedures?
Ans:
Triggers in Oracle are blocks of code that automatically run when certain actions happen in a table, such as inserting, updating, or deleting rows. You don’t have to call them they run on their own when the defined event occurs. In contrast, stored procedures are also blocks of PL/SQL code, but they only run when you call them manually. Triggers are mainly used to enforce rules or log changes, while procedures are used when you want to perform specific tasks that are under your control.
3. What is context switching in PL/SQL and why does it matter?
Ans:
Context switching in PL/SQL happens when the system switches back and forth between the SQL engine and the PL/SQL engine. This back and forth movement takes extra time and resources especially when it happens too often. For example, if a program uses SQL statements inside a loop written in PL/SQL, each switch can slow things down. Reducing unnecessary context switches can help improve performance.
4. Describe the various SQL join types and their functions.
Ans:
SQL joins are way to combine rows from the two or more tables. Only the matching rows from both tables are returned by an INNER JOIN. The matched rows from the right table and every row from the left table are obtained using an LEFT JOIN. By providing all rows from the right table and the corresponding rows from the left, a RIGHT JOIN accomplishes the opposite. A FULL OUTER JOIN incorporates every entry from both tables, displaying matches when they are found and using NULLs to fill in any blanks.
5. What is the difference between a function and a procedure in PL/SQL?
Ans:
In PL/SQL, both functions and procedures are reusable blocks of code, but they serve different purposes. A function is designed to return a single value and can be used inside SQL statements. A procedure, on the other hand, may or may not return a value and is mainly used to perform actions or tasks. Procedures can have input and output parameters, while functions always return one result. In simple terms, use a function when you need a value and a procedure when you want to do something.
6. What is the purpose of the EXPLAIN PLAN in Oracle?
Ans:
The EXPLAIN PLAN command is used to understand how Oracle will run a SQL query. It shows the steps Oracle will take to retrieve data, including which indexes or joins will be used, the order of operations and an estimate of the cost involved. This information is helpful for developers and database administrators to find performance issues and improve query efficiency. It’s a valuable tool for tuning SQL statements.
7. What do analytical functions in Oracle do? Give one example.
Ans:
Analytical functions in Oracle are used for advanced calculations across a set of rows, while still returning a result for each individual row. These functions are useful in reports and data analysis where you need things like running totals, rankings, or averages. For example, the RANK() function can assign a ranking number to each row based on a column's value, such as scores or sales, without grouping the data into a single row.
8. How are SQL and PL/SQL different from each other?
Ans:
SQL is a language used to perform operations like selecting, inserting, updating, or deleting data in a database. It is mainly focused on data manipulation. PL/SQL, on the other hand, is an extension of SQL that includes programming features such as loops, conditions and error handling. With PL/SQL, you can write complex programs and automate tasks within the database. While SQL is for working with data, PL/SQL helps you write full programs to manage that data more efficiently.
9. What is exception handling in PL/SQL and why is it useful?
Ans:
Exception handling in PL/SQL is a way to catch and handle errors that happen while a program is running. Instead of the program crashing when something goes wrong, you can define what should happen for example, show a message, skip an operation, or log the error. PL/SQL includes built-in exceptions like NO_DATA_FOUND and also allows you to create custom exceptions. This helps make your programs more stable and easier to manage when something unexpected happens.
10. What are collections in PL/SQL and why are they used?
Ans:
Collections in PL/SQL are data structures used to store multiple values together in a single variable. There are three main types: associative arrays, nested tables and varrays. Associative arrays are flexible and use keys to access elements. Nested tables can hold any number of items and can also be stored in database columns. Varrays are similar to arrays and have a fixed maximum size. These collections make it easier to work with lists or groups of related data.
