1. What is a database index and why is it useful?
Ans:
A database index is a structure that helps the database locate rows quickly, making queries faster, especially on large tables. While indexes improve search performance, they consume additional storage and may slightly slow down insert, update or delete operations because the index itself needs to be updated. Proper use of indexes balances performance and efficiency.
2. How can the performance of a slow SQL query be enhanced?
Ans:
To improve a slow SQL query, you can create indexes on frequently used columns, avoid unnecessary subqueries, select only the columns you need and keep database statistics updated. Using table partitioning and analyzing the execution plan with EXPLAIN also helps identify bottlenecks and optimize query execution for better performance.
3. What is a subquery in SQL and when should it be applied?
Ans:
A query that is nestled inside another query and generates intermediate results for the primary query is called a subquery. It is useful when a calculation or condition depends on the result of another query. Subqueries can be uncorrelated, running independently or correlated, depending on the outer query. SELECT, INSERT, UPDATE and DELETE statements can all be used with them.
4. What does a self-join do in SQL?
Ans:
A self-join connects a table to itself to compare rows or find relationships within the same table. This is helpful for hierarchical data, such as identifying managers and employees within the same employee table. Aliases are used to differentiate the two instances of the table to avoid confusion in queries.
5. How do DELETE and TRUNCATE differ?
Ans:
Based on a criterion, DELETE eliminates particular rows from a table; it can be undone if necessary. TRUNCATE removes all rows instantly and cannot be undone. DELETE processes each row individually and is slower for large datasets, whereas TRUNCATE empties the table at once, making it faster and more efficient.
6. What is a correlated subquery and how does it work?
Ans:
A correlated subquery executes once for each row in the main query and depends on values from the outer query. WHERE clauses frequently use it to filter rows according to related data. While powerful, correlated subqueries can be slower than regular subqueries because they run multiple times.
7. What is the purpose of database normalization?
Ans:
Database normalization organizes data into smaller, related tables to reduce redundancy and prevent inconsistencies during inserts, updates or deletions. It guarantees accurate data storage, consistency throughout the database and efficient data storage. Normalization also simplifies database maintenance and improves overall performance.
8. How does an outer join differ from an inner join?
Ans:
Only rows with matching values in both tables are returned by an INNER JOIN. All rows from one table and any matching rows from the other are returned via an OUTER JOIN, with unmatched rows being filled in with NULL. Outer joins can be LEFT, RIGHT or FULL, depending on which table’s data you want fully included in the results.
9. What is a trigger in PL/SQL and when is it used?
Ans:
A trigger is a special PL/SQL procedure that executes automatically when certain events occur, such as INSERT, UPDATE or DELETE operations. Triggers help enforce rules, maintain audit logs and automate repetitive tasks without manual intervention, ensuring data integrity and consistent operations.
10. How are cursors utilized in PL/SQL?
Ans:
Cursors act as pointers to process query results one row at a time. Whereas the programmer defines explicit cursors for intricate multi-row operations, Oracle automatically generates implicit cursors for basic queries. Using cursors allows precise control over row-by-row data handling and improves efficiency in PL/SQL programs.
