Which ACID property ensures that a transaction's changes are permanent if it completes successfully, even in the event of a system crash?
Atomicity
Durability
Isolation
Consistency
You're performing a complex transaction and realize you need to undo a portion of the changes made within the current transaction. However, you don't want to roll back the entire transaction. Which SQL feature would allow you to selectively undo a part of the transaction?
COMMIT
ROLLBACK
SET TRANSACTION
SAVEPOINT
You have a table 'Orders' with order details and want to identify the first order placed by each customer based on the order date. Which combination of concepts would be most appropriate?
Window function (ROW_NUMBER()) and CTE
CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
Recursive CTE and aggregate functions
What is a key difference between a regular subquery and a correlated subquery?
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.
Regular subqueries can access columns from the outer query, while correlated subqueries cannot.
Correlated subqueries are executed only once for the entire outer query, while regular subqueries are executed for each row.
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
To improve the performance of complex queries by storing intermediate results.
To define a recursive relationship within a table.
To create a temporary, named result set that exists only within the scope of the current query.
To replace the need for views in a database.
You have a table 'Orders' with columns 'OrderID', 'CustomerID', and 'OrderDate'. Write a SQL query to find the customers who placed orders in the last week of the year 2022.
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate BETWEEN '2022-12-25' AND '2022-12-31';
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate >= DATEADD(day, -7, GETDATE());
SELECT DISTINCT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2022 AND MONTH(OrderDate) = 12;
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate LIKE '%2022-12%';
Which window function would you use to assign a unique, sequential number to each row within a partition of a result set?
NTILE()
RANK()
ROW_NUMBER()
DENSE_RANK()
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?
Increase the memory allocated to the database server.
Use a stored procedure instead of ad-hoc queries.
Optimize the database server's configuration parameters.
Add an index to the 'customer_city' column.
In SQL, when a subquery is used in the FROM clause, it essentially acts as a:
Filter on the main query.
Sorting mechanism.
Join condition.
Temporary table.
You have a table 'Orders' with columns 'OrderID', 'CustomerID', and 'OrderDate'. You want to rank customers within each order date based on the order they placed (earliest to latest). Which window function should you use?
ROW_NUMBER() OVER (PARTITION BY OrderDate ORDER BY OrderID)
DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate)
NTILE(4) OVER (PARTITION BY OrderDate ORDER BY OrderID)
RANK() OVER (ORDER BY OrderDate, OrderID)