|
Question: What is a transaction in SQL?
|
Answer: A transaction in SQL is a logical unit of work that contains one or more SQL statements.
It is a series of operations performed on a database that must be executed as a single, indivisible unit.
Transactions ensure data integrity and consistency by either committing all changes if the transaction is
successful or rolling back all changes if an error occurs.
|
|
Question: What is a correlated subquery in SQL?
|
Answer: A correlated subquery is a subquery that depends on the outer query for its values.
It is executed once for each row processed by the outer query and can reference columns from the outer query.
Correlated subqueries are useful for filtering or computing values based on conditions from the outer query.
|
|
Question: What is a self-referential foreign key in SQL?
|
Answer: A self-referential foreign key is a foreign key within a table that references the primary key column(s)
of the same table.
It establishes a hierarchical or recursive relationship within the table, allowing each row to
relate to other rows within the same table.
|
|
|
|
|
|
Question: What is a common table expression (CTE) in SQL?
|
Answer: In SQL a common table expression (CTE) is a temporary named result set that can be referenced
within a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs allow for recursive queries, simplification of complex queries,
and the reuse of result sets within a single query.
|
|
Question: What are the uses of MAX() and MIN() functions?
|
Answer: The MAX() function returns the maximum value of a column in a result set,
while the MIN() function returns the minimum value of a column.
Both functions are used with the SELECT statement and are typically used with numerical or date columns.
|
|
Question: What is a deadlock in SQL, and how can it be prevented?
|
Answer: A deadlock in SQL occurs when two or more transactions are waiting for each other to release locks on
resources held by the other, resulting in a deadlock situation where neither transaction can proceed.
Deadlocks can be prevented by ensuring that transactions acquire locks in a consistent order,
minimizing the time locks are held, and using deadlock detection and resolution mechanisms provided by the database management system.
|
|
|
|
|
|
Question: What is a composite index in SQL?
|
Answer: A composite index, also known as a multi-column index, is an index that includes more than one column
from a table. It allows queries to efficiently search for rows based on multiple columns, improving the performance of queries
that involve conditions on those columns.
|
|
Question: What is the purpose of the BETWEEN operator in SQL?
|
Answer: The BETWEEN operator is used to retrieve rows where a value falls within a specified range,
inclusive of the endpoints. It is often used in conjunction with the WHERE clause to filter rows based on a range of values.
|