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.
60 questionsLast updated
Files have no protection when many users write at once, so updates get lost. A crash can leave half-written data, nothing enforces rules such as unique emails, and searches read the whole file. A DBMS solves all four.
Lesson: Why applications use a databaseAn index. Like the index at the back of a book, it tells the DBMS where the matching rows are, so it does not have to read every row.
Lesson: Why applications use a databaseNo. NULL means the value is unknown or missing. 0 is a real number and '' is a real, empty text; both are known values.
customer_id instead of the customer's name and city?So each fact is stored once. If the customer moves city, one row in customers changes and every order still points to the right, updated data.
TRUNCATE DDL or DML? Why does it matter?TRUNCATE is DDL in MySQL: it empties a table by recreating it rather than deleting row by row. Because of that it cannot be undone with ROLLBACK, unlike DELETE. The Changing Data module compares the two.
It is free (Community Edition), fast for typical web workloads, easy to install and run, and very widely known, so hiring and help are easy. All major cloud providers also offer it as a managed service.
Lesson: SQL, and why we start with MySQLError 1045 (Access denied) comes from the server, so the server is running and the user or password is wrong. Error 2003 (Can't connect) means no server answered on that host and port, so it is stopped or on another port.
Lesson: Install MySQL Server and MySQL WorkbenchIt ends a statement and sends it to the server. Without it the client shows -> and keeps waiting, so one statement can span many lines.
/ and DIV in MySQL?/ always gives a decimal result (10 / 4 is 2.5000). DIV gives only the whole-number part (10 DIV 4 is 2).
Every user or program gets only the permissions it needs to do its job, and nothing more. For a typical app user that is SELECT, INSERT, UPDATE and DELETE on its own database, without DROP or ALTER.
'user'@'host' do?It says where the user may connect from. 'localhost' allows only the same machine, '%' allows any machine, and a specific address allows just that one. 'app'@'localhost' and 'app'@'%' are two different users.
orders not store the product directly, but use a separate order_items table?One order can contain many products, and one product appears in many orders. order_items holds one row per product per order, with its quantity and price, so each order can have any number of lines.
SOURCE and a SQL statement?SOURCE is a command of the mysql client program, not SQL: the client reads the file and sends each statement inside it to the server. That is why it works in the client but not, for example, from application code.
DESCRIBE and SHOW CREATE TABLE?DESCRIBE gives a short grid of columns with types, NULL, key markers and defaults. SHOW CREATE TABLE gives the complete definition, including named foreign keys, CHECK rules, the engine and the character set.
The wrong database is worse, because nothing fails: you silently read or change the wrong data. That is why it is worth checking SELECT DATABASE(); and connecting straight into the right database.
It is gone for good, with the structure. There is no undo in the client, so the only way back is a backup. On real databases I check the table name and the selected database before running DROP TABLE.
The type tells the database what values are allowed and how to store and compare them. A number column rejects text, a date column rejects "31-02-2025", and amounts can be summed exactly. That check happens for every app that writes to the table, not just one.
Lesson: CREATE TABLE and DROP TABLEINT holds about 2.1 billion values (4.29 billion as UNSIGNED), which is plenty for most tables. I use BIGINT for tables that can really grow past that, such as payment events or log rows in a large app. Changing the type later on a huge table is slow, so I decide by the long-term size.
It stops negative values from being stored, but it has a side effect: subtracting two UNSIGNED values that would go below zero is an error (1690), even inside a SELECT. Many teams keep a signed INT and add a CHECK (stock >= 0) rule, which makes the intent clear.
For long free text such as reviews, descriptions or notes, where I cannot set a sensible limit. A row can hold at most 65,535 bytes across its columns, so very large VARCHARs hit that limit; TEXT is stored mostly outside the row. For short values that I search, sort or show in lists, I keep VARCHAR.
Lesson: Text types: VARCHAR, TEXT, CHAR and ENUMNo, 50 characters. With utf8mb4 a character can take 1 to 4 bytes, so the column may use up to 200 bytes plus the length prefix, but it still holds 50 Hindi letters or emoji.
Lesson: Text types: VARCHAR, TEXT, CHAR and ENUMOnly as a name: BOOLEAN and BOOL mean TINYINT(1). TRUE is stored as 1 and FALSE as 0, and the column can still hold other small numbers. I store only 0 and 1 and name such columns is_... or has_....
Give the column a default: created_at DATETIME DEFAULT CURRENT_TIMESTAMP (or TIMESTAMP). Inserts that leave the column out get the server's current time. Then every app that writes to the table gets the same behaviour.
Two requests can read the same MAX at the same moment and both use the same new id, so one insert fails or, worse, rows get mixed up. AUTO_INCREMENT hands out each number exactly once inside the database, which avoids this race.
Lesson: Auto-generated ids with AUTO_INCREMENTNo. DELETE FROM t removes the rows but the counter continues where it was. Only TRUNCATE TABLE, or dropping and recreating the table, starts again from 1.
Yes, if every stored value fits. If any value is longer, strict mode refuses the change with a "data truncated" error and nothing is changed. I first find and fix the long values, then shrink the column.
Lesson: Change a table with ALTER TABLECheck the current definition with SHOW CREATE TABLE, test the exact ALTER on a copy with realistic data, take a backup, and run it at a quiet time, combining related changes into one ALTER so the table is rebuilt once. Afterwards I verify with DESCRIBE and a few SELECTs.
Lesson: Change a table with ALTER TABLEInnoDB supports transactions, crash recovery, foreign keys and row-level locking, and it is the default since MySQL 5.5. MyISAM has none of these: a crash can damage tables, and it ignores foreign keys silently. I would only meet MyISAM in an old system, and I would plan to convert it.
Lesson: Naming, storage engines and character setsFLOAT for money gives totals that are off by paise, INT for mobile numbers fails for most numbers above 2,147,483,647, and dates stored as text sort in the wrong order and accept impossible dates. All three look fine in a quick test and fail with real data.
Lesson: Practice: tables and data typesThe dates are stored as text in day-first form, so they sort by the day number instead of by calendar order. The fix is a DATE column with values in year-first form; existing rows have to be converted first, because a direct MODIFY to DATE fails with error 1292.
Lesson: Practice: tables and data typesA natural key is a real-world value such as an email, a PAN or a train number. A surrogate key is a number the database generates only to name the row, like an AUTO_INCREMENT id. I prefer surrogate keys because they never change and never carry private data.
Emails change, and a key is copied into other tables such as orders, so every copy would have to change too. I would use a surrogate customer_id as the key and keep email as a normal column that must be unique.
Only when "unknown" or "not given" is a real, expected answer, like an optional phone number or a delivery date for an order not yet delivered. Every column a row cannot make sense without should be NOT NULL.
Lesson: NOT NULL and DEFAULTStrict mode is STRICT_TRANS_TABLES in sql_mode. With it on, MySQL rejects missing or invalid values with an error. With it off, MySQL stores made-up values like 0 or an empty string and only gives a warning that applications rarely check, so bad data gets in silently.
Both stop repeated values. A table has only one primary key and its columns can never be NULL; a table can have many UNIQUE constraints, and a UNIQUE column may hold NULLs. The primary key is the row's main identity that other tables point to.
Lesson: UNIQUE and CHECK constraintsCHECK (price > 0) accept a NULL price?A CHECK fails only when the rule is false. With a NULL price, price > 0 is unknown, not false, so the row passes. If the price is required I also make the column NOT NULL.
It is a column in a child table whose values must exist in the primary key (or a unique key) of a parent table. It keeps the link between the tables valid, so no row can point at something that does not exist.
Lesson: Foreign keys: linking two tablesThat is error 1452: the value I am inserting or updating does not exist in the parent table. I check that the parent row was inserted first and that I am using the right id, and the message tells me which constraint and columns are involved.
Lesson: Foreign keys: linking two tablesIt turns foreign key checks off for the session, which reset and import scripts use to drop or load tables in any order. While it is off, orphan rows can get in, and turning it back on does not re-check them, so I use it only in such scripts and switch it on again immediately.
Lesson: Foreign keys: linking two tablesWhen the child still makes sense without the parent, such as a product whose category is removed or a ticket whose assigned agent leaves. The foreign key column must allow NULL, otherwise MySQL refuses the rule with error 1830.
Lesson: ON DELETE: CASCADE, RESTRICT and SET NULLA surrogate key like customer_id never changes, so there is nothing to cascade. It matters for natural keys that can change, such as a station code or a product code, where it updates every child automatically.
MySQL checks existing rows, so the ALTER fails. I first find the bad rows with a SELECT, agree on the right values and fix them with UPDATE (or delete true junk), and then add the constraint so it protects all new rows.
Lesson: Practice: keys and constraintsWith a CHECK that compares two columns: CHECK (from_acc <> to_acc), named like chk_transfers_self. Both columns are also NOT NULL foreign keys to the accounts table.
I leave the id column out of the column list and MySQL assigns the next number. Writing NULL or DEFAULT for it does the same. I only give an id myself when loading fixed data, such as a practice script.
It copies the rows a query returns into another table in one step, for example archiving old orders, building a restock list or saving a copy of rows before a risky change. The SELECT must return the same number of columns, in the same order, as the column list.
If I was inside a transaction, I run ROLLBACK before committing. Otherwise MySQL has already saved it, so I restore the old values from a backup or from a copy of the table. To prevent it, I check with a SELECT first and use safe update mode.
The expression is worked out inside the database from the value that is there at that moment. If I read 10, compute 9 and write 9, another sale made in between is lost. The Transactions module covers this kind of race in depth.
Lesson: UPDATE with a WHERE clauseDELETE removes some or all rows, row by row, can use WHERE and can be rolled back. TRUNCATE removes all rows at once by re-creating the table, restarts AUTO_INCREMENT and is committed immediately. DROP removes the rows and the table itself.
Another table, orders, has a foreign key that points to customers, and InnoDB never truncates a referenced table. If I really need it empty, I use DELETE FROM customers after removing the child rows; usually I should not empty it at all.
I call LAST_INSERT_ID() straight after the INSERT, on the same connection. In application code, the database library returns the same value as the generated key of the insert.
The id of the first of the five rows. For a simple multi-row INSERT, MySQL gives the rows consecutive ids, so the last one is the first id plus 4.
Records is the number of rows sent, Duplicates counts rows that hit an existing key (it matters with options such as IGNORE), and Warnings counts problems MySQL adjusted for. If warnings are above 0, I run SHOW WARNINGS at once.
1 means a new row was inserted, 2 means an existing row was updated, and 0 means the existing row already had the same values. For a multi-row upsert the counts are added together.
Lesson: Upserts with ON DUPLICATE KEY UPDATEON DUPLICATE KEY UPDATE changes the existing row in place and keeps every column I do not mention. REPLACE deletes the old row and inserts a new one, so other columns go back to defaults, the id can change, child rows may block it or be cascaded away, and it counts 2 affected rows.
Not necessarily. MySQL counts only rows whose values really changed, so two of the five already had the new values. The client shows it as Rows matched: 5 Changed: 3.
INSERT ... AS new ON DUPLICATE KEY UPDATE with only the columns the file owns, such as price. It inserts new products and updates existing ones in one statement, without the race of a separate check and without the data loss of REPLACE or the hidden errors of INSERT IGNORE.
It reads columns the screen does not need, which wastes time and network, and it can expose private columns. Its shape also changes when someone adds a column, which can break code that expects a fixed set of columns. Naming the columns keeps the result small and stable.
Lesson: SELECT columns and aliasesThere is a missing comma: SELECT name price reads price as an alias for name, because AS is optional. MySQL gives no error, so it is easy to miss. Writing AS every time makes the mistake stand out.
No. MySQL works out WHERE before it builds the SELECT list, so the alias does not exist yet, and you get Unknown column ... in 'where clause'. Repeat the expression in WHERE instead, for example WHERE salary * 12 > 1500000.
I use a half-open range: order_date >= '2025-04-01' AND order_date < '2025-05-01'. It includes every day of April, works for any month length and does not wrap the column in a function. Dates are always written year first.
Each lesson has quizzes, practice and more interview questions on the same topic.
Open the first lesson