SQL Interview Questions

Use a subquery to exclude the max salary, then find the max of the remaining. Also solvable with DENSE RANK() or LIMIT/OFFSET

• How to retrieve the second-highest salary of an employee?

  Use a subquery to exclude the max salary, then find the max of the remaining. Also solvable with DENSE RANK() or LIMIT/OFFSET

• How to get the nth highest salary?

  Use DENSE RANK() to assign consecutive ranks, then filter by rank = N. DENSE RANK() handles ties correctly without skipping ranks

• How do you fetch all employees whose salary is greater than the average salary?

  Use a subquery in the WHERE clause to compute the average. The subquery runs once and returns a scalar value for comparison

• Write a query to display the current date and time.

  CURRENT TIMESTAMP is the ANSI standard way to get the current date and time. Also: NOW() (MySQL/Postgres), GETDATE() (SQL Server), SYSDATE (Oracle)

• How to find duplicate records in a table?

  Group by the column and use HAVING to filter groups with count 1. HAVING filters after aggregation, unlike WHERE

• How can you delete duplicate rows?

  Use ROW NUMBER() to number duplicates within each group, then delete rows where row num 1. Keeps the first occurrence of each duplicate group

• How to get the common records from two tables?

  INTERSECT returns only rows that exist in both result sets. Both queries must have the same number and type of columns Duplicates are removed (like UNION )

• How to retrieve the last 10 records from a table?

  Sort by the primary key in descending order and limit to 10. "Last" depends on your definition use the appropriate column for ordering