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 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.
Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Scalar subquery
Inline view
Non-correlated subquery
Correlated subquery
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 Orders GROUP BY CustomerID HAVING COUNT(*) > 5
SELECT CustomerID FROM (SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID) AS CustomerOrders WHERE OrderCount > 5
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 < GETDATE() - 90;
SELECT * FROM Customers WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate < DATEADD(day, -90, GETDATE());
You are tasked with finding employees who do not manage any other employees. Which type of JOIN would be most suitable for this scenario?
FULL OUTER JOIN
INNER JOIN
LEFT JOIN
RIGHT JOIN
You want to retrieve products from a 'Products' table where the product name starts with 'App' and ends with 'e'. Which query will give you the correct result?
SELECT * FROM Products WHERE ProductName LIKE 'App_e';
SELECT * FROM Products WHERE ProductName IN ('App', 'e');
SELECT * FROM Products WHERE ProductName BETWEEN 'App' AND 'e';
SELECT * FROM Products WHERE ProductName LIKE 'App%e';
Which of the following ACID properties ensures that any changes made within a transaction are permanent, even in case of system failures?
Durability
Atomicity
Consistency
Isolation
You are tasked with finding the total sales for each month of the year. You have a table 'Orders' with columns 'OrderID', 'OrderDate', and 'SalesAmount'. Which query correctly calculates the monthly sales totals?
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY Month;
SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY MONTH(OrderDate);
What is the purpose of the ROW_NUMBER() function in SQL?
Assigns a unique sequential integer to each row within a partition.
Returns the rank of each row based on the values in a specified column.
Calculates the running total of a numeric column.
Determines the number of rows in a result set.
In SQL, a self-join is used to:
Join a table to itself using an alias.
Improve query performance by reducing the number of joins.
Combine data from multiple tables based on a common column.
Join two tables with different schemas.