Introduction
MySQL continues to dominate the world of relational database management systems (RDBMS). With its open-source nature, ease of use, and widespread adoption by companies and developers alike, it’s no surprise that MySQL is a core skill expected in many technical interviews. Whether you’re a fresher, an experienced developer, or someone transitioning into database-related roles, preparing for MySQL interview questions is crucial.
In this comprehensive and updated guide, we present the Top 50 MySQL Interview Questions and Answers, designed to help you understand MySQL from the ground up. We’ve grouped questions from basic to advanced levels, with detailed answers, examples, and explanations.
✅ Why Should You Prepare for MySQL Interview Questions?
MySQL is widely used in full-stack development, data analysis, backend engineering, and DevOps.
Questions often appear in technical interviews, coding rounds, and system design discussions.
This guide enhances both conceptual clarity and hands-on SQL skills.
📘 Basic MySQL Interview Questions and Answers
1. What is MySQL?
MySQL is an open-source RDBMS that uses Structured Query Language (SQL) to manage data. It supports a client-server model and is widely used for web applications.
2. What are the different data types in MySQL?
Numeric: INT, FLOAT, DOUBLE, DECIMAL
String: CHAR, VARCHAR, TEXT, BLOB
Date & Time: DATE, TIME, DATETIME, TIMESTAMP, YEAR
Example:
CREATE TABLE sample (
id INT,
name VARCHAR(50),
created_at DATETIME
);
3. Difference between CHAR and VARCHAR?
CHAR(n): Fixed-length, always reserves n characters.
VARCHAR(n): Variable-length, stores actual characters used.
Example:
CHAR(10) -- Always reserves 10 characters
VARCHAR(10) -- Stores only the number of characters entered
4. Types of JOINs in MySQL
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN (simulated)
CROSS JOIN
Example:
SELECT a.name, b.department
FROM employees a
INNER JOIN departments b ON a.dept_id = b.id;
5. Difference between WHERE and HAVING?
WHERE: Filters rows before GROUP BY
HAVING: Filters rows after GROUP BY
Example:
SELECT dept_id, COUNT(*) as emp_count
FROM employees
GROUP BY dept_id
HAVING emp_count > 5;
6. What is a Primary Key?
A primary key uniquely identifies each row and cannot be NULL.
Example:
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50)
);
7. What is a Foreign Key?
A foreign key links one table to another using a reference to a primary key.
Example:
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
8. How to create an index in MySQL?
Indexes speed up SELECT queries.
CREATE INDEX idx_username ON users(username);
9. What are Transactions?
A transaction is a group of SQL queries executed as a single unit with ACID properties.
Example:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
10. TRUNCATE vs DELETE vs DROP
| Operation | Removes Data | Removes Structure | Transaction Safe |
|---|---|---|---|
| DELETE | Yes | No | Yes |
| TRUNCATE | Yes (Fast) | No | No |
| DROP | Yes | Yes | No |
11-50: Intermediate to Advanced MySQL Interview Questions
11. Retrieve Current Date and Time
SELECT NOW(); -- Date & time
SELECT CURDATE(); -- Date only
SELECT CURTIME(); -- Time only
12. What are Stored Procedures?
Stored procedures are precompiled SQL routines stored in the database.
DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
SELECT * FROM users;
END //
DELIMITER ;
13. Difference between UNION and UNION ALL
UNION removes duplicates.
UNION ALL includes all rows, including duplicates.
14. Pagination in MySQL
SELECT * FROM users LIMIT 10 OFFSET 20; -- Page 3, 10 per page
15. Prevent SQL Injection
Use Prepared Statements
Sanitize Inputs
Example (PHP):
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$email]);
16. UNIQUE Constraint
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
17. Types of Indexes
PRIMARY
UNIQUE
INDEX
FULLTEXT
SPATIAL
18. Composite Key
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
19. ACID Properties
Atomicity
Consistency
Isolation
Durability
20. Update Data in MySQL
UPDATE users SET name = 'John Doe' WHERE id = 5;
21. What is Normalization?
1NF: Atomic values
2NF: No partial dependencies
3NF: No transitive dependencies
22. What is Denormalization?
Introducing redundancy to improve performance.
23. IS NULL vs = NULL
Use IS NULL to check for NULLs.
24. AUTO_INCREMENT Column
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
25. IN vs EXISTS
IN: List of values
EXISTS: Subquery returns rows
26. Default Storage Engine
InnoDB (as of MySQL 5.5+)
27. Subqueries
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);
28. Views in MySQL
CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';
29. Triggers
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();
30. Copy Table Structure
CREATE TABLE new_table LIKE old_table;
31. Copy Table with Data
CREATE TABLE new_table AS SELECT * FROM old_table;
32. BETWEEN vs IN
BETWEEN for range
IN for specific values
33. IFNULL() Function
SELECT name, IFNULL(email, 'N/A') FROM users;
34. Find Duplicates
SELECT email, COUNT(*) FROM users
GROUP BY email
HAVING COUNT(*) > 1;
35. NOW() vs CURDATE() vs SYSDATE()
NOW(): Current timestamp
CURDATE(): Current date
SYSDATE(): Evaluated at call time
36. GROUP_CONCAT()
SELECT department, GROUP_CONCAT(name) AS employees
FROM users
GROUP BY department;
37. Case-Insensitive Search
SELECT * FROM users WHERE LOWER(name) = 'john';
38. CASE Statement
SELECT name,
CASE status
WHEN 1 THEN 'Active'
WHEN 0 THEN 'Inactive'
ELSE 'Unknown'
END AS status_desc
FROM users;
39. Limit Rows in Result
SELECT * FROM users LIMIT 5;
40. Simulate FULL OUTER JOIN
SELECT * FROM A LEFT JOIN B ON A.id = B.id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id = B.id;
41. Nth Highest Salary
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET N-1;
42. EXPLAIN in MySQL
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
43. Modify Column Data Type
ALTER TABLE users MODIFY COLUMN age SMALLINT;
44. Rename Table
RENAME TABLE old_table TO new_table;
45. Last Inserted ID
SELECT LAST_INSERT_ID();
46. DELETE vs TRUNCATE Performance
TRUNCATE is faster
DELETE is safer (transactional)
47. SET SQL_SAFE_UPDATES = 0
Allows unsafe updates/deletes without WHERE clause.
48. Error Handling in Stored Procedures
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET @error = 'An error occurred';
49. Second Highest Salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
50. Export and Import MySQL Database
Export:
mysqldump -u user -p dbname > backup.sql
Import:
mysql -u user -p dbname < backup.sql
🏁 Conclusion
Mastering MySQL is more than memorizing syntax—it’s about thinking in terms of data relationships, performance, and real-world scenarios. This list of Top 50 MySQL Interview Questions and Answers equips you with the tools and knowledge to confidently tackle any MySQL-related technical interview.
Keep revisiting this post for quick revisions and check out related guides on:
Bookmark this page and share it with fellow learners. Stay confident, stay curious!