SQL Interview Questions — Basics

The Anatomy of a SELECT Statement

The SELECT statement is the primary vehicle for data retrieval in a relational database. Understanding how it operates requires recognizing the logical order of operations, which begins with the FROM clause to identify the data source, followed by the WHERE clause to narrow down the dataset based on specific predicates. When we select columns, we are essentially projecting a subset of our table onto a result set. The reason we specify columns explicitly rather than using the wildcard asterisk is for performance optimization and code maintainability. If the underlying table structure changes, explicitly defined columns prevent your application from breaking or returning unexpected data formats. By mastering the sequence in which SQL processes clauses, you can predict exactly how filtering happens before the final projection is returned to the user, allowing you to write highly predictable and clean code.

-- Select specific columns to reduce I/O overhead
SELECT 
    customer_name, 
    email_address
FROM customers
-- WHERE filters rows before the final column projection;
WHERE account_status = 'active';

Filtering Data with WHERE Clauses

The WHERE clause acts as a gatekeeper, determining which rows from the source tables satisfy your requirements. It uses logical operators such as AND, OR, and NOT to refine criteria. A critical nuance to remember during interviews is how SQL handles NULL values. Because a NULL represents the absence of data, it is not equal to any value, including zero or an empty string; therefore, you must use IS NULL or IS NOT NULL operators. When you chain multiple conditions, the database engine uses operator precedence. Parentheses are your best tool for grouping logic, ensuring the database evaluates your intended hierarchy of filters. Reasoning through complex conditions by mentally isolating each part of the boolean expression is the key to writing queries that avoid logical errors and ensure your final dataset includes exactly what the business logic demands.

-- Using AND/OR with parentheses to control logic
SELECT product_id, price
FROM products
-- Parentheses ensure the OR logic is isolated from the AND check
WHERE (category = 'Electronics' OR category = 'Office')
AND price > 100
AND discount_code IS NOT NULL;

Ordering and Limiting Results

After retrieving and filtering, you often need to organize the data for reporting. The ORDER BY clause is used to sort the result set, and it works by sorting based on the final, computed result set just before it is returned. You can sort by multiple columns, where the second column acts as a tie-breaker for the first. Meanwhile, the LIMIT clause serves to restrict the number of returned rows, which is vital for preventing memory overflow when dealing with millions of records. It is important to note that without an ORDER BY clause, the database does not guarantee any specific sequence for the output. If your requirements demand the top N rows, you must pair LIMIT with ORDER BY to ensure that the rows you retrieve are the most relevant based on the sorting criteria provided.

-- Get the 5 most expensive products
SELECT product_name, price
FROM products
-- Sort first to determine which rows reach the top
ORDER BY price DESC
-- Limit returns only the requested subset
LIMIT 5;

Aggregating Data with GROUP BY

Aggregation functions allow you to perform calculations across a set of rows, such as counting items or summing sales totals. The GROUP BY clause is the mechanism used to define the buckets for these calculations. When you use GROUP BY, every column in your SELECT statement must either be an aggregate function or appear within the GROUP BY clause itself; otherwise, the database engine cannot map the aggregate result back to the individual rows. This is a common point of failure for beginners. Think of GROUP BY as a way to collapse multiple source rows into a single summary row for every unique combination of values in the grouping columns. This is the foundation for almost every analytical metric, turning raw event logs into meaningful trends, totals, and counts that business stakeholders rely on for decision-making.

-- Calculate total sales per category
SELECT 
    category, 
    SUM(sales_amount) as total_revenue
FROM transactions
-- Grouping collapses all rows into category buckets
GROUP BY category
-- HAVING filters the results AFTER the aggregation
HAVING SUM(sales_amount) > 1000;

The Difference Between WHERE and HAVING

A frequent interview trap is the confusion between WHERE and HAVING. The rule of thumb is simple: WHERE filters the raw, individual rows before any grouping or calculation happens, whereas HAVING filters the summarized groups after the aggregation has taken place. If you attempt to filter by an aggregate total in a WHERE clause, the query will fail because the aggregate values do not exist until the grouping process is complete. By understanding this execution sequence, you can place your filtering logic in the correct spot. Always ask yourself: Does this constraint apply to a single row, or does it apply to the result of a calculation? If it is the former, use WHERE. If it is the latter, such as finding categories with high total sales, use the HAVING clause.

-- Filtering rows BEFORE grouping (WHERE)
SELECT status, COUNT(*) 
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY status
-- Filtering groups AFTER aggregation (HAVING)
HAVING COUNT(*) > 50;

Interview questions

All SQL interview questions →

Check yourself

Take the full SQL quiz →