Coding/Practical SQL Interview Questions

1. Find the second highest salary in a table.

 Using DISTINCT and ORDER BY with LIMIT / OFFSET (MySQL, PostgreSQL)

SQL

SELECT DISTINCT Salary

FROM Employees

ORDER BY Salary DESC

LIMIT 1 OFFSET 1;

 

• ORDER BY Salary DESC: Sorts salaries from highest to lowest.

• OFFSET 1: Skips the highest salary.

• LIMIT 1: Returns the second highest.

✅ Using a Subquery (Works in most SQL dialects)

SQL

SELECT MAX(Salary) AS SecondHighest

FROM Employees

WHERE Salary < (

    SELECT MAX(Salary) FROM Employees

);

 

• The inner query gets the highest salary.

• The outer query finds the maximum salary less than the highest, i.e., the second highest.

✅ Using ROW_NUMBER() (SQL Server, PostgreSQL, Oracle)

SQL

WITH RankedSalaries AS (

  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank

  FROM Employees

)

SELECT Salary

FROM RankedSalaries

WHERE Rank = 2;

 

 

 

2. Get the nth highest salary without using TOP or LIMIT.

To find the nth highest salary in a table without using TOP or LIMIT, you can use the DENSE_RANK() or ROW_NUMBER() window function, depending on whether you want to handle duplicates.

✅ Using DENSE_RANK() (Handles duplicates)

SQL

WITH RankedSalaries AS (

  SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank

  FROM Employees

)

SELECT Salary

FROM RankedSalaries

WHERE Rank = n;  -- Replace n with the desired rank

 

• DENSE_RANK() assigns the same rank to duplicate salaries.

• This returns all salaries that are tied at the nth rank.

✅ Using ROW_NUMBER() (Ignores duplicates)

SQL

WITH RankedSalaries AS (

  SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum

  FROM Employees

)

SELECT Salary

FROM RankedSalaries

WHERE RowNum = n;  -- Replace n with the desired position

 

• ROW_NUMBER() gives a unique number to each row.

• This returns exactly one salary, even if there are duplicates.

🧠 Tip:

• Use DENSE_RANK() if you want to treat duplicate salaries as the same rank.

• Use ROW_NUMBER() if you want strict positional ranking.

 

 

3. Find employees who earn more than the average salary of their department.

To find employees who earn more than the average salary of their department, you can use a correlated subquery or a CTE with a join. Here's how both approaches work:

✅ 1. Using a Correlated Subquery

SQL

SELECT EmployeeID, Name, DepartmentID, Salary

FROM Employees E

WHERE Salary > (

    SELECT AVG(Salary)

    FROM Employees

    WHERE DepartmentID = E.DepartmentID

);

 

• The subquery calculates the average salary per department.

• The outer query selects employees whose salary is greater than that average.

✅ 2. Using a CTE (Common Table Expression)

SQL

WITH DepartmentAvg AS (

    SELECT DepartmentID, AVG(Salary) AS AvgSalary

    FROM Employees

    GROUP BY DepartmentID

)

SELECT E.EmployeeID, E.Name, E.DepartmentID, E.Salary

FROM Employees E

JOIN DepartmentAvg D ON E.DepartmentID = D.DepartmentID

WHERE E.Salary > D.AvgSalary;

 

• The CTE computes the average salary per department.

• The main query joins this with the Employees table and filters accordingly.

 

 

4. Write a query to get duplicate records in a table.

 

 

5. Delete duplicate rows but keep the latest one based on timestamp.

To delete duplicate rows but keep the latest one based on a timestamp, you can use a Common Table Expression (CTE) with ROW_NUMBER() or RANK() to identify duplicates and retain only the most recent entry.

✅ Example SQL Query

Assume your table is named Employees and has columns: EmployeeID, Name, Email, UpdatedAt.

SQL

WITH RankedEmployees AS (

  SELECT *,

         ROW_NUMBER() OVER (

           PARTITION BY Email  -- or any column(s) that define duplicates

           ORDER BY UpdatedAt DESC

         ) AS rn

  FROM Employees

)

DELETE FROM RankedEmployees

WHERE rn > 1;

 

🔍 Explanation:

• PARTITION BY Email: Groups duplicates based on the Email column.

• ORDER BY UpdatedAt DESC: Ensures the latest record gets rn = 1.

• DELETE WHERE rn > 1: Deletes all older duplicates, keeping the most recent.

🧠 Tip:

• Replace Email with the column(s) that define what a "duplicate" means in your context.

• Make sure UpdatedAt is a reliable timestamp for determining recency.

 

 

6. Retrieve department-wise maximum salary along with employee name.

To retrieve the department-wise maximum salary along with the employee name, you can use a Common Table Expression (CTE) or a subquery with a join. Here's how to do it:

✅ Using CTE with RANK() or ROW_NUMBER()

SQL

WITH RankedEmployees AS (

  SELECT

    EmployeeID,

    Name,

    DepartmentID,

    Salary,

    RANK() OVER (

      PARTITION BY DepartmentID

      ORDER BY Salary DESC

    ) AS Rank

  FROM Employees

)

SELECT EmployeeID, Name, DepartmentID, Salary

FROM RankedEmployees

WHERE Rank = 1;

 

Show more lines

• PARTITION BY DepartmentID: Groups employees by department.

• ORDER BY Salary DESC: Ranks employees by salary within each department.

• WHERE Rank = 1: Selects the top earners per department.

✅ Alternative: Subquery with Join

SQL

SELECT E.EmployeeID, E.Name, E.DepartmentID, E.Salary

FROM Employees E

JOIN (

  SELECT DepartmentID, MAX(Salary) AS MaxSalary

  FROM Employees

  GROUP BY DepartmentID

) D ON E.DepartmentID = D.DepartmentID AND E.Salary = D.MaxSalary;

 

• The subquery finds the maximum salary per department.

• The outer query joins it to get the employee(s) earning that salary.

 

 

7. Get a running total of salaries ordered by joining date.

To get a running total of salaries ordered by joining date, you can use the SUM() window function with the OVER() clause and an ORDER BY on the joining date.

✅ SQL Query Example

Assuming your table is named Employees and has columns: EmployeeID, Name, Salary, JoiningDate:

SQL

SELECT

  EmployeeID,

  Name,

  Salary,

  JoiningDate,

  SUM(Salary) OVER (

    ORDER BY JoiningDate

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  ) AS RunningTotal

FROM Employees;

 

🔍 Explanation:

• SUM(Salary) OVER (...): Calculates the running total.

• ORDER BY JoiningDate: Ensures salaries are added in order of joining.

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Includes all previous rows up to the current one.

 

 

8. Find employees who have not been assigned to any department.

To find employees who have not been assigned to any department, you can check for NULL values in the DepartmentID column or use a LEFT JOIN to identify unmatched records.

✅ Option 1: Using IS NULL

SQL

SELECT EmployeeID, Name

FROM Employees

WHERE DepartmentID IS NULL;

 

• This assumes that unassigned employees have a NULL in the DepartmentID column.

✅ Option 2: Using LEFT JOIN and IS NULL

SQL

SELECT E.EmployeeID, E.Name

FROM Employees E

LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID

WHERE D.DepartmentID IS NULL;

 

• This finds employees whose DepartmentID does not match any record in the Departments table.

🧠 Tip:

• Use the first method if you're sure NULL is used for unassigned departments.

• Use the second method if you want to catch mismatches due to missing foreign key relationships.

 

 

9. Find customers who bought products A and B but not C.

To find customers who bought products A and B but not C, you can use SQL with grouping and filtering logic. Here's a clean approach using HAVING:

✅ Assumptions:

• Table: Sales

• Columns: CustomerID, ProductName

✅ SQL Query:

SQL

SELECT CustomerID

FROM Sales

WHERE ProductName IN ('A', 'B', 'C')

GROUP BY CustomerID

HAVING

  COUNT(DISTINCT CASE WHEN ProductName = 'A' THEN 1 END) > 0 AND

  COUNT(DISTINCT CASE WHEN ProductName = 'B' THEN 1 END) > 0 AND

  COUNT(DISTINCT CASE WHEN ProductName = 'C' THEN 1 END) = 0;

 

🔍 Explanation:

• WHERE ProductName IN ('A', 'B', 'C'): Limits rows to relevant products.

• GROUP BY CustomerID: Groups purchases by customer.

• HAVING clause:

• Ensures the customer bought A and B.

• Ensures the customer did not buy C.

 

 

10. Pivot rows into columns without using PIVOT function.

To pivot rows into columns without using the PIVOT function, you can use CASE statements with GROUP BY. This method works across most SQL dialects (e.g., MySQL, PostgreSQL, SQL Server).

✅ Example Scenario

Suppose you have a table Sales:

CustomerID	Product	Amount
1	A	100
1	B	150
2	A	200
2	C	250

You want to pivot it so each product becomes a column:

CustomerID	Product_A	Product_B	Product_C
1	100	150	NULL
2	200	NULL	250

✅ SQL Query Using CASE

SQL

SELECT

  CustomerID,

  SUM(CASE WHEN Product = 'A' THEN Amount ELSE NULL END) AS Product_A,

  SUM(CASE WHEN Product = 'B' THEN Amount ELSE NULL END) AS Product_B,

  SUM(CASE WHEN Product = 'C' THEN Amount ELSE NULL END) AS Product_C

FROM Sales

GROUP BY CustomerID;

 

🔍 Explanation:

• CASE WHEN Product = 'A' THEN Amount: Filters rows for product A.

• SUM(...): Aggregates amounts per customer.

• GROUP BY CustomerID: Groups data by customer to pivot rows into columns.