SQL Query Fundamentals Questions

Core SQL for reading and shaping data: SELECT, filtering with WHERE, sorting, DISTINCT, and single-table aggregation with GROUP BY, HAVING, and aggregate functions. Covers reasoning about NULL handling, grouping semantics, and writing correct queries against a given schema. The baseline query-writing surface most data and engineering interviews open with.

EasyTechnical

71 practiced

Can a SELECT alias be used in GROUP BY or ORDER BY? Explain how this differs across PostgreSQL, MySQL, and SQL Server, and give a portable recommendation for writing SQL that needs to run on more than one engine.

Sample Answer

Whether a SELECT alias can be reused in GROUP BY or ORDER BY differs by engine: MySQL and PostgreSQL both generally allow it, SQL Server does not permit it in GROUP BY (though it does in ORDER BY); writing portable SQL means not relying on the permissive behavior. Structured elaboration sql -- Works in PostgreSQL and MySQL SELECT DATE_TRUNC('month', ts) AS month, COUNT(*) FROM events GROUP BY month ORDER BY month; -- Portable across all three (repeats the expression instead of the alias) SELECT DATE_TRUNC('month', ts) AS month, COUNT(*) FROM events GROUP BY DATE_TRUNC('month', ts) ORDER BY DATE_TRUNC('month', ts); The reason engines differ comes down to when aliases are considered "in scope": logically, GROUP BY is evaluated before the SELECT list assigns aliases, so strictly by the standard SQL execution order, the alias shouldn't exist yet when GROUP BY runs. PostgreSQL and MySQL extend the standard to allow it anyway as a convenience; SQL Server follows the stricter interpretation for GROUP BY. Worked example A query grouping by a computed month expression will run unmodified on PostgreSQL and MySQL using the alias, but needs the full expression repeated in GROUP BY to run on SQL Server without a syntax error. Trade-offs and pitfalls If the SQL only ever runs on one known engine, use whichever form that engine's team finds most readable. If the query needs to be portable (a shared library, a multi-engine analytics tool, or simply not knowing the eventual target), always repeat the expression rather than relying on alias reuse, since that form works everywhere without exception.

MediumTechnical

50 practiced

Given users(email), write a query grouping by a computed expression (the substring after '@', case-insensitively) to count users per email domain. How would you speed this grouping up with a functional index in PostgreSQL, and how would you exclude malformed rows with no '@'?

Sample Answer

Grouping by a computed expression, the substring after '@' in an email column here, works exactly like grouping by any ordinary column; the expression is evaluated per row first, and the GROUP BY then groups on its result, with malformed rows (missing the expected structure) needing an explicit filter to exclude before grouping. Structured elaboration sql SELECT LOWER(SUBSTR(email, INSTR(email, '@') + 1)) AS domain, COUNT(*) AS n FROM users WHERE INSTR(email, '@') > 0 GROUP BY domain; INSTR(email, '@') > 0 filters out any row where the email has no '@' at all (a malformed row that would otherwise make the SUBSTR expression behave unpredictably or return an empty/nonsensical domain); this filter belongs in WHERE, evaluated before the grouping expression runs, not as an afterthought. Speeding this pattern up: a plain B-tree index on the raw email column can't help a query grouping on a computed expression of that column; a functional index built directly on the expression itead (CREATE INDEX ON users (LOWER(SUBSTR(email, INSTR(email,'@')+1))) in engines that support expression indexes) lets the engine avoid recomputing the substring extraction for every row on each query. Worked example Given emails ' a@Example.com', ' b@EXAMPLE.com', ' c@other.com', and a malformed 'noatsign' with no '@' at all: the WHERE filter correctly excludes the malformed row before grouping, and the remaining three rows correctly group into example.com (count 2, case-normalized) and other.com (count 1). Trade-offs and pitfalls Malformed-row filtering here is a light-touch, single-condition guard appropriate for a quick grouping query; a systematic sweep for many different kinds of malformed email data (missing domain, multiple '@' signs, invalid characters) is a data-quality/validation concern in its own right, belonging to a dedicated data-validation topic rather than being fully solved inline in every query that happens to touch the email column.

MediumTechnical

40 practiced

Given orders(order_id, created_at TIMESTAMP), explain why filtering March 2024 with created_at BETWEEN '2024-03-01' AND '2024-03-31' can miss rows, since created_at includes a time-of-day. Write a correct, index-friendly query using a half-open range instead.

Sample Answer

MediumTechnical

40 practiced

SELECT category, product_name FROM sales GROUP BY category; raises an error about a non-aggregated column. Explain why, and give two different corrections depending on business intent: picking one representative product per category, versus listing every category/product pair.

Sample Answer

The error means the engine can't decide which value of product_name to display for a category that has more than one; SQL requires every non-grouped, non-aggregated column in the SELECT list to have a single, unambiguous value per group, and by default it refuses to guess. Structured elaboration SELECT category, product_name FROM sales GROUP BY category groups rows by category, but if a category has multiple distinct product_name values, the engine has no rule for picking one (some engines, like MySQL in non-strict mode, will silently pick an arbitrary one instead of erroring, which is arguably worse than an error). Two genuinely different fixes depending on intent: sql -- Pick one representative product per category (e.g., alphabetically first) SELECT category, MIN(product_name) AS a_product FROM sales GROUP BY category; -- List every distinct category/product pair, no aggregation at all SELECT DISTINCT category, product_name FROM sales; Worked example Given sales(category, product_name) with rows ('a','p1'), ('a','p2'), ('b','p3'): the representative-row fix returns ('a','p1'), ('b','p3') (p1 chosen via MIN as an arbitrary but deterministic tie-breaker). The list-all-pairs fix returns all three rows unchanged, since there's no aggregation happening at all. Trade-offs and pitfalls The two fixes answer genuinely different business questions, not two syntaxes for the same thing: "give me one product per category" versus "show me every product-category combination that exists". Picking the wrong one produces a query that runs without error but silently answers a question nobody asked.

EasyTechnical

40 practiced

Given products(product_id, name, category), write a query returning rows where category is one of 'electronics', 'appliances', or 'furniture'. Show it two ways: using IN and using chained OR comparisons. Which is clearer, and does it matter for performance?

Sample Answer

Unlock Full Question Bank

Get access to all SQL Query Fundamentals interview questions and detailed answers.

Join thousands of developers preparing for their dream job.