Top 25 MySQL Interview Questions
Curated questions covering SQL queries, joins, indexes, transactions, normalization, stored procedures, and optimization.
What is MySQL and what are its key features?
MySQL is an open-source relational database management system (RDBMS). Key features: ACID compliance, SQL support, multiple storage engines (InnoDB, MyISAM), replication, partitioning, full-text search, and wide platform support.
What is the difference between DDL, DML, DCL, and TCL?
- DDL (Data Definition Language) — CREATE, ALTER, DROP, TRUNCATE.
- DML (Data Manipulation Language) — SELECT, INSERT, UPDATE, DELETE.
- DCL (Data Control Language) — GRANT, REVOKE.
- TCL (Transaction Control Language) — COMMIT, ROLLBACK, SAVEPOINT.
What are the different types of JOINs in MySQL?
- INNER JOIN — returns rows with matching values in both tables.
- LEFT JOIN — all rows from left table + matching rows from right (NULL if no match).
- RIGHT JOIN — all rows from right table + matching rows from left.
- FULL OUTER JOIN — all rows from both tables (MySQL uses UNION of LEFT and RIGHT).
- CROSS JOIN — Cartesian product of both tables.
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;What is the difference between WHERE and HAVING?
WHERE filters rows before grouping (cannot use aggregate functions). HAVING filters groups after GROUP BY (can use aggregate functions).
SELECT dept, COUNT(*) as cnt, AVG(salary) as avg_sal
FROM employees
WHERE status = "active"
GROUP BY dept
HAVING AVG(salary) > 50000;What are indexes in MySQL?
Indexes speed up data retrieval by creating a data structure (B-tree by default) that allows fast lookups. Types: PRIMARY KEY, UNIQUE, INDEX (non-unique), FULLTEXT, SPATIAL. Indexes slow down writes but speed up reads.
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);
EXPLAIN SELECT * FROM users WHERE email = "test@example.com";What is the difference between PRIMARY KEY and UNIQUE KEY?
- PRIMARY KEY — uniquely identifies each row; cannot be NULL; only one per table; automatically creates a clustered index.
- UNIQUE KEY — ensures uniqueness; can have NULL values (one NULL per column); multiple per table.
What is normalization?
- 1NF — eliminate repeating groups; each column has atomic values.
- 2NF — 1NF + no partial dependencies (non-key columns depend on the whole primary key).
- 3NF — 2NF + no transitive dependencies (non-key columns depend only on the primary key).
- BCNF — stricter version of 3NF.
What is denormalization?
Denormalization intentionally introduces redundancy to improve read performance. Used in data warehouses and reporting databases where read speed is more important than write efficiency.
What are transactions in MySQL?
A transaction is a group of SQL statements that execute as a single unit. ACID properties: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed data persists).
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK on errorWhat is the difference between InnoDB and MyISAM?
- InnoDB — supports transactions, foreign keys, row-level locking, ACID compliance. Default engine.
- MyISAM — no transactions or foreign keys; table-level locking; faster for read-heavy workloads; supports FULLTEXT search.
What is a stored procedure?
A stored procedure is a precompiled set of SQL statements stored in the database. It can accept parameters, contain logic, and be called by name. Reduces network traffic and improves performance.
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
SELECT * FROM orders WHERE user_id = userId;
END //
DELIMITER ;
CALL GetUserOrders(42);What is a trigger in MySQL?
A trigger automatically executes SQL statements in response to INSERT, UPDATE, or DELETE events on a table. Can be BEFORE or AFTER the event.
CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
INSERT INTO audit_log(user_id, action, ts) VALUES (OLD.id, "DELETE", NOW());What is a view in MySQL?
A view is a virtual table based on a SELECT query. It simplifies complex queries, provides security by restricting column access, and presents data in a specific format.
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = "active";
SELECT * FROM active_users;What is the difference between DELETE, TRUNCATE, and DROP?
- DELETE — removes specific rows; can use WHERE; logged; can be rolled back; triggers fire.
- TRUNCATE — removes all rows; faster; resets AUTO_INCREMENT; cannot be rolled back in most cases; no triggers.
- DROP — removes the entire table structure and data permanently.
What is a subquery?
A subquery is a query nested inside another query. Can be used in SELECT, FROM, WHERE, and HAVING clauses.
-- Correlated subquery
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);
-- Subquery in FROM
SELECT dept, avg_sal FROM (SELECT dept, AVG(salary) avg_sal FROM employees GROUP BY dept) t;What is the difference between UNION and UNION ALL?
UNION combines results of two SELECT statements and removes duplicates. UNION ALL combines results without removing duplicates — faster because no deduplication step.
What are window functions in MySQL?
Window functions perform calculations across a set of rows related to the current row without collapsing them into groups. Available in MySQL 8.0+.
SELECT name, salary, dept,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank,
SUM(salary) OVER (PARTITION BY dept) as dept_total
FROM employees;What is the EXPLAIN statement?
EXPLAIN shows how MySQL executes a query — which indexes are used, join types, and estimated rows scanned. Essential for query optimisation.
EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = "pending";What is a foreign key?
A foreign key enforces referential integrity between two tables. It ensures that a value in one table corresponds to a valid value in another table. Supported by InnoDB.
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);What is the difference between CHAR and VARCHAR?
- CHAR(n) — fixed length; always uses n bytes; faster for fixed-length data like codes.
- VARCHAR(n) — variable length; uses only as much space as needed + 1-2 bytes for length; better for variable-length strings.
What are aggregate functions in MySQL?
Aggregate functions compute a single result from multiple rows: COUNT(), SUM(), AVG(), MIN(), MAX(), GROUP_CONCAT(). Used with GROUP BY.
SELECT dept,
COUNT(*) as headcount,
AVG(salary) as avg_salary,
MAX(salary) as top_salary
FROM employees
GROUP BY dept;What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right rows are NULL.
What is query optimisation in MySQL?
- Use indexes on columns in WHERE, JOIN, and ORDER BY clauses.
- Avoid SELECT * — select only needed columns.
- Use EXPLAIN to analyse query plans.
- Avoid functions on indexed columns in WHERE (prevents index use).
- Use LIMIT for pagination.
- Normalise data to reduce redundancy.
What is the difference between NOW() and CURDATE()?
NOW() returns the current date and time (DATETIME). CURDATE() returns only the current date (DATE). CURTIME() returns only the current time.
What is AUTO_INCREMENT in MySQL?
AUTO_INCREMENT automatically generates a unique integer for a column (usually the primary key) when a new row is inserted. The value increments by 1 each time. You can set the starting value with ALTER TABLE.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO users (name) VALUES ("Alice"); -- id = 1 automatically