1. What is the role of an index in a database, and why is it used?
Ans:
An index is a database structure designed to accelerate data retrieval from tables. It allows the system to locate rows quickly without scanning every record. While indexes enhance query performance for SELECT operations, they consume extra storage and may slightly slow down INSERT, UPDATE, or DELETE actions due to maintenance requirements.
2. How can SQL query efficiency be improved?
Ans:
Query efficiency can be enhanced by creating indexes on frequently filtered or joined columns, selecting only necessary fields, simplifying complex nested queries, reviewing execution plans, and keeping statistics up to date. Partitioning tables or reorganizing data can further reduce query execution time significantly.
3. What is a subquery, and when is it used?
Ans:
A subquery is a query nested inside another SQL statement to provide results for filtering, calculations, or conditional operations. It can be used with SELECT, INSERT, UPDATE, or DELETE statements. Subqueries may depend on the outer query (correlated) or work independently, helping simplify complex queries and retrieve dynamic data efficiently.
4. What is a self-join, and how is it applied?
Ans:
A self-join occurs when a table is joined with itself using different aliases to compare records within the same table. It is useful for analyzing hierarchical or relational data, such as employee-manager relationships. Self-joins allow structured examination of data that relates within a single table.
5. How do DELETE and TRUNCATE operations differ in Oracle?
Ans:
DELETE is a DML command that removes specific rows based on conditions and supports rollback. TRUNCATE is a DDL operation that removes all rows instantly and cannot be undone. DELETE logs each deletion individually, making it slower, while TRUNCATE bypasses detailed logging for faster table clearance.
6. What is a correlated subquery, and how does it function?
Ans:
A correlated subquery is a nested SQL query that references columns from the outer query for execution. It runs once per row in the main query, allowing row-specific comparisons. Unlike independent subqueries, correlated subqueries are useful for conditional calculations based on outer query data.
7. What is database normalization, and why is it practiced?
Ans:
Normalization is the process of organizing database tables to reduce redundancy and ensure consistency. It involves dividing large tables into smaller, connected tables and establishing relationships between them. This approach prevents anomalies during inserts, updates, or deletions and ensures a logically structured and maintainable database.
8. How does an OUTER JOIN differ from an INNER JOIN?
Ans:
An INNER JOIN retrieves only the rows that have matching values in both tables. An OUTER JOIN returns all rows from one table along with the matching rows from another, filling unmatched fields with NULLs. OUTER JOINs can be LEFT, RIGHT, or FULL depending on which table’s unmatched data should appear in the results.
9. What is an Oracle trigger, and what is its purpose?
Ans:
A trigger is an automatic PL/SQL procedure that executes when specific events, such as INSERT, UPDATE, or DELETE, occur on a table. Triggers enforce business rules, maintain data consistency, and can log changes for auditing. They ensure important operations run automatically without manual intervention.
10. What is a cursor in PL/SQL, and how is it used?
Ans:
A cursor is a pointer in PL/SQL that holds query results in memory for row-by-row processing. Implicit cursors handle single-row queries automatically, while explicit cursors are defined for processing multiple rows. Cursors provide precise control over large datasets and allow efficient data manipulation.
Fees Starts From
