1. What is an index in a database and why is it important?
Ans:
A data structure called an index speeds up table data retrieval procedures. It speeds up the database ability to locate rows enhancing query performance. Indexes are particularly useful for large tables where searching for specific rows can be time-consuming without them. However, they also require additional storage space and can slow down data modification operations.
2. How would you optimize a slow-running SQL query?
Ans:
- Using Indexes: Create indexes on columns are commonly utilized in ORDER BY, JOIN, and WHERE clauses to speed up data retrieval.
- Rewriting the Query: Simplify complex joins, reduce the use of subqueries and avoid SELECT when only specific columns are needed.
- Updating Statistics: Ensure the database statistics are up-to-date, as they help the query optimizer choose the most efficient execution plan.
- Partitioning Tables: Divide large tables into smaller more manageable pieces called partitions which can be queried independently.
Additionally, using the EXPLAIN statement provides insights into the query execution plan, helping identify bottlenecks and areas for optimization.
3. What is a subquery in SQL and when would you use it?
Ans:
A query nestled inside another query is called a subquery. Data is retrieved using criteria that are obtained from another query. When you need to carry out operations depending on intermediate results, subqueries come in handy. The SELECT, INSERT, UPDATE, and DELETE statements can all use them. Whether a subquery references columns from the outer query determines whether it is correlated or uncorrelated.
4. What is a self-join in SQL?
Ans:
A self-join is a specific type of join where a table is joined with itself. It is helpful for locating connections inside a single table, including hierarchical data or comparing rows. In a self join the same table appears twice in the from clause with different aliases to distinguish between the two instances. Self joins are commonly used to query hierarchical data structures like organizational charts.
5. How do the commands DELETE and TRUNCATE vary from one another?
Ans:
The DELETE command is a DML statement eliminates table rows according to a condition and can be rolled back. The TRUNCATE command is a DDL statement that removes all rows from a table and cannot be rolled back. Additionally, DELETE can be used with a WHERE clause to delete specific rows, whereas TRUNCATE removes all rows without any condition. TRUNCATE is generally faster than DELETE because it does not generate individual row delete statements.
6. What is a correlated subquery?
Ans:
A correlated subquery is a subquery references columns from the outer query. Unlike a regular subquery, which is independent, For every row that the outer query processes, a correlated subquery is examined once.This type of subquery is useful for comparing each row to a set of values. Correlated subqueries are often used in WHERE clauses to filter results based on related data.
7. What is normalization in databases?
Ans:
A database design method called normalization removes unwanted features including insertion, update and deletion anomalies and lessens data redundancy. It involves organizing the attributes and tables of a database to ensure that dependencies are properly enforced. The process typically involves dividing large tables into smaller ones and defining relationships among them. Normalization helps in maintaining data integrity and consistency.
8. What distinguishes an OUTER JOIN from an INNER JOIN?
Ans:
An INNER JOIN returns only the rows have matching values in both tables. All rows from a single table are returned via an OUTER JOIN, and the matched rows from the second table if there is no match, the result is NULL on the side of the second table. Outer joins can be further classified into LEFT, RIGHT and FULL OUTER joins, depending on which table's rows are returned when there is no match.
9. What is a trigger in PL/SQL?
Ans:
A trigger is a unique type of stored procedure that starts up automatically when the database server experiences an event. When a user attempts to alter data using a data manipulation language (DML) event, DML triggers start to run. INSERT, UPDATE, and DELETE statements on a table or view are examples of DML events.
10. What is a cursor in PL/SQL?
Ans:
A cursor in PL/SQL is a pointer to a context area that stores the SQL statement and its processing information. Implicit cursors, which are automatically created by Oracle for SQL DML statements and explicit cursors which are specified for sophisticated queries by the programmer requiring multiple fetches. Cursors allow for row-by-row processing of query results.
