1. Explain the difference between DELETE, TRUNCATE and DROP in SQL.
Ans:
In SQL, the DELETE command removes rows from a table based on a condition and the changes can be rolled back if needed. TRUNCATE, on the other hand, deletes all rows from a table but cannot be rolled back, as it is a faster operation that clears everything. DROP is used to completely remove a table along with its data and structure and this action also cannot be rolled back. The choice depends on whether you want to remove specific rows, clear all data, or permanently remove the table.
2. What SQL query optimization techniques have you used?
Ans:
SQL query optimization is about improving the performance of queries so they run faster and use fewer resources. Some common techniques include creating the right indexes to speed up searches, selecting only the necessary columns instead of using SELECT * and minimizing subqueries by rewriting them into joins when possible. Another useful method is checking the execution plan to see how the query is being processed and then simplifying or restructuring it to reduce complexity.
3. What kinds of SQL indexes are there and when is it best to use them?
Ans:
SQL provides several types of indexes, such as unique indexes (to enforce uniqueness), composite indexes (on multiple columns) and full-text indexes (for searching text data). However, indexes are not always helpful. For example, using indexes on columns with very few distinct values, like TRUE/FALSE, does not improve performance and can even slow things down. Also, since indexes need to be updated whenever data is inserted, updated, or deleted, using too many indexes can negatively impact write performance.
4. Can you explain the concept of joins in SQL?
Ans:
To combine data from two or more tables based on a shared column, SQL joins are utilized. These are the most popular join types: FULL OUTER JOIN, which returns rows when there is a match in either table; LEFT JOIN, which displays all rows from the left table and the matching ones from the right; RIGHT JOIN, which does the opposite; and INNER JOIN, which displays only rows with matching values in both tables.
5. What is a correlated subquery?
Ans:
A subquery that relies on the outer query for its values is known as a correlated subquery. Unlike a regular subquery which runs independently, a correlated subquery is executed once for every row processed by the main query. This makes it useful when comparing each row in a table against a set of values, but it can also be slower than a regular subquery due to repeated execution.
6. What are cursors in PL/SQL and what types are there?
Ans:
Cursors in PL/SQL are used to handle query results by pointing to the context area where the SQL statement and its processing information are stored. There are the two types of cursors implicit and explicit. Implicit cursors are created automatically by Oracle when simple queries like INSERT, UPDATE, or DELETE are executed. Explicit cursors, on the other hand, are declared by the programmer and are useful for handling complex queries where multiple rows need to be fetched one at a time.
7. How does exception handling work in PL/SQL?
Ans:
Exception handling in PL/SQL is a way to manage errors that occur while a program is running. Instead of stopping abruptly, the program can catch these errors and respond in a controlled manner. There are predefined exceptions such as NO_DATA_FOUND or TOO_MANY_ROWS, as well as user-defined exceptions created by developers. By handling exceptions, programs can continue running smoothly or fail gracefully with proper messages instead of crashing.
8. What is performance tuning in SQL?
Ans:
Performance tuning in SQL is the process of making queries run faster and more efficiently. This involves checking and improving execution plans, using indexes effectively, avoiding unnecessary data retrieval and writing simpler queries. It also includes regular monitoring of the database to identify bottlenecks and adjusting queries or indexes as needed. Performance tuning ensures that applications using the database remain quick and responsive.
9. What is the difference between a procedure and a function in PL/SQL?
Ans:
In PL/SQL, both procedures and functions are reusable blocks of code, but they serve different purposes. A procedure is designed to perform a task, such as inserting or updating records, but it does not return a value directly. A function, however, performs a specific operation and always returns a single value, which makes it suitable for use in SQL expressions. Procedures are executed using the EXECUTE command, while functions can be called within queries.
10. What are packages in PL/SQL?
Ans:
Packages in PL/SQL are collections of related procedures, functions, variables and other elements grouped together as a single unit. They help organize code into modules, making it easier to manage and reuse. A package has two parts: the specification, which defines the elements that are accessible outside the package and the body, which contains the actual implementation. Packages improve modularity, encapsulation and maintainability of code in large applications.
