SQL Mock Interview: 20 Questions in 50 Minutes

A view is a saved SELECT query that behaves like a virtual table. It simplifies complex joins for users, hides sensitive columns for security, and provides a stable interface when underlying tables change. A normal view does not store data, so it runs the query each time. Simple views can be updatable, while views with aggregates or joins usually are not.

GROUP BY groups rows that share values in specified columns so aggregates can be computed for each group. Every column in SELECT must either be in GROUP BY or inside an aggregate function. SELECT city, COUNT(*) FROM customers GROUP BY city is valid, but adding name without aggregating it is an error in standard SQL, though older MySQL modes allowed it.

Common types are INT, BIGINT and SMALLINT for whole numbers, DECIMAL or NUMERIC for exact values like money, FLOAT for approximate values, CHAR for fixed-length and VARCHAR for variable-length text, DATE, TIME, DATETIME or TIMESTAMP for dates, and BOOLEAN where supported. Choosing correct types saves space, improves performance and prevents errors such as storing amounts in FLOAT and getting rounding issues.

A composite index covers multiple columns, such as (customer_id, order_date). It is sorted by the first column, then the second within it. It helps queries filtering on customer_id alone or on both columns, but not queries filtering only on order_date, because of the leftmost prefix rule. Put the most commonly filtered equality column first, followed by range columns.

DISTINCT removes duplicate rows from the result based on all selected columns. GROUP BY groups rows so you can apply aggregates per group. SELECT DISTINCT city FROM customers and SELECT city FROM customers GROUP BY city give the same output, but GROUP BY is needed when you also want counts or sums. Overusing DISTINCT to hide duplicate join rows is a common mistake.

In standard SQL, GROUP BY and HAVING are evaluated before SELECT, so aliases are not available there. MySQL and PostgreSQL allow aliases in GROUP BY, and MySQL allows them in HAVING, but SQL Server does not. For portable code, repeat the expression or wrap the query in a subquery or CTE and filter on the alias in the outer query.

A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.

UNION combines the results of two queries and removes duplicate rows, which requires sorting or hashing. UNION ALL combines results and keeps all rows, so it is faster. Both need the same number of columns with compatible types in the same order. Use UNION ALL when duplicates are impossible or acceptable, such as stacking monthly tables, and UNION only when deduplication is required.

A frame defines which rows around the current row are included, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average. When ORDER BY is present and no frame is specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which produces a running total but includes all ties of the current value. Without ORDER BY, the frame is the whole partition.

Exact distinct counts need sorting or hashing all values, which is expensive. Options are filtering the date range tightly, pre-aggregating daily distinct users into a summary table, indexing the columns involved, or using approximate functions such as APPROX_COUNT_DISTINCT or HyperLogLog where small error is acceptable. Explain the accuracy trade-off to stakeholders before switching to approximations.

Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.

Check whether the subquery returns any NULL values, because NOT IN with a NULL makes every comparison unknown. Run the subquery alone and look for NULLs. Fix it with NOT EXISTS, which handles NULLs correctly, or add WHERE column IS NOT NULL inside the subquery. Also confirm data types match so values compare as expected.

With ORDER BY and no explicit frame, the default frame is RANGE UNBOUNDED PRECEDING, which treats all rows with the same order value as peers and includes them together. When several rows share the same date, the running total adds them all at once. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, or add a unique tiebreaker to ORDER BY.

This is a race condition. Wrap the check and booking in one transaction and lock the seat row with SELECT ... FOR UPDATE so the second transaction waits and then sees it is taken. Alternatively use an atomic update such as UPDATE seats SET booked_by = :user WHERE seat_id = :id AND booked_by IS NULL and check affected rows, plus a unique constraint as a final safety net.

Situation: the product team wanted to know where users dropped off in the onboarding flow. Task: build a funnel from raw event logs. Action: I used window functions to order events per user, LEAD to find the next step and time taken, and grouped the results into a funnel with conversion rates. Result: we found a 40 percent drop at KYC upload, and the redesign improved completion by 15 percent.

Situation: my manager asked why a report was late every Monday. Task: explain and get approval for a fix. Action: I compared the database to a busy shop counter where reports and customers queue together, showed the timing data, and proposed a separate reporting copy. Result: he understood the trade-off in five minutes, approved the read replica, and Monday reports became on time.

Situation: the sales head needed region-wise quarterly numbers for a client meeting the same afternoon. Task: deliver accurate figures within three hours. Action: I confirmed metric definitions, reused tested queries, wrote one grouped query with ROLLUP for subtotals and cross-checked totals with the finance summary. Result: the report was delivered in two hours, and it later became a scheduled weekly report.

Situation: a daily settlement report took 45 minutes and often missed its deadline. Task: bring it under ten minutes. Action: I studied the execution plan, replaced a non-SARGable date filter, added a composite covering index, pre-aggregated a large join in a temp table and updated statistics. Result: runtime dropped to six minutes, and the finance team received reports on time every day.