Loading...
Loading...
Loading...
Core CS and SQL interview guide
Keys, normalisation, transactions, ACID, indexing and concurrency control. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
60 questionsLast updated
An index can speed up finding the rows to update, but changing an indexed column requires updating the index too. Updating non-indexed columns has no index cost beyond finding the rows.
Lesson: Indexing basicsRun EXPLAIN and look for full scans or a high rows estimate; check that filter, join and sort columns are indexed in the right order; remove functions and leading wildcards on indexed columns; then measure again. If needed, add a covering composite index.
Follow-up: EXPLAIN shows the index is used, but the query is still slow. Now what? Check how many rows it still reads, whether it needs a sort or a temporary table, and whether a covering or composite index would help.
Common mistake: Answering "add an index" straight away. First run EXPLAIN on the real query, change one thing, then measure again.
Lesson: Indexing basicsSQL databases use tables with a fixed schema, joins and strong ACID transactions, and usually scale up or with read replicas. NoSQL databases use other models, often with flexible schemas, and many are designed to scale out across machines, sometimes with weaker consistency.
Follow-up: Give one case where SQL clearly wins. Payments: they need ACID transactions, constraints and joins across accounts, orders and refunds, which relational databases do natively.
Common mistake: Saying "NoSQL is faster or newer". Be specific: faster at which access pattern, and what it gives up.
Lesson: SQL vs NoSQL at a high levelDocument stores such as MongoDB, key-value stores such as Redis, wide-column stores such as Cassandra and HBase, and graph databases such as Neo4j.
Follow-up: Give one product for each type. Document: MongoDB. Key-value: Redis. Wide-column: Cassandra. Graph: Neo4j.
Lesson: SQL vs NoSQL at a high levelIt stores a value under a key and supports get, put and delete very fast, often in memory. It is used for caches, sessions, OTPs, rate-limit counters and leaderboards.
Lesson: SQL vs NoSQL at a high levelIt stores rows in partitions spread across many nodes, where each row can have many, varying columns. It handles very high write volumes such as time-series and event logs, with queries designed around the partition key.
Lesson: SQL vs NoSQL at a high levelIt stores nodes and edges with properties and follows relationships directly instead of joining tables. It suits social networks, recommendations, fraud detection and route finding.
Lesson: SQL vs NoSQL at a high levelNo. It speeds up early changes, but without a schema the application must validate fields, and old documents in different shapes stay around. Many teams add schema validation even in document stores.
Lesson: SQL vs NoSQL at a high levelMaking one server more powerful with more CPU, RAM or faster storage. It is simple and needs no code changes, but has a hardware limit and remains a single point of failure.
Follow-up: What is the limit of vertical scaling? One machine has a ceiling on CPU and RAM and is a single point of failure, and very large servers get expensive quickly.
Lesson: SQL vs NoSQL at a high levelSplitting a dataset across multiple machines by a shard key, such as user id, so each shard holds a part of the data. Queries with the shard key go to one shard; others may have to ask all shards.
Follow-up: What goes wrong with a poor shard key? One shard gets most of the traffic (a hot shard), or a common query has to visit every shard. A good key spreads load evenly and keeps related data together.
Lesson: SQL vs NoSQL at a high levelPick a key with many values that spreads load evenly and is present in most queries, such as user_id. Avoid keys that create hot spots, such as the current date for new writes.
Common mistake: Sharding on a column most users share, such as country. It creates hot shards; use a high-variety key such as user_id.
Lesson: SQL vs NoSQL at a high levelFor a distributed data store, when a network partition happens, the system must choose between consistency (every read sees the latest write) and availability (every request gets a response). Without a partition it can provide both.
Follow-up: Does CAP apply when the network is healthy? No. It describes what you give up during a partition. With a healthy network, a system can offer both consistency and availability.
Common mistake: Saying "pick any two of the three". Partitions will happen, so the real choice during a partition is consistency or availability.
Lesson: SQL vs NoSQL at a high levelBecause network partitions cannot be ruled out in a system spread across machines. Since P must be tolerated, the real choice during a partition is between C and A.
Lesson: SQL vs NoSQL at a high levelA system that refuses writes it cannot replicate to a majority is CP, often the case with HBase or MongoDB using majority writes. A system that accepts writes on any replica and reconciles later is AP, as Cassandra can be configured. Many databases let you tune this per operation.
Lesson: SQL vs NoSQL at a high levelA guarantee that if writes stop, all replicas will eventually hold the same value. Reads in between may return stale data.
Follow-up: Where is eventual consistency acceptable? A like counter or a view count: showing 1,203 instead of 1,205 for a moment does no harm. For a bank balance it is not acceptable.
Lesson: SQL vs NoSQL at a high levelBasically Available, Soft state, Eventually consistent: a description of many NoSQL systems' approach, contrasting with ACID's strict guarantees.
Lesson: SQL vs NoSQL at a high levelMany support atomic operations on a single document or key, and some now support multi-document transactions, such as MongoDB 4.0 and later, often with higher cost. Relational databases remain the natural fit for complex multi-row transactions.
Follow-up: Name a NoSQL database that supports transactions. MongoDB supports multi-document ACID transactions since version 4.0, at some performance cost.
Lesson: SQL vs NoSQL at a high levelEither by embedding related data inside a document, which is fast to read but duplicates data, or by storing references (ids) and fetching related documents separately or with an aggregation lookup.
Lesson: SQL vs NoSQL at a high levelWhen data is structured and related, integrity matters and multi-row transactions are needed: payments, orders, bookings, inventory, college records. It is the usual default.
Lesson: SQL vs NoSQL at a high levelNot in general. A key-value lookup in memory is faster than a SQL join, but a well-indexed SQL query is fast too, and NoSQL can be slow for queries it was not designed for. Speed depends on the access pattern.
Common mistake: Answering "yes, NoSQL is faster". It is faster for specific patterns like key lookups at scale, and slower or harder for joins and multi-row transactions.
Lesson: SQL vs NoSQL at a high levelUsing different databases for different parts of one system according to their strengths, such as MySQL for orders, Redis for caching and Elasticsearch for search.
Lesson: SQL vs NoSQL at a high levelSELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 1, or without LIMIT, SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp). The second form returns NULL when there is no second salary.
Follow-up: What if two employees share the highest salary? The max-below-max query still works: the subquery returns the single highest value, so the duplicates count once and the next lower salary comes back.
Common mistake: Using LIMIT 1 OFFSET 1 without DISTINCT. If the top salary is shared, that returns the highest salary again instead of the second.
Lesson: Classic SQL interview queriesWith DISTINCT plus LIMIT 1 OFFSET N-1; with a correlated subquery where exactly N − 1 distinct salaries are greater; or with DENSE_RANK() OVER (ORDER BY salary DESC) = N, which also returns everyone at that salary.
Follow-up: How would you write it in SQL Server, which has no LIMIT? Use DENSE_RANK(), or a nested SELECT TOP with DISTINCT salaries ordered descending and then the lowest of those.
Lesson: Classic SQL interview queriesROW_NUMBER gives every row a unique number. RANK gives ties the same rank and then skips numbers (1, 2, 2, 4). DENSE_RANK gives ties the same rank without gaps (1, 2, 2, 3).
Follow-up: Which one do you use for Nth highest salary, and why? DENSE_RANK, because it does not skip numbers after ties, so rank N is the Nth distinct salary.
Common mistake: Saying RANK and DENSE_RANK are the same. After a tie RANK skips numbers (1, 2, 2, 4) and DENSE_RANK does not (1, 2, 2, 3).
Lesson: Classic SQL interview queriesFor each salary in the outer query, the inner query counts the distinct salaries greater than it. The salary with exactly N − 1 greater salaries is the Nth highest. It runs the inner query per row, so it is O(n²).
Follow-up: What is its time complexity? O(n²): for every row the subquery scans the table again. It works on every database but is slow on big tables.
Lesson: Classic SQL interview queriesGroup by the columns that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM t GROUP BY email HAVING COUNT(*) > 1.
Follow-up: How do you prevent duplicates in future? Add a UNIQUE constraint on the column, such as email. The database then rejects a duplicate insert.
Lesson: Classic SQL interview queriesMySQL does not allow a DELETE or UPDATE to select from the same table directly in a subquery (error 1093). Wrapping the subquery in a derived table makes MySQL materialise it first.
Lesson: Classic SQL interview queriesA join of a table with itself using two aliases. Employees and their managers: FROM emp e JOIN emp m ON e.manager_id = m.emp_id.
Follow-up: Why do you need aliases? Both copies of the table have the same column names, so aliases like e and m say which copy each column comes from.
Lesson: Classic SQL interview queriesUse a LEFT JOIN from employee to manager, so employees with manager_id NULL still appear with a NULL (or COALESCE'd) manager name.
Lesson: Classic SQL interview queriesWHERE filters individual rows before grouping and cannot use aggregates. HAVING filters groups after GROUP BY and can use aggregates such as COUNT(*) > 2.
Follow-up: Can one query have both? Yes. WHERE filters the rows first, GROUP BY groups what is left, and HAVING filters the groups.
Common mistake: Saying "HAVING is only for aggregates". A plain column test belongs in WHERE, which filters earlier and is cheaper.
Lesson: Classic SQL interview queriesSELECT dept, COUNT(*) AS c FROM emp GROUP BY dept ORDER BY c DESC LIMIT 1. If ties matter, compare with the maximum count in a subquery or use RANK().
Join each department's MAX(salary) back to the table on dept and salary, use a correlated subquery salary = (SELECT MAX(salary) ... WHERE dept = e.dept), or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) = 1.
Follow-up: What changes if tied top earners must all appear? Join on salary = the department maximum, or use RANK() = 1 over each department, so everyone sharing the top salary is returned.
Lesson: Classic SQL interview queriesname is neither grouped nor aggregated, so a group has no single defined name. Standard SQL and MySQL's default ONLY_FULL_GROUP_BY mode reject it.
Lesson: Classic SQL interview queriesIn MySQL, no: it is DDL and causes an implicit commit. Some databases, such as PostgreSQL, can roll back TRUNCATE inside a transaction.
Follow-up: What about PostgreSQL? PostgreSQL allows TRUNCATE inside a transaction and can roll it back. MySQL commits implicitly, so it cannot.
Lesson: Classic SQL interview queriesTRUNCATE, because it drops and recreates the table instead of deleting and logging row by row.
Lesson: Classic SQL interview queriesSELECT name FROM emp e WHERE NOT EXISTS (SELECT 1 FROM emp x WHERE x.manager_id = e.emp_id). Avoid NOT IN with a subquery that may return NULL, because then it returns no rows.
Rank within each department and filter: SELECT * FROM (SELECT name, dept, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) r FROM emp) t WHERE r <= 3.
Use a LEFT JOIN and keep rows where the right side is NULL, or NOT EXISTS: SELECT s.* FROM student s LEFT JOIN application a ON a.roll_no = s.roll_no WHERE a.roll_no IS NULL.
With a window function: SELECT emp_id, salary, SUM(salary) OVER (ORDER BY emp_id) AS running_total FROM emp.
COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values.
Lesson: Classic SQL interview queriesI would use student(roll_no PK, name, branch, cgpa), company(company_id PK, name), drive(drive_id PK, company_id FK, drive_date, ctc_lpa) and application(roll_no FK, drive_id FK, status, with both keys as the primary key). Company to drive is 1:N, so the foreign key sits on drive; student to drive is M:N, so application is the junction table. In every table the only determinant is the key, so all four are in BCNF.
Follow-up: Where would a student's skills go? A separate student_skill(roll_no, skill) table with both columns as the key. Skills are a multivalued attribute, and a comma-separated column would break 1NF.
Common mistake: Drawing tables before stating the cardinalities. Say "one company, many drives; many students, many drives" aloud first, then derive the keys.
Lesson: Practice: DBMS mixed round and SQL drillThe key is (order_id, product_id), but customer_id and customer_name depend on order_id alone, and product_name on product_id alone. Those are partial dependencies, so the table fails 2NF. I split it into customer(customer_id, customer_name), product(product_id, product_name), orders(order_id, customer_id FK) and order_item(order_id, product_id, qty). Each fact is stored once, and joining on the keys rebuilds the original rows.
Follow-up: Is customer_id → customer_name a problem in the new orders table? No. customer_name now lives only in customer, where customer_id is the key. orders keeps just the foreign key, so there is no transitive dependency.
Common mistake: Fixing it with an index or a constraint. The problem is repeated data, so the cure is to decompose the table.
Lesson: Practice: DBMS mixed round and SQL drillBoth statements run in one transaction. If the receipt insert fails, I roll back, so the wallet debit is undone: atomicity. Only when both succeed do I COMMIT, and the committed change is durable through the redo log.
Follow-up: In Spring, how do you make sure it rolls back? Put @Transactional on the service method. An unchecked exception from the second statement rolls back the whole method; a checked exception needs rollbackFor.
Common mistake: Running the two statements with autocommit on. Each commits alone, so a failure leaves the money debited and no receipt.
Lesson: Practice: DBMS mixed round and SQL drillSELECT name FROM student s WHERE NOT EXISTS (SELECT 1 FROM application a WHERE a.roll_no = s.roll_no), or a LEFT JOIN to application with WHERE a.roll_no IS NULL. The trap is NOT IN: if the subquery returns even one NULL, NOT IN is never true and the query returns no rows.
Follow-up: Which of the two is faster? Usually the same: the optimiser turns both into an anti-join. Pick the one that reads clearest and check EXPLAIN on real data.
Common mistake: Using an inner join and filtering for NULLs. An inner join drops the students with no application, so you can never find them.
Lesson: Practice: DBMS mixed round and SQL drillRank the students inside each branch with RANK() OVER (PARTITION BY branch ORDER BY cgpa DESC) in a derived table and keep rank 1. RANK gives tied students the same rank, so all of them appear. Without window functions, join student to the per-branch maximum: join on branch and cgpa = MAX(cgpa) from a GROUP BY branch subquery.
Common mistake: Writing SELECT branch, name, MAX(cgpa) ... GROUP BY branch. The name is not grouped, so MySQL rejects it, and other modes show an arbitrary name.
Lesson: Practice: DBMS mixed round and SQL drillAn index on application(drive_id), so MySQL can find that drive's rows without scanning the whole table. The primary key is (roll_no, drive_id), which starts with roll_no, so it cannot serve a lookup on drive_id alone: the leftmost-prefix rule. After adding the index, I run EXPLAIN again to confirm that type is ref.
Follow-up: Would (drive_id, roll_no) be better? Yes for this query. It serves WHERE drive_id = ? and also holds the join column, so MySQL may not need to read the table rows at all.
Lesson: Practice: DBMS mixed round and SQL drillA composite index on (branch, cgpa). The equality column goes first so the tree narrows to CSE, and cgpa second so the range and the ORDER BY are served in index order with no extra sort. Two single-column indexes would let MySQL use only one of them.
Common mistake: Putting the range column first, as (cgpa, branch). After a range on the first column, the index cannot narrow by branch any more.
Lesson: Practice: DBMS mixed round and SQL drillThe head of department repeats for every student of a branch, because branch → hod. Changing a head means updating many rows (update anomaly), and a branch with no students cannot be stored (insert anomaly). I split it into student(roll_no, name, branch) and branch(branch, hod), which is in 3NF and BCNF.
Lesson: Practice: DBMS mixed round and SQL drillAnother transaction committed a change between the two reads: a non-repeatable read, or a phantom if rows were inserted. READ COMMITTED allows it. REPEATABLE READ, the InnoDB default, gives the transaction a snapshot so the totals stay the same, and SERIALIZABLE would also stop phantoms.
Common mistake: Blaming the query. The query is fine: the isolation level decided how much of other transactions' commits it could see.
Lesson: Practice: DBMS mixed round and SQL drillOrders and payments stay in MySQL or PostgreSQL, because they need transactions, constraints and joins. Session tokens go to Redis, a key-value store with expiry. The click events are write-heavy and append-only, so a wide-column store like Cassandra, or a data warehouse, fits better than the main database.
Common mistake: Putting everything in one database "because it is simpler". Match each data set to its access pattern, and say what you give up.
Lesson: Practice: DBMS mixed round and SQL drillThat breaks 1NF: you cannot index or search one number, check uniqueness, or update one number without rewriting the whole text. I would store one row per number in student_phone(roll_no, phone). If a report needs one string, build it with GROUP_CONCAT instead of storing it.
Lesson: Practice: DBMS mixed round and SQL drillIt finds the row through an index, takes an exclusive lock on it, writes the old value to the undo log and changes the row in memory. The change also goes to the redo log. On COMMIT the redo log is flushed to disk and the lock is released; on ROLLBACK the undo log restores the old value.
Follow-up: What happens to another transaction that wants the same row? It waits for the lock until this transaction commits or rolls back, or fails with a lock timeout. If the two wait on each other, InnoDB picks a victim instead.
Lesson: Practice: DBMS mixed round and SQL drillFind them with GROUP BY email HAVING COUNT(*) > 1. Delete the extra copies with a self join that keeps the lowest id: DELETE a FROM t a JOIN t b ON a.email = b.email AND a.id > b.id. Then ALTER TABLE t ADD UNIQUE (email), so the database rejects new duplicates.
Common mistake: Cleaning the data but not adding the constraint. Without UNIQUE, the next import brings the duplicates back.
Lesson: Practice: DBMS mixed round and SQL drillDELETE in batches, for example 10,000 rows per transaction in a loop, keeps locks and the undo log small. If the whole table can go, TRUNCATE is far faster but takes no WHERE and commits at once. For ongoing cleanup, partition the table by month and drop old partitions.
Lesson: Practice: DBMS mixed round and SQL drillInnoDB stores rows in primary-key order. An increasing id appends to the end of the tree, which is cheap; a random UUID inserts into the middle, causing page splits, and its larger size is copied into every secondary index. I use auto-increment or a time-ordered id unless ids must be created outside the database.
Lesson: Practice: DBMS mixed round and SQL drillYes. Joins, and deletes of the parent row, look up child rows by the foreign key, so without an index each lookup scans the child table. InnoDB creates an index automatically if none exists, but a composite index you design yourself, such as (drive_id, roll_no), can serve more queries.
Lesson: Practice: DBMS mixed round and SQL drillShopLite has customer, product, category, orders and order_item tables in MySQL. Order to product is M:N through order_item, which holds quantity and price, and the foreign keys are indexed. Checkout runs inside one @Transactional method, so stock, order and payment succeed or fail together. I added an index on orders(customer_id, created_at) after EXPLAIN showed a full scan on the order history page.
Follow-up: What would you change if ShopLite grew 100 times? Add read replicas for reports, cache hot product pages in Redis, and archive or shard old orders. Orders and payments would stay relational.
Common mistake: Listing table names only. Add the keys, the cardinalities, one index with its reason and one transaction: those show that you made decisions.
Lesson: Practice: DBMS mixed round and SQL drillGroup the applications by student and compare the count of distinct drives with the number of drives: SELECT roll_no FROM application GROUP BY roll_no HAVING COUNT(DISTINCT drive_id) = (SELECT COUNT(*) FROM drive). This pattern is called relational division. COUNT(DISTINCT ...) matters if a student could appear twice for one drive.
Lesson: Practice: DBMS mixed round and SQL drillJoin student to application and use conditional aggregation: SELECT s.branch, SUM(a.status = 'Selected') / COUNT(*) AS rate FROM student s JOIN application a ON a.roll_no = s.roll_no GROUP BY s.branch. In MySQL the comparison gives 1 or 0, so the SUM counts the selected rows. In other databases use SUM(CASE WHEN ... THEN 1 ELSE 0 END).
Lesson: Practice: DBMS mixed round and SQL drillI check three things. Each piece is in the target normal form: for every FD, the left side is a super key. The decomposition is lossless: the common attributes are a key of one piece, so a join gives back the original rows. And every original FD lives inside one piece, so it can be enforced without a join.
Lesson: Practice: DBMS mixed round and SQL drillEach lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson