← Back to Interview Prep
🗄️

SQL & DBMS Interview Q&A

50 SQL and DBMS scenario-based questions — covering basic queries, joins, indexes, normalization, transactions, window functions, and NoSQL.

10 SQL Basics10 Joins & Indexes10 Normalization & Transactions10 Window Functions10 NoSQL & Scaling
1

SQL Basics & Aggregations

Q1.What is the difference between WHERE and HAVING in SQL?

  • ▸WHERE filters rows BEFORE aggregation — it cannot reference aggregate functions.
  • ▸HAVING filters groups AFTER aggregation — used with GROUP BY.
  • ▸Example: SELECT dept, COUNT(*) FROM emp GROUP BY dept HAVING COUNT(*) > 5; — filters departments with more than 5 employees.

Q2.Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

  • ▸INNER JOIN: returns only rows where there is a match in BOTH tables.
  • ▸LEFT JOIN: returns all rows from the left table and matched rows from the right. Unmatched right rows are NULL.
  • ▸RIGHT JOIN: opposite of LEFT JOIN. All rows from right, matched from left.
  • ▸FULL OUTER JOIN: returns all rows from both tables. NULLs where no match on either side.

Q3.What is the difference between UNION and UNION ALL?

  • ▸UNION combines two result sets and REMOVES duplicate rows (slower due to deduplication).
  • ▸UNION ALL combines result sets and KEEPS all rows including duplicates (faster).
  • ▸Use UNION ALL when you know there are no duplicates or don't care about them.

Q4.What is a subquery? What is the difference between a correlated and non-correlated subquery?

  • ▸A subquery is a query nested inside another query.
  • ▸Non-correlated subquery: runs once independently and the result is used by the outer query.
  • ▸Correlated subquery: references columns from the outer query and re-runs for EACH row of the outer query — typically slower.

Q5.What is the difference between DELETE, TRUNCATE, and DROP?

  • ▸DELETE: removes rows one by one with a WHERE clause. Can be rolled back. Triggers fire.
  • ▸TRUNCATE: removes all rows at once. Much faster. Cannot use WHERE. Cannot be rolled back (in most DBs). Resets auto-increment.
  • ▸DROP: removes the entire table structure and data permanently.

Q6.What is a NULL in SQL? How do you check for NULL properly?

  • ▸NULL represents an unknown or missing value — it is NOT zero or an empty string.
  • ▸You cannot use = NULL or != NULL. Always use IS NULL or IS NOT NULL.
  • ▸NULL in comparisons always evaluates to UNKNOWN. Use COALESCE(col, default_val) to handle NULLs in expressions.

Q7.What is the difference between a primary key, unique key, and foreign key?

  • ▸Primary Key: uniquely identifies each row. Cannot be NULL. One per table.
  • ▸Unique Key: enforces uniqueness but allows one NULL. Multiple unique keys per table allowed.
  • ▸Foreign Key: enforces referential integrity — it must match a value in the referenced (parent) table or be NULL.

Q8.What are aggregate functions in SQL? Name and explain the key ones.

  • ▸COUNT(*): counts all rows including NULLs. COUNT(col) skips NULLs.
  • ▸SUM(col): total of numeric column values. AVG(col): average. MAX/MIN: highest/lowest.
  • ▸All aggregate functions ignore NULLs except COUNT(*).

Q9.What is the difference between a view and a table?

  • ▸A table physically stores data on disk.
  • ▸A view is a stored SQL query — a virtual table. It does not store data itself.
  • ▸Views simplify complex queries, restrict column access, and provide a stable interface even if underlying tables change.

Q10.What is the purpose of the GROUP BY clause and what restriction does it impose?

  • ▸GROUP BY groups rows with the same values in specified columns into summary rows (for use with aggregate functions).
  • ▸Restriction: every column in SELECT must either appear in GROUP BY or be inside an aggregate function.
  • ▸Example: SELECT dept, AVG(salary) FROM employees GROUP BY dept;
2

Joins, Indexes & Performance Tuning

Q11.You have a query joining 3 tables that runs in 30 seconds. How do you optimize it?

  • ▸First: run EXPLAIN ANALYZE to see the query plan and identify full table scans.
  • ▸Add indexes on JOIN columns (foreign keys) and WHERE filter columns if missing.
  • ▸Check for implicit type casts in join conditions (e.g., joining INT to VARCHAR) — these prevent index use.
  • ▸Consider denormalization or a materialized view if the query is run very frequently.

Q12.What is a database index and how does it work internally?

  • ▸An index is a data structure (usually a B-Tree) built on one or more columns to speed up lookups.
  • ▸Instead of scanning every row, the database traverses the B-Tree to find matching rows in O(log n).
  • ▸Trade-off: indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must also update the index).

Q13.When should you NOT create an index?

  • ▸On small tables — a full table scan is often faster than an index lookup for few rows.
  • ▸On columns with low cardinality (few distinct values) like boolean or gender columns.
  • ▸On columns rarely used in WHERE, JOIN, or ORDER BY clauses.
  • ▸On tables with very frequent INSERT/UPDATE/DELETE — index maintenance overhead can hurt write performance.

Q14.What is a composite index? How does column order matter?

  • ▸A composite index covers multiple columns: CREATE INDEX idx ON orders(customer_id, order_date).
  • ▸A query can use this index only if it filters on the LEFTMOST columns first.
  • ▸Example: WHERE customer_id = 5 AND order_date > "2024-01" uses the index. WHERE order_date > "2024-01" alone does NOT (it skips customer_id).

Q15.What is a covering index?

  • ▸A covering index is one that contains ALL the columns a query needs — so the DB never needs to touch the actual table.
  • ▸Example: SELECT customer_id, order_date FROM orders WHERE customer_id = 5; — if the index includes both columns, the query is answered entirely from the index.
  • ▸This is the fastest possible query path for read-heavy patterns.

Q16.What is a self-join? Give a real-world example.

  • ▸A self-join joins a table with itself by using different aliases.
  • ▸Example: Find all employees and their managers — both stored in the same employee table.
  • ▸SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;

Q17.What is the difference between a clustered and non-clustered index?

  • ▸Clustered index: determines the physical order of data rows on disk. Only ONE per table. In SQL Server/MySQL InnoDB, the primary key is always clustered.
  • ▸Non-clustered index: a separate structure with pointers to data rows. Multiple allowed per table.
  • ▸Clustered index lookups are fastest because no second lookup needed after finding the key.

Q18.How do you write a query to find the second highest salary?

  • ▸Method 1 (subquery): SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)
  • ▸Method 2 (DENSE_RANK): SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM employees) t WHERE rnk = 2
  • ▸DENSE_RANK approach generalizes to Nth highest easily.

Q19.What is a CROSS JOIN and when would you use it?

  • ▸CROSS JOIN produces the Cartesian product — every row of table A combined with every row of table B.
  • ▸Result set size: |A| × |B| rows.
  • ▸Use cases: generating combinations (e.g., all color-size combinations for a product), or creating a date dimension in a data warehouse.

Q20.How do you find duplicate rows in a table?

  • ▸SELECT email, COUNT(*) as cnt FROM users GROUP BY email HAVING COUNT(*) > 1;
  • ▸This groups by the duplicate key and shows any email appearing more than once.
  • ▸To delete duplicates keeping one row: use ROW_NUMBER() window function partitioned by the duplicate key and delete rows where row_number > 1.
3

Normalization, Transactions & Concurrency

Q21.Explain 1NF, 2NF, and 3NF normalization with examples.

  • ▸1NF: each column must hold atomic values (no sets/arrays). Each row must be unique.
  • ▸2NF: must be 1NF + every non-key column must depend on the ENTIRE primary key (no partial dependency).
  • ▸3NF: must be 2NF + no transitive dependencies (non-key columns should not depend on other non-key columns).

Q22.What is denormalization and when is it appropriate?

  • ▸Denormalization intentionally introduces redundancy by combining tables or storing derived data.
  • ▸Appropriate when: read performance is critical and joins are too slow, or data is mostly read (analytics/reporting).
  • ▸Trade-off: faster reads, but data consistency must be managed at the application level.

Q23.What are ACID properties in a database transaction?

  • ▸Atomicity: all operations in a transaction succeed or all are rolled back.
  • ▸Consistency: a transaction brings the DB from one valid state to another.
  • ▸Isolation: concurrent transactions cannot see each other's intermediate changes.
  • ▸Durability: once committed, changes persist even if the system crashes (written to disk/WAL).

Q24.What are transaction isolation levels and what problems does each solve?

  • ▸READ UNCOMMITTED: can see uncommitted changes (dirty reads possible).
  • ▸READ COMMITTED (default in most DBs): only sees committed data. Prevents dirty reads.
  • ▸REPEATABLE READ: same query returns same rows in a transaction. Prevents non-repeatable reads.
  • ▸SERIALIZABLE: fully sequential transactions. Prevents phantom reads. Highest isolation, lowest concurrency.

Q25.What is a deadlock in a database? How is it detected and resolved?

  • ▸Deadlock: two transactions each hold a lock the other needs — both wait forever.
  • ▸Detection: databases periodically scan for dependency cycles and automatically kill one transaction (the victim).
  • ▸Prevention: always access tables in the same order, use short transactions, and add appropriate indexes to reduce lock duration.

Q26.What is the difference between optimistic and pessimistic locking?

  • ▸Pessimistic locking: locks the record when you read it (SELECT ... FOR UPDATE). Prevents others from modifying it. Good when conflicts are frequent.
  • ▸Optimistic locking: no lock on read. At update time, check a version column — if changed, abort and retry. Good when conflicts are rare.
  • ▸Optimistic locking is common in web apps (e.g., Django's select_for_update or version-field pattern).

Q27.What is a stored procedure and when should you use it?

  • ▸A stored procedure is precompiled SQL logic stored on the DB server.
  • ▸Benefits: reduced network round-trips (execute complex logic in one call), can be cached, can enforce security.
  • ▸Drawbacks: harder to test/version control, mixes business logic into DB. Prefer in performance-critical batch operations.

Q28.What is referential integrity? What happens when you delete a parent record with children?

  • ▸Referential integrity ensures FK values always point to valid PK values in the parent table.
  • ▸On parent DELETE: RESTRICT (error), CASCADE (auto-delete children), SET NULL (set FK to NULL), SET DEFAULT.
  • ▸Always define ON DELETE behavior explicitly when creating foreign keys.

Q29.What is a trigger in SQL? What are the risks of using them?

  • ▸A trigger is code that automatically runs BEFORE or AFTER an INSERT, UPDATE, or DELETE on a table.
  • ▸Uses: audit logging, enforcing complex constraints, cascading updates.
  • ▸Risks: hidden business logic hard to debug, performance overhead, can cause cascading trigger chains, hard to trace in application code.

Q30.What is the CAP theorem and how does it apply to distributed databases?

  • ▸CAP states a distributed system can guarantee only 2 of 3: Consistency, Availability, Partition Tolerance.
  • ▸Since network partitions are unavoidable in distributed systems, you choose CA or CP or AP trade-offs.
  • ▸Example: HBase/MongoDB (CP), Cassandra/DynamoDB (AP), traditional RDBMS in single node (CA).
4

Window Functions & Advanced Queries

Q31.What are window functions in SQL? How are they different from GROUP BY?

  • ▸Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing them into groups.
  • ▸GROUP BY collapses rows. Window functions keep all rows and add a computed column.
  • ▸Syntax: FUNC() OVER (PARTITION BY col ORDER BY col)

Q32.Explain ROW_NUMBER(), RANK(), and DENSE_RANK() with an example.

  • ▸ROW_NUMBER(): assigns a unique number to each row (no ties). 1,2,3,4.
  • ▸RANK(): assigns same rank to ties, then SKIPS the next rank(s). 1,1,3,4.
  • ▸DENSE_RANK(): assigns same rank to ties, DOES NOT skip. 1,1,2,3.
  • ▸Use case: DENSE_RANK to find Nth highest salary.

Q33.What is LAG() and LEAD() in SQL? Give a business use case.

  • ▸LAG(col, n): accesses a value from n rows BEFORE the current row in the window.
  • ▸LEAD(col, n): accesses a value from n rows AFTER the current row.
  • ▸Use case: calculate month-over-month revenue change — LAG(revenue, 1) gives last month's revenue.

Q34.Write a query to calculate a running total of sales per month.

  • ▸SELECT month, sales, SUM(sales) OVER (ORDER BY month) AS running_total FROM monthly_sales;
  • ▸The OVER (ORDER BY month) creates a cumulative window from the first row up to the current row.
  • ▸This is the standard running total / cumulative sum pattern in SQL.

Q35.How do you delete duplicate rows while keeping the one with the lowest ID?

  • ▸DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
  • ▸Or using ROW_NUMBER: DELETE FROM users WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn FROM users) t WHERE rn > 1);
  • ▸The ROW_NUMBER approach is more flexible and works in databases that don't support subqueries in DELETE.
5

NoSQL, Scaling & Architecture

Q41.What is the difference between SQL (relational) and NoSQL databases?

  • ▸SQL: structured schema, ACID transactions, relations via foreign keys. Best for complex queries and strong consistency.
  • ▸NoSQL: flexible/schema-less, horizontal scaling, eventual consistency in many cases. Best for large-scale unstructured or semi-structured data.
  • ▸Types of NoSQL: Document (MongoDB), Key-Value (Redis), Wide-Column (Cassandra), Graph (Neo4j).

Q42.When would you choose MongoDB over PostgreSQL?

  • ▸Choose MongoDB when: schema changes frequently, data is hierarchical/nested JSON, and horizontal scaling is needed from day one.
  • ▸Choose PostgreSQL when: data has complex relationships, you need strong ACID guarantees, or your queries involve complex joins and aggregations.
  • ▸Many modern apps use both — PostgreSQL for transactional data and MongoDB for flexible/document data.

Q43.What is Redis and how is it used in web applications?

  • ▸Redis is an in-memory key-value store known for sub-millisecond performance.
  • ▸Common uses: caching DB query results, session storage, pub/sub messaging, rate limiting, and leaderboards.
  • ▸Data expires automatically via TTL (Time-To-Live), making it ideal for temporary data like sessions and OTP codes.

Q44.What is database sharding?

  • ▸Sharding splits a large database horizontally — distributing rows across multiple database servers (shards).
  • ▸Each shard holds a subset of data (e.g., users A-M on shard 1, N-Z on shard 2).
  • ▸Benefits: horizontal scalability. Challenges: cross-shard queries, rebalancing shards, and increased operational complexity.

Q45.What is the difference between vertical and horizontal scaling for databases?

  • ▸Vertical scaling (scale up): add more CPU/RAM/disk to the existing server. Simple but has a ceiling.
  • ▸Horizontal scaling (scale out): add more servers. Required for petabyte-scale systems.
  • ▸RDBMS typically scales vertically. NoSQL databases (Cassandra, MongoDB) are designed for horizontal scaling.

🗄️ Pro Tip for SQL Interviews

Always mention EXPLAIN / EXPLAIN ANALYZE when asked about query optimization. Interviewers love candidates who know how to read a query plan.

← Back to All Interview Guides