You are joining tables 'Customers' and 'Orders' using NATURAL JOIN. Both tables have a column named 'CustomerID'. What happens during the join?
Both 'CustomerID' columns are included, potentially with duplicate data.
Only the 'CustomerID' from 'Customers' is included.
The 'CustomerID' columns are used for the join, avoiding duplicate columns in the result.
The join fails because column names are identical.
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?
Non-Correlated Subquery
Scalar Subquery
Correlated Subquery
Inline View
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.
Determines the number of rows in a result set.
Calculates the running total of a numeric column.
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 ORDER BY MONTH(OrderDate);
SELECT EXTRACT(MONTH FROM OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders ORDER BY Month;
SELECT MONTH(OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
SELECT STRFTIME('%Y-%m', OrderDate) AS Month, SUM(SalesAmount) AS TotalSales FROM Orders GROUP BY Month;
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 LIKE 'App_e';
SELECT * FROM Products WHERE ProductName BETWEEN 'App' AND 'e';
What is the maximum level of recursion allowed in a recursive CTE?
100
It depends on the database system's configuration.
1,000
10
What is a disadvantage of having too many indexes on a table?
Reduced data integrity because of potential index corruption.
Increased storage space required for the indexes.
Increased complexity in managing the database schema.
Slower query execution due to the overhead of maintaining the indexes.
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 >= DATEADD(day, -7, GETDATE());
SELECT DISTINCT CustomerID FROM Orders WHERE OrderDate LIKE '%2022-12%';
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 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
From a table 'Products', retrieve all products whose names start with 'A' or 'B' and end with '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';
SELECT * FROM Products WHERE ProductName LIKE 'A%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';