SQL Interview Questions

INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns every row from the left table plus matches, with NULLs where none exist; RIGHT is the mirror. FULL OUTER returns all rows from both sides. LEFT JOIN with WHERE right.id IS NULL finds rows with no match, a common data check.

GROUP BY the email and keep groups with HAVING COUNT(*) > 1. Normalise with LOWER and TRIM if duplicates differ by case or spaces. To see the full duplicate rows, use a window function such as COUNT(*) OVER (PARTITION BY email) or join back to the grouped result.

Use DENSE_RANK() OVER (ORDER BY salary DESC) and filter rank = N; add PARTITION BY department for per-group results. Alternatives are a MAX subquery for the second highest, or SELECT DISTINCT with ORDER BY, LIMIT 1 OFFSET N-1. Clarify how ties and NULLs should be handled.

Compare source and target with row counts, checksums or aggregates, and row-level diffs using EXCEPT. Check orphans with NOT EXISTS, duplicates on business keys, NULLs in required columns, invalid values and broken totals. Beware the NOT IN with NULL trap, and automate the checks so they run on every load.

Isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) trade concurrency for protection against dirty reads, non-repeatable reads, phantoms and lost updates. Test races with truly concurrent check-then-act requests and verify constraints or locking prevent duplicates.