SQL
Question and Answers
Q1: Find the second highest salary in a
table where duplicate salaries exist.
SELECT MAX(Salary) AS
SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary)
FROM Employees);
Q2: Find the longest streak of consecutive
orders placed by the same customer.
WITH RankedOrders AS (
SELECT
CustomerID,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY
CustomerID ORDER BY OrderDate) AS
RowNum
FROM Orders
),
Streaks AS (
SELECT
CustomerID,
OrderDate,
RowNum - DENSE_RANK() OVER
(PARTITION BY CustomerID ORDER BY
OrderDate) AS StreakGroup
FROM RankedOrders
)
SELECT
CustomerID,
COUNT(*) AS LongestStreak
FROM Streaks
GROUP BY CustomerID, StreakGroup
ORDER BY LongestStreak DESC
LIMIT 1;
Q3: Retrieve the name of the
employee(s) whose salary is greater than
the average salary of their department.
SELECT E.EmployeeName
FROM Employees E
JOIN Departments D ON E.DepartmentID =
D.DepartmentID
WHERE E.Salary > (
SELECT AVG(E1.Salary)
FROM Employees E1
WHERE E1.DepartmentID =
D.DepartmentID);
Q4: Find the customers who have made
purchases in every month of the year.
SELECT CustomerID
FROM Orders
WHERE YEAR(PurchaseDate) =
YEAR(CURRENT_DATE)
GROUP BY CustomerID
HAVING COUNT(DISTINCT
MONTH(PurchaseDate)) = 12;
Q5: Identify all products that have never
been ordered but are still listed in the
Products table.
SELECT ProductID, ProductName
FROM Products
WHERE ProductID NOT IN (SELECT
DISTINCT ProductID FROM Orders);
Q6: Calculate the cumulative revenue for
each product category over time.
SELECT
Category,
Date,
SUM(Revenue) OVER (PARTITION BY
Category ORDER BY Date) AS
CumulativeRevenue
FROM Sales;
Q7: Identify the top 3 highest-grossing
products per category using window
functions.
SELECT *
FROM (
SELECT
ProductID,
Category,
Sales,
RANK() OVER (PARTITION BY Category
ORDER BY Sales DESC) AS Rank
FROM Products
) RankedProducts WHERE Rank <= 3;
Q8: Find gaps in a sequential InvoiceID
column and output the range of missing
IDs.
WITH Gaps AS (
SELECT
InvoiceID + 1 AS StartGap,
LEAD(InvoiceID) OVER (ORDER BY
InvoiceID) - 1 AS EndGap
FROM Invoices
)
SELECT StartGap, EndGap
FROM Gaps WHERE StartGap <= EndGap;
Q9: Find the most recent manager for
each employee in a hierarchical structure.
SELECT
E.EmployeeID,
M.ManagerID,
M.AssignDate
FROM Employees E
JOIN Managers M ON E.EmployeeID =
M.EmployeeID
WHERE M.AssignDate = (
SELECT MAX(AssignDate)
FROM Managers
WHERE EmployeeID = E.EmployeeID);
Q10: Identify customers who made
purchases in the last 3 months but not
during the same period last year.
SELECT DISTINCT CustomerID
FROM Orders
WHERE PurchaseDate >=
DATEADD(MONTH, -3, CURRENT_DATE)
AND CustomerID NOT IN (
SELECT CustomerID FROM Orders
WHERE PurchaseDate BETWEEN
DATEADD(YEAR, -1, DATEADD(MONTH, -3,
CURRENT_DATE)) AND DATEADD(YEAR, -1,
CURRENT_DATE));
Q11: Create a pivot table showing total
sales for each product by month.
SELECT ProductID,
SUM(CASE WHEN MONTH(OrderDate)
= 1 THEN Sales ELSE 0 END) AS JanSales,
SUM(CASE WHEN MONTH(OrderDate)
= 2 THEN Sales ELSE 0 END) AS FebSales,
...
SUM(CASE WHEN MONTH(OrderDate)
= 12 THEN Sales ELSE 0 END) AS DecSales
FROM Orders
GROUP BY ProductID;
Q12: Write a recursive query to calculate
the factorial of a given number stored in
a table.
WITH RECURSIVE Factorial(Number,
Result) AS (
SELECT Number, 1
FROM Numbers
WHERE Number = 1
UNION ALL
SELECT N.Number, F.Result * N.Number
FROM Numbers N
JOIN Factorial F ON N.Number =
F.Number + 1
)
SELECT Result
FROM Factorial
WHERE Number = (SELECT MAX(Number)
FROM Numbers);
Q13: Find overlapping events that
occurred within the same hour.
SELECT E1.EventID, E2.EventID
FROM Events E1, Events E2
WHERE E1.EventID < E2.EventID
AND ABS(TIMESTAMPDIFF(MINUTE,
E1.Timestamp, E2.Timestamp)) <= 60;
Q14: Find employees who have worked in
more than two departments
simultaneously.
SELECT EmployeeID
FROM Assignments
GROUP BY EmployeeID, StartDate,
EndDate
HAVING COUNT(DISTINCT DepartmentID)
> 2;
Q15: Determine if any subsets of
products have the same total price.
SELECT COUNT(*)
FROM (
SELECT SUM(Price) AS SubsetSum
FROM Products
GROUP BY ProductID
HAVING COUNT(*) > 1) Subsets;
Q16: Fetch the median salary without
using aggregate functions twice.
SELECT AVG(Salary)
FROM (
SELECT Salary
FROM Employees
ORDER BY Salary
LIMIT 2 OFFSET (SELECT COUNT(*)
FROM Employees) / 2 - 1) MedianSalaries;
Q17: Identify user sessions with more
than 10 requests in 5 minutes.
SELECT UserID, COUNT(*) AS
RequestCount
FROM Requests
WHERE Timestamp >= DATEADD(MINUTE,
-5, Timestamp)
GROUP BY UserID HAVING COUNT(*) > 10;
Q18: Delete duplicate rows while
retaining the most recent.
DELETE FROM Employees
WHERE EmployeeID NOT IN (
SELECT MAX(EmployeeID)
FROM Employees
GROUP BY Name, Department
);
Q19: Fetch the nth highest salary in a
large table.
SELECT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET N-1;
Q20: Improve performance for fetching
product sales by category over a date
range.
CREATE INDEX idx_category_date ON
Sales(Category, SaleDate);
SELECT Category, SUM(Sales)
FROM Sales
WHERE SaleDate BETWEEN '2024-01-01'
AND '2024-12-31'
GROUP BY Category;
For Career Guidance,
Check Out our page:
www.nityacloudtech.com
