Interview Questions

1) Select top N rows

Question: Return top 5 highest paid employees.

Schema: Employees(emp_id, name, salary)

SELECT TOP (5) emp_id, name, salary
FROM Employees
ORDER BY salary DESC;

Explanation: TOP is SQL Server syntax. TOP (5) returns five rows with highest salary.

2) Second highest salary

Question: Find the 2nd highest salary.

SELECT DISTINCT salary
FROM Employees
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Or (ROW_NUMBER / DENSE_RANK):

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employees
) t
WHERE rnk = 2;

Explanation: OFFSET/FETCH or window functions solve Nth-highest reliably.

3) Remove duplicate rows (keep one)

Question: Delete duplicate employees by name, keep lowest emp_id.

WITH CTE AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY emp_id) rn
  FROM Employees
)
DELETE FROM CTE WHERE rn > 1;

Explanation: ROW_NUMBER partitions duplicates; delete rows >1.

4) Top N per group

Question: Top 3 salaries per department.

Schema: Employees(emp_id, name, salary, dept_id)

SELECT emp_id, name, salary, dept_id
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn
  FROM Employees
) t
WHERE rn <= 3;

Explanation: Partition by dept_id and order by salary.

5) Find employees with no orders (LEFT JOIN / NOT EXISTS)

Schema: Employees(emp_id), Orders(order_id, emp_id)

-- Using LEFT JOIN
SELECT e.emp_id
FROM Employees e
LEFT JOIN Orders o ON e.emp_id = o.emp_id
WHERE o.order_id IS NULL;

-- Or using NOT EXISTS
SELECT e.emp_id
FROM Employees e
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.emp_id = e.emp_id
);

Explanation: Two common patterns to find “orphans”.

6) Correlated subquery: employees with above-average salary

Schema: Employees(emp_id, name, salary)

SELECT emp_id, name, salary
FROM Employees e
WHERE salary > (SELECT AVG(salary) FROM Employees);

Explanation: Scalar subquery compares each row to overall average.

7) Aggregate with HAVING

Question: Departments with more than 10 employees.

SELECT dept_id, COUNT(*) AS emp_count
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 10;

Explanation: HAVING filters groups after aggregation.

8) Partitioned running total (cumulative sum)

Question: Running total of orders per customer by date.

Schema: Orders(order_id, customer_id, amount, order_date)

SELECT order_id, customer_id, order_date, amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM Orders
ORDER BY customer_id, order_date;

Explanation: SUM() OVER gives cumulative sums per customer.

9) Find gaps in sequence (missing IDs)

Schema: Orders(order_id INT)

SELECT t.order_id + 1 AS start_missing, MIN(n.order_id) - 1 AS end_missing
FROM Orders t
JOIN Orders n ON n.order_id > t.order_id
GROUP BY t.order_id
HAVING MIN(n.order_id) > t.order_id + 1;

Simpler (find single missing values):

SELECT n.order_id - 1 AS missing_id
FROM Orders n
LEFT JOIN Orders p ON n.order_id = p.order_id + 1
WHERE p.order_id IS NULL;

Explanation: Methods to detect gaps in sequences.

10) Pivot rows to columns

Schema: Sales(emp_id, sale_year, amount)

SELECT emp_id, ISNULL([2019],0) AS Y2019, ISNULL([2020],0) AS Y2020, ISNULL([2021],0) AS Y2021
FROM (
  SELECT emp_id, sale_year, amount FROM Sales
) src
PIVOT (
  SUM(amount) FOR sale_year IN ([2019],[2020],[2021])
) p;

Explanation: PIVOT aggregates and transforms rows into columns.

11) Unpivot columns to rows

Schema: MonthlySales(emp_id, Jan, Feb, Mar)

SELECT emp_id, Month, Sales
FROM MonthlySales
UNPIVOT (
  Sales FOR Month IN (Jan, Feb, Mar)
) up;

Explanation: UNPIVOT turns columns to rows for analysis.

12) String aggregation (concatenate rows) – STRING_AGGSchema: Categories(cat_id, name), ProductCategories(product_id, cat_id)

SELECT p.product_id,
  STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY c.name) AS categories
FROM Products p
JOIN ProductCategories pc ON p.product_id = pc.product_id
JOIN Categories c ON pc.cat_id = c.cat_id
GROUP BY p.product_id;

Explanation: STRING_AGG (SQL Server 2017+) concatenates grouped values.

13) Pagination (OFFSET…FETCH)

Question: Return 10 rows per page.

SELECT emp_id, name
FROM Employees
ORDER BY emp_id
OFFSET 10 * (@PageNumber - 1) ROWS
FETCH NEXT 10 ROWS ONLY;

Explanation: Standard way to implement pagination.

14) Find duplicate groups with counts

Schema: Customers(name, email)

SELECT name, email, COUNT(*) AS cnt
FROM Customers
GROUP BY name, email
HAVING COUNT(*) > 1;

Explanation: Identifies duplicates by columns.

15) Update rows using JOIN

Schema: Products(product_id, price), PriceAdjustments(product_id, adj_pct)

UPDATE p
SET price = price * (1 + pa.adj_pct)
FROM Products p
JOIN PriceAdjustments pa ON p.product_id = pa.product_id;

Explanation: SQL Server allows UPDATE ... FROM JOIN syntax.

16) Delete with JOIN (delete child rows for a certain parent)

Schema: Orders(order_id, customer_id), Customers(customer_id)

DELETE o
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.status = 'inactive';

Explanation: Deletes orders for inactive customers.

17) Insert-select (bulk insert)

Schema: ArchiveOrders same structure as Orders

INSERT INTO ArchiveOrders (order_id, customer_id, amount, order_date)
SELECT order_id, customer_id, amount, order_date
FROM Orders
WHERE order_date < '2023-01-01';

Explanation: Move old rows into archive table.

18) Use MERGE for upsert (insert or update)

Schema: Inventory(product_id, qty), Incoming(product_id, qty)

MERGE Inventory AS target
USING Incoming AS src
ON target.product_id = src.product_id
WHEN MATCHED THEN
  UPDATE SET target.qty = target.qty + src.qty
WHEN NOT MATCHED BY TARGET THEN
  INSERT (product_id, qty) VALUES (src.product_id, src.qty);

Explanation: MERGE combines insert/update in one statement.

19) Transaction with error handling

Question: Show pattern (T-SQL).

BEGIN TRY
  BEGIN TRAN;
    -- multiple statements
    UPDATE Accounts SET balance = balance - 100 WHERE acc_id = 1;
    UPDATE Accounts SET balance = balance + 100 WHERE acc_id = 2;
  COMMIT TRAN;
END TRY
BEGIN CATCH
  ROLLBACK TRAN;
  THROW; -- rethrow error
END CATCH;

Explanation: Use transactions to ensure atomicity.

20) Find customers with orders in all months

Schema: Orders(customer_id, order_date)

SELECT customer_id
FROM (
  SELECT customer_id, MONTH(order_date) AS m
  FROM Orders
  WHERE YEAR(order_date) = 2024
  GROUP BY customer_id, MONTH(order_date)
) x
GROUP BY customer_id
HAVING COUNT(DISTINCT m) = 12;

Explanation: Counts distinct months per customer. If it equals 12 then customer has orders in all months.

21) Use Common Table Expression (CTE) – recursive example (factorial or hierarchy)

Question: Get employee hierarchy (reporting chain)

Schema: Employees(emp_id, manager_id, name)

WITH Hierarchy AS (
  SELECT emp_id, manager_id, name, 0 AS level
  FROM Employees
  WHERE emp_id = 1  -- root manager
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.name, h.level + 1
  FROM Employees e
  JOIN Hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM Hierarchy;

Explanation: Recursive CTE to traverse hierarchy.

22) Use CROSS APPLY / OUTER APPLY

Question: For each customer, get their latest order.

SELECT c.customer_id, c.name, o.*
FROM Customers c
OUTER APPLY (
  SELECT TOP (1) *
  FROM Orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY order_date DESC
) o;

Explanation: APPLY lets you run a correlated subquery that can return rows per outer row.

23) Correlated subquery to find the most recent order per customer

SELECT o.*
FROM Orders o
WHERE o.order_date = (
  SELECT MAX(order_date)
  FROM Orders o2
  WHERE o2.customer_id = o.customer_id
);

Explanation: Classic pattern for “latest row per group”.

24) Use EXISTS vs IN – performance consideration

Question: List customers with orders.

-- Using EXISTS (usually preferred)
SELECT c.customer_id
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id);

-- Using IN
SELECT customer_id FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders);

Explanation: EXISTS is often faster for correlated checks with large datasets.

25) Find consecutive days streak (gaps-and-islands)

Schema: UserLog(user_id, activity_date)

WITH Numbered AS (
  SELECT user_id, activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) rn
  FROM (SELECT DISTINCT user_id, CAST(activity_date AS DATE) activity_date FROM UserLog) t
),
Groups AS (
  SELECT user_id, activity_date,
    DATEADD(DAY, -rn, activity_date) AS grp
  FROM Numbered
)
SELECT user_id, MIN(activity_date) AS start_date, MAX(activity_date) AS end_date, COUNT(*) AS streak
FROM Groups
GROUP BY user_id, grp
ORDER BY user_id, start_date;

Explanation: Transform into islands by subtracting ROW_NUMBER to form group keys.

26) Dynamic pivot (when pivot columns unknown) – outline

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(sale_year), ',') FROM (SELECT DISTINCT sale_year FROM Sales) s;

SET @sql = N'
SELECT emp_id, ' + @cols + '
FROM (
  SELECT emp_id, sale_year, amount FROM Sales
) src
PIVOT (
  SUM(amount) FOR sale_year IN (' + @cols + ')
) p;';

EXEC sp_executesql @sql;

Explanation: To create a dynamic pivot in SQL Server when the pivot column values are unknown or change frequently, we must use dynamic SQL to build the column list at runtime and inject it into a PIVOT template. Build IN(...) list dynamically and execute.

27) Use TRY_CONVERT / TRY_PARSE to safely convert data

Schema: ImportData(val VARCHAR(50))

SELECT val,
  TRY_CONVERT(INT, val) AS int_val,
  TRY_CONVERT(DATE, val) AS date_val
FROM ImportData;

Explanation: The TRY_CONVERT function in SQL Server converts an expression of one data type to another, but instead of throwing an error when the conversion fails, it safely returns NULL. It is supported in SQL Server 2012 and later versions

28) JSON parsing using OPENJSON

Schema: Orders(order_id, json_details), json_details = '{"items":[{"id":1,"qty":2},{"id":2,"qty":1}]}'

SELECT o.order_id, item.*
FROM Orders o
CROSS APPLY OPENJSON(o.json_details, '$.items')
WITH (
  id INT '$.id',
  qty INT '$.qty'
) AS item;

Explanation: OPENJSON parses JSON and returns rows/columns.

29) Use WINDOW functions: RANK vs ROW_NUMBER vs DENSE_RANK

Question: Demonstrate difference when ties exist (top 3 students by score).

Schema: Students(student_id, score)

SELECT student_id, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) rn,
  RANK() OVER (ORDER BY score DESC) rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) dr
FROM Students;

Explanation: ROW_NUMBER always unique, RANK leaves gaps after ties, DENSE_RANK no gaps.

30) Efficiently count distinct values – COUNT(DISTINCT ...) vs GROUP BY

Question: Count distinct customers who placed orders.

-- Simple
SELECT COUNT(DISTINCT customer_id) FROM Orders;

-- Or using GROUP BY (when you also need other aggregates)
SELECT COUNT(*) AS distinct_customers
FROM (
  SELECT customer_id FROM Orders GROUP BY customer_id
) t;

Explanation: COUNT(DISTINCT) is concise; GROUP BY offers extensibility.