Loading...
Loading...
Loading...
Core CS and SQL interview guide
Queries, joins, grouping, subqueries, constraints, indexes and design. Every answer is short enough to say aloud, and every question links to the lesson that teaches it.
59 questionsLast updated
I total the sales per product, then in a CTE add ROW_NUMBER() or DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) and keep rank <= 3. I state the tie rule, because it decides which function I use.
I put the next number beside each row with LEAD(invoice_no) OVER (ORDER BY invoice_no) and keep rows where the difference is more than 1. The missing range is from the current number + 1 to the next number − 1. Without window functions, a NOT EXISTS check for invoice_no + 1 finds where each gap starts.
Yes. Window functions run after GROUP BY, so they work on the grouped rows. For example RANK() OVER (ORDER BY SUM(salary) DESC) ranks departments by payroll, and SUM(SUM(salary)) OVER () gives the total of all department payrolls.
When the same query must be reused by other queries, reports, users or applications. A CTE only lives inside one statement; a view is saved in the database and can also be used with GRANT to limit what a user can see.
It is a picture of the design: entities as boxes, relationships as lines, and marks that say how many rows can be on each side. It is cheap to change on paper, and it lets the team and the client check the design before any data depends on it.
Lesson: Entities, attributes and ER diagramsAn entity that cannot be identified on its own, only together with its owner. A hostel room numbered 101 exists in several blocks, so the room is identified by block plus room number; in SQL that is a composite key or a UNIQUE (block_code, room_no).
I ask one question from each side: "one A has how many B?" and "one B has how many A?". One and one is 1:1, many and one is 1:N, and many and many is M:N. Asking only one side is how people miss many-to-many links.
Lesson: One-to-one and one-to-many relationshipsOften I would. I keep them separate when the second part is optional and would be mostly NULL, when it is sensitive and needs different access, or when it is large and rarely read, like a profile photo or KYC documents.
A table that stores a many-to-many relationship, one row per linked pair. It has a foreign key to each side and usually a composite primary key on the pair, like enrollments(student_id, course_id).
order_items store unit_price when products already has a price?Because prices change. unit_price records what the customer paid for that product in that order, so old bills and revenue reports stay correct after a sale or a price rise. It is a fact about the pair, not a duplicate of the current price.
They are problems caused by storing one fact in many rows. An update anomaly leaves copies that disagree after a partial update; an insert anomaly stops you storing a fact without some unrelated fact; a delete anomaly loses a fact when you delete another. In a wide fest sheet: two phone numbers for one coordinator, no way to add an event before a registration, and an event vanishing with its last registration.
Lesson: Anomalies and why we normaliseOnly to disprove them. If HACK appears with two fees, event_code → fee is broken. But rows that agree today prove nothing; the dependency must come from the business rule, so I confirm it with the people who own the data.
A table is in 1NF when every cell holds one atomic value, there are no repeating groups such as phone1, phone2, and every row can be identified by a primary key. It is the base the other normal forms build on.
It depends on how the data is used. If the app never searches or sorts by surname, a full name can be one value. If it lists customers by city or pincode, those parts must be their own columns; atomic means "never needs splitting for this system".
Lesson: First normal form (1NF)1NF: one value per cell, no repeating groups, a key on every table. 2NF: 1NF plus no column depending on only part of a composite key. 3NF: 2NF plus no non-key column depending on another non-key column; in short, every column depends on the key, the whole key and nothing but the key.
Lesson: Second and third normal formpincode → city in a customers table?Create pincodes(pincode PK, city), keep pincode in customers as a foreign key, and drop city from customers. Reports join the two tables, and a city name is stored once per pincode.
It is the right starting point because it keeps data consistent. Sometimes a read-heavy report is too slow with many joins, and then I denormalise a specific column or table on purpose, with a plan to keep the copy correct; the next lesson is about exactly that.
Lesson: Second and third normal formA trigger is SQL that MySQL runs automatically when rows are inserted, updated or deleted. The risk is coverage: an insert trigger does nothing on updates or deletes, and triggers are easy to forget when reading the code, so I document them and keep a check query.
Lesson: When denormalising is reasonableWhen the data changes as often as it is read, when the slow query can be fixed with an index or by removing a join, and early in a project before any real numbers exist. Each copy is extra code and a new way for data to disagree.
Lesson: When denormalising is reasonableorders(order_id, item1, qty1, item2, qty2, item3, qty3). What do you say?It breaks 1NF with a repeating group, limits orders to three items, and makes "how many of item X did we sell" a three-column query. I would create order_items(order_id, product_id, quantity, unit_price) with the pair as the key.
A users table and a follows(follower_id, followee_id, followed_at) join table with both columns referencing users and the pair as the primary key. A CHECK (follower_id <> followee_id) stops self-follows, and counts of followers are a GROUP BY or, if read very often, a stored count kept by a trigger.
It is like the index at the back of a textbook: a sorted list of words with page numbers. Instead of reading the whole book, you find the word in the sorted list and turn straight to the page. A database index keeps column values sorted with a pointer to each row.
Lesson: Full table scan versus index lookupMySQL keeps recently used data pages in memory, in the InnoDB buffer pool. The first run may read from disk; the second finds the data in memory. That is why I never judge an index by a single timing.
Lesson: Full table scan versus index lookupInnoDB uses B+trees. A binary tree has only two children per node, so a crore keys need about 24 levels, and each level could be a disk read. A B-tree page holds hundreds of keys, so the same data fits in 3 or 4 levels.
Lesson: How a B-tree index worksUsually not as the clustered key. A random UUID inserts into the middle of the tree, splits pages, and is wide, which also makes every secondary index bigger. I prefer an AUTO_INCREMENT key and keep the UUID in a separate unique column when the app needs a public id.
I put the columns compared with = first and the range column last, because after a range the remaining columns cannot narrow the search. Then I check which other queries share the leftmost columns, so one index can serve several of them.
Yes. NULL means unknown, and two unknowns are not considered equal, so a unique index accepts many NULLs. If the value is required, I add NOT NULL as well.
It shows the optimizer's plan: for each table, how it will be read, which index is used and roughly how many rows will be examined. Plain EXPLAIN does not run the statement, so it is safe even on a DELETE; EXPLAIN ANALYZE is the version that runs it.
Both read everything, but index reads every entry of an index while ALL reads every row of the table. index is often cheaper because an index is smaller, especially when Extra says Using index, but it is still a full scan.
It runs the query and shows, for every step of the plan, the real time, rows and loops next to the estimates. I use EXPLAIN first because it is instant; when the plan looks fine but the query is still slow, or I need proof of a speed-up, I use EXPLAIN ANALYZE.
It recalculates the index statistics InnoDB keeps for a table, such as how many distinct values each index has. With UPDATE HISTOGRAM ON col it also builds a histogram for a column, which helps estimates on non-indexed columns.
It runs the query for real, so it costs as much as the query itself, and it should only be used on reads. On a busy server I run it on a copy of the data, or at a quiet time, and never on a query I would not run anyway.
Lesson: EXPLAIN ANALYZE: real timingsI use a half-open range: col >= '2026-03-01' AND col < '2026-03-02'. It keeps the column bare, includes every time on that day, and avoids guessing the last second such as 23:59:59.
It is an index that contains every column a query needs, so MySQL never reads the table rows; EXPLAIN shows Using index. I create one for a frequent, important query, often a report over many rows, by adding its selected columns after the filter columns.
No. Only indexes that contain a changed column are touched. Updating a column that is in no secondary index changes just the row; changing the primary key is the expensive case, because every secondary index stores it.
Lesson: The cost of indexes on writesLoad the data first and create the secondary indexes afterwards: building an index once from all rows is faster than updating it row by row. Inserting in primary key order also helps, because rows are appended at the end of the clustered index.
Lesson: The cost of indexes on writes(a, b) serves the filter: equality, then the range. The sort on c will still need a filesort, because after a range on b the rows are not in c order. If the filter keeps few rows, that sort is cheap; if ORDER BY c LIMIT n matters more, (a, c) could be better, and I would compare both plans.
There is no fixed number; as few as cover the important queries. A read-heavy reporting table can afford more, a table with heavy inserts should have only what its queries need. I prefer a few composite indexes that each serve several queries, and I remove redundant ones.
Lesson: Practice: indexes and query plansWith autocommit on, which is the MySQL default, every statement is committed as soon as it finishes. START TRANSACTION pauses it for one transaction, and SET autocommit = 0 turns it off for the session, so changes wait for an explicit COMMIT.
No. DDL statements cause an implicit commit: MySQL commits the open transaction before running them, and the DDL itself cannot be undone with ROLLBACK. So I keep structure changes out of data transactions. Some databases, such as PostgreSQL, do allow DDL inside a transaction.
Lesson: START TRANSACTION, COMMIT and ROLLBACKUsually not. MySQL undoes only the failed statement and leaves the transaction open with the earlier changes pending. The application must catch the error and send ROLLBACK. Deadlocks are an exception: InnoDB rolls back the whole victim transaction.
Lesson: ACID with a bank transferNot for MySQL: it simply reports 0 rows affected. For the transfer it is a failure, for example a wrong account number, so I check the affected row count of each statement and roll back if it is not what I expect.
Lesson: ACID with a bank transferDirty reads (reading uncommitted changes), non-repeatable reads (the same row changes between two reads), phantom reads (the same search returns new rows) and lost updates (one write silently overwrites another). Isolation levels are defined by which of the read anomalies they allow.
Lesson: Dirty, non-repeatable and phantom readsIn development usually one person uses the app, so transactions never overlap. The bug needs two requests at almost the same moment, such as a sale opening, which happens only under real load. That is why I reproduce them by hand with two sessions.
Lesson: Dirty, non-repeatable and phantom readsREAD UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, from weakest to strongest. InnoDB uses REPEATABLE READ by default, while PostgreSQL, Oracle and SQL Server default to READ COMMITTED.
Lesson: Isolation levels in MySQLWhen a short transaction must behave as if it ran alone and I cannot easily lock the exact rows myself. It costs concurrency: plain reads take shared locks, writers wait, and deadlocks become common, so the application must be ready to retry.
Lesson: Isolation levels in MySQLIt reads rows and takes exclusive row locks on them until the transaction ends, so no other transaction can lock or change them meanwhile. I use it when I must read a value, decide something in the application, and then write, for example checking a seat or a balance before changing it.
Lesson: Row locks with SELECT ... FOR UPDATEBoth are MySQL 8.0 options for locking reads. NOWAIT returns error 3572 at once instead of waiting for a lock, which is good for "this seat is being booked" messages. SKIP LOCKED skips locked rows, which lets many workers take different jobs from one queue table without waiting for each other.
Lesson: Row locks with SELECT ... FOR UPDATEAdd a version INT NOT NULL DEFAULT 1 column. Read the row with its version; save with UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?, passing the version you read. If the affected row count is 0, someone saved first, so the app reloads the data and asks the user to try again.
1213 is a deadlock: there was a cycle, and the whole victim transaction was rolled back at once. 1205 is a lock wait timeout: the statement waited longer than innodb_lock_wait_timeout for a lock, and by default only that statement is rolled back, so the transaction is still open and must be rolled back by the app.
A longer timeout does nothing for deadlocks, which are detected at once. For ordinary waits it only makes users wait longer behind a slow transaction. The real fix is shorter transactions and a consistent lock order.
Lesson: Deadlocks: what they are and how to avoid themREPEATABLE READ in one transaction, started with START TRANSACTION WITH CONSISTENT SNAPSHOT, so every number in the report comes from the same moment. Plain reads do not lock anything, so the report does not block the shop while it runs.
Lesson: Practice: transactions and concurrencyA deadlock (1213) is detected at once and the whole transaction is rolled back, so the app simply retries it. A lock wait timeout (1205) comes after waiting, by default 50 seconds, and only the statement is undone, so the app must roll back first. Both mean transactions are holding locks too long or in conflicting orders.
Lesson: Practice: transactions and concurrencyFor InnoDB tables I use mysqldump --single-transaction. It reads every table from one consistent snapshot inside a transaction, so it does not lock the tables and the backup never contains half of an order.
I restore it into a test database and compare it with the original: row counts per table and a few totals, such as the sum of order values. A backup that has never been restored is untested.
Lesson: Backups with mysqldump and restoringBecause the subquery returns a NULL. x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the second part is unknown, so no row passes. I would rewrite it with NOT EXISTS, or filter the NULLs out of the subquery.
I run EXPLAIN to see whether it scans the whole table, then add an index on the columns in its WHERE, JOIN and ORDER BY, in that order of use. I check the plan again, and I make sure I am not wrapping an indexed column in a function such as YEAR(order_date).
ALWAYS refuses an id you type yourself, so the sequence stays the only source of ids. BY DEFAULT accepts typed ids, which helps when copying data in, but the sequence does not move, so I must setval it afterwards or the next generated id collides.
AUTO_INCREMENT and back-tick quoting are syntax errors; MySQL functions such as DATE_FORMAT and IFNULL do not exist; and DATETIME and TINYINT(1) are not PostgreSQL types. Behaviour also changes quietly: integer division and case-sensitive comparisons.
Each lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson