Suppose there is an employee table with a unique id column, but there are duplicate values in employee name and salary. Write a query to delete the duplicate records.

You can delete duplicates by keeping the row with the smallest id for each (name, salary) pair. One common approach is to use a CTE with ROW_NUMBER():

WITH cte AS (

SELECT id,

     name,
     salary,
     ROW_NUMBER() OVER (PARTITION BY name, salary ORDER BY id) AS rn

FROM employee

)

DELETE FROM employee

WHERE id IN (SELECT id FROM cte WHERE rn > 1);

Explanation: The CTE assigns a row number to each row within the same name‑salary group, ordered by id. The first row (rn = 1) is kept; all others (rn > 1) are considered duplicates and are deleted. Complexity is O(n log n) due to the window function, and the delete runs in a single statement.