SQL Interview Questions and Answers (MySQL)

SQL (Structured Query Language) is the standard language for defining, querying and changing data in relational databases, while MySQL is a database management system that understands SQL.

Think of SQL as the language and MySQL as one product that speaks it. PostgreSQL, Oracle Database, SQL Server and SQLite also use SQL, but each adds its own extensions and small differences. For example, MySQL uses LIMIT for pagination, AUTO_INCREMENT for generated ids and backticks for quoting identifiers, while SQL Server uses TOP and IDENTITY. Core statements like SELECT, INSERT, UPDATE, DELETE, joins and GROUP BY work almost the same everywhere.

Interview tip: A common follow-up is naming a few MySQL-specific features, so keep LIMIT, AUTO_INCREMENT and storage engines such as InnoDB ready.

SQL commands are grouped by what they act on: structure (DDL), data (DML), permissions (DCL) and transactions (TCL).

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE, RENAME. They change the schema.
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE. Many books also put SELECT here; others call it DQL (Data Query Language).
• DCL (Data Control Language): GRANT and REVOKE for user privileges.
• TCL (Transaction Control Language): START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.

In MySQL, DDL statements cause an implicit commit: they end the current transaction, and their own changes cannot be rolled back.

Both prevent duplicate values, but a table can have only one primary key and it can never be NULL, while a table can have many unique keys and they accept NULL.

• Primary key: identifies each row and implies NOT NULL. In MySQL's InnoDB engine the table data is physically organised by the primary key (the clustered index).
• Unique key: enforces uniqueness on other columns such as email or PAN number. In MySQL a unique column can hold multiple NULLs, because one NULL is never equal to another.
• Both automatically create an index.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone VARCHAR(15) UNIQUE
);

Interview tip: Expect "can a unique key be NULL?" as the follow-up: in MySQL yes, and several rows can have NULL in that column.

A foreign key is a column (or set of columns) in one table whose values must match a primary key or unique key value in another table, which keeps references between tables valid.

It enforces referential integrity: you cannot insert an order for a customer that does not exist, and you cannot delete a customer who still has orders unless you say what should happen. ON DELETE and ON UPDATE choose the behaviour: RESTRICT or NO ACTION (block the change, the default), CASCADE (delete or update the child rows too) or SET NULL. In MySQL, foreign keys are enforced by the InnoDB engine; MyISAM tables do not enforce them.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
);

DELETE removes selected rows, TRUNCATE removes all rows but keeps the table, and DROP removes the table itself along with its data, indexes and triggers.

• DELETE is DML. It supports WHERE, removes rows one at a time, fires DELETE triggers and can be rolled back inside a transaction. It does not reset the AUTO_INCREMENT counter.
• TRUNCATE is DDL in MySQL. It drops and re-creates the table internally, so it is much faster on big tables. It resets AUTO_INCREMENT, does not fire triggers and causes an implicit commit, so it cannot be rolled back.
• DROP is DDL. The table definition is gone; you would need CREATE TABLE again.

In MySQL, TRUNCATE also fails on an InnoDB table that is referenced by a foreign key from another table.

Interview tip: The usual follow-up is "which one can be rolled back?": only DELETE, and only while its transaction is not yet committed.

WHERE filters individual rows before grouping, while HAVING filters groups after GROUP BY has run, which is why only HAVING can use aggregate results like COUNT(*) or SUM(salary).

SELECT dept_id, COUNT(*) AS staff
FROM employees
WHERE salary > 30000       -- row filter
GROUP BY dept_id
HAVING COUNT(*) >= 5;      -- group filter

This returns departments that have at least five employees earning above 30,000. Put a condition in WHERE whenever it does not need an aggregate: rows removed early mean less data to group, and WHERE conditions can use indexes. In MySQL, HAVING may also refer to an alias from the SELECT list, such as HAVING staff >= 5.

A join combines rows from two tables using a related column; the join type decides what happens to rows that have no match.

• INNER JOIN: only rows that match in both tables.
• LEFT JOIN: all rows from the left table, with NULLs where the right table has no match.
• RIGHT JOIN: all rows from the right table, with NULLs on the left side.
• FULL OUTER JOIN: all rows from both sides. MySQL does not support it directly.
• CROSS JOIN: every left row paired with every right row (Cartesian product).

-- employees: Asha (dept 10), Ravi (dept 20), Neha (no dept)
-- departments: 10 Sales, 20 IT, 30 HR
SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- rows: Asha Sales | Ravi IT | Neha NULL

With INNER JOIN the same query returns only Asha and Ravi. With RIGHT JOIN it returns Asha, Ravi and a row with a NULL employee for HR. A CROSS JOIN of these tables returns 3 x 3 = 9 rows.

CHAR(n) is fixed length and VARCHAR(n) is variable length; in both, n is the maximum number of characters, not bytes.

• CHAR(10) always reserves room for 10 characters. In MySQL, shorter values are padded with spaces when stored and trailing spaces are removed when read back, so a trailing space you inserted is lost. The maximum length is 255.
• VARCHAR(10) stores only the characters you give plus a 1 or 2 byte length prefix, and keeps trailing spaces. It can be declared much longer, limited by MySQL's 65,535-byte maximum row size.

Use CHAR for values that always have the same length, such as CHAR(2) country codes or fixed-format codes, and VARCHAR for names, emails and addresses where the length varies.

GROUP BY collapses rows that share the same value into one row per group, and aggregate functions such as COUNT, SUM, AVG, MIN and MAX compute one value for each group.

SELECT dept_id, COUNT(*) AS staff, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

• COUNT(*) counts rows; COUNT(col) counts only non-NULL values of col; COUNT(DISTINCT col) counts unique non-NULL values.
• SUM, AVG, MIN and MAX ignore NULLs.
• Every column in the SELECT list must be grouped or aggregated. MySQL 8 enforces this by default through the ONLY_FULL_GROUP_BY SQL mode, except for columns that are functionally dependent on the grouped columns (for example, when you group by the primary key).

Interview tip: A favourite trap: AVG(bonus) skips NULL bonuses, so it is not the same as SUM(bonus) / COUNT(*).

ORDER BY sorts the result (ascending by default, DESC for descending) and LIMIT restricts how many rows come back; together they give you top-N lists and pagination.

-- 3 highest-paid employees
SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC
LIMIT 3;

-- page 3 with 10 rows per page (skip 20 rows)
SELECT id, title FROM posts ORDER BY id LIMIT 10 OFFSET 20;
-- MySQL also accepts the short form: LIMIT 20, 10

Without ORDER BY, row order is not guaranteed, so LIMIT alone returns unpredictable rows. In MySQL, NULLs come first in ascending order and last in descending order. Large offsets get slow because MySQL still reads and throws away the skipped rows; for deep pages, filter on the last key you saw instead, as in WHERE id > 1000 ORDER BY id LIMIT 10.

Constraints are rules declared on columns or tables that the database checks on every insert and update, so invalid data is rejected no matter which application writes it.

• NOT NULL: a value is required.
• UNIQUE: no two rows can share the value.
• PRIMARY KEY: unique and not null; identifies the row.
• FOREIGN KEY: the value must exist in the referenced table.
• CHECK: a condition every row must satisfy. MySQL enforces CHECK only from version 8.0.16; earlier versions accepted the syntax and silently ignored it.
• DEFAULT: the value used when none is supplied (strictly a column default, but usually listed with constraints).

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT DEFAULT 0
);

NULL means unknown or missing, not zero or an empty string, so a normal comparison with NULL gives NULL (unknown) instead of true or false.

SELECT NULL = NULL,             -- NULL
       NULL IS NULL,            -- 1
       NULL <=> NULL,           -- 1 (MySQL's NULL-safe equality)
       COALESCE(NULL, 'N/A'),   -- 'N/A'
       CONCAT('a', NULL);       -- NULL

• Use IS NULL and IS NOT NULL; WHERE phone = NULL never matches anything.
• COALESCE(a, b, ...) returns the first non-NULL argument; MySQL's IFNULL(a, b) is a two-argument version.
• All aggregates except COUNT(*) skip NULLs.
• Arithmetic with NULL gives NULL, so salary + NULL is NULL.

Interview tip: Interviewers like to follow up with NOT IN on a list that contains NULL, which returns no rows at all (see the EXISTS vs IN question).

UNION combines the results of two queries and removes duplicate rows, while UNION ALL keeps every row, duplicates included.

SELECT 1 AS n UNION SELECT 1 UNION SELECT 2;
-- two rows: 1 and 2

SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2;
-- three rows: 1, 1 and 2

Removing duplicates takes extra work (MySQL typically de-duplicates through a temporary table), so UNION ALL is faster and should be your default when duplicates are impossible or wanted. Both queries must return the same number of columns with compatible types, and the column names come from the first SELECT. An ORDER BY at the very end sorts the combined result.

DECIMAL stores exact values with a fixed number of digits, while FLOAT and DOUBLE store approximate binary floating-point values, so use DECIMAL for money and anything that must add up exactly.

DECIMAL(10,2) means up to 10 digits in total, 2 of them after the decimal point, so the largest value is 99999999.99. Floating-point types cannot represent many decimal fractions such as 0.1 exactly, so sums drift by tiny amounts and equality checks can fail: a FLOAT column holding 0.1 may not match WHERE amount = 0.1. FLOAT and DOUBLE are fine for scientific readings, sensor data or coordinates, where tiny rounding errors do not matter.

CREATE TABLE invoices (
  id INT PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL,   -- rupees and paise, exact
  latitude DOUBLE                  -- approximate is fine here
);

A composite key is a key made of two or more columns whose combination is unique, even though each column on its own may repeat.

CREATE TABLE enrollments (
  student_id INT,
  course_id INT,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id)
);

Here a student can join many courses and a course has many students, but the same student cannot enrol in the same course twice. A candidate key is any minimal set of columns that could uniquely identify a row; in a users table both id and email qualify. You pick one candidate key as the primary key, and the others usually become unique keys. A super key is any set of columns that is unique, whether minimal or not.

The cleanest answer finds the maximum salary that is below the overall maximum; it handles ties at the top and returns NULL when there is no second salary.

-- salaries: 50000, 80000, 80000, 70000
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- 70000

Another common version uses LIMIT with an offset. You must add DISTINCT, otherwise the duplicate 80000 is returned as the second highest:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- 70000

If the table has only one distinct salary, this second query returns an empty result rather than NULL; wrap it as a scalar subquery, SELECT (...) AS second_highest, to get NULL instead.

Interview tip: Mention the duplicate-salary case before the interviewer does; it is usually the real point of the question.

Use DENSE_RANK() so that tied salaries share a rank, then pick rank N; this works in MySQL 8 and handles duplicates correctly.

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 3;

For salaries 90000, 80000, 80000 and 70000 this returns 70000, because both 80000 rows get rank 2. RANK() would give 70000 rank 4 and return nothing for N = 3. Without window functions, use SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1. In MySQL, LIMIT and OFFSET accept only integer constants, ? placeholders in prepared statements, or variables inside stored programs, not an expression like N-1, so compute the offset first.

Group by the columns that define a duplicate and keep groups with COUNT(*) > 1; to delete, join the table to itself and remove every copy except the one with the lowest id.

-- find duplicate emails
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- delete duplicates, keep the smallest id per email
DELETE u1 FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

The DELETE removes a row whenever another row with the same email and a smaller id exists, so exactly one row per email survives. This multi-table DELETE syntax is MySQL-specific. Run the equivalent SELECT first, take a backup, and afterwards add a UNIQUE index on email so duplicates cannot come back.

A self join joins a table to itself using two different aliases, which is how you compare rows of the same table, such as an employee row with the manager's row.

-- id | name   | salary | manager_id
-- 1  | Amit   | 90000  | NULL
-- 2  | Bina   | 95000  | 1
-- 3  | Chetan | 60000  | 1
-- 4  | Divya  | 70000  | 3
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary
ORDER BY e.name;
-- Bina  | 95000 | Amit   | 90000
-- Divya | 70000 | Chetan | 60000

e is the employee copy and m the manager copy of the same table. Chetan earns less than Amit, so he is filtered out. Amit has no manager, so the inner join drops him; use LEFT JOIN if you need to list everyone with their manager, including the top boss.

Interview tip: A typical follow-up is listing each manager with a count of direct reports, which is the same self join plus GROUP BY m.id, m.name.

Normalization is organising tables so that each fact is stored in one place, which removes redundancy and the update, insert and delete anomalies it causes.

• 1NF: every column holds a single atomic value and there are no repeating groups. A phones column holding two numbers separated by a comma breaks it; move phones to their own table.
• 2NF: 1NF, and every non-key column depends on the whole primary key, not part of it. In order_items(order_id, product_id, product_name, qty), product_name depends only on product_id, so it belongs in products.
• 3NF: 2NF, and non-key columns do not depend on other non-key columns (no transitive dependency). In students(id, name, dept_id, dept_name), dept_name depends on dept_id, so move it to departments.

2NF only matters for tables with composite keys; a 1NF table whose primary key is a single column is automatically in 2NF.

Interview tip: Describe the anomaly with an example, such as renaming a department in 500 student rows; it shows understanding better than reciting definitions.

An index is a separate, sorted data structure on one or more columns that lets the database find matching rows without scanning the whole table, much like the index at the back of a book.

MySQL's InnoDB engine stores its indexes as B+trees: values are kept sorted in a balanced tree, so finding one value, a range (BETWEEN, >, LIKE 'abc%') or the next row in order takes only a few page reads even on millions of rows. Without an index, WHERE email = 'x' is a full table scan. Primary keys and unique keys are indexed automatically, and InnoDB also indexes foreign key columns. Add other indexes for columns used often in WHERE, JOIN, ORDER BY and GROUP BY.

CREATE INDEX idx_users_email ON users (email);
SHOW INDEX FROM users;

A normal subquery is independent and can run once on its own, while a correlated subquery refers to a column of the outer query, so logically it is evaluated again for every outer row.

-- Non-correlated: can run once
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE city = 'Pune');

-- Correlated: uses e.dept_id from the current outer row
SELECT name, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees
                WHERE dept_id = e.dept_id);

The second query finds employees paid above their own department's average. Correlated subqueries are easy to write but can be slow on large tables. MySQL's optimizer can rewrite some of them, and you can often replace one with a join to a grouped derived table or a window function such as AVG(salary) OVER (PARTITION BY dept_id).

IN checks whether a value appears in a list or subquery result, while EXISTS only checks whether the subquery returns at least one row; for positive checks they give the same result, but NOT IN and NOT EXISTS behave differently when NULLs are involved.

-- customers with at least one order
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- danger: if any orders.customer_id is NULL, this returns no rows
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

x NOT IN (1, 2, NULL) can never be true, because the comparison with NULL is unknown, so every row is filtered out. NOT EXISTS has no such problem, so prefer it for rows without a match. On performance, MySQL 8 often turns both IN and EXISTS subqueries into the same semijoin or antijoin plan, so check EXPLAIN instead of assuming one is always faster.

Interview tip: If asked which is faster, say it depends on the plan and that you would confirm with EXPLAIN; the old rule that EXISTS always wins is not reliable.

Combine a LEFT JOIN and a RIGHT JOIN with a union: the left join gives every left row with its matches, and the right join adds the right rows that had no match.

SELECT e.name AS employee, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL;
-- rows: Asha Sales | Ravi IT | Neha NULL | NULL HR

Using the same employees and departments as the joins question, WHERE e.id IS NULL keeps only departments without employees, so matched rows are not repeated, and UNION ALL skips a de-duplication step. Many answers use plain UNION without that filter; it works on simple data but also collapses genuine duplicate rows that a real full outer join would keep.

A view is a saved SELECT query that you can query like a table; it stores the query definition, not the data, so every read runs the underlying query.

CREATE VIEW active_customers AS
SELECT id, name, email FROM customers WHERE status = 'active';

SELECT * FROM active_customers WHERE name LIKE 'A%';

Views hide complex joins, give a stable interface when tables change, and help security: you can grant access to a view without granting access to the base table's sensitive columns. A view is updatable when each of its rows maps to exactly one base-table row, so no aggregates, DISTINCT, GROUP BY or UNION. WITH CHECK OPTION blocks changes that would make a row fall out of the view. MySQL has no materialized views; you emulate them with a summary table refreshed by a scheduled job or triggers.

Both are named blocks of SQL stored in the database, but a function returns exactly one value and can be used inside a query, while a procedure is run with CALL and can do much more.

• In MySQL, a function's parameters are input only and it must RETURN one value; a procedure can have IN, OUT and INOUT parameters and can return result sets.
• A function can be used in SELECT, WHERE and other expressions; a procedure cannot.
• A procedure can use COMMIT and ROLLBACK; a stored function is not allowed to commit or roll back.

DELIMITER //
CREATE FUNCTION with_gst(price DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
  RETURN ROUND(price * 1.18, 2);
END //
CREATE PROCEDURE dept_count(IN d INT, OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM employees WHERE dept_id = d;
END //
DELIMITER ;
SELECT name, with_gst(price) FROM products;
CALL dept_count(10, @n); SELECT @n;

DELIMITER is a mysql client command that lets the semicolons inside the body pass through without ending the CREATE statement.

A trigger is code the database runs automatically before or after an INSERT, UPDATE or DELETE on a table, commonly used for audit logs, derived values and extra validation.

CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_log (emp_id, old_salary, new_salary, changed_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

In MySQL, triggers are always row-level (FOR EACH ROW). OLD holds the row before the change and NEW the row after; INSERT triggers have only NEW, DELETE triggers only OLD, and a BEFORE trigger can change NEW values. This example fires on every update, even when the salary did not change, so a real version would check NEW.salary <> OLD.salary first. TRUNCATE and cascaded foreign key actions do not fire triggers. Keep triggers small, because hidden logic is hard to debug and slows every write.

ACID is the set of guarantees a transactional database gives so that data stays correct despite crashes and many users working at once.

• Atomicity: all statements in a transaction succeed or none do. A bank transfer never debits one account without crediting the other. InnoDB uses undo logs to roll back.
• Consistency: a transaction moves the database from one valid state to another, respecting constraints and keys.
• Isolation: concurrent transactions do not see each other's partial work; how strictly depends on the isolation level.
• Durability: once committed, data survives a crash. With default settings, InnoDB flushes its redo log to disk at every commit.

In MySQL, InnoDB (the default engine) is ACID compliant, while the older MyISAM engine has no transactions at all.

Wrap related statements between START TRANSACTION and COMMIT, and run ROLLBACK if anything fails, so the changes are applied together or not at all.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- on any error, the application runs ROLLBACK instead

MySQL runs with autocommit on by default, so every standalone statement is its own transaction; START TRANSACTION suspends that until you commit or roll back. SAVEPOINT name and ROLLBACK TO SAVEPOINT name undo only part of a transaction. DDL such as CREATE, ALTER and TRUNCATE causes an implicit commit, and transactions work only on transactional engines like InnoDB.

Interview tip: A strong follow-up answer: lock the source row with SELECT ... FOR UPDATE before checking the balance, so two concurrent transfers cannot both spend the same money.

SQL is written starting with SELECT, but it is logically processed starting with FROM; this order explains which clauses can see which columns and aliases.

FROM / JOIN   -- build the row set
WHERE         -- filter rows
GROUP BY      -- form groups
HAVING        -- filter groups
SELECT        -- compute columns, aliases, window functions
DISTINCT      -- remove duplicate rows
ORDER BY      -- sort
LIMIT         -- cut the result

That is why WHERE cannot use an alias defined in SELECT or an aggregate like COUNT(*), and why window functions cannot appear in WHERE. MySQL is lenient and also lets GROUP BY and HAVING refer to SELECT aliases, but portable SQL should rely on aliases only in ORDER BY. This is the logical order; the optimizer may execute steps differently as long as the result is the same.

All three number rows in order of some column and differ only in how they treat ties: ROW_NUMBER always gives unique numbers, RANK gives ties the same rank and then skips numbers, and DENSE_RANK gives ties the same rank without gaps.

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees
ORDER BY salary DESC, name;
-- name  | salary | rn | rnk | drnk
-- Asha  | 90000  | 1  | 1   | 1
-- Neha  | 80000  | 2  | 2   | 2
-- Ravi  | 80000  | 3  | 2   | 2
-- Karan | 70000  | 4  | 4   | 3

Unlike GROUP BY, window functions keep every row and add a computed column. PARTITION BY restarts the numbering for each group, for example ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC). Window functions and CTEs (WITH) are available from MySQL 8.0; MySQL 5.7 has neither.

Interview tip: Give ROW_NUMBER a tie-breaker column in its ORDER BY, otherwise tied rows can be numbered differently from one run to the next.

Number the rows inside each department with ROW_NUMBER() (or DENSE_RANK() if all ties should be included) and filter on that number in an outer query, because window functions cannot be used directly in WHERE.

WITH ranked AS (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC, id) AS rn
  FROM employees
)
SELECT dept_id, name, salary
FROM ranked
WHERE rn <= 2
ORDER BY dept_id, rn;

PARTITION BY dept_id restarts the count at 1 for every department. With ROW_NUMBER you get at most two rows per department even when salaries tie; with DENSE_RANK you get everyone in the top two salary levels, which can be more than two people. Before MySQL 8 this needed a correlated subquery that counted how many higher salaries exist in the same department, which is much slower on big tables.

Isolation levels control how much a transaction can see of other transactions' changes; MySQL's InnoDB engine defaults to REPEATABLE READ.

• READ UNCOMMITTED: can see other transactions' uncommitted changes (dirty reads).
• READ COMMITTED: sees only committed data, but reading the same row twice can return different values (non-repeatable reads).
• REPEATABLE READ: plain SELECTs read from a snapshot taken at the transaction's first read, so repeated reads return the same data. For locking reads, UPDATE and DELETE over a range, InnoDB adds gap (next-key) locks so others cannot insert phantom rows into that range.
• SERIALIZABLE: like REPEATABLE READ, but InnoDB turns plain SELECTs into shared-lock reads when autocommit is off, so transactions behave as if run one after another.

SELECT @@transaction_isolation;   -- REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Higher levels give stronger guarantees at the cost of more locking and waiting.

Interview tip: A sharp follow-up is whether REPEATABLE READ allows phantoms: the SQL standard permits them, but InnoDB prevents them with snapshots for plain reads and gap locks for locking reads.

A composite index sorts entries by the first column, then by the second within it, and so on, so MySQL can seek with it only when the search starts from the leftmost column or columns.

CREATE INDEX idx_city_age_name ON users (city, age, name);

-- WHERE city = 'Pune'                              -> uses index
-- WHERE city = 'Pune' AND age = 25                 -> uses index
-- WHERE city = 'Pune' AND age > 25 AND name = 'Ravi'
--                                    -> seeks on city and age only
-- WHERE age = 25                                   -> usually cannot seek
-- WHERE name = 'Ravi'                              -> usually cannot seek

It works like a phone directory sorted by surname, then first name: finding every Sharma is easy, finding every Rahul is not. Once a column is used with a range (>, BETWEEN, LIKE 'a%'), later columns cannot narrow the seek, though MySQL may still filter on them inside the index. So put equality columns first and the range column last. MySQL 8 has a skip scan optimisation for some queries that miss the first column, but do not design around it. The order of conditions in WHERE does not matter; the column order in the index does.

Interview tip: Interviewers may ask whether an index on (a, b) makes a separate index on (a) redundant; usually it does, because queries on a alone can use the composite index.

Indexes speed up reads but cost space and slow every INSERT, UPDATE and DELETE, since each index must be updated too; and some query patterns stop MySQL using an index at all.

• Write-heavy tables pay that cost on every write; unused indexes also waste buffer pool memory.
• Low-selectivity columns such as is_active: if a value matches a large share of rows, a full scan is often cheaper and the optimizer ignores the index.
• Functions on the column: WHERE YEAR(created_at) = 2025 cannot seek on an index on created_at; write it as the range created_at >= '2025-01-01' AND created_at < '2026-01-01'.
• Leading wildcard: LIKE '%kumar' cannot seek on a B-tree index; LIKE 'kumar%' can.
• Type mismatch: comparing a VARCHAR column with a number, as in WHERE phone = 9876543210, forces a conversion and skips the index.

When you must filter on an expression, MySQL 8 supports functional indexes on expressions.

Put EXPLAIN in front of the query to see the plan MySQL will use: the order it reads tables, the index it picks and roughly how many rows it expects to examine.

EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid';

• type: the access method, roughly from best to worst const, eq_ref, ref, range, index, ALL. ALL means a full table scan.
• possible_keys and key: the indexes considered and the one actually chosen; key = NULL means no index was used.
• rows and filtered: the estimated rows examined and the percentage left after the conditions.
• Extra: watch for Using filesort and Using temporary (extra sorting or a temporary table); Using index means a covering index answered the query without reading table rows.

EXPLAIN ANALYZE in MySQL 8 actually runs the query and reports real row counts and timings, which helps when estimates are wrong. Fix the plan by adding or reordering indexes or rewriting conditions, then run EXPLAIN again.

Interview tip: Expect to be asked what a covering index is right after Using index comes up, so connect the two in your answer.

SQL injection happens when user input is pasted into a SQL string, so crafted input changes the meaning of the query; prepared statements prevent it by sending the query and the values separately, so values are never parsed as SQL.

// Vulnerable: input becomes part of the SQL text
const sql = "SELECT * FROM users WHERE email = '" + email + "'";
// email = "' OR 1=1 #"  gives  WHERE email = '' OR 1=1 #'
// # starts a comment in MySQL, so every user is returned.

// Safe (Node.js mysql2): the ? placeholder is bound as data
const [rows] = await db.execute(
  'SELECT * FROM users WHERE email = ?', [email]
);

With a placeholder, the input ' OR 1=1 # is just an odd email that matches nothing. Placeholders work only for values, not for table names, column names or sort direction, so those must be checked against a fixed allow-list. Manual escaping is a weaker fallback. Also give the application's database user only the privileges it needs, so a successful attack can do less damage.

Interview tip: If asked whether escaping input is enough, say parameterised queries are the primary defence because escaping depends on getting every call and character set right.

In InnoDB the clustered index is the table itself, with rows stored in a B+tree ordered by the primary key, while a secondary index is a separate B+tree whose entries hold the indexed columns plus the row's primary key value.

• A lookup by primary key reaches the full row in one tree search.
• A secondary index lookup finds the primary key, then searches the clustered index for the row. If the index already holds every column the query needs, it is a covering index and the second lookup is skipped (Using index in EXPLAIN).
• If a table has no primary key, InnoDB uses the first UNIQUE index whose columns are all NOT NULL, or else a hidden internal row id.

So keep primary keys short, since every secondary index stores a copy, and prefer increasing keys like AUTO_INCREMENT over random UUIDs, which insert into random pages and cause page splits.

BCNF (Boyce-Codd Normal Form) is a stricter version of 3NF: for every non-trivial functional dependency X → Y, X must be a superkey. 3NF allows an exception when Y is part of a candidate key; BCNF does not.

Example: (student, course, teacher) where each teacher teaches one course. The key is (student, course), but teacher → course holds and teacher is not a key. The table is in 3NF, because course is part of a key, but not in BCNF. Splitting it into (teacher, course) and (student, teacher) fixes that, but no longer enforces (student, course) → teacher through a key.

Denormalization is the deliberate opposite: adding redundant data to make reads faster, such as storing order_total on orders or keeping a daily sales summary table instead of summing millions of rows. Do it only after measuring a real read bottleneck, and plan how the copies stay in sync.

A deadlock happens when two transactions each hold a lock the other needs, so neither can continue; InnoDB detects the cycle and rolls back one of them so the other can finish.

-- T1: UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- T2: UPDATE accounts SET balance = balance - 50  WHERE id = 2;  -- locks row 2
-- T1: UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for T2
-- T2: UPDATE accounts SET balance = balance + 50  WHERE id = 1;  -- waits for T1
-- InnoDB rolls back one transaction, which gets:
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
--   try restarting transaction

• Access rows and tables in the same order everywhere (for example, always lock the lower account id first).
• Keep transactions short and do not wait for user input inside one.
• Index the columns in WHERE clauses of UPDATE and DELETE, so InnoDB locks fewer rows.
• Treat the error as normal and retry the whole transaction in application code.

SHOW ENGINE INNODB STATUS shows details of the latest deadlock.