Questions in SQL

1. Basic Aggregations & Filters Q1: Find the second highest salary from an Employee table. The Scenario: Very common check for subquery logic. The Query: SQL SELECT MAX(Salary) AS SecondHighestSalary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees); Q2: Find the Nth highest salary from a table. The Scenario: Generalizing Q1 using LIMIT and OFFSET. (Replace 3 with N−1). The Query: SQL SELECT Salary  FROM Employees ORDER BY Salary DESC LIMIT 1 OFFSET 3; -- Finds the 4th highest salary Q3: Display the total number of employees hired per year. The Scenario: Tests date extraction and grouping functions. The Query: SQL SELECT YEAR(HireDate) AS HireYear, COUNT(EmployeeID) AS TotalHired FROM Employees GROUP BY YEAR(HireDate) ORDER BY HireYear DESC; Q4: Find departments that have more than 5 employees. The Scenario: Tests understanding of filtering aggregated data using HAVING instead of WHERE. The Query: SQL SELECT DepartmentID, COUNT(EmployeeID) AS EmployeeCount FROM Employees GROUP BY DepartmentID HAVING COUNT(EmployeeID) > 5; 2. Joins & Relational Logic Q5: Fetch all employees along with their manager's name from the same table. The Scenario: Tests Self-Join logic where a table references its own primary key. The Query: SQL SELECT e.EmployeeName AS Employee, m.EmployeeName AS Manager FROM Employees e LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID; Q6: Get a list of all departments and the total salary paid in each, including departments with zero employees. The Scenario: Tests handling missing relational links using LEFT JOIN and replacing NULL values. The Query: SQL SELECT d.DepartmentName, COALESCE(SUM(e.Salary), 0) AS TotalSalary FROM Departments d LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID GROUP BY d.DepartmentID, d.DepartmentName; Q7: Find all customers who have never placed an order. The Scenario: Identifying unlinked rows across tables. The Query: SQL SELECT c.CustomerID, c.CustomerName FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderID IS NULL; Q8: Find items that were ordered in 2025 but NOT ordered in 2026. The Scenario: Evaluates set difference operations (EXCEPT or NOT IN). The Query: SQL SELECT ProductID FROM Orders WHERE YEAR(OrderDate) = 2025 EXCEPT SELECT ProductID FROM Orders WHERE YEAR(OrderDate) = 2026; 3. Data Cleansing & Deduplication Q9: Write a query to find duplicate records in a table based on specific columns. The Scenario: Critical backend troubleshooting task for logs or IoT metrics. The Query: SQL SELECT DeviceID, Timestamp, COUNT(*) AS OccurrenceCount FROM DeviceLogs GROUP BY DeviceID, Timestamp HAVING COUNT(*) > 1; Q10: Delete duplicate rows permanently from a table, keeping only the lowest unique ID instance. The Scenario: Mutating data structures to fix duplication issues. The Query: DELETE FROM OrderItems WHERE ctid NOT IN (     SELECT MIN(ctid)     FROM OrderItems     GROUP BY OrderID, ProductID ); 4. Advanced Window Functions Q11: Rank employees within each department based on their salary. The Scenario: Tests Window Functions (DENSE_RANK()) to group and sort subsets without collapsing rows. The Query: SQL SELECT EmployeeID, DepartmentID, Salary,        DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank FROM Employees; Q12: Fetch the top 3 highest-paid employees from every department. The Scenario: Building on top of window rankings using a Common Table Expression (CTE). The Query: SQL WITH RankedEmployees AS (     SELECT EmployeeID, DepartmentID, Salary,            DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS UserRank     FROM Employees ) SELECT DepartmentID, EmployeeID, Salary FROM RankedEmployees WHERE UserRank <= 3; Q13: Calculate the running total of sales orders sorted by order date. The Scenario: Essential for building financial metrics and real-time ledger histories. The Query: SELECT      OrderID,     OrderDate,     Amount,          SUM(Amount) OVER (         ORDER BY OrderDate     ) AS RunningTotal FROM SalesOrders; Q14: Find the difference in sales amount between the current order and the previous order. The Scenario: Accessing surrounding rows via analytic lookups using LAG(). The Query: SQL SELECT OrderID, OrderAmount,        LAG(OrderAmount, 1) OVER (ORDER BY OrderDate) AS PreviousOrderAmount,        (OrderAmount - LAG(OrderAmount, 1) OVER (ORDER BY OrderDate)) AS PerformanceDelta FROM SalesOrders; 5. Complex Transformations & String Logic Q15: Swap values of a 'Gender' column dynamically (Change 'M' to 'F' and vice-versa) in a single update statement. The Scenario: Tests inline conditional statements. The Query: SQL UPDATE Users SET Gender = CASE      WHEN Gender = 'M' THEN 'F'     WHEN Gender = 'F' THEN 'M'     ELSE Gender  END; Q16: Query employees whose names start with 'A' and are at least 5 characters long. The Scenario: Evaluates Pattern Matching strings (LIKE and Wildcards). The Query: SQL SELECT EmployeeName  FROM Employees  WHERE EmployeeName LIKE 'A____%'; -- 'A' followed by 4 underscores and any trailing text Q17: Capitalize only the first letter of a lowercase string column. The Scenario: String slicing, transformations, and formatting operations. The Query: SQL SELECT CONCAT(UPPER(SUBSTRING(Username, 1, 1)), LOWER(SUBSTRING(Username, 2))) AS CleanedName FROM Users; 6. Advanced Business Logic & Subqueries Q18: Find employees who earn more than the average salary of their specific department. The Scenario: Tests Correlated Subqueries where the inner loop relies on outer context variables. The Query: SQL SELECT e1.EmployeeID, e1.DepartmentID, e1.Salary FROM Employees e1 WHERE e1.Salary > (     SELECT AVG(e2.Salary)      FROM Employees e2      WHERE e2.DepartmentID = e1.DepartmentID ); Q19: Pivot order statuses into distinct layout columns to show statistics overview summary metrics. The Scenario: Modifying vertical table structures into horizontal reporting matrix panels. The Query: SQL SELECT      COUNT(CASE WHEN Status = 'PENDING' THEN 1 END) AS PendingCount,     COUNT(CASE WHEN Status = 'COMPLETED' THEN 1 END) AS CompletedCount,     COUNT(CASE WHEN Status = 'CANCELLED' THEN 1 END) AS CancelledCount FROM Orders; Q20: Find active sessions where users logged in consecutively for 3 or more days. The Scenario: High-difficulty query testing user retention tracking. The Query: SQL WITH DateRanked AS (     SELECT UserID, LoginDate,            -- Subtracting sequential ranks normalizes continuous groupings into static dates            LoginDate - INTERVAL (DENSE_RANK() OVER (PARTITION BY UserID ORDER BY LoginDate)) DAY AS GroupingDate     FROM UserLogins ) SELECT UserID FROM DateRanked GROUP BY UserID, GroupingDate HAVING COUNT(*) >= 3;