Advanced SQL Coding Scenarios set - 1(1–10)

 These are the types of questions frequently asked for Senior SQL Developer, Tableau Lead, BI Lead, BI Architect, Data Analyst, and Data Warehouse interviews.

---

1. Find Duplicate Records

Question

Find duplicate employee names.

SELECT EmpName,
       COUNT(*)
FROM Employee
GROUP BY EmpName
HAVING COUNT(*) > 1;

---

2. Delete Duplicate Records

Question

Delete duplicates while keeping the first record.

WITH CTE AS
(
SELECT *,
       ROW_NUMBER() OVER
       (PARTITION BY EmpName ORDER BY EmpID) rn
FROM Employee
)
DELETE FROM CTE
WHERE rn > 1;

---

3. Find 2nd Highest Salary

SELECT Salary
FROM
(
SELECT Salary,
       DENSE_RANK() OVER
       (ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk = 2;

---

4. Find 3rd Highest Salary

SELECT Salary
FROM
(
SELECT Salary,
       DENSE_RANK() OVER
       (ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk = 3;

---

5. Find Nth Highest Salary

DECLARE @N INT = 5;

SELECT Salary
FROM
(
SELECT Salary,
       DENSE_RANK() OVER
       (ORDER BY Salary DESC) rk
FROM Employee
)x
WHERE rk = @N;

---

6. Highest Salary in Each Department

SELECT *
FROM
(
SELECT *,
       DENSE_RANK() OVER
       (
       PARTITION BY DeptID
       ORDER BY Salary DESC
       ) rk
FROM Employee
)x
WHERE rk = 1;

---

7. Top 3 Salaries in Each Department

SELECT *
FROM
(
SELECT *,
       DENSE_RANK() OVER
       (
       PARTITION BY DeptID
       ORDER BY Salary DESC
       ) rk
FROM Employee
)x
WHERE rk <= 3;

---

8. Employees Earning More Than Manager

SELECT e.EmpName
FROM Employee e
JOIN Employee m
ON e.ManagerID = m.EmpID
WHERE e.Salary > m.Salary;

---

9. Find Employees Without Manager

SELECT *
FROM Employee
WHERE ManagerID IS NULL;

---

10. Find Employees Joined in Last 30 Days

SELECT *
FROM Employee
WHERE JoinDate >= DATEADD(day,-30,GETDATE());