Which of the following is a valid use case for a recursive CTE?
Finding the average salary of employees in a department.
Retrieving all employees and their managers in a hierarchical structure.
Updating multiple rows in a table based on a condition.
Creating a new table based on the results of a query.
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 LIKE 'App_e';
SELECT * FROM Products WHERE ProductName IN ('App', 'e');
SELECT * FROM Products WHERE ProductName BETWEEN 'App' AND 'e';
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 LIKE '%2022-12%';
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 BETWEEN '2022-12-25' AND '2022-12-31';
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
SAVEPOINT
SET TRANSACTION
ROLLBACK
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 < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate IS NOT NULL AND LastPurchaseDate < DATEADD(day, -90, GETDATE());
SELECT * FROM Customers WHERE LastPurchaseDate < GETDATE() - 90;
SELECT * FROM Customers WHERE LastPurchaseDate IS NULL;
Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Inline view
Correlated subquery
Scalar subquery
Non-correlated subquery
What is a CTE (Common Table Expression) in SQL?
A stored procedure used to encapsulate and reuse SQL code.
A type of index used for optimizing query performance.
A temporary, named result set that can be referenced within a single query.
A permanent table structure stored in the database.
What does the 'Atomicity' property in ACID guarantee?
The database remains in a consistent state before and after a transaction.
A transaction either completes entirely or has no effect at all.
Changes made by a committed transaction are permanent.
Transactions are processed in complete isolation from each other.
What is the result of a CROSS JOIN between a table with 5 rows and a table with 3 rows?
3 rows
5 rows
8 rows
15 rows
You have a table named 'Employees' with columns 'EmployeeID', 'Department', and 'Salary'. Write a SQL query to fetch the top 3 highest-paid employees from each department, along with their rank within the department.
SELECT EmployeeID, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 3;
SELECT EmployeeID, Department, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 3;
SELECT EmployeeID, Department, Salary FROM Employees ORDER BY Department, Salary DESC LIMIT 3;
WITH RankedEmployees AS ( SELECT EmployeeID, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM Employees ) SELECT EmployeeID, Department, Salary, Rank FROM RankedEmployees WHERE Rank <= 3;