1. What is an index in a database and why is it useful?
Ans:
An index is a database structure that organizes table data to allow faster retrieval of specific rows. It improves query performance significantly, especially in large tables, by quickly locating the required records. While indexes accelerate read operations, they consume additional storage and can slow down INSERT, UPDATE, and DELETE actions due to maintenance overhead.
2. How can a slow SQL query be optimized?
Ans:
Improving SQL query performance involves several techniques. Creating indexes on frequently filtered or joined columns, limiting selected columns, and reducing nested subqueries can help. Additionally, keeping table statistics up to date, partitioning large datasets, and analyzing execution plans using EXPLAIN identify bottlenecks. Applying these practices ensures faster query execution and efficient use of resources.
3. What is a subquery in SQL and when is it used?
Ans:
A subquery is a nested query embedded within another SQL statement, providing intermediate results for filtering, calculation, or comparison. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements. They can be correlated, referring to outer query columns, or uncorrelated, running independently. Subqueries are helpful for handling complex retrieval scenarios or conditional data processing.
4. What is a self-join in SQL?
Ans:
A self-join occurs when a table is joined with itself using aliases to differentiate the instances. This technique is useful for comparing records within the same table, such as linking employees with their managers or parent-child relationships. Self-joins are commonly applied in hierarchical queries, recursive structures, and situations where intra-table relationships need analysis.
5. How does DELETE differ from TRUNCATE in SQL?
Ans:
DELETE is a DML operation that removes selected rows using a condition and supports rollback. TRUNCATE is a DDL command that removes all rows from a table at once and does not allow undoing the operation. DELETE is slower because it logs each row deletion, whereas TRUNCATE is faster, bypassing individual logging and quickly clearing the table.
6. What is a correlated subquery?
Ans:
A correlated subquery is a nested query that references columns from the outer query, executing for every row processed by the main query. Unlike standalone subqueries, which run once independently, correlated subqueries provide row-specific comparisons and calculations. They are useful in scenarios where each outer row must be evaluated against inner query results.
7. What is database normalization?
Ans:
Normalization is the practice of organizing database tables to reduce redundancy and maintain consistency. It involves splitting large tables into smaller, interrelated tables with clear relationships. This approach minimizes anomalies during insertion, updates, or deletions and ensures data remains accurate, consistent, and easier to maintain.
8. How does an OUTER JOIN differ from an INNER JOIN?
Ans:
An INNER JOIN returns only rows that have matching values in both tables. An OUTER JOIN returns all rows from one table and includes corresponding rows from the other table, inserting NULL where no match exists. Outer joins can be LEFT, RIGHT, or FULL depending on which table’s unmatched rows should be included in the results.
9. What is a trigger in PL/SQL?
Ans:
A trigger is a specialized stored procedure that executes automatically when certain database events occur, such as INSERT, UPDATE, or DELETE operations. Triggers are often used to enforce business rules, maintain consistency, or log database changes for auditing purposes. They operate without manual execution, ensuring automated data handling and integrity.
10. What is a cursor in PL/SQL?
Ans:
A cursor is a memory pointer in PL/SQL that holds the result set of a SQL query for processing. Implicit cursors are automatically generated for simple SQL statements, whereas explicit cursors are defined by developers for multi-row queries requiring repeated fetching. Cursors enable efficient row-by-row processing of query results within PL/SQL programs.
Fees Starts From
