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);

Read More

⭐ Mastering SQL Transactions — A Complete 360° Guide with Examples, Modes, Errors, TRY…CATCH, Deadlocks, Isolation Levels & Real Scenarios

 Transactions are the backbone of data integrity in SQL Server. They ensure your data stays accurate even when operations fail, systems crash, or multiple users modify data at the same time.

But when should we explicitly use them? What happens if we don’t? How does SQL Server behave internally?

This 360-degree guide explains SQL transactions through:

• Auto-commit vs Explicit transactions

• Meaningful real examples

• Errors + rollback scenarios

• TRY…CATCH

• XACT_ABORT

• Deadlocks

• Isolation levels

• Flowcharts and diagrams

At the end, you’ll be able to confidently decide when, why, and how to use transactions.

---

🔵 1. Understanding SQL Transaction Modes

SQL Server supports three modes:

Mode	Behaviour
Auto-Commit	Default. Every statement is a transaction.
Implicit Transactions	SQL starts a transaction automatically until you commit.
Explicit Transactions	Developer manually controls BEGIN / COMMIT / ROLLBACK.

In this article, we will focus on:

• Auto-commit

• Explicit transactions

These are the modes used 99% of the time in real systems.

---

🔵 2. Auto-Commit Mode (Default Mode)

Every INSERT, UPDATE, or DELETE is automatically committed.

Let’s create two simple tables:

CREATE TABLE dbo.Checking
(
    ID INT NOT NULL,
    Amount DECIMAL(19, 2) NOT NULL,
    ShortDescription NVARCHAR(100) NOT NULL,
    TransactionDate DATE NOT NULL
);
GO
CREATE TABLE dbo.Savings
(
    ID INT NOT NULL,
    Amount DECIMAL(19, 2) NOT NULL,
    ShortDescription NVARCHAR(100) NOT NULL,
    TransactionDate DATE NOT NULL
);
GO

Insert example data:

INSERT INTO dbo.Checking
(
    ID,
    Amount,
    ShortDescription,
    TransactionDate
)
VALUES
(1, 25.00, 'Starting my checking account', GETDATE());
INSERT INTO dbo.Savings
(
    ID,
    Amount,
    ShortDescription,
    TransactionDate
)
VALUES
(1, 100.00, 'Starting my savings account, Thanks dad!', GETDATE());
SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Output

Table	Amount
Savings	100
Checking	25

Auto-commit is simple — you issue the statement, SQL Server commits it.

---

🔵 3. The Problem With Auto-Commit (Real Example)

We now simulate a small real banking operation:

Attempt to move ₹100 from Savings → Checking

INSERT INTO dbo.Savings
VALUES (2, -100.00, 'Taking money from savings', GETDATE());

INSERT INTO dbo.Checking
VALUES (2, 100.00,  'Taking money out of my account for a copy of a new NES game, sorry dad! I will replace it after I get a job.'
, GETDATE());

❌ The second insert fails

(because the message is too long or violates a rule)

But the first insert already succeeded, and auto-commit cannot undo it.

SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Result: Bad Data

ID	Total
1	0.00 ❌ (should not happen)

2

25.00

This is the main weakness of auto-commit.

---

🔵 4. Explicit Transactions: The Fix

Explicit transactions allow us to group multiple steps into one logical unit.

Syntax:

BEGIN TRANSACTION;
-- operations
COMMIT;        -- save changes
-- or
ROLLBACK;      -- undo all changes

---

🔵 5. Real Example: Fixing the Savings → Checking Transfer

We run the exact same operation using a transaction:

When we use XACT_ABORT ON keyword, we no need write rollback, it automatically rollback the transaction when it fails, below is the code

SET XACT_ABORT ON;
BEGIN TRANSACTION;

INSERT INTO dbo.Savings
VALUES (2, -100.00, 'Taking money out of savings', GETDATE());

INSERT INTO dbo.Checking
VALUES (2, 100.00, 'Taking money out of my account for a copy of a new NES games, sorry dad! I will replace it after I get a job.', GETDATE());

COMMIT TRANSACTION;

✔ Behaviour

If any statement fails, SQL Server automatically:

• Rolls back

• Restores all data

• Maintains integrity

SELECT SUM(Amount) AS [Total], -- SELECT Statement
       'Savings' AS [AccountType]
FROM dbo.Savings
UNION ALL
SELECT SUM(Amount) AS [Total],
       'Checking' AS [AccountType]
FROM dbo.Checking;
GO

✔ Result (Correct)

This example is the core reason to use explicit transactions.

---

🔵 6. Using TRY…CATCH for Better Safety

Best practice in SQL Server:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.Savings VALUES (3, -200, 'Transfer', GETDATE());
    INSERT INTO dbo.Checking VALUES (3, 200, 'Transfer', GETDATE());

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Transaction Failed: ' + ERROR_MESSAGE();
END CATCH;

This ensures:

• Partial inserts don’t occur

• Errors are logged

• Data stays consistent

---

🔵 7. Understanding Deadlocks (Simple Visual Explanation)

A deadlock happens when:

• Transaction A waits for resources held by B

• Transaction B waits for resources held by A

Result → SQL Server kills one transaction

---

🔶 Deadlock Diagram

---

Real Example

Session A

BEGIN TRAN;
UPDATE Checking SET Amount = Amount - 50 WHERE ID = 1;
WAITFOR DELAY '00:00:10';
UPDATE Savings SET Amount = Amount + 50 WHERE ID = 1;
COMMIT;

Session B

BEGIN TRAN;
UPDATE Savings SET Amount = Amount - 20 WHERE ID = 1;
WAITFOR DELAY '00:00:10';
UPDATE Checking SET Amount = Amount + 20 WHERE ID = 1;
COMMIT;

💥 Deadlock occurs

✔ Deadlock Fix

Always update tables in the same order:

UPDATE Checking ...
UPDATE Savings ...

This prevents circular waits.

---

🔵 8. Understanding Isolation Levels (With Diagrams)

Isolation levels control how transactions interact.

---

8.1 READ UNCOMMITTED (Dirty Reads Allowed)

---

8.2 READ COMMITTED (Default Level – No Dirty Reads)

---

8.3 REPEATABLE READ (Prevents Non-Repeatable Reads)

---

8.4 SERIALIZABLE (Strictest — Fully Locked)

---

🔵 9. Flowchart: How SQL Transaction Works

---

🔵 10. Putting It All Together (360° View)

✔ When Auto-Commit Is Enough

• Simple INSERT of one row

• Logging data

• Background audit entries

✔ When You MUST Use Explicit Transactions

• Multi-step financial operations

• Inventory deductions

• Any operation affecting multiple tables

• Critical business logic

• Data migrations

✔ Why TRY…CATCH Matters

• Prevents partial changes

• Enables clean rollback

• Improves reliability

✔ Why XACT_ABORT Is Useful

• Automatically rolls back on runtime errors

• Prevents stuck transactions

✔ Deadlock Awareness

• Happens naturally under concurrency

• Ordering and proper indexing greatly reduce them

✔ Isolation Levels Matter

• They control concurrency vs. accuracy

• Choose based on system needs

---

⭐ Final Conclusion

SQL transactions are not just a feature — they are a safety system.
They ensure:

• Accuracy

• Consistency

• Reliability

• Protection from partial failures

Understanding auto-commit vs explicit, using TRY…CATCH, handling deadlocks, and choosing the right isolation level gives you complete control over data integrity.

You now have a full 360-degree understanding of SQL transactions.

Read More