Expert level sql quiz

What is the difference between `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` window functions in SQL?

`ROW_NUMBER()` assigns a unique row number, `RANK()` skips ranks for ties, `DENSE_RANK()` does not skip ranks for ties

`ROW_NUMBER()` does not allow duplicates, `RANK()` assigns unique ranks, `DENSE_RANK()` is faster

`ROW_NUMBER()` assigns a rank based on order, `RANK()` sorts in reverse, `DENSE_RANK()` assigns ranks with a fixed interval

`ROW_NUMBER()` and `RANK()` are the same, `DENSE_RANK()` is slower

What does the `WITH` clause in SQL (also known as Common Table Expressions or CTE) allow you to do?

Define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE query

Create a new table for temporary use

Write complex subqueries directly in the FROM clause

Define a view that persists across queries

What is the effect of using `FOR UPDATE` in a SQL query?

It locks the rows returned by the query for update operations

It updates all rows in the result set

It ensures that the result set is sorted before returning

It restricts the result set to read-only

What is a materialized view in SQL?

A view that stores the results of a query physically and can be refreshed periodically

A temporary table that is used only for the duration of the query

A view that calculates results dynamically each time it is queried

A regular view that supports data updates

What does the `EXPLAIN` keyword do in SQL?

It shows the execution plan for a query, allowing optimization analysis

It describes the table structure and schema

It gives a detailed explanation of how a SQL query works

It runs the query in dry run mode

What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data, while a non-clustered index is a separate structure

A clustered index is faster than a non-clustered index for all queries

A clustered index can be applied to multiple columns, whereas a non-clustered index is restricted to a single column

A non-clustered index determines the physical order of data, while a clustered index does not

Which of the following is true about the `ACID` properties of transactions in SQL?

ACID stands for Atomicity, Consistency, Isolation, and Durability

ACID is a set of properties that ensures transactions are fast and efficient

ACID guarantees that all transactions are non-blocking

ACID is only applicable to NoSQL databases

What is the purpose of the `ROLLBACK` statement in SQL?

It undoes changes made by the current transaction

It commits the transaction changes to the database

It saves the current state of the database

It initiates a new transaction

In SQL, what is a deadlock and how can it be resolved?

A situation where two or more transactions are blocked forever, resolved by using `SET DEADLOCK_PRIORITY`

A situation where data becomes corrupted, resolved by restoring from backup

A situation where transactions cannot be committed, resolved by increasing the database size

A situation where queries take too long, resolved by optimizing the query execution plan

What is the purpose of the `PARTITION BY` clause in window functions in SQL?

It divides the result set into partitions for window functions to operate on

It limits the result set to a specific set of rows

It sorts the result set in descending order

It combines multiple columns into a single column