SQL Queries Code Interviews QA 50 plus

By

Here are SQL-focused interview questions with only the relevant SQL code:

1. Find the second highest salary from an Employee table.

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

Using ROW_NUMBER():

WITH RankedSalaries AS (
  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
  FROM Employees
)
SELECT Salary AS SecondHighestSalary
FROM RankedSalaries
WHERE Rank = 2;


---

2. Write a query to calculate a running total of sales.

SELECT
  OrderID,
  OrderDate,
  Amount,
  SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;


---

3. Retrieve customers who placed no orders using a LEFT JOIN.

SELECT c.CustomerID, c.CustomerName
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL;


---

4. Write a query to find the top 3 highest salaries.

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

Using DENSE_RANK():

WITH RankedSalaries AS (
  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
  FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank <= 3;


---

5. Find duplicate rows in a table.

SELECT Column1, Column2, COUNT(*)
FROM TableName
GROUP BY Column1, Column2
HAVING COUNT(*) > 1;


---

6. Delete duplicate rows while keeping one.

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY ID) AS RowNum
  FROM TableName
)
DELETE FROM TableName
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);


---

7. Write a query to calculate a 3-month rolling average of revenue.

SELECT
  Month,
  AVG(Revenue) OVER (
    ORDER BY Month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS RollingAvgRevenue
FROM RevenueTable;


---

8. Query to pivot table data (convert rows to columns).

SELECT ProductID,
       SUM(CASE WHEN Month = 'Jan' THEN Sales ELSE 0 END) AS JanSales,
       SUM(CASE WHEN Month = 'Feb' THEN Sales ELSE 0 END) AS FebSales
FROM SalesTable
GROUP BY ProductID;


---

9. Query to find employees who earn more than their manager.

SELECT e.EmployeeID, e.Salary, m.Salary AS ManagerSalary
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE e.Salary > m.Salary;


---

10. Find the nth highest salary using a subquery.

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET (n-1);

Here are more SQL queries covering advanced concepts and common interview scenarios:


---

11. Find employees hired in the last 30 days.

SELECT *
FROM Employees
WHERE HireDate >= DATEADD(DAY, -30, GETDATE());


---

12. Query to identify gaps in sequential IDs.

SELECT ID + 1 AS MissingID
FROM TableName
WHERE NOT EXISTS (
  SELECT 1 FROM TableName AS t WHERE t.ID = TableName.ID + 1
);


---

13. Write a query to transpose columns to rows.

SELECT ProductID, 'Jan' AS Month, JanSales AS Sales FROM SalesTable
UNION ALL
SELECT ProductID, 'Feb' AS Month, FebSales AS Sales FROM SalesTable;


---

14. Query to find the cumulative percentage of sales.

WITH CTE AS (
  SELECT ProductID, Sales, SUM(Sales) OVER () AS TotalSales
  FROM SalesTable
),
RankedSales AS (
  SELECT ProductID, Sales, SUM(Sales) OVER (ORDER BY Sales DESC) AS CumulativeSales, TotalSales
  FROM CTE
)
SELECT ProductID, Sales, (CumulativeSales * 1.0 / TotalSales) * 100 AS CumulativePercentage
FROM RankedSales;


---

15. Find customers with orders greater than the average order value.

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING AVG(OrderAmount) > (SELECT AVG(OrderAmount) FROM Orders);


---

16. Write a query to fetch common records between two tables.

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID;


---

17. Query to find employees belonging to multiple departments.

SELECT EmployeeID
FROM EmployeeDepartments
GROUP BY EmployeeID
HAVING COUNT(DISTINCT DepartmentID) > 1;


---

18. Query to get the top N salaries per department.

WITH RankedSalaries AS (
  SELECT DepartmentID, EmployeeID, Salary,
         RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
  FROM Employees
)
SELECT DepartmentID, EmployeeID, Salary
FROM RankedSalaries
WHERE Rank <= N;


---

19. Find the median salary of employees.

SELECT AVG(Salary) AS MedianSalary
FROM (
  SELECT Salary
  FROM Employees
  ORDER BY Salary
  OFFSET (SELECT COUNT(*) / 2 FROM Employees) ROWS
  FETCH NEXT 1 ROWS ONLY
) AS MedianSubquery;


---

20. Query to delete duplicate rows based on multiple columns.

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY ID) AS RowNum
  FROM TableName
)
DELETE FROM TableName
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);


---

21. Find the first and last order date for each customer.

SELECT CustomerID,
       MIN(OrderDate) AS FirstOrderDate,
       MAX(OrderDate) AS LastOrderDate
FROM Orders
GROUP BY CustomerID;


---

22. Query to calculate the percentage change in sales between two months.

SELECT Month, 
       (Sales - LAG(Sales) OVER (ORDER BY Month)) * 100.0 / LAG(Sales) OVER (ORDER BY Month) AS PercentageChange
FROM SalesTable;


---

23. Write a query to display employee IDs along with their managers in a hierarchy.

WITH ManagerHierarchy AS (
  SELECT EmployeeID, ManagerID, 1 AS Level
  FROM Employees
  WHERE ManagerID IS NOT NULL
  UNION ALL
  SELECT e.EmployeeID, m.ManagerID, Level + 1
  FROM Employees e
  JOIN ManagerHierarchy m ON e.ManagerID = m.EmployeeID
)
SELECT * FROM ManagerHierarchy;


---

24. Write a query to calculate a moving average over a 7-day period.

SELECT Date,
       AVG(Sales) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MovingAverage
FROM SalesTable;


---

25. Query to find customers who have placed exactly 3 orders.

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) = 3;

Here’s an extended list of advanced SQL queries covering various real-world scenarios:


---

26. Query to find the highest salary in each department.

SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;


---

27. Write a query to find all employees who have never received a bonus.

SELECT e.EmployeeID, e.Name
FROM Employees e
LEFT JOIN Bonuses b ON e.EmployeeID = b.EmployeeID
WHERE b.BonusAmount IS NULL;


---

28. Query to count the number of orders placed by each customer and rank them.

SELECT CustomerID, COUNT(OrderID) AS OrderCount,
       RANK() OVER (ORDER BY COUNT(OrderID) DESC) AS Rank
FROM Orders
GROUP BY CustomerID;


---

29. Find customers who placed orders on two consecutive days.

SELECT DISTINCT o1.CustomerID
FROM Orders o1
JOIN Orders o2
ON o1.CustomerID = o2.CustomerID AND DATEDIFF(DAY, o1.OrderDate, o2.OrderDate) = 1;


---

30. Query to find all products that have never been sold.

SELECT ProductID, ProductName
FROM Products
WHERE ProductID NOT IN (SELECT DISTINCT ProductID FROM OrderDetails);


---

31. Write a query to group sales by quarter and calculate total revenue.

SELECT 
  DATEPART(YEAR, OrderDate) AS Year,
  DATEPART(QUARTER, OrderDate) AS Quarter,
  SUM(TotalAmount) AS TotalRevenue
FROM Orders
GROUP BY DATEPART(YEAR, OrderDate), DATEPART(QUARTER, OrderDate);


---

32. Find overlapping date ranges in a booking system.

SELECT b1.BookingID, b2.BookingID
FROM Bookings b1
JOIN Bookings b2
ON b1.BookingID != b2.BookingID
AND b1.StartDate <= b2.EndDate
AND b2.StartDate <= b1.EndDate;


---

33. Query to find the total number of employees hired each month.

SELECT FORMAT(HireDate, 'yyyy-MM') AS HireMonth, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY FORMAT(HireDate, 'yyyy-MM');


---

34. Write a query to calculate the ratio of male to female employees.

SELECT 
  SUM(CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END) * 1.0 / 
  SUM(CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END) AS MaleToFemaleRatio
FROM Employees;


---

35. Query to find the order that generated the highest revenue.

SELECT TOP 1 OrderID, SUM(Amount) AS TotalRevenue
FROM OrderDetails
GROUP BY OrderID
ORDER BY TotalRevenue DESC;


---

36. Query to list employees whose salaries are above their department’s average salary.

SELECT EmployeeID, Name, Salary, DepartmentID
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);


---

37. Find consecutive dates where no orders were placed.

SELECT Date
FROM Calendar
WHERE Date NOT IN (SELECT DISTINCT OrderDate FROM Orders)
AND EXISTS (
  SELECT 1 FROM Calendar c
  WHERE c.Date = Calendar.Date + 1
  AND c.Date NOT IN (SELECT DISTINCT OrderDate FROM Orders)
);


---

38. Query to find employees with more than one manager.

SELECT EmployeeID
FROM EmployeeManagers
GROUP BY EmployeeID
HAVING COUNT(ManagerID) > 1;


---

39. Write a query to fetch the nth largest value in a column without using LIMIT or OFFSET.

SELECT MIN(Salary) AS NthHighestSalary
FROM Employees
WHERE Salary IN (
  SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC FETCH FIRST n ROWS ONLY
);


---

40. Query to fetch employees who report indirectly to the CEO.

WITH Hierarchy AS (
  SELECT EmployeeID, ManagerID, 0 AS Level
  FROM Employees
  WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, h.Level + 1
  FROM Employees e
  JOIN Hierarchy h ON e.ManagerID = h.EmployeeID
)
SELECT EmployeeID
FROM Hierarchy
WHERE Level > 1;


---

41. Query to identify duplicate entries in a column.

SELECT ColumnName, COUNT(*) AS DuplicateCount
FROM TableName
GROUP BY ColumnName
HAVING COUNT(*) > 1;


---

42. Query to delete rows with NULL values in any column.

DELETE FROM TableName
WHERE Column1 IS NULL OR Column2 IS NULL OR Column3 IS NULL;


---

43. Write a query to calculate the age of employees based on their birth date.

SELECT EmployeeID, Name, DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age
FROM Employees;


---

44. Query to find the average, minimum, and maximum order value for each customer.

SELECT CustomerID, 
       AVG(OrderAmount) AS AvgOrderValue, 
       MIN(OrderAmount) AS MinOrderValue, 
       MAX(OrderAmount) AS MaxOrderValue
FROM Orders
GROUP BY CustomerID;


---

45. Query to calculate the rank of products based on total sales.

SELECT ProductID, SUM(SalesAmount) AS TotalSales,
       RANK() OVER (ORDER BY SUM(SalesAmount) DESC) AS Rank
FROM Sales
GROUP BY ProductID;

---

46. Query to find the longest streak of consecutive days with sales.

WITH ConsecutiveDays AS (
  SELECT SaleDate, 
         ROW_NUMBER() OVER (ORDER BY SaleDate) - 
         DATEDIFF(DAY, MIN(SaleDate) OVER (), SaleDate) AS StreakGroup
  FROM Sales
)
SELECT MIN(SaleDate) AS StartDate, MAX(SaleDate) AS EndDate, COUNT(*) AS StreakLength
FROM ConsecutiveDays
GROUP BY StreakGroup
ORDER BY StreakLength DESC;


---

47. Find employees with salaries in the top 10% of their department.

WITH DepartmentSalary AS (
  SELECT DepartmentID, Salary,
         NTILE(10) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Percentile
  FROM Employees
)
SELECT EmployeeID, DepartmentID, Salary
FROM DepartmentSalary
WHERE Percentile = 1;


---

48. Query to calculate the difference between two successive transactions for each customer.

SELECT CustomerID, TransactionID, 
       Amount - LAG(Amount) OVER (PARTITION BY CustomerID ORDER BY TransactionDate) AS Difference
FROM Transactions;


---

49. Write a query to get the average processing time for each day of the week.

SELECT DATENAME(WEEKDAY, ProcessingDate) AS DayOfWeek,
       AVG(DATEDIFF(MINUTE, StartTime, EndTime)) AS AvgProcessingTime
FROM Processes
GROUP BY DATENAME(WEEKDAY, ProcessingDate);


---

50. Query to calculate total sales for the current month and previous month for comparison.

SELECT 
  SUM(CASE WHEN MONTH(OrderDate) = MONTH(GETDATE()) THEN TotalAmount ELSE 0 END) AS CurrentMonthSales,
  SUM(CASE WHEN MONTH(OrderDate) = MONTH(GETDATE()) - 1 THEN TotalAmount ELSE 0 END) AS PreviousMonthSales
FROM Orders;


---

51. Query to get the cumulative count of users registered by each date.

SELECT RegistrationDate, 
       COUNT(*) OVER (ORDER BY RegistrationDate ROWS UNBOUNDED PRECEDING) AS CumulativeUsers
FROM Users;


---

52. Find overlapping intervals for meetings.

SELECT m1.MeetingID, m2.MeetingID
FROM Meetings m1
JOIN Meetings m2 
ON m1.MeetingID != m2.MeetingID 
   AND m1.StartTime < m2.EndTime
   AND m2.StartTime < m1.EndTime;


---

53. Query to fetch products with no orders in the last 6 months.

SELECT ProductID, ProductName
FROM Products
WHERE ProductID NOT IN (
  SELECT DISTINCT ProductID
  FROM Orders
  WHERE OrderDate >= DATEADD(MONTH, -6, GETDATE())
);


---

54. Write a query to find employees who joined in the same month and year as their manager.

SELECT e.EmployeeID, e.Name, e.HireDate, e.ManagerID
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE MONTH(e.HireDate) = MONTH(m.HireDate)
  AND YEAR(e.HireDate) = YEAR(m.HireDate);


---

55. Query to retrieve all pairs of customers who have ordered the same product.

SELECT DISTINCT o1.CustomerID AS Customer1, o2.CustomerID AS Customer2
FROM Orders o1
JOIN Orders o2 ON o1.ProductID = o2.ProductID
WHERE o1.CustomerID < o2.CustomerID;


---

56. Write a query to calculate the average revenue per user (ARPU) per month.

SELECT FORMAT(OrderDate, 'yyyy-MM') AS Month,
       SUM(TotalAmount) * 1.0 / COUNT(DISTINCT CustomerID) AS ARPU
FROM Orders
GROUP BY FORMAT(OrderDate, 'yyyy-MM');


---

57. Find the highest-priced product in each category and its rank globally.

WITH CategoryRanks AS (
  SELECT CategoryID, ProductID, Price,
         RANK() OVER (PARTITION BY CategoryID ORDER BY Price DESC) AS CategoryRank,
         RANK() OVER (ORDER BY Price DESC) AS GlobalRank
  FROM Products
)
SELECT * 
FROM CategoryRanks
WHERE CategoryRank = 1;


---

58. Query to list all employees who are part of the longest reporting chain.

WITH EmployeeHierarchy AS (
  SELECT EmployeeID, ManagerID, 1 AS Level
  FROM Employees
  WHERE ManagerID IS NOT NULL
  UNION ALL
  SELECT e.EmployeeID, h.ManagerID, h.Level + 1
  FROM Employees e
  JOIN EmployeeHierarchy h ON e.ManagerID = h.EmployeeID
)
SELECT EmployeeID
FROM EmployeeHierarchy
WHERE Level = (SELECT MAX(Level) FROM EmployeeHierarchy);


---

59. Calculate the percentage contribution of each product to total sales.

SELECT ProductID, 
       SUM(SalesAmount) AS TotalSales,
       (SUM(SalesAmount) * 100.0 / SUM(SUM(SalesAmount)) OVER ()) AS PercentageContribution
FROM Sales
GROUP BY ProductID;


---

60. Query to find rows where a column's value changes compared to the previous row.

SELECT ID, ColumnName
FROM (
  SELECT ID, ColumnName, 
         LAG(ColumnName) OVER (ORDER BY ID) AS PreviousValue
  FROM TableName
) AS Changes
WHERE ColumnName != PreviousValue OR PreviousValue IS NULL;


---

61. Query to compute the rolling sum of sales for the last 7 days.

SELECT Date, 
       SUM(Sales) OVER (ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RollingSum
FROM SalesData;


---

62. Query to find customers who have only ever ordered a single product.

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(DISTINCT ProductID) = 1;


---

63. Write a query to get the revenue contribution of new customers each month.

WITH CustomerFirstOrders AS (
  SELECT CustomerID, MIN(OrderDate) AS FirstOrderDate
  FROM Orders
  GROUP BY CustomerID
)
SELECT FORMAT(o.OrderDate, 'yyyy-MM') AS Month,
       SUM(o.TotalAmount) AS RevenueFromNewCustomers
FROM Orders o
JOIN CustomerFirstOrders cfo ON o.CustomerID = cfo.CustomerID
WHERE FORMAT(o.OrderDate, 'yyyy-MM') = FORMAT(cfo.FirstOrderDate, 'yyyy-MM')
GROUP BY FORMAT(o.OrderDate, 'yyyy-MM');


---

64. Query to detect circular dependencies in a hierarchy.

WITH RecursiveHierarchy AS (
  SELECT EmployeeID, ManagerID, CAST(EmployeeID AS VARCHAR(MAX)) AS Path
  FROM Employees
  WHERE ManagerID IS NOT NULL
  UNION ALL
  SELECT e.EmployeeID, e.ManagerID, CONCAT(r.Path, '->', e.EmployeeID)
  FROM Employees e
  JOIN RecursiveHierarchy r ON e.ManagerID = r.EmployeeID
  WHERE CHARINDEX(CAST(e.EmployeeID AS VARCHAR(MAX)), r.Path) = 0
)
SELECT * 
FROM RecursiveHierarchy
WHERE CHARINDEX('->', Path) > 0;






Comments

Post a Comment

Share this to your friends

Popular posts from this blog

"How to maintain or retain tabs in same tab after button click events or postback?" using JQuery in ASP.NET C#

By
Image
In this post I'll share an details about " How to maintain or retain tabs in same tab after button click events or postback? " Step 1: you need to download Jquery and JQueryUI Javascript libraries from this site http://jqueryui.com/ Step 2: As usually you can create ASP.NET website from Visual Studio IDE and add Jquery and JqueryUI plugins in the header section of aspx page. Step 3: Add HiddenField control inside aspx page which is very useful to retain tab in same page Step 4: Use the HiddenField ID in Jquery code to indicate that CurrentTab Index Step 5: In code Behind, using Enumerations concept give the tab index values as user defined variable  Step 6: Use the Enum values in every Button click events on different tabs to check that tab could be retained in the same tab Further, Here I'll give the code details and snap shot pictures, 1. Default.aspx: Design Page First Second Third ...
Read more

Login and Registration forms in C# windows application with Back end Microsoft SQL Server for data access

By
Image
In this article, I'm gonna share about how to make login and register form with MS SQL database; 1. Flow Chart Logic 2. Normal Features 3. Form Designs Login Form Design Sign in Form Design Password Retrieve Form 4. Database Design and SQL queries and Stored Procedure Create new Database as "schooldata" create table registerdata (  ID int identity,  Username nvarchar(100),  Password nvarchar(100),  Fullname  nvarchar(100),  MobileNO nvarchar(100),  EmailID nvarchar(100)  ) select * from registerdata create procedure regis (  @Username as nvarchar(100),  @Password as nvarchar(100),  @Fullname as nvarchar(100),  @MobileNO as nvarchar(100),  @EmailID as nvarchar(100)  ) as begin insert into registerdata (Username, Password, Fullname, MobileNO,EmailID) values (@Username, @Password, @Fullname, @MobileNO, @EmailID) ...
Read more