MySQL Interview Q & A

1. What is MySQL, and how is it different from SQL?

MySQL is an open-source relational database management system (software). SQL is the language used to talk to relational databases. MySQL understands SQL plus its own extensions such as LIMIT and AUTO_INCREMENT. More.

2. What are DDL, DML, DQL, DCL and TCL?

• DDL: structure (CREATE, ALTER, DROP, TRUNCATE, RENAME).
• DML: data changes (INSERT, UPDATE, DELETE).
• DQL: queries (SELECT).
• DCL: permissions (GRANT, REVOKE).
• TCL: transactions (START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT).

3. What is the default port and default storage engine?

Port 3306; storage engine InnoDB (since MySQL 5.5).

SELECT @@default_storage_engine AS engine;

Result: 1 row

4. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length (padded with spaces), good for codes of fixed size. VARCHAR(n) stores only the characters used plus a 1–2 byte length prefix, good for names and text.

5. What is the difference between DATETIME and TIMESTAMP?

TIMESTAMP is stored in UTC, converted to the session time zone, and covers 1970–2038. DATETIME is stored exactly as written and covers years 1000–9999. More.

6. What is NULL? How do you test for it?

NULL means unknown or missing. Comparisons with NULL give NULL, so use IS NULL / IS NOT NULL.

SELECT NULL = NULL AS equals, NULL IS NULL AS is_null;

Result: 1 row

7. What is the difference between PRIMARY KEY and UNIQUE?

8. What is a foreign key? What do ON DELETE CASCADE and SET NULL do?

A foreign key makes a column reference another table's key, so orphan rows can't exist. When the parent row is deleted, CASCADE deletes the child rows too, SET NULL sets the child column to NULL, and the default (RESTRICT) refuses the delete. More.

9. Which constraints does MySQL support?

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (enforced since 8.0.16) and DEFAULT.

10. What is a composite key?

A key made of two or more columns, unique only as a combination, for example PRIMARY KEY (student_id, course_id) in an enrolments table.

11. What does AUTO_INCREMENT do, and why are there gaps?

It generates increasing integer IDs. Gaps appear after deletes, failed inserts and rollbacks because MySQL never reuses numbers. That is normal. More.

12. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and can't use aggregates; HAVING filters groups after GROUP BY and can.

SELECT dept, AVG(salary) AS avg_sal FROM employees WHERE salary > 50000 GROUP BY dept HAVING AVG(salary) > 60000;

Result: 1 row

13. In what order does MySQL process a SELECT?

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. That's why an alias from SELECT can be used in ORDER BY but not in WHERE.

14. Explain INNER, LEFT, RIGHT and CROSS JOIN.

• INNER: only matching rows from both tables.
• LEFT: all left rows + matches (NULLs where none).
• RIGHT: all right rows + matches.
• CROSS: every combination (Cartesian product).

MySQL has no FULL OUTER JOIN; use LEFT JOIN UNION RIGHT JOIN. More.

15. What is a self join? Show each employee with their manager.

SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

Result: 8 rows

16. UNION vs UNION ALL?

UNION removes duplicate rows (extra work); UNION ALL keeps all rows and is faster. Use UNION ALL unless you need distinct rows.

17. How do you find the second highest salary?

Several ways; with DENSE_RANK ties are handled correctly (Bilal and Chen share 72000):

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

Using LIMIT/OFFSET

SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) t WHERE rnk = 2;

Using DENSE_RANK (returns every employee with that salary)

18. How do you find the highest-paid employee in each department?

SELECT dept, name, salary FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees e
) t WHERE rnk = 1 ORDER BY dept;

Result: 4 rows

19. How do you find duplicate values?

Group by the column and keep groups with more than one row. Here two employees share an email:

SELECT email, COUNT(*) AS copies FROM employees GROUP BY email HAVING COUNT(*) > 1;

Result: 1 row

20. Why does NOT IN sometimes return no rows?

If the sub-query returns a NULL, x NOT IN (…, NULL) is never TRUE. Use NOT EXISTS or filter NULLs out. More.

SELECT name FROM employees WHERE emp_id NOT IN (SELECT manager_id FROM employees);

NOT IN with a NULL manager_id: empty

SELECT name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id);

NOT EXISTS: employees who manage nobody

21. What is the difference between DELETE, TRUNCATE and DROP?

22. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)?

Rows; non-NULL values; different non-NULL values.

SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS have_manager, COUNT(DISTINCT dept) AS depts FROM employees;

Result: 1 row

23. What is GROUP_CONCAT?

SELECT dept, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS people FROM employees GROUP BY dept;

Result: 3 rows

24. What are window functions? Give an example.

Functions that calculate across related rows without collapsing them (MySQL 8+), using OVER (…): ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM() OVER…

SELECT name, dept, salary,
       salary - AVG(salary) OVER (PARTITION BY dept) AS vs_dept_avg
FROM employees ORDER BY dept, salary DESC;

Result: 8 rows

25. RANK vs DENSE_RANK vs ROW_NUMBER?

SELECT name, salary,
       ROW_NUMBER() OVER w AS row_num, RANK() OVER w AS rnk, DENSE_RANK() OVER w AS dense
FROM employees WINDOW w AS (ORDER BY salary DESC) LIMIT 5;

Result: 5 rows

Ties: ROW_NUMBER still numbers 2, 3; RANK gives both 2 and skips to 4; DENSE_RANK gives both 2 and continues with 3.

26. Does MySQL have FIRST() and LAST() functions?

No. Use ORDER BY … LIMIT 1, MIN/MAX, ROW_NUMBER, or FIRST_VALUE/LAST_VALUE window functions (mind LAST_VALUE's frame). More.

27. What is an index and how does it work?

A sorted B-tree structure that lets MySQL find rows without scanning the whole table. It speeds up WHERE, JOIN and ORDER BY, but costs disk space and slows writes. More.

28. What is a clustered index?

In InnoDB, the primary key index stores the full rows in key order: the table is the clustered index. Secondary indexes store the primary key value to find the row.

29. What is the leftmost-prefix rule?

A composite index (a, b, c) can serve conditions on a, a, b or a, b, c, but not normally on b alone (MySQL 8 can sometimes use a slower skip scan).

30. What is a covering index?

An index containing every column a query needs, so MySQL answers from the index alone (EXPLAIN shows Using index).

31. Why might MySQL not use an index?

• A function on the column: WHERE YEAR(created) = 2026.
• A leading wildcard: LIKE '%abc'.
• Type conversion (comparing a string column with a number).
• Low selectivity: most rows match, so a scan is cheaper.
• Stale statistics (run ANALYZE TABLE).

32. How do you analyse a slow query?

Run EXPLAIN (or EXPLAIN ANALYZE) and look at type (ALL = full scan), key, rows and Extra (Using filesort / temporary). Enable the slow query log to find slow queries in production.

EXPLAIN SELECT name FROM employees WHERE emp_id = 3;

Result: 1 row

33. How do you paginate large tables efficiently?

Deep OFFSET reads and discards rows. Use keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20. More.

34. What is ACID?

Atomicity (all or nothing), Consistency (rules hold), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). More.

35. What isolation levels exist and which is the default?

READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (InnoDB default) and SERIALIZABLE.

SELECT @@transaction_isolation AS isolation;

Result: 1 row

36. What is MVCC?

Multi-Version Concurrency Control: InnoDB keeps older row versions (in undo logs) so readers see a consistent snapshot without locking, while writers continue.

37. What is a deadlock and how does MySQL handle it?

Two transactions each wait for a lock the other holds. InnoDB detects it and rolls back one (error 1213); the application should retry. Prevent it by locking rows in a consistent order and keeping transactions short.

38. What does SELECT … FOR UPDATE do?

Reads rows and locks them exclusively until the transaction ends, so no one else can change them in between. Add SKIP LOCKED for job queues or NOWAIT to fail fast. More.

39. Which statements cause an implicit commit?

DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME), user statements (CREATE USER, GRANT), LOCK TABLES and starting a new transaction. They can't be rolled back.

40. InnoDB vs MyISAM?

41. How do you create a user and give read-only access?

CREATE USER 'report'@'10.0.0.%' IDENTIFIED BY 'Str0ng#Pass';
GRANT SELECT ON sales.* TO 'report'@'10.0.0.%';

42. How do you change a password?

ALTER USER 'u'@'h' IDENTIFIED BY 'new'; Use RETAIN CURRENT PASSWORD for zero-downtime rotation. More.

43. How do you back up and restore a database?

mysqldump -u root -p --single-transaction --routines --events shop > shop.sql
mysql -u root -p shop < shop.sql

For large databases use MySQL Shell's dump utilities or physical backups (MySQL Enterprise Backup, Percona XtraBackup).

44. How do you rename a database?

There is no RENAME DATABASE. Create a new database and move tables with RENAME TABLE old.t TO new.t, or dump and restore. More.

45. What is replication?

Copying changes from a source server to replicas through the binary log, for read scaling, backups and failover. Group Replication and InnoDB Cluster add automatic failover.

46. How do you prevent SQL injection from Java?

Never concatenate user input into SQL. Use PreparedStatement with ? placeholders (or JPA/Spring Data, which do this for you), validate identifiers such as table names against an allow-list, and give the application account minimal privileges.