Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Durability
Consistency
Isolation
Atomicity
In SQL, what is the purpose of a transaction?
To retrieve data from a database.
To define a unit of work that must be executed as a whole to maintain database consistency.
To create a backup of the database.
To optimize query performance.
What is a key advantage of using a subquery in the FROM clause to create a derived table?
It can enhance performance by pre-calculating and storing intermediate results.
All of the above
It allows you to reuse the derived table multiple times within the same query.
It improves the readability of complex queries by breaking them down into smaller, more manageable parts.
Which of these is NOT a common SQL performance tuning technique?
Selecting only the required columns instead of using SELECT *.
Using appropriate data types for columns.
Filtering data on indexed columns whenever possible.
Using wildcard characters (%) at the beginning of a search pattern.
Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Correlated subquery
Inline view
Scalar subquery
Non-correlated subquery
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%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName BETWEEN 'A' AND 'B' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName LIKE '[AB]%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%e' AND ProductName LIKE 'B%e';
You have a table 'Customers' with a column 'LastPurchaseDate' that can contain NULL values. Write a query to retrieve all customers who have NOT made a purchase in the last 90 days.
SELECT * FROM Customers WHERE LastPurchaseDate IS NULL;
SELECT * FROM Customers WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate < GETDATE() - 90;
You have a table named 'Orders' with columns 'OrderID' and 'CustomerID'. You need to find the customers who have placed more than 5 orders. Which query achieves this using a subquery in the FROM clause?
SELECT DISTINCT CustomerID FROM Orders WHERE OrderID IN (SELECT OrderID FROM Orders GROUP BY OrderID HAVING COUNT(*) > 5)
SELECT CustomerID FROM Orders WHERE EXISTS (SELECT 1 FROM Orders WHERE CustomerID = Orders.CustomerID GROUP BY CustomerID HAVING COUNT(*) > 5)
SELECT CustomerID FROM (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID) AS CustomerOrders WHERE OrderCount > 5
SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) > 5
What is the result of a CROSS JOIN between a table with 5 rows and a table with 3 rows?
5 rows
15 rows
3 rows
8 rows
You are analyzing sales data and want to display the running total of sales for each month. What SQL concept would be MOST suitable for this task?
Window function with SUM() OVER (ORDER BY...)
GROUP BY clause with SUM() aggregate function
Correlated subquery with SUM() function
Self join with aggregate function