1. What are the key differences between DELETE, TRUNCATE, and DROP in SQL?
Ans:
The DELETE statement removes specific rows from a table based on a condition and allows for transaction rollback if needed. TRUNCATE eliminates all rows from a table quickly, but it cannot be rolled back, making it faster than DELETE. DROP completely removes the table, including its structure and data, and this action is permanent. The choice between these operations depends on whether you need to preserve the table structure or require rollback functionality.
2. What SQL query optimization techniques have you applied in projects?
Ans:
SQL query optimization involves techniques like using appropriate indexing, selecting only necessary columns, and minimizing complex subqueries. Analyzing query execution plans helps identify performance bottlenecks, and simplifying complex queries can enhance efficiency. These practices ensure faster query response times and optimize the utilization of database resources.
3. What types of indexes exist in SQL, and when should indexes be avoided?
Ans:
In SQL, indexes come in different types, such as unique indexes, composite indexes, and full-text indexes. While indexes speed up data retrieval, they should be avoided on columns with low cardinality (e.g., boolean values), as they offer minimal performance improvement and can degrade performance during data modification operations like INSERT, UPDATE, and DELETE.
4. Can you explain the concept of joins in SQL?
Ans:
Joins are used to combine data from multiple tables based on common columns. The main types are: INNER JOIN, which returns matching rows from both tables; LEFT JOIN, which returns all rows from the left table and matching rows from the right; RIGHT JOIN, which returns all rows from the right table with matching rows from the left; and FULL OUTER JOIN, which includes rows where a match exists in either table. Joins are essential for linking related data across tables.
5. What is a correlated subquery?
Ans:
A correlated subquery is a query that references columns from the outer query in its condition. Unlike independent subqueries, which execute once, a correlated subquery is executed for each row processed by the outer query. This type of subquery is particularly useful for row-by-row comparisons and can generate dynamic results based on the outer query's data.
6. What are cursors in PL/SQL, and what types exist?
Ans:
Cursors in PL/SQL are pointers that hold and manage the result sets of SQL queries. There are implicit cursors, which are automatically generated for single-row queries, and explicit cursors, which are defined by the programmer for multi-row queries that require repeated fetch operations. Cursors allow efficient processing and navigation of query results.
7. How is exception handling managed in PL/SQL?
Ans:
Exception handling in PL/SQL is used to handle errors or abnormal events that occur during execution. PL/SQL provides a set of predefined exceptions, such as NO_DATA_FOUND and TOO_MANY_ROWS, as well as the ability to define custom exceptions. These exceptions are handled within an EXCEPTION block, ensuring the program can either recover from errors or fail gracefully without interrupting the entire system.
8. What is performance tuning in SQL?
Ans:
Performance tuning in SQL involves optimizing query performance and ensuring efficient database resource usage. This can be achieved by analyzing execution plans, implementing the right indexes, simplifying complex queries, and continuously monitoring system performance. Effective tuning results in faster query execution, reduced server load, and enhanced overall system performance.
9. How does a procedure differ from a function in PL/SQL?
Ans:
In PL/SQL, a procedure is a block of code designed to perform a specific task but does not return a value. A function, however, performs operations and returns a single value, which can be used in SQL queries or expressions. Functions can be directly embedded in SQL statements, whereas procedures must be explicitly called using the EXECUTE command.
10. What are packages in PL/SQL?
Ans:
Packages in PL/SQL are collections of related functions, procedures, variables, and other database objects grouped together as a single unit. They promote modularity and encapsulation, making code easier to manage and reuse. A package consists of a specification, which declares the components, and a body, which contains the implementation. Packages help organize complex PL/SQL programs and improve their maintainability.
