SQL Interview Questions and Answers for Data Analyst and Engineering Roles

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions like COUNT or SUM. Use WHERE for row conditions and HAVING for conditions on aggregates.

INNER JOIN returns only matching rows; LEFT/RIGHT JOIN keep all rows from one side and NULLs for non-matches; FULL OUTER JOIN keeps all rows from both; CROSS JOIN returns the Cartesian product. A SELF JOIN joins a table to itself.

UNION combines two result sets and removes duplicate rows, which costs a sort/dedupe step; UNION ALL keeps all rows including duplicates and is faster. Use UNION ALL unless you specifically need distinct rows.

DELETE removes selected rows and is logged and rollback-able; TRUNCATE removes all rows quickly with minimal logging and resets identity; DROP removes the entire table. DELETE and TRUNCATE keep the schema, DROP does not.

A primary key uniquely identifies each row in a table and cannot be NULL. A foreign key references a primary key in another table to enforce referential integrity between the two tables.

NULL means unknown, so comparisons with NULL return unknown, not true. Use IS NULL / IS NOT NULL to test it, and COALESCE to substitute a default. Aggregates like COUNT(column) ignore NULLs.

FROM and JOINs run first, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, and finally LIMIT. This is why a SELECT alias can't be used in WHERE.

ROW_NUMBER gives every row a unique sequential number. RANK gives ties the same rank but leaves gaps afterward (1,1,3). DENSE_RANK gives ties the same rank with no gaps (1,1,2). All are window functions used with OVER(ORDER BY ...).

Use a window function: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rk FROM employees) t WHERE rk = 2. Alternatively use a correlated subquery or OFFSET 1 FETCH NEXT 1 ROW.

A window function (OVER clause) computes an aggregate across related rows without collapsing them, so each row keeps its detail. GROUP BY reduces the result to one row per group. Use windows for running totals, rankings, and moving averages.

Both produce an intermediate result. A CTE (WITH clause) is named, can be referenced multiple times, and can be recursive, which improves readability. A subquery is inline and can be correlated with the outer query. CTEs don't inherently perform better.

IN checks membership against a list or subquery and can be slow with large lists and tricky with NULLs. EXISTS returns true as soon as the subquery finds a matching row and is usually more efficient for correlated existence checks.

A subquery that references a column from the outer query, so it is evaluated once per outer row. It's useful for row-by-row comparisons, such as rows above the average for their group, but can be slower than a JOIN or window function.

Use a window function: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Add PARTITION BY a key to get per-group running totals.

Find them with GROUP BY the duplicate columns HAVING COUNT(*) > 1. Remove them by keeping one row per group using ROW_NUMBER() OVER (PARTITION BY dup_cols ORDER BY id) and deleting rows where the number is greater than 1.

A clustered index defines the physical order of the table's rows, so there can be only one per table. A non-clustered index is a separate structure with pointers to the rows, and you can have many. Both speed up reads but add write cost.

LAG and LEAD are window functions that access a value from a previous or following row without a self-join. They're used for period-over-period comparisons, like the difference between this month and last month.

Both remove duplicates. DISTINCT simply returns unique rows. GROUP BY groups rows so you can apply aggregate functions per group; use it when you need counts, sums, or other aggregates alongside the distinct values.

Normalization organizes data to reduce redundancy and update anomalies, through 1NF (atomic values), 2NF (no partial dependencies), and 3NF (no transitive dependencies). Analytics workloads often deliberately denormalize into star schemas for query speed.

Atomicity (all-or-nothing transactions), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), and Durability (committed data survives failures). Together they guarantee reliable transaction processing.

Read the execution plan, add indexes on filtered and joined columns, select only needed columns, filter early, avoid functions on indexed columns in WHERE, replace correlated subqueries with joins or windows, and refresh statistics.

Use conditional aggregation: SUM(CASE WHEN category = 'A' THEN value END) as one column per category, grouped by the row key. Some databases also offer a PIVOT operator, but conditional aggregation is the portable approach.

A regular view is a saved query that runs each time it's referenced, always showing live data. A materialized view stores the result physically for fast reads and must be refreshed to reflect new data, trading freshness for speed.