Preparing your learning space...
100% through Interview Questions tutorials
Every query in all three parts runs against this small employees and departments pair. The data is asymmetric on purpose — Gina works in Finance but there's no Finance department row, and HR exists but nobody works there. That's what makes the join examples in Part 2 actually demonstrate something instead of all returning the same result.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
joined_date DATE
);
INSERT INTO employees (name, department, salary, joined_date) VALUES
('Aisha', 'Engineering', 95000, '2021-03-01'),
('Brian', 'Engineering', 82000, '2022-07-15'),
('Chloe', 'Marketing', 61000, '2020-11-02'),
('David', 'Sales', 73000, '2019-05-20'),
('Elena', 'Sales', 69000, '2023-01-09'),
('Farhan', 'Engineering', 98000, '2018-08-12'),
('Gina', 'Finance', 71000, '2024-02-10'); -- no matching department row, on purpose
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
location VARCHAR(50)
);
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building A'),
('Marketing', 'Building B'),
('Sales', 'Building B'),
('HR', 'Building C'); -- no employees work here, on purpose
The DROP TABLE IF EXISTS lines let you re-run the whole block as many times as you want without ending up with a pile of duplicate rows.
These are the questions that show up in basically every junior interview, and honestly a fair number of senior ones too. The answers are short on purpose: read them, then say them out loud. If you can explain a primary key without staring at the screen, you're already in better shape than half the people who sit down for these.
This is usually the very first question, and it trips people up because the words look almost identical. SQL (Structured Query Language) is a language — a standard way of talking to a database. MySQL is a database management system — a piece of software that actually understands that language.
Think of it like this: SQL is English, and MySQL is one specific person who speaks it. Same language, different speakers. And every speaker has their own accent and quirks — that's why MySQL's flavor of SQL has small differences from PostgreSQL's or SQL Server's. If someone asks "what's the difference between SQL and MySQL?", the answer they want in one line is: SQL is the language, MySQL is the software that runs it.
A DBMS just stores and retrieves data. An RDBMS does that too, but it also stores data in a specific structure — tables with rows and columns — and enforces relationships between those tables using keys.
That "relational" part is the whole point. It's what lets you say "every order belongs to a customer" and have the database actually stop you from attaching an order to a customer that doesn't exist.
MySQL is an RDBMS. (MongoDB is a DBMS — document-style — but it is not relational.) So if the interviewer asks "is MySQL a DBMS or an RDBMS?", the word they want is RDBMS, plus a one-line reason why.
Every table needs a primary key — the column (or combination of columns) that uniquely identifies each row. Two rules: it can't be NULL, and it can't repeat.
A foreign key is a column in one table that points at a primary key in another table. Unlike a primary key, it can be NULL and it can repeat. A customer can place many orders, so order.customer_id shows up over and over again.
CREATE TABLE customers (
id INT PRIMARY KEY, -- primary key
name VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id) -- foreign key
);
Try to insert an order with customer_id = 99 when there's no customer 99, and MySQL rejects it. That's the foreign key doing its job.
Interviewers love throwing these acronyms around. Here's the cheat sheet:
CREATE, ALTER, DROP, TRUNCATE — deals with the structure.SELECT, INSERT, UPDATE, DELETE — deals with the data.GRANT, REVOKE — who's allowed to do what.COMMIT, ROLLBACK, SAVEPOINT — managing transactions.Nine times out of ten, when someone asks this they just want to hear SELECT/INSERT/UPDATE/DELETE under DML and CREATE/ALTER/DROP under DDL. Nail those two and the rest of the acronym is bonus.
SELECT name, salary
FROM employees
WHERE department = 'Engineering' AND salary > 85000;
Two people match: Aisha (95k) and Farhan (98k). Nothing fancy here — they're checking that you can combine conditions with AND and that you remember single quotes around string values. Numbers don't get quotes, strings do.
Classic trap question. WHERE filters rows before any grouping happens. HAVING filters groups after grouping happens. The quick way to remember: you reach for HAVING when the condition mentions an aggregate, like AVG or COUNT.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;
Try replacing HAVING with WHERE AVG(salary) > 70000 and MySQL will just error out — WHERE never gets to see aggregates. And here's the second trap: you can use both in the same query. WHERE throws away rows first, the survivors get grouped, and then HAVING filters the groups.
It removes duplicate rows from the result. Useful when you don't care about the duplicates — you just want the list of unique values.
SELECT DISTINCT department FROM employees;
That returns Engineering, Marketing, Sales, Finance — four rows, even though Engineering appears three times in the table. Without DISTINCT you'd get all seven department entries including the repeats.
NULL means "no value — there never was one." An empty string '' means "there is a value, and the value is nothing." They are not the same thing, and this costs people marks constantly.
The big gotcha: NULL is not equal to anything, including itself. So WHERE name = NULL returns nothing — even for rows where name actually is NULL. You need IS NULL.
-- add a row with a NULL salary to see this in action
INSERT INTO employees (name, department, salary, joined_date)
VALUES ('Hana', 'HR', NULL, '2024-05-01');
SELECT * FROM employees WHERE salary = NULL; -- empty result. nothing at all.
SELECT * FROM employees WHERE salary IS NULL; -- returns Hana
(That INSERT also quietly shows that NULL is what you get when you omit a nullable column — salary has no NOT NULL constraint, so leaving it out is fine.)
Hana will stick around in the table for the rest of Part 1 — that's fine, don't worry about her. The reset block at the start of Part 2 puts the table back to its clean 7 rows.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
ORDER BY sorts, DESC flips it to descending, LIMIT 3 keeps the top three. Toss in that ASC is the default so you can leave it out — a small detail that makes you sound like someone who actually types SQL every day. (Hana's NULL salary sorts last in a descending sort, so she doesn't interfere.)
LIKE does fuzzy matching. % matches any number of characters (including zero), and _ matches exactly one character.
SELECT name FROM employees WHERE name LIKE 'a%'; -- starts with 'a'
SELECT name FROM employees WHERE name LIKE '%an'; -- ends with 'an'
'a%' gives you Aisha. '%an' gives you Brian and Farhan. And one detail worth knowing: LIKE is case-insensitive by default in MySQL, so 'a%' matches both 'Aisha' and a hypothetical 'alex'.
Two friendly shortcuts. IN checks membership in a list; BETWEEN checks a range — and it's inclusive on both ends, which trips up people coming from other languages.
SELECT * FROM employees WHERE department IN ('Engineering', 'Sales');
SELECT * FROM employees WHERE salary BETWEEN 70000 AND 90000;
BETWEEN 70000 AND 90000 is the same as salary >= 70000 AND salary <= 90000. Both ends count.
An interview classic, and the answer fits in three rows:
DELETE FROM employees WHERE department = 'HR'; -- removes rows, keeps the table
TRUNCATE TABLE employees; -- empties everything, keeps the table
DROP TABLE employees; -- the table is gone. forever.
Run the last two on a copy, not on anything you care about. Trust me. (In case you're wondering: if you've added Hana in Q8, that first DELETE removes her — she's the only HR employee.)
COUNT, SUM, AVG, MIN, MAX. Count rows, add them up, average them, grab the smallest and largest.
SELECT COUNT(*) FROM employees; -- number of rows
SELECT SUM(salary) FROM employees; -- total salary bill
SELECT AVG(salary) FROM employees; -- average salary
SELECT MIN(salary), MAX(salary) FROM employees;
Two things interviewers quietly check here. COUNT(*) counts every row, NULLs included, while COUNT(salary) would skip rows where salary is NULL. And AVG/SUM ignore NULLs entirely — so if three people earn 10k and one has NULL, the average is 10k, not 7.5k. Subtle, but the kind of subtlety that separates a memorized answer from a real one.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
One rule you'll be asked about: every column in the SELECT that isn't an aggregate has to appear in the GROUP BY. MySQL is famously lenient here — older versions would let you select a non-grouped column and just grab an arbitrary value — but that leniency is a trap, and current MySQL versions with ONLY_FULL_GROUP_BY enabled (it's on by default now) will error out just like every other database does.
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. A table that puts a few of them to work:
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
sku VARCHAR(20) NOT NULL UNIQUE, -- required, and never repeated
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) CHECK (price >= 0), -- no negative prices
in_stock BOOLEAN DEFAULT TRUE
);
CHECK is a bit of a latecomer to MySQL — it's only actually enforced from version 8.0.16 — but mentioning that shows you've read recent release notes, which is never a bad look in an interview.
The real join coverage is in Part 2, but if a basic interview asks you to explain joins, here's the one-liner that saves you: an INNER JOIN keeps only the rows that match in both tables; a LEFT JOIN keeps every row from the left table and fills the gap with NULL when the right side has no match.
That's the basics done. Once you're comfortable with all of this — and I mean actually comfortable, not just familiar — move on to Part 2. Joins, indexes, normalization... that's where the questions that filter people out live.
These are the questions that separate "I watched a tutorial" from "I can actually build something." A lot of companies hand the basic stuff to a phone screener and keep these for the real interview.
Reset first. If you ran the examples in Part 1, your table is no longer the clean 7 rows — we added Hana, and you may have deleted or updated a few rows along the way. Re-run the setup block below before starting Part 2 so every result below matches what you'll see.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
joined_date DATE
);
INSERT INTO employees (name, department, salary, joined_date) VALUES
('Aisha', 'Engineering', 95000, '2021-03-01'),
('Brian', 'Engineering', 82000, '2022-07-15'),
('Chloe', 'Marketing', 61000, '2020-11-02'),
('David', 'Sales', 73000, '2019-05-20'),
('Elena', 'Sales', 69000, '2023-01-09'),
('Farhan', 'Engineering', 98000, '2018-08-12'),
('Gina', 'Finance', 71000, '2024-02-10');
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
location VARCHAR(50)
);
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building A'),
('Marketing', 'Building B'),
('Sales', 'Building B'),
('HR', 'Building C');
Normalization is the process of organizing a table so you don't store the same fact twice in different places. Each normal form fixes a specific kind of mess:
"Red, Green, Blue" squished into a single cell.(student_id, course_id). A course_name column depends only on course_id, not on the student — so it belongs in a separate courses table.employee_id and also stores department_name right next to it, that name is a fact about the department, not the employee. Move it out.There's BCNF and 4NF and 5NF above all this, but honestly? If an interviewer asks about normalization, 1NF/2NF/3NF with a real example is what they want. The departments table in your setup is a 3NF fix — we moved department info out of the employees table because it wasn't a fact about the employee.
-- the 1NF sin: a comma-separated list crammed into one cell
CREATE TABLE bad_design (
emp_name VARCHAR(50),
skills VARCHAR(100) -- 'sql, python, excel' in one cell. that's the violation.
);
-- the 1NF fix: one row per skill, keyed by the pair
CREATE TABLE employee_skills (
emp_id INT,
skill VARCHAR(30),
PRIMARY KEY (emp_id, skill)
);
An index is a sorted lookup structure — in InnoDB it's a B-tree — that lets MySQL find rows without reading the whole table. Without an index, finding one row in a million-row table means checking all million rows. With an index, it's a handful of tree levels.
The detail that impresses is clustered vs non-clustered. A clustered index is the table — the rows are physically stored in that order. InnoDB always stores a table as a clustered index on its primary key. A secondary (non-clustered) index stores the indexed column values plus a pointer back to the clustered row.
CREATE INDEX idx_emp_department ON employees(department);
-- same thing, written the other way:
ALTER TABLE employees ADD INDEX idx_emp_department (department);
After that, WHERE department = 'Sales' can walk the index instead of scanning every row. The trade-off: every index you add makes INSERT/UPDATE slower, because now that index has to be maintained on every write. Indexes are a read-speed / write-speed bargain, and the interviewer wants to hear you say that.
One table rarely answers everything. Here are the joins you'll get asked about, all on our two tables:
-- INNER JOIN: only rows that match in both tables
SELECT e.name, d.location
FROM employees e
INNER JOIN departments d ON e.department = d.dept_name;
-- LEFT JOIN: every employee, NULL where the department has no matching row
SELECT e.name, d.location
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name;
-- RIGHT JOIN: every department, NULL where no employee works there
SELECT e.name, d.location
FROM employees e
RIGHT JOIN departments d ON e.department = d.dept_name;
-- FULL OUTER JOIN: MySQL doesn't have one. Fake it with a UNION.
SELECT e.name, d.location
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_name
UNION
SELECT e.name, d.location
FROM employees e
RIGHT JOIN departments d ON e.department = d.dept_name;
Here's what each returns with our setup:
The e and d are table aliases — short names so you don't retype employees ten times. You don't strictly need them, but you do need them when both tables have a column with the same name, and an interviewer will see right through you if you can't handle that.
Two more joins to mention so you can say you know they exist:
CREATE TABLE org (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
INSERT INTO org VALUES
(1, 'Aisha', NULL),
(2, 'Brian', 1),
(3, 'Chloe', 1),
(4, 'David', 2);
SELECT e.name AS employee, m.name AS manager
FROM org e
LEFT JOIN org m ON e.manager_id = m.id;
Note the LEFT JOIN here — a manager_id of NULL (Aisha, the boss) still gets a row, just with a NULL manager. Swap it to an INNER JOIN and Aisha disappears, because her manager_id doesn't match anything.
A subquery is a query inside a query. The simple kind — find everyone earning more than the overall average:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
The inner query runs first, produces one number, and the outer query uses it. This kind of scalar subquery is great and cheap. The ones to be careful with are the correlated ones (next question) — they get re-run per row and can get slow on big tables.
JOIN vs subquery is one of those "there's no universal answer" questions, and saying that out loud is actually a good answer. A decent rule of thumb: if you need columns from both tables in the result, use a JOIN — the optimizer can usually do more with it. If you're only checking a condition ("which employees have placed an order?"), a subquery with EXISTS often reads better. Give the rule, give the exception, and you sound like someone who's hit this problem in real code.
A correlated subquery references a column from the outer query, so it gets re-evaluated for every single row the outer query examines. The classic example: employees who earn more than the average of their own department.
SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
Here e.department changes for each employee, so MySQL recomputes the inner average every time. That's why correlated subqueries are slow on big tables — it's essentially N subqueries, not one. On our seven rows it's instant, which is fine. The interviewer wants two things from you: proof you can write it, and a sign you know it's expensive. Don't skip the second part.
Both stack two queries' results vertically. The difference: UNION removes duplicate rows, UNION ALL doesn't. Removing duplicates costs work, so UNION ALL is faster — use it when you know the sets can't overlap, or when you genuinely don't care about duplicates.
SELECT name FROM employees WHERE department = 'Engineering'
UNION
SELECT name FROM employees WHERE salary > 70000;
Engineering gives you Aisha, Brian, Farhan. Over 70k gives you Aisha, Farhan, David, Gina. UNION collapses the overlap, so the result is 5 rows. Swap in UNION ALL and you get 7, duplicates included. Two rules to mention: both queries must return the same number of columns, and the column names come from the first query.
A view is a saved query that you can SELECT from like it's a table. It doesn't store data — it stores the definition, and every time you query the view, MySQL runs the underlying query.
CREATE VIEW engineering_team AS
SELECT name, salary FROM employees WHERE department = 'Engineering';
-- now you just:
SELECT * FROM engineering_team WHERE salary > 90000;
Why people use them: to hide complexity (your app does SELECT * FROM monthly_sales instead of a 50-line monster), to enforce security (grant access to a view that shows only certain columns), and to keep a stable interface even when the underlying tables change. The downside people forget: a view sitting on top of a slow query is just a slow query with a nicer name.
Both are named blocks of SQL you save in the database and call later. The differences that matter:
MAX(), NOW(), something you'd put in a SELECT.CALL, and it doesn't slot into a SELECT.DELIMITER //
CREATE PROCEDURE raise_salary(IN p_id INT, IN p_amount DECIMAL(10,2))
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE id = p_id;
END //
DELIMITER ;
CALL raise_salary(1, 5000);
The DELIMITER // business is just so MySQL doesn't choke on the semicolons inside the procedure. You'll see it in every example and never in real life, because when you call procedures from application code, the driver handles it for you. Worth being able to say that, because it shows you understand what the weird syntax is actually for.
A function must return a value; a procedure doesn't have to. A procedure can return multiple result sets; a function can't. Functions are also allowed inside WHERE clauses; procedures aren't.
Triggers are blocks of SQL that fire automatically when a specific event happens on a table — an INSERT, UPDATE, or DELETE, either before or after it. Common uses: audit logs, keeping a last_updated column current, maintaining a counter.
CREATE TABLE salary_changes (
emp_id INT,
old_salary DECIMAL(10,2),
new_salary DECIMAL(10,2),
changed_at DATETIME
);
CREATE TRIGGER log_salary_change
BEFORE UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_changes (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
-- try it:
UPDATE employees SET salary = 99999 WHERE id = 1;
SELECT * FROM salary_changes; -- the trigger logged it automatically
OLD and NEW are the before and after versions of the row. Triggers are powerful, but they're also invisible — a future dev (or you, six months from now) can burn an hour wondering who changed the data. Name that downside and you'll sound like you have production scars, because you do.
A transaction is a group of operations that either all succeed or all fail. There's no in-between. The classic example is transferring money: take 100 out of account A, put it in account B. If the second statement fails after the first ran, you've just destroyed money. A transaction makes both happen, or neither.
-- create a tiny accounts table first
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10,2)
);
INSERT INTO accounts VALUES (1, 1000), (2, 500);
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- make it permanent
-- or, if something went wrong halfway:
ROLLBACK; -- undo everything since START TRANSACTION
ACID is the acronym for what makes a transaction trustworthy:
Interviewers love "what's ACID?" and silently check you can name all four letters with a sentence each. Tying it back to the money-transfer example is the part that makes them nod.
This is where the "I" in ACID meets the real world. Isolation levels control how much of another transaction's uncommitted work you can see, and there are four of them:
SELECT @@transaction_isolation; -- check your current level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Learn three terms cold, because they drive every follow-up question: dirty read (reading uncommitted data), non-repeatable read (same row, two reads, different values), phantom read (a new row appears between two reads). Memorize those and you'll never be caught flat-footed here.
AUTO_INCREMENT is the auto-numbering column. Insert a row without specifying it and MySQL hands out the next number. The gotcha everyone hits: DELETE rows and the numbers do not come back — AUTO_INCREMENT keeps counting upward. (TRUNCATE is what resets it, remember from Part 1.)
INSERT INTO employees (name, department, salary) VALUES ('Ivan', 'Engineering', 88000);
SELECT LAST_INSERT_ID(); -- the id MySQL just assigned to Ivan
LAST_INSERT_ID() is how you insert a row and immediately grab its new id to insert related rows in another table. And no, MAX(id) is not a safe substitute — two connections inserting at once will both read the wrong MAX. That's a genuinely useful thing to know, and interviewers who work on real systems check for it.
NULLs are a fact of life, so MySQL gives you tools to handle them:
-- COALESCE: returns the first non-NULL value
SELECT name, COALESCE(salary, 0) AS salary_or_zero FROM employees;
-- IFNULL: the two-argument shorthand
SELECT name, IFNULL(salary, 0) FROM employees;
-- CASE: if/else, in SQL
SELECT name,
CASE
WHEN salary > 90000 THEN 'high'
WHEN salary > 70000 THEN 'medium'
ELSE 'low'
END AS bracket
FROM employees;
-- NULLIF: returns NULL when its two arguments are equal
SELECT NULLIF(10, 10); -- NULL
SELECT NULLIF(10, 20); -- 10
The real difference between COALESCE and IFNULL: not much for daily use, but COALESCE takes any number of arguments and is standard SQL — it works on PostgreSQL and SQL Server too. Saying that unprompted is a nice flex.
There's a subtlety to LIKE that people forget: what if you actually want to search for a % or an _? Those are wildcards, so they need escaping.
CREATE TABLE notes (note VARCHAR(100));
INSERT INTO notes (note) VALUES ('discount 50% off'), ('plain text');
-- find rows containing a literal percent sign:
SELECT * FROM notes WHERE note LIKE '%\%%';
Here '%\%%' means: anything, then a literal %, then anything. The backslash is the escape character, so \% means "the character %, not the wildcard." Without the backslash, '%%%' would match every row. This is more of a "do you know it exists" question than a whiteboard one, but it's fun to know, and it impresses.
This comes up constantly. The pattern: GROUP BY the columns that should be unique, count them, and keep only the groups with more than one member.
-- add a deliberate duplicate first
INSERT INTO employees (name, department, salary, joined_date)
VALUES ('David', 'Sales', 73000, '2019-05-20');
SELECT name, department, COUNT(*)
FROM employees
GROUP BY name, department
HAVING COUNT(*) > 1;
GROUP BY collapses the identical rows into one group, then HAVING COUNT(*) > 1 keeps only the groups that had at least two rows. That's the whole trick. To actually remove the extras, the standard move is to keep the MIN(id) per group and delete everything else — interviewers love asking the follow-up, so have that answer ready.
If you're comfortable with Part 2, you can honestly hold your own in most interviews that stop at "database knowledge." Part 3 is where it gets fun — deadlocks, replication, EXPLAIN plans, the stuff people with real MySQL-on-a-big-system experience talk about.
Everything in this part assumes you're comfortable with Parts 1 and 2. These questions are aimed at senior candidates, or at anyone who claims to have run MySQL at some scale — a few million rows, a few hundred requests a second, that kind of thing.
Reset first. Part 2 left the tables in a mess — Aisha got a raise, we added Ivan, then a duplicate David. Re-run the setup below so Part 3's examples match what you'll see.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
joined_date DATE
);
INSERT INTO employees (name, department, salary, joined_date) VALUES
('Aisha', 'Engineering', 95000, '2021-03-01'),
('Brian', 'Engineering', 82000, '2022-07-15'),
('Chloe', 'Marketing', 61000, '2020-11-02'),
('David', 'Sales', 73000, '2019-05-20'),
('Elena', 'Sales', 69000, '2023-01-09'),
('Farhan', 'Engineering', 98000, '2018-08-12'),
('Gina', 'Finance', 71000, '2024-02-10');
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
location VARCHAR(50)
);
INSERT INTO departments (dept_name, location) VALUES
('Engineering', 'Building A'),
('Marketing', 'Building B'),
('Sales', 'Building B'),
('HR', 'Building C');
A good answer has three parts: find the slow queries, look at their execution plan, then fix the obvious stuff — usually a missing index or a full table scan. The two tools are the slow query log and EXPLAIN.
-- see what MySQL actually does with your query
EXPLAIN SELECT name, salary FROM employees WHERE department = 'Sales' AND salary > 70000;
EXPLAIN prints a table describing the execution. The columns interviewers actually care about:
ALL means a full table scan, which is bad. ref or range means it used an index, which is good.NULL means none.Using filesort or Using temporary are warnings that something heavy is happening.The pro move is asking "would an index help?", then actually creating the index and re-running EXPLAIN to watch the rows number drop. Interviewers eat that up — it shows you diagnose instead of guess.
A composite index is an index on multiple columns, like INDEX(department, salary). The gotcha — the leftmost prefix rule — is that such an index can only be used when the leftmost column appears in the filter.
CREATE INDEX idx_dept_salary ON employees(department, salary);
-- these two CAN use idx_dept_salary
SELECT * FROM employees WHERE department = 'Sales';
SELECT * FROM employees WHERE department = 'Sales' AND salary > 70000;
-- this one CANNOT (it skips the leftmost column)
SELECT * FROM employees WHERE salary > 70000;
It's like a phone book sorted by last name then first name. You can find "Smith, John" fast, but you can't use the ordering to find everyone named "John". So when you design a composite index, put the most selective column first — or at least the one you filter on most often.
Related term worth having ready: a covering index. If the index contains every column the query needs, MySQL can answer the query entirely from the index without touching the table's rows at all. That's the fastest kind of query that exists.
Because every INSERT, UPDATE, and DELETE now has to update every relevant index as well. The table data lives in one place, but each index is a separate structure that all have to be kept in sync. Ten indexes on a table means ten extra things to update on every write.
That trade is worth making for a read-heavy app — and most apps are read-heavy. But on a table that gets hammered with writes, you trim the index count to the essentials. The interviewer wants to hear that you understand indexes are a bargain, not a free win.
Partitioning splits one logical table into physical chunks based on a rule you define — usually a range of dates or a hash of some column. Your queries still reference the same table name, but MySQL only touches the partitions that could contain the answer. That's called partition pruning.
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024)
);
Notice the primary key had to include order_date. Every unique key in a partitioned table must contain the partition column — that one constraint alone trips people up. Partitioning shines for "old data we never touch": you can drop an entire partition instead of running a massive DELETE. But it is not a magic performance button, and saying "a lot of people partition and wonder why nothing got faster" is the kind of honesty that reads as real experience.
Replication means running several MySQL servers that keep copies of the same data. The classic setup: one primary (master) that handles writes, and one or more replicas (slaves) that receive a copy of every change and handle reads. That's the read/write split.
How it actually works: every change to the primary is written to the binary log (binlog). Each replica connects to the primary, reads the binlog, and replays the changes. It's asynchronous by default — a replica is always a little behind, never a real-time mirror.
Why people run it:
Commands you might get asked about:
SHOW MASTER STATUS; -- on the primary: current binlog position
SHOW SLAVE STATUS; -- on a replica: is it running, how far behind
-- (in MySQL 8.0.22+, that second one is SHOW REPLICA STATUS)
Saying "the replica is eventually consistent, not real-time" is the kind of nuance that separates people who read about replication from people who've actually operated it.
People mix these up constantly. Partitioning splits a table inside one server. Sharding spreads whole chunks of data across multiple servers entirely — each shard is its own database on its own machine, sometimes its own data center.
-- sharding usually lives in app code or a proxy, but the split is often
-- something natural, like a modulo of the key:
SELECT * FROM users WHERE user_id % 4 = 0; -- hypothetical shard 0 of 4
The hard part of sharding isn't the setup — it's the questions. What happens to queries that need data from every shard? How do you join across shards? Auto-increment ids collide between shards unless you make them global. Scaling up (a bigger server) vs scaling out (more servers) is the mental model; anyone who sharded a project that didn't need it has a scar from it, and admitting that wins points.
Locks are how MySQL stops concurrent transactions from tripping over each other.
Locks also come in two flavors:
The one-liner to say out loud: readers can read alongside readers, but a writer blocks everyone. And MySQL decides which locks to take automatically from your statements — you don't normally issue LOCK TABLES yourself. Saying that is a good sign you actually work with it, not just read about it.
-- if you ever do need explicit locking:
LOCK TABLES employees WRITE;
-- ...do your thing...
UNLOCK TABLES;
A deadlock is when two transactions each hold a lock the other needs, and neither can move forward:
InnoDB detects this and kills one of them with an error like "Deadlock found when trying to get lock; try restarting transaction." The loser gets rolled back automatically.
SHOW ENGINE INNODB STATUS; -- details of the last deadlock live here,
-- under "LATEST DETECTED DEADLOCK"
How to avoid them: keep transactions short (fewer locks held for less time), access rows in a consistent order (always A then B, never B then A), and be ready to catch the deadlock error and retry the transaction. The "touch tables in the same order" one is the fix people actually use in production — mention it and you're past the textbook answer.
MyISAM is the old default storage engine; InnoDB replaced it. The differences are basically a checklist of reasons MyISAM is legacy:
SHOW ENGINES; -- supported engines and their status
SHOW TABLE STATUS WHERE Name = 'employees'; -- the engine a specific table uses
Interview version of the answer: "InnoDB, always. It's the default, it does transactions, row locking, and foreign keys. MyISAM only makes sense for some read-only niche." Nobody in the room is rooting for MyISAM.
MVCC stands for Multi-Version Concurrency Control, and it's the trick that lets InnoDB have a reader and a writer on the same row at the same time without them blocking each other. Instead of locking the row so the reader has to wait, InnoDB keeps multiple versions of each row around — old committed versions in the undo log, plus the current one.
When a transaction starts, it gets a snapshot. Every row carries hidden version info (which transaction created it, which deleted it), and a plain SELECT just reads the version that was current when the snapshot was taken. Meanwhile a writer happily updates the current version. The reader never blocks the writer, and the writer never blocks the reader.
That's also why REPEATABLE READ works in InnoDB: the snapshot is fixed when the transaction starts, so no matter how many times you read the same row, you see the same data — the changes belong to newer versions your snapshot can't see. This is deep water, but being able to say "consistent reads are based on a snapshot, not locks" in one sentence makes you sound like you actually read the InnoDB docs.
In classic database theory, REPEATABLE READ allows phantom reads — another transaction inserts a new row that matches your query, and it pops into view mid-transaction. But InnoDB, at its default REPEATABLE READ, prevents them using gap locks (part of what's called next-key locking).
A next-key lock combines a record lock with a gap lock on the empty space around the row. It locks not just existing rows but the gaps between them, so another transaction literally cannot insert a new row into a range you're reading. That makes InnoDB's REPEATABLE READ behave more like SERIALIZABLE when it comes to phantoms — without actually serializing everything.
The nuanced answer that lands well: "technically REPEATABLE READ allows phantoms, but InnoDB's implementation closes that gap with next-key locks." Then name SERIALIZABLE as the fully-serialized level, and mention the gotcha: if your query doesn't use an index, InnoDB may take gap locks over huge ranges — which tanks concurrency.
The buffer pool is InnoDB's in-memory cache of data pages. When you read a row, InnoDB loads the page containing it into memory; the next read serves it from memory, which is orders of magnitude faster than touching disk. Writes get buffered too and flushed to disk in batches.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- how much RAM InnoDB is allowed to use
Sizing this is one of the most common real-world tuning moves. Too small, and everything hits disk and you're I/O-bound. Too big, and you starve the operating system of memory. The rule of thumb that gets nods: the data you touch most often should fit in the buffer pool. If your hot data fits, reads are fast; if it doesn't, no amount of SQL optimization saves you.
The two families:
mysqldump) — dumps the data as SQL statements: CREATE TABLE, INSERT... You can read it in a text editor, it's portable across MySQL versions and even to other databases, and it's what you use for small-to-medium data.mysqldump -u root -p interview_db > backup.sql
The part everyone skips: point-in-time recovery. A logical backup restores you to the moment the dump was taken. Everything after that lives in the binary log. So the pro workflow is: restore the backup, then replay the binlog up to the exact second before the disaster — which is why you have to back up the binlog too.
And the most important sentence in any interview answer about backups: "I always test restores." A backup you've never restored is a guess. That line lands, because everyone in the room knows a horror story about the untested backup.
SQL injection is what happens when user input gets pasted straight into a query and changes its meaning. The classic login-form example: the user types something that turns your query into "always true."
-- the attacker's payload
SET @payload = "x' OR '1'='1";
-- a vulnerable app builds the query by concatenating the input into it:
SET @bad_sql = CONCAT("SELECT * FROM employees WHERE name = '", @payload, "'");
PREPARE stmt FROM @bad_sql;
EXECUTE stmt; -- every row comes back. the OR hijacked the WHERE.
DEALLOCATE PREPARE stmt;
-- the safe version: the input is a parameter, not part of the SQL
PREPARE safe_stmt FROM 'SELECT * FROM employees WHERE name = ?';
SET @safe = "Aisha";
EXECUTE safe_stmt USING @safe;
DEALLOCATE PREPARE safe_stmt;
Look at what the payload did: name = 'x' OR '1'='1' — the '1'='1' is always true, so the whole WHERE becomes true for every row. The fix is the prepared statement: the input is sent separately from the query, so the database treats it as data, never as SQL. In application code that means using your language's parameterized queries (PDO::prepare, mysqli prepare, etc.) instead of string concatenation.
Second line of defense: least privilege — the app's database user shouldn't have DROP or DELETE if it doesn't need them. And worth saying out loud: escaping user input is a band-aid; parameterized queries are the actual fix.
Window functions compute a value across a set of rows related to the current row, without collapsing them into groups the way GROUP BY does. The classic interview question they power is "find the second-highest salary."
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;
The difference between the three:
And the second-highest-salary query, the one everyone memorizes:
SELECT name, salary
FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;
You need DENSE_RANK here (or RANK with a DISTINCT later) because two people can tie for highest — with ROW_NUMBER, the #2 row might actually be someone who tied for #1. One extra detail to volunteer: these need MySQL 8.0+. Saying that unprompted shows you've been burned by a 5.7 server before.
A Common Table Expression is a named subquery you define once at the top and reference below. It turns long, nested queries into something that reads top to bottom.
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;
Same result as a subquery, but the query reads like a story instead of inside-out. Two details interviewers like: CTEs can be recursive — the canonical way to walk a tree like an org chart in MySQL 8 — and unlike a derived table in FROM, a CTE can be referenced multiple times in the same query. That second point is usually the "aha" they're fishing for.
MySQL's JSON type stores a JSON document as a real, validated column with JSON functions to work on it. It's tempting to use it for everything because it feels flexible. It's right for documents whose schema changes often, or genuinely nested data that would be painful to normalize into tables. It's wrong for anything you'll filter, sort, or join on a lot — a JSON column isn't indexed like a normal column, and reaching inside it is slower than a real indexed column.
CREATE TABLE user_profiles (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
profile JSON
);
INSERT INTO user_profiles (name, profile) VALUES
('Aisha', '{"age": 30, "city": "Dubai", "skills": ["sql", "python"]}'),
('Brian', '{"age": 27, "city": "Karachi"}');
SELECT name, JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) AS city FROM user_profiles;
-- or the shorthand you'll actually see in code:
SELECT name, profile->>'$.city' AS city FROM user_profiles;
The ->> operator (that's the one with the extra > that also unquotes) is the form you'll see in real codebases. A good one-line answer: "JSON is for flexibility, relational is for integrity — the more you filter a field, the more it should be a real column."
That's the full set — 48 questions across three levels. If you got through all of it and actually ran the queries rather than just skimmed them, you're in better shape than most candidates walking into these interviews. Good luck.
Save your progress and earn XP for completing tutorials.
Technology
MySQL
Lesson group
Interview Questions
Progress
100% complete