What is the purpose of the ROW_NUMBER() function in SQL?
Determines the number of rows in a result set.
Returns the rank of each row based on the values in a specified column.
Calculates the running total of a numeric column.
Assigns a unique sequential integer to each row within a partition.
What is a key difference between a regular subquery and a correlated subquery?
Correlated subqueries are executed only once for the entire outer query, while regular subqueries are executed for each row.
Regular subqueries can access columns from the outer query, while correlated subqueries cannot.
Regular subqueries can modify data, while correlated subqueries are read-only.
Correlated subqueries depend on the outer query for their results, while regular subqueries are independent.
From a table 'Products', retrieve all products whose names start with 'A' or 'B' and end with 'e'.
SELECT * FROM Products WHERE ProductName LIKE 'A%e' AND ProductName LIKE 'B%e';
SELECT * FROM Products WHERE ProductName LIKE '[AB]%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName BETWEEN 'A' AND 'B' AND ProductName LIKE '%e';
What is the maximum level of recursion allowed in a recursive CTE?
It depends on the database system's configuration.
10
1,000
100
Which ACID property ensures that any changes made within a transaction are applied to the database in a manner that prevents partial updates, preserving data consistency?
Durability
Atomicity
Consistency
Isolation
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
You are using a correlated subquery to compare values in each row of a table to aggregated data from another table. What is a potential performance concern with this approach?
Correlated subqueries are not recommended for use with aggregated data.
Correlated subqueries can be computationally expensive, especially with large datasets, as the subquery might be executed repeatedly for each row of the outer query.
Correlated subqueries can lead to faster data retrieval due to their targeted nature.
Correlated subqueries are generally more efficient than joins.
What SQL statement is used to apply the changes made within a transaction permanently to the database?
SAVE
EXECUTE
UPDATE
COMMIT
You have a large table with millions of records. You frequently query the table based on a specific column 'customer_city'. What is the most effective way to improve the performance of these queries?
Add an index to the 'customer_city' column.
Use a stored procedure instead of ad-hoc queries.
Optimize the database server's configuration parameters.
Increase the memory allocated to the database server.
What is a CTE (Common Table Expression) in SQL?
A type of index used for optimizing query performance.
A permanent table structure stored in the database.
A temporary, named result set that can be referenced within a single query.
A stored procedure used to encapsulate and reuse SQL code.