79 ) sql queries to write

 

SQL Queries for Common Interview Problems

1) Find the 4th Highest Salary

Question: Write a query to find the 4th highest salary from an Employee table.

Query:

SQL

WITH RankedSalaries AS (
    SELECT 
        EmployeeID, 
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) as SalaryRank
    FROM Employees
)
SELECT DISTINCT Salary
FROM RankedSalaries
WHERE SalaryRank = 4;

2) Delete Duplicate Records

Question: Write a query to delete duplicate records from a table while keeping one unique instance.

Query:

SQL

WITH CTE_Duplicates AS (
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ROW_NUMBER() OVER (
            PARTITION BY FirstName, LastName, Email 
            ORDER BY (SELECT NULL)
        ) AS RowNum
    FROM Employees
)
DELETE FROM CTE_Duplicates
WHERE RowNum > 1;

3) Highest Salary of Each Department

Question: Write a query to display the highest salary of each department as department name and highest salary.

Query:

SQL

SELECT 
    d.DepartmentName,
    MAX(e.Salary) AS HighestSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

4) Count Number of Subjects per Student (Comma-Separated Data)

Question: Write a query to find the number of subjects for each student when the data is stored in a comma-separated format (e.g., s1, (20,40,50)).

Query:

SQL

SELECT 
    name,
    LEN(marks) - LEN(REPLACE(marks, ',', '')) + 1 AS NumberOfSubjects
FROM StudentMarks;

SQL Queries and Sample Outputs for Advanced Interview Problems (SSMS)

5) Cumulative Salary of Each Employee by Department

Question: Write a query to calculate the cumulative (running total) salary of each employee ordered by their salary within their respective department.

Query:

SQL

SELECT 
    DepartmentID,
    EmployeeID,
    EmployeeName,
    Salary,
    SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSalary
FROM Employees;

Sample Output (2 Rows):

DepartmentID	EmployeeID	EmployeeName	Salary	CumulativeSalary
10	101	Alice	5000	5000
10	102	Bob	7000	12000

6) Customers Purchasing the Same Product in Different Stores in Same Month

Question: Write a query to find customers who have purchased the same product across different physical stores within the exact same month and year.

Query:

SQL

SELECT DISTINCT 
    c.CustomerID,
    c.CustomerName,
    p.ProductID,
    FORMAT(o.OrderDate, 'yyyy-MM') AS PurchaseMonth
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
GROUP BY c.CustomerID, c.CustomerName, p.ProductID, FORMAT(o.OrderDate, 'yyyy-MM')
HAVING COUNT(DISTINCT o.StoreID) > 1;

Sample Output (2 Rows):

CustomerID	CustomerName	ProductID	PurchaseMonth
C001	John Smith	P105	2026-06
C042	Sarah Connor	P201	2026-06

7) Most Sold Product by Each State

Question: Write a query to find the most sold product (by total quantity) in each state using window ranking functions.

Query:

SQL

WITH StateProductSales AS (
    SELECT 
        s.StateName,
        p.ProductName,
        SUM(o.Quantity) AS TotalQuantitySold,
        ROW_NUMBER() OVER (PARTITION BY s.StateName ORDER BY SUM(o.Quantity) DESC) AS RN
    FROM Orders o
    JOIN Stores s ON o.StoreID = s.StoreID
    JOIN Products p ON o.ProductID = p.ProductID
    GROUP BY s.StateName, p.ProductName
)
SELECT 
    StateName,
    ProductName,
    TotalQuantitySold
FROM StateProductSales
WHERE RN = 1;

Sample Output (2 Rows):

StateName	ProductName	TotalQuantitySold
California	Wireless Mouse	1420
Texas	Mechanical Keyboard	980

8) Department-Wise Sum of Salaries and Max Salary

Question: Write a query to find the total sum of salaries and the maximum salary for each department.

Query:

SQL

SELECT 
    d.DepartmentName,
    SUM(e.Salary) AS TotalDepartmentSalary,
    MAX(e.Salary) AS MaximumDepartmentSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

Sample Output (2 Rows):

DepartmentName	TotalDepartmentSalary	MaximumDepartmentSalary
IT	45000	12000
HR	28000	9500