Top 10 SQLInterview Questions & Answers (2025)

INNER JOIN returns rows that have matching values in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right (NULL for no match). RIGHT JOIN is the mirror — all rows from the right. FULL OUTER JOIN returns all rows from both, with NULLs where there's no match. LEFT JOIN is the most commonly used; INNER JOIN is the default.

WHERE filters rows before grouping (before GROUP BY is applied) and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregate functions (HAVING COUNT(*) > 5). Rule of thumb: WHERE filters individual rows; HAVING filters aggregated groups. You can use both in the same query.

An index is a data structure (typically a B-tree) that allows the database to find rows without scanning the entire table. Like a book index, it maps column values to row locations. Indexes dramatically speed up SELECT queries with WHERE, JOIN, and ORDER BY on indexed columns but slow down INSERT/UPDATE/DELETE (the index must be maintained). Create indexes on columns used in WHERE and JOIN conditions.

Normalisation reduces data redundancy and improves integrity. 1NF: each column has atomic (indivisible) values, no repeating groups. 2NF: 1NF + every non-key attribute is fully functionally dependent on the entire primary key (no partial dependency — matters for composite keys). 3NF: 2NF + no transitive dependencies (non-key attribute doesn't depend on another non-key attribute).

A PRIMARY KEY uniquely identifies each row, cannot be NULL, and there can only be one per table. A UNIQUE KEY also enforces uniqueness but allows one NULL and there can be multiple per table. Both create an index automatically. Use PRIMARY KEY for the entity identifier; use UNIQUE constraints for other columns that must be unique (email, username).

A transaction is a sequence of operations executed as a single unit. ACID properties ensure reliability: Atomicity (all operations succeed or all are rolled back), Consistency (the database moves from one valid state to another), Isolation (concurrent transactions appear sequential), Durability (committed data survives system failures). Transactions are critical for financial operations.

DELETE removes specific rows based on a WHERE clause — it's DML, logged, and can be rolled back. TRUNCATE removes all rows from a table instantly — it's DDL, minimally logged, cannot be used with WHERE, and in many databases cannot be rolled back within a transaction. DROP removes the entire table structure and data permanently.

Window functions perform calculations across a set of rows related to the current row (a 'window') without collapsing them into groups like GROUP BY. Common functions: ROW_NUMBER(), RANK(), DENSE_RANK() for ranking; LAG(), LEAD() for accessing previous/next rows; SUM(), AVG() with OVER (PARTITION BY col ORDER BY col). Essential for analytics queries.

A subquery is a query nested inside another query (in WHERE, FROM, or SELECT clause). A CTE (Common Table Expression) uses the WITH keyword to define a named, temporary result set you can reference multiple times in the main query. CTEs are more readable for complex queries, can be recursive, and some databases optimise them as materialised results.

UNION combines result sets of two queries and removes duplicate rows (requires sorting/hashing). UNION ALL combines result sets and keeps all duplicates — it's faster because no deduplication is needed. Use UNION when duplicates are a concern; use UNION ALL for performance when you know results are distinct or duplicates are acceptable.