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

73 practiced

Given page_views(user_id, page_id, viewed_at), compare using DISTINCT versus GROUP BY to compute the number of unique users per page. Which is more appropriate here, and why?

Sample Answer

DISTINCT and GROUP BY can produce the identical result for a plain "how many unique X" question, but GROUP BY additionally lets you compute an aggregate per group in the same query, which DISTINCT alone cannot do. Structured elaboration sql -- DISTINCT (just the unique values, no aggregate) SELECT DISTINCT user_id FROM page_views WHERE page_id = 100; -- GROUP BY (lets you also count views per user, for instance) SELECT user_id, COUNT(*) AS views FROM page_views WHERE page_id = 100 GROUP BY user_id; For "how many unique users viewed page 100", both SELECT COUNT(DISTINCT user_id) and SELECT COUNT(*) FROM (SELECT user_id FROM page_views WHERE page_id=100 GROUP BY user_id) give the same number; the optimizer typically treats them equivalently. GROUP BY earns its keep once you need something per-group beyond simple deduplication. Worked example Given page_views(user_id, page_id, viewed_at) with user 1 viewing page 100 twice and user 2 viewing it once: SELECT DISTINCT user_id FROM page_views WHERE page_id=100 returns two rows (users 1 and 2). SELECT page_id, COUNT(DISTINCT user_id) FROM page_views GROUP BY page_id returns page 100 with 2 unique users, the same underlying count, from a query shape that could also report each user's individual view count in the same pass if needed. Trade-offs and pitfalls For pure deduplication with no aggregate need, DISTINCT is more direct and often reads more clearly to the next person; reach for GROUP BY once you're already computing an aggregate (COUNT, SUM, etc.) per group, rather than adding an unnecessary DISTINCT+subquery when GROUP BY would do both jobs in one pass.

MediumTechnical

51 practiced

Describe how to write a parameterized SQL query for a report where the user can optionally filter by product_category and/or region. Show a template using placeholders, and how to make the filter a no-op when a parameter is NULL or not provided.

Sample Answer

A parameterized filter that should be a no-op when its parameter isn't supplied needs the "parameter is NULL, so don't filter" condition combined with the real comparison via OR, evaluated once per optional parameter. Structured elaboration sql SELECT order_id FROM orders WHERE (:category IS NULL OR product_category = :category) AND (:region IS NULL OR region = :region); Each optional filter is wrapped in its own (:param IS NULL OR column = :param) clause: when the parameter is NULL, the left side of the OR is TRUE, making the whole clause TRUE regardless of the column's value (a no-op filter); when the parameter has a real value, the left side is FALSE, so the actual comparison on the right side is what determines whether the row qualifies. The same pattern generalizes cleanly to any number of independent optional filters, ANDed together. Worked example Given orders in categories electronics (region east) and toys (region west), with :category = 'electronics' and :region left unset (NULL): the query correctly returns only the electronics order, applying the category filter while the region clause is a no-op due to its NULL parameter. Trade-offs and pitfalls This pattern is convenient to write but has a real performance cost worth naming: the OR construction typically defeats a plain index on the filtered column, since the optimizer can't assume the comparison will always run; for a report with heavy, frequent optional-filter usage at scale, some engines and query builders instead generate the WHERE clause dynamically (omitting a condition entirely when its parameter is absent) rather than relying on this always-present-but-sometimes-no-op OR pattern, trading a bit of code-generation complexity for better index usage.

EasyTechnical

73 practiced

Explain why WHERE phone = NULL returns no rows even when some rows have a NULL phone value. Write the correct query to select rows where phone IS NULL, and a second one for rows where it IS NOT NULL.

MediumTechnical

46 practiced

Given orders(order_id, customer_id, amount, order_date) and vip_customers(customer_id), write a query returning orders placed by VIP customers using IN (subquery), then an equivalent using EXISTS, then an equivalent using JOIN. Discuss the performance and semantic considerations among the three for large tables.

Sample Answer

MediumTechnical

42 practiced

Using ROLLUP(category, subcategory) on sales(category, subcategory, amount), write a query returning category, subcategory, total_amount, and a label distinguishing detail rows from subtotal and grand-total rows. Explain how GROUPING() or GROUPING_ID helps identify which rows are totals.

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.