Write SQL to Find Duplicate Rows

Problem

Oracle frequently mixes SQL into the design round. Write a query to find duplicate records in a table and explain JOIN types and ACID.

Requirements

• Find rows duplicated on (name, email)
• Explain INNER vs LEFT vs FULL JOIN
• Explain ACID and normalization

What evaluators look for

• Correct GROUP BY ... HAVING COUNT(*) > 1
• Clear understanding of indexing impact
• Trade-offs of normalization vs denormalization

Extension

Now delete duplicates keeping the lowest id — write that query.