What does the 'Atomicity' property in ACID guarantee?
Transactions are processed in complete isolation from each other.
A transaction either completes entirely or has no effect at all.
The database remains in a consistent state before and after a transaction.
Changes made by a committed transaction are permanent.
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?
Optimize the database server's configuration parameters.
Increase the memory allocated to the database server.
Add an index to the 'customer_city' column.
Use a stored procedure instead of ad-hoc queries.
What is the primary purpose of using a Common Table Expression (CTE) in SQL?
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.
To define a recursive relationship within a table.
To improve the performance of complex queries by storing intermediate results.
You're asked to retrieve the names of all employees who earn more than the average salary of their respective departments. This necessitates calculating the average salary per department and then comparing it to individual employee salaries. Which type of subquery would be most appropriate for this scenario?
Correlated Subquery
Scalar Subquery
Inline View
Non-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 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
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)
You are tasked with identifying products that have sold more units than the average units sold for all products in their respective category. Which SQL query structure is best suited for this scenario?
None of the above
Simple Subquery
Subquery in the FROM Clause
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?
GROUP BY clause with SUM() aggregate function
Correlated subquery with SUM() function
Window function with SUM() OVER (ORDER BY...)
Self join with aggregate function
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
Recursive CTE and aggregate functions
CROSS JOIN and GROUP BY
SELF JOIN and WHERE clause
Which ACID property ensures that changes made within a transaction are permanently written to the database if the transaction succeeds?
Durability
Consistency
Isolation
Atomicity
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 YEAR(OrderDate) = 2022 AND MONTH(OrderDate) = 12;
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate LIKE '%2022-12%';
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate >= DATEADD(day, -7, GETDATE());