Sql¶
22 cards — 🟢 6 easy | 🟡 4 medium | 🔴 5 hard
🟢 Easy (6)¶
1. What type of language is SQL?
Show answer
SQL is declarative — you describe the result you want, not a step-by-step procedure. You say "give me all orders over $100" and the engine decides how to retrieve them. This means the database optimizer chooses the execution plan, which is why understanding indexes and EXPLAIN matters for performance.Remember: "JOIN = combine rows from two tables." INNER = only matches, LEFT = all from left + matches, RIGHT = all from right, FULL = all from both.
Name origin: SQL was originally called SEQUEL (Structured English Query Language) at IBM in 1974, later shortened to SQL for trademark reasons.
Fun fact: SQL is pronounced both "S-Q-L" and "sequel" — both are accepted. ISO standard says "S-Q-L."
2. What does the WHERE clause filter?
Show answer
Individual rows before any grouping occurs. WHERE runs early in the logical processing order (right after FROM), so you cannot use aggregate functions like COUNT or SUM in WHERE — those do not exist yet. Example: WHERE status = 'active' filters row by row.Remember: "WHERE filters rows, HAVING filters groups." HAVING comes after GROUP BY and can use aggregate functions.
Analogy: WHERE is like a bouncer at the door — it checks each row individually before letting it into the party (grouping).
3. What does the HAVING clause filter?
Show answer
Groups after aggregation (e.g., after GROUP BY). Use HAVING for conditions on aggregate results: HAVING COUNT(*) > 5 keeps only groups with more than 5 rows. Common gotcha: putting aggregate conditions in WHERE instead of HAVING — the query will error because aggregates do not exist at WHERE time.Remember: "WHERE = row filter (before GROUP BY). HAVING = group filter (after GROUP BY)." This is the #1 SQL interview question.
4. What does ORDER BY do?
Show answer
Sorts the final result rows by specified column(s), ASC (default) or DESC. Without ORDER BY, row order is undefined — do not rely on insertion order. You can sort by column position (ORDER BY 2) or expressions. Gotcha: ORDER BY on large result sets without LIMIT can be expensive; ensure the column is indexed for best performance.Gotcha: Without ORDER BY, SQL does not guarantee row order — even if results appear sorted, the next execution may differ.
5. What does GROUP BY do?
Show answer
Groups rows sharing the same values in specified columns, enabling aggregate functions (COUNT, SUM, AVG) to operate per group. Example: SELECT dept, COUNT(*) FROM employees GROUP BY dept. Every non-aggregate column in SELECT must appear in GROUP BY — otherwise the database does not know which row's value to show for the group.Remember: "Every non-aggregate column in SELECT must be in GROUP BY." This rule ensures unambiguous results — the database knows which value to show for each group.
6. What does the FROM clause specify?
Show answer
The source table(s) from which data will be retrieved. You can join multiple tables (INNER JOIN, LEFT JOIN, etc.), use subqueries as derived tables, or reference CTEs. FROM runs first in the logical order, which is why every other clause operates on the rows it produces.Remember: "EXPLAIN shows the query plan." Look for full table scans (bad), index scans (good), and estimated row counts.
Under the hood: FROM is processed first in the logical order. This is why you can reference table columns in WHERE — the tables are already resolved.
🟡 Medium (4)¶
1. What is the difference between COUNT(*) and COUNT(col)?
Show answer
COUNT(*) counts all rows including those with NULLs — it counts row existence. COUNT(col) counts only rows where that column is not NULL. Gotcha: COUNT(col) on a column with many NULLs will return a much smaller number than COUNT(*), which can silently produce wrong percentages or averages.Gotcha: COUNT(DISTINCT col) counts unique non-NULL values. Often confused with COUNT(col) which counts all non-NULL values including duplicates.
2. Why is "x = NULL" wrong in SQL?
Show answer
NULL represents unknown, not a value. Comparing anything to NULL with = yields NULL (unknown), not TRUE or FALSE. You must use IS NULL or IS NOT NULL instead. Even NULL = NULL returns NULL. This three-valued logic (TRUE, FALSE, NULL) is one of the most common sources of subtle SQL bugs.Remember: "NULL is not a value, it\'s the absence of a value." Use IS NULL, never = NULL. This three-valued logic trips up even experienced developers.
3. Name five common SQL aggregate functions.
Show answer
COUNT() — row count, SUM() — total, AVG() — mean, MIN() — smallest, MAX() — largest. All except COUNT(*) ignore NULLs. Gotcha: AVG of a column with NULLs divides by the non-NULL count, not total rows — this can skew results. Use COALESCE(col, 0) if NULLs should count as zero.Gotcha: AVG ignores NULLs — if 5 of 10 rows are NULL, AVG divides by 5, not 10. Use COALESCE(col, 0) if NULLs should count as zero.
4. Why should you inspect row-level data before writing a GROUP BY query?
Show answer
To verify you understand the data shape — check for unexpected NULLs, duplicates, or data quality issues that would silently produce wrong aggregates. Run SELECT * with a LIMIT first. A GROUP BY on dirty data can produce counts that look plausible but are wrong, which is worse than an obvious error.Remember: "SELECT * LIMIT 10 before GROUP BY." Always inspect raw data first to catch NULLs, duplicates, and data quality issues.
🔴 Hard (5)¶
1. What is the logical processing order of a SQL SELECT statement?
Show answer
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT. This differs from the written order which starts with SELECT. Understanding this order explains why you cannot use a SELECT alias in WHERE (WHERE runs first), why HAVING can reference aggregates (GROUP BY already ran), and why ORDER BY can use aliases (it runs after SELECT).Remember: "FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT." Mnemonic: Friendly Whales Gather Happily, Singing On Lakes.
2. Why can't you use an aggregate function in a WHERE clause?
Show answer
WHERE filters individual rows before GROUP BY runs, so aggregate values (COUNT, SUM, AVG) do not exist yet. Use HAVING instead, which runs after grouping. Example of the error: WHERE COUNT(*) > 5 fails. Correct form: HAVING COUNT(*) > 5. This distinction trips up even experienced developers who do not internalize the logical execution order.Remember: "WHERE runs before GROUP BY, so aggregates don\'t exist yet." Use HAVING for aggregate conditions.
3. What does NULL = NULL evaluate to in SQL?
Show answer
It evaluates to NULL (unknown), not TRUE. This means WHERE col = NULL returns no rows. Joins on nullable columns can silently drop rows. To compare NULLs safely use IS NULL, IS NOT NULL, or COALESCE. In CASE expressions, use WHEN col IS NULL, not WHEN col = NULL. This is one of SQL's most misunderstood behaviors.Fun fact: This behavior is defined by the SQL standard\'s three-valued logic (TRUE, FALSE, UNKNOWN). NULL = NULL yields UNKNOWN, not TRUE.
4. Why can't you reference a column alias from SELECT in the WHERE clause?
Show answer
Because WHERE is processed before SELECT in the logical execution order (FROM -> WHERE -> GROUP BY -> HAVING -> SELECT). The alias does not exist yet when WHERE runs. Workaround: repeat the expression in WHERE, or use a subquery/CTE where the alias is defined in the inner query and filtered in the outer query.Gotcha: Some databases (MySQL) allow alias references in HAVING but not WHERE. PostgreSQL follows the standard strictly.
5. How do NULLs affect aggregate functions like SUM and AVG?
Show answer
Most aggregates (SUM, AVG, MIN, MAX) silently ignore NULL values. COUNT(col) ignores NULLs; only COUNT(*) counts all rows. The danger: AVG divides by non-NULL count, so a column with 10 rows where 5 are NULL computes the average of only 5 values. Use COALESCE(col, 0) if NULLs should be treated as zero to avoid skewed results.Gotcha: SUM of all NULLs returns NULL, not 0. COUNT(*) of an empty table returns 0, but SUM returns NULL. Use COALESCE(SUM(col), 0) to avoid surprises.