SQL Reference Guide

Revision Time: 2 mins

20. SQL Interview Tricks Reference

Quick revision for core SQL interview distinctions.

GROUP BY vs DISTINCT

Return: Value

Deduplicate rows (DISTINCT) vs compute aggregates (GROUP BY).

Syntax signature:DISTINCT filters row duplicates; GROUP BY collapses partitions to compute aggregates.

Code snippet:

python

SELECT DISTINCT dept FROM t; vs SELECT dept, COUNT(*) FROM t GROUP BY dept;

Expected Output:Unique list vs aggregated counts.

Remember: Always ensure correct syntax formatting when calling GROUP BY vs DISTINCT.

WHERE vs HAVING

Return: Value

Row-level filter (WHERE) vs aggregate-level filter (HAVING).

Syntax signature:WHERE runs BEFORE grouping; HAVING runs AFTER grouping.

Code snippet:

python

WHERE age > 18; vs HAVING COUNT(*) > 5;

Expected Output:Pre-filter vs post-filter.

Remember: Always ensure correct syntax formatting when calling WHERE vs HAVING.

UNION vs UNION ALL

Return: Value

Stack queries dropping duplicates (UNION) vs preserving all rows (UNION ALL).

Syntax signature:UNION involves an expensive unique sort; UNION ALL simply appends (faster).

Code snippet:

python

SELECT id FROM t1 UNION SELECT id FROM t2;

Expected Output:Deduplicated combined sets.

Remember: Always ensure correct syntax formatting when calling UNION vs UNION ALL.

DELETE vs TRUNCATE vs DROP

Return: Value

DML delete rows (DELETE) vs DDL deallocate tables (TRUNCATE) vs drop schemas (DROP).

Syntax signature:DELETE scans and logs rows (slow); TRUNCATE drops storage page references (fast); DROP deletes table definition.

Code snippet:

python

DELETE FROM logs; vs TRUNCATE TABLE logs; vs DROP TABLE logs;

Expected Output:Cleared rows vs emptied table vs deleted structure.

Remember: Always ensure correct syntax formatting when calling DELETE vs TRUNCATE vs DROP.

EXISTS vs IN

Return: Value

Boolean match check (EXISTS) vs array list inclusion check (IN).

Syntax signature:EXISTS stops scanning on first match; IN loads complete subquery array in memory.

Code snippet:

python

WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id); vs WHERE id IN (SELECT id FROM t2);

Expected Output:Optimized boolean exits vs matching elements list.

Remember: Always ensure correct syntax formatting when calling EXISTS vs IN.

RANK vs DENSE_RANK

Return: Value

Rank duplicate values with gaps (RANK) vs without gaps (DENSE_RANK).

Syntax signature:RANK ties 1, 1, 3... DENSE_RANK ties 1, 1, 2...

Code snippet:

python

RANK() OVER (ORDER BY score DESC); vs DENSE_RANK() OVER (ORDER BY score DESC);

Expected Output:1, 1, 3 ranking values vs 1, 1, 2 ranking values.

Remember: Always ensure correct syntax formatting when calling RANK vs DENSE_RANK.

ROW_NUMBER vs RANK

Return: Value

Sequential row increments (ROW_NUMBER) vs duplicate value ties (RANK).

Syntax signature:ROW_NUMBER guarantees unique indexes; RANK shares indices on duplicate ties.

Code snippet:

python

ROW_NUMBER() OVER (ORDER BY score DESC); vs RANK() OVER (ORDER BY score DESC);

Expected Output:1, 2, 3 row indexes vs 1, 1, 3 ranking values.

Remember: Always ensure correct syntax formatting when calling ROW_NUMBER vs RANK.

COUNT(*) vs COUNT(column)

Return: Value

Count all table rows (COUNT(*)) vs count non-null values in column (COUNT(column)).

Syntax signature:COUNT(*) counts null records; COUNT(column) ignores null cells.

Code snippet:

python

SELECT COUNT(*) FROM t; vs SELECT COUNT(phone) FROM t;

Expected Output:Total rows count vs active numbers count.

Remember: Always ensure correct syntax formatting when calling COUNT(*) vs COUNT(column).

Previous Topic19. SQL for Data Engineers