Real-time SQL practical tasks are frequently asked in interviews for SQL Developer, Data Engineer, MSBI Developer, and DBA roles.

This guide covers essential hands-on problems to test your querying, optimization, joins, window functions, indexing, transactions, and more.

---

1. Query Writing & Joins

1️⃣ Retrieve the second highest salary from the Employees table.

Table: Employees(EmpID, EmpName, Salary)

✔️ Answer

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

---

2️⃣ Get the nth highest salary (write a reusable query).

Input: n = 3
✔️ Answer

SELECT Salary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
) x
WHERE rnk = 3;

---

3️⃣ Display employees who have never placed an order.

Tables: Employees, Orders
✔️ Answer

SELECT e.EmpID, e.EmpName
FROM Employees e
LEFT JOIN Orders o ON e.EmpID = o.EmpID
WHERE o.EmpID IS NULL;

---

4️⃣ Find the department with the highest total salary.

Tables: Employee(EmpID, DeptID, Salary)

✔️ Answer

SELECT TOP 1 DeptID, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY DeptID
ORDER BY TotalSalary DESC;

---

2. Aggregations & Grouping

5️⃣ Count employees in each department and filter only departments with more than 5 employees.

✔️ Answer

SELECT DeptID, COUNT(*) AS EmpCount
FROM Employee
GROUP BY DeptID
HAVING COUNT(*) > 5;

---

6️⃣ Find the highest-paid employee in each department.

✔️ Answer

SELECT *
FROM (
    SELECT EmpID, EmpName, DeptID, Salary,
           ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rn
    FROM Employee
) x
WHERE rn = 1;

---

3. Window Functions

7️⃣ Calculate running total of sales by date.

Table: Sales(SaleDate, Amount)

✔️ Answer

SELECT SaleDate, Amount,
       SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;

---

8️⃣ Rank products based on revenue within each category.

✔️ Answer

SELECT ProductID, Category, Revenue,
       RANK() OVER (PARTITION BY Category ORDER BY Revenue DESC) AS CategoryRank
FROM ProductSales;

---

4. Subqueries & CTEs

9️⃣ Get employees earning more than the average salary.

✔️ Answer

SELECT EmpID, EmpName, Salary
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

---

🔟 Recursively retrieve a hierarchy of employees (CTE).

Table: Employees(EmpID, EmpName, ManagerID)

✔️ Answer

WITH EmpCTE AS (
    SELECT EmpID, EmpName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.EmpID, e.EmpName, e.ManagerID
    FROM Employees e
    INNER JOIN EmpCTE c ON e.ManagerID = c.EmpID
)
SELECT * FROM EmpCTE;

---

5. String, Date & Data Cleaning

1️⃣1️⃣ Reverse a string without using REVERSE()

✔️ Answer

WITH cte AS (
    SELECT 1 AS pos, SUBSTRING('SQLSERVER',1,1) AS ch
    UNION ALL
    SELECT pos + 1, SUBSTRING('SQLSERVER', pos+1, 1)
    FROM cte
    WHERE pos < LEN('SQLSERVER')
)
SELECT STRING_AGG(ch,'') WITHIN GROUP (ORDER BY pos DESC) AS Reversed;

---

1️⃣2️⃣ Extract only numeric characters from a mixed string (A12B34).

✔️ Answer

SELECT 
    STRING_AGG(value,'') AS DigitsOnly
FROM STRING_SPLIT('A12B34','')
WHERE value LIKE '[0-9]';

---

1️⃣3️⃣ Find the age of an employee using DOB.

✔️ Answer

SELECT EmpName,
       DATEDIFF(YEAR, DOB, GETDATE()) AS Age
FROM Employee;

---

6. Advanced Joins & Set Operations

1️⃣4️⃣ Retrieve customers who bought 'Product A' but never bought 'Product B'.

✔️ Answer

SELECT DISTINCT c.CustomerID
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.Product = 'Product A'
EXCEPT
SELECT DISTINCT CustomerID
FROM Orders
WHERE Product = 'Product B';

---

1️⃣5️⃣ Find orders that appear in one table but not in another.

✔️ Answer

SELECT * FROM Orders_2023
EXCEPT
SELECT * FROM Orders_2024;

---

7. Indexing & Performance

1️⃣6️⃣ Create a non-clustered index to improve a search query.

✔️ Answer

CREATE INDEX IX_Employee_DeptID_Salary
ON Employee(DeptID, Salary);

---

1️⃣7️⃣ Find queries causing table scans (interviewer expects explanation).

✔️ How to answer (talk + demo query)
Use execution plan:

SET SHOWPLAN_ALL ON;
SELECT * FROM Employee WHERE Salary = 50000;
SET SHOWPLAN_ALL OFF;

If index missing → table/clustered scan.

---

8. Stored Procedures, Functions & Triggers

1️⃣8️⃣ Write a stored procedure to fetch employees by department.

✔️ Answer

CREATE PROCEDURE sp_GetEmployeesByDept
    @DeptID INT
AS
BEGIN
    SELECT EmpID, EmpName, Salary
    FROM Employee
    WHERE DeptID = @DeptID;
END

---

1️⃣9️⃣ Create a function to return yearly salary.

✔️ Answer

CREATE FUNCTION fn_YearlySalary(@monthlySalary INT)
RETURNS INT
AS
BEGIN
    RETURN @monthlySalary * 12;
END

---

2️⃣0️⃣ Trigger to log updates in a table.

✔️ Answer

CREATE TRIGGER trg_LogSalaryUpdate
ON Employee
FOR UPDATE
AS
INSERT INTO EmployeeLog(EmpID, OldSalary, NewSalary, UpdatedOn)
SELECT d.EmpID, d.Salary, i.Salary, GETDATE()
FROM deleted d
JOIN inserted i ON d.EmpID = i.EmpID;

---

9. Transactions, Error Handling, ACID

2️⃣1️⃣ Write a transaction with TRY…CATCH.

✔️ Answer

BEGIN TRY
    BEGIN TRAN

    UPDATE Accounts SET Balance = Balance - 500 WHERE AccID = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccID = 2;

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

---

10. Real-Time Data Engineering Tasks

2️⃣2️⃣ Pivot monthly sales into columns.

✔️ Answer

SELECT *
FROM Sales
PIVOT (
    SUM(Amount) FOR MonthName IN ([Jan],[Feb],[Mar],[Apr])
) AS p;

---

2️⃣3️⃣ Split comma-separated values into multiple rows.

✔️ Answer

SELECT EmpID, value AS Skill
FROM EmployeeSkills
CROSS APPLY STRING_SPLIT(Skills, ',');

---

2️⃣4️⃣ Merge updates from a staging table into main table.

✔️ Answer

MERGE INTO Employee AS target
USING Employee_Staging AS source
ON target.EmpID = source.EmpID
WHEN MATCHED THEN 
    UPDATE SET target.Salary = source.Salary
WHEN NOT MATCHED THEN
    INSERT(EmpID, EmpName, Salary)
    VALUES(source.EmpID, source.EmpName, source.Salary);