1. What is the difference between DELETE, TRUNCATE, and DROP in SQL?
Ans:
DELETE is used to remove specific rows from a table based on a condition, and the changes can be rolled back if needed. TRUNCATE removes all rows from a table but does not allow rollback, making it faster than DELETE. DROP completely deletes the table along with its structure and data permanently. The choice between them depends on whether you need to preserve the table structure or require rollback capability.
2. What SQL query optimization techniques have you applied in projects?
Ans:
SQL query optimization is achieved by using proper indexing, selecting only the required columns, and minimizing nested subqueries. Analyzing execution plans helps identify bottlenecks, and rewriting complex queries to simplify logic can significantly improve performance. These techniques ensure faster query execution and efficient database resource utilization.
3. What types of indexes exist in SQL, and when should indexes be avoided?
Ans:
SQL supports several types of indexes, including unique indexes, composite indexes, and full-text indexes. Indexes improve search performance but should be avoided on columns with low distinct values, such as boolean fields, because they offer little benefit and can slow down data manipulation operations like INSERT, UPDATE, and DELETE.
4. Can you explain the concept of joins in SQL?
Ans:
Joins are used to combine rows from two or more tables based on related columns. Common types include INNER JOIN, which returns only 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 returns rows if there is a match in either table. Joins are essential for retrieving related data efficiently.
5. What is a correlated subquery?
Ans:
A correlated subquery is a subquery that depends on values from the outer query. Unlike independent subqueries, it executes once for each row processed by the outer query. Correlated subqueries are useful for performing row-by-row comparisons and generating results that depend on data from the outer query.
6. What are cursors in PL/SQL, and what are the types?
Ans:
Cursors in PL/SQL are pointers that hold SQL query results in memory for processing. There are implicit cursors, which are automatically created for single-row operations, and explicit cursors, which are defined by programmers for multi-row queries requiring repeated fetch operations. Cursors help manage and navigate through query results efficiently.
7. How is exception handling managed in PL/SQL?
Ans:
Exception handling in PL/SQL is used to manage errors or unexpected events during program execution. PL/SQL provides predefined exceptions like NO_DATA_FOUND and TOO_MANY_ROWS, and also allows custom exceptions. Exceptions are handled within the EXCEPTION block to ensure programs execute smoothly or fail gracefully without disrupting the system.
8. What is performance tuning in SQL?
Ans:
Performance tuning in SQL focuses on improving query execution speed and efficient use of database resources. It includes analyzing execution plans, applying indexes strategically, rewriting complex queries, and monitoring system performance. Proper tuning ensures faster response times and better overall database performance.
9. How does a procedure differ from a function in PL/SQL?
Ans:
A procedure in PL/SQL is a block of code that performs specific tasks but does not return a value, while a function performs operations and returns a single result. Functions can be used directly in SQL statements, whereas procedures must be explicitly invoked using the EXECUTE command. Both are essential for organizing and automating database logic.
10. What are packages in PL/SQL?
Ans:
Packages in PL/SQL are collections of related procedures, functions, variables, and other elements grouped into a single module. They promote modularity, encapsulation, and code reuse. Each package has a specification, which declares the components, and a body, which contains the implementation details. Packages simplify development and maintenance of complex PL/SQL programs.
Fees Starts From
