1. What is a database index and why is it important?
Ans:
A database index is a structure that improves the speed of data retrieval from tables. It allows the system to quickly locate specific rows without scanning the entire table. While indexes boost SELECT query performance, they consume additional storage and can slightly slow down INSERT, UPDATE, or DELETE operations due to maintenance overhead.
2. How can SQL query performance be optimized?
Ans:
Query performance can be enhanced using several strategies. Adding indexes on frequently filtered or joined columns, selecting only necessary fields, simplifying nested queries, analyzing execution plans, and keeping statistics updated all help. Partitioning large tables or reorganizing data can also significantly reduce execution time.
3. What is a subquery and when should it be applied?
Ans:
A subquery is a query nested inside another SQL statement, providing results for filtering, calculations, or conditional operations. Subqueries can be used with SELECT, INSERT, UPDATE, or DELETE commands. They may be correlated, depending on the outer query, or independent, simplifying complex queries and enabling dynamic data retrieval.
4. What is a self-join in SQL?
Ans:
A self-join is when a table is joined with itself using different aliases to compare rows within the same table. It is useful for analyzing hierarchical relationships, such as manager-employee connections or parent-child data. Self-joins help organize and examine data structures where relationships exist within a single table.
5. How do DELETE and TRUNCATE commands differ?
Ans:
DELETE is a DML operation that removes specific rows based on a condition and allows rollback. TRUNCATE is a DDL command that removes all rows from a table instantly and cannot be undone. DELETE logs each row individually, making it slower, while TRUNCATE bypasses detailed logging, offering faster performance for clearing tables.
6. What is a correlated subquery in SQL?
Ans:
A correlated subquery is a nested query that depends on values from the outer query for its execution. It runs once for every row in the main query, allowing row-level comparisons. Unlike independent subqueries, which execute only once, correlated subqueries are ideal for conditional calculations that rely on outer query data.
7. What is normalization in databases?
Ans:
Normalization is the process of structuring database tables to minimize redundancy and maintain data consistency. It involves splitting large tables into smaller, related ones and defining relationships among them. This design prevents anomalies during inserts, updates, or deletions, ensuring the database remains accurate and logically organized.
8. How is an OUTER JOIN different from an INNER JOIN?
Ans:
An INNER JOIN returns only rows with matching values in both tables. An OUTER JOIN returns all rows from one table along with matching rows from the other, filling unmatched columns with NULLs. 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 Oracle PL/SQL and why is it used?
Ans:
A trigger is a PL/SQL stored procedure that executes automatically when certain events, such as INSERT, UPDATE, or DELETE, occur in a table. Triggers enforce business rules, maintain data integrity, and can log changes for auditing purposes. They ensure automatic execution of important operations without manual intervention.
10. What is a cursor in PL/SQL and how does it work?
Ans:
A cursor in PL/SQL is a pointer that stores the result set of a query in memory for row-by-row processing. Implicit cursors are automatically created for single-row queries, while explicit cursors are defined to handle multi-row queries with repeated fetches. Cursors allow precise control and efficient processing of large datasets.
Fees Starts From
