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;
SELECT EmployeeID, Department, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 3;
SELECT EmployeeID, Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank FROM Employees WHERE Rank <= 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;
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
Correlated Subquery
Scalar Subquery
Which SQL statement is used to discard the changes made within a transaction and revert to the previous state?
DISCARD
ROLLBACK
REVERT
UNDO
You have a table of orders and want to calculate the running total of order amounts for each customer, ordered by order date. Which SQL feature would be most efficient for this task?
Cross Join
Self Join
Window Function
Recursive CTE
From a table 'Products', retrieve all products whose names start with 'A' or 'B' and end with 'e'.
SELECT * FROM Products WHERE ProductName LIKE 'A%' OR ProductName LIKE 'B%' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName BETWEEN 'A' AND 'B' AND ProductName LIKE '%e';
SELECT * FROM Products WHERE ProductName LIKE 'A%e' AND ProductName LIKE 'B%e';
SELECT * FROM Products WHERE ProductName LIKE '[AB]%e';
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?
Self join with aggregate function
Window function with SUM() OVER (ORDER BY...)
Correlated subquery with SUM() function
GROUP BY clause with SUM() aggregate function
Which type of subquery can access columns from the outer query, potentially leading to performance implications?
Correlated subquery
Non-correlated subquery
Scalar subquery
Inline view
What is a disadvantage of having too many indexes on a table?
Increased storage space required for the indexes.
Reduced data integrity because of potential index corruption.
Slower query execution due to the overhead of maintaining the indexes.
Increased complexity in managing the database schema.
In SQL, a self-join is used to:
Combine data from multiple tables based on a common column.
Join a table to itself using an alias.
Join two tables with different schemas.
Improve query performance by reducing the number of joins.
You need to represent a hierarchical organization structure in a relational database table. Which approach is most suitable for querying and traversing this hierarchical data?
Storing the entire hierarchy as a single string in a column
Using multiple tables with foreign key relationships to represent different levels
Creating a separate table for each level of the hierarchy
Implementing a Recursive CTE to query and navigate the hierarchy