5-7 years of experience in SQL - interview question and answer

​

• What is the difference between COALESCE() and ISNULL()?
  COALESCE returns the first non-NULL expression among its arguments and is ANSI SQL standard, whereas ISNULL is SQL Server specific and takes only two arguments. COALESCE is preferred for portability and flexibility.

  ​

• Write a query to find the third highest salary from an employee table.

  text

  SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee));

  This nested query filters out the top two salaries and gets the next highest.

  ​

• How do you identify duplicate rows in a table?

  text

  SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

  This groups rows by the target column and filters those with duplicates.

  ​

• Explain database partitioning and its advantages.
  Partitioning divides large tables into smaller segments, improving query performance, scalability, and maintenance. Types include range, list, and hash partitioning.

  ​

• What is a cursor and when would you use it?
  A cursor allows row-by-row processing of query results, useful for complex operations that cannot be expressed as set-based queries. However, cursors can degrade performance, so use them sparingly.

  ​

• Write a query to compute running totals using window functions.

  text

  SELECT product_id, sales_date, SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY sales_date) AS running_total FROM sales;

  Window functions like SUM() OVER() enable complex analytics within SQL.

  ​

Additional Tips

• Be ready to optimize queries, explain indexing strategies, and handle performance bottlenecks.

• Demonstrate your ability to write clear, efficient SQL leveraging subqueries, joins, CTEs, and window functions.

• Use real examples from your experience to highlight your problem-solving skills and business impact.