Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Correlated subquery
Scalar subquery
Non-correlated subquery
Inline view
You need to find all customers in your 'Customers' table who haven't provided a phone number. The 'PhoneNumber' column can contain NULL values. Which query achieves this?
SELECT * FROM Customers WHERE PhoneNumber IS NULL;
SELECT * FROM Customers WHERE PhoneNumber <> '';
SELECT * FROM Customers WHERE PhoneNumber NOT IN (NULL);
SELECT * FROM Customers WHERE PhoneNumber = NULL;
What does a clustered index do?
It creates a copy of the table data, sorted by the indexed column.
It improves the performance of all queries on the table.
It defines the order in which data is physically stored in the table.
It stores a pointer to the data pages where the indexed values are located.
Which ACID property ensures that changes made within a transaction are permanently written to the database if the transaction succeeds?
Atomicity
Durability
Isolation
Consistency
You are tasked with finding employees who do not manage any other employees. Which type of JOIN would be most suitable for this scenario?
RIGHT JOIN
FULL OUTER JOIN
LEFT JOIN
INNER JOIN
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 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;
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;
You need to find all employees who share the same job title as their manager. Which join operation is MOST suitable for this scenario?
CROSS JOIN
Self-JOIN
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
Inline View
Scalar Subquery
Correlated Subquery
You are given a table 'EmployeeHierarchy' with columns 'EmployeeID' and 'ManagerID', representing an employee hierarchy. Write a recursive SQL query to retrieve the entire hierarchy starting from the CEO (EmployeeID = 1).
WITH RecursiveHierarchy AS ( SELECT EmployeeID, ManagerID FROM EmployeeHierarchy WHERE EmployeeID = 1 UNION ALL SELECT eh.EmployeeID, eh.ManagerID FROM EmployeeHierarchy eh JOIN RecursiveHierarchy rh ON eh.ManagerID = rh.EmployeeID ) SELECT * FROM RecursiveHierarchy;
SELECT EmployeeID, ManagerID FROM EmployeeHierarchy WHERE ManagerID IS NULL;
SELECT EmployeeID, ManagerID FROM EmployeeHierarchy ORDER BY ManagerID;
SELECT EmployeeID, ManagerID FROM EmployeeHierarchy WHERE EmployeeID = 1;
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;