MySQL remains one of the most popular relational database management systems in the world, widely used by developers, data analysts, and database administrators alike. Whether you’re preparing for your first job or aiming to level up in your career, having a solid understanding of MySQL is essential.

In this comprehensive guide, we’ve compiled the Top 50 most commonly asked MySQL interview questions, covering everything from basic syntax to advanced database concepts. Each question is paired with a detailed explanation and practical example to help you ace your next interview confidently.

Whether you’re a fresher, experienced developer, or someone switching roles, these MySQL questions will sharpen your SQL skills and boost your interview performance.

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). It’s widely used for web applications and supports a client-server architecture.

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. What is the difference between CHAR and VARCHAR?

• CHAR(n): Fixed-length (always n bytes)
  
  
• VARCHAR(n): Variable-length (up to n bytes)
  
  

Example:

CHAR(10)  — Always reserves 10 characters

VARCHAR(10)  — Stores only the number of characters entered

4. What are the different types of JOINs in MySQL?

• INNER JOIN
  
  
• LEFT JOIN
  
  
• RIGHT JOIN
  
  
• FULL JOIN (Not supported natively in MySQL, can be simulated)
  
  
• CROSS JOIN
  
  

Example:

SELECT a.name, b.department

FROM employees a

INNER JOIN departments b ON a.dept_id = b.id;

5. What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping.
  
  
• HAVING filters rows after grouping.
  
  

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 record in a table. It must be unique and not NULL.

Example:

CREATE TABLE users (

    id INT PRIMARY KEY,

    username VARCHAR(50)

);

7. What is a Foreign Key?

A foreign key links two tables. It refers to the primary key in another table.

Example:

CREATE TABLE orders (

    id INT PRIMARY KEY,

    user_id INT,

    FOREIGN KEY (user_id) REFERENCES users(id)

);

8. How do you create an index in MySQL?

Indexes improve the performance of SELECT queries.

Example:

CREATE INDEX idx_username ON users(username);

9. What are transactions in MySQL?

A transaction is a sequence of SQL statements treated as a single unit. It follows 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. What is the difference between TRUNCATE, DELETE, and DROP?

11. How to retrieve the current date and time in MySQL?

SELECT NOW();        — Current date and time

SELECT CURDATE();    — Current date

SELECT CURTIME();    — Current time

12. What are stored procedures?

Stored procedures are precompiled SQL statements that are stored in the database.

Example:

DELIMITER //

CREATE PROCEDURE GetUsers()

BEGIN

   SELECT * FROM users;

END //

DELIMITER ;

13. What is the difference between UNION and UNION ALL?

• UNION removes duplicates.
  
  
• UNION ALL includes duplicates.
  
  

14. How do you implement pagination in MySQL?

Use LIMIT with OFFSET.

Example:

SELECT * FROM users LIMIT 10 OFFSET 20;  — Page 3 with page size 10

15. How can you prevent SQL injection in MySQL?

• Use prepared statements
  
  
• Sanitize user inputs
  
  
• Use ORM frameworks
  
  

Example in PHP:

$stmt = $pdo->prepare(“SELECT * FROM users WHERE email = ?”);

$stmt->execute([$email]);

16. What is a UNIQUE constraint?

A UNIQUE constraint ensures all values in a column are different.

Example:

CREATE TABLE users (

    id INT PRIMARY KEY,

    email VARCHAR(100) UNIQUE

);

17. What are the different types of indexes in MySQL?

• PRIMARY – Unique and not null.
  
  
• UNIQUE – Prevents duplicates.
  
  
• INDEX (non-unique)
  
  
• FULLTEXT – For text searching.
  
  
• SPATIAL – For geometry types.
  
  

18. What is a composite key?

A composite key is a primary key made up of multiple columns.

Example:

CREATE TABLE enrollments (

    student_id INT,

    course_id INT,

    PRIMARY KEY (student_id, course_id)

);

19. What are the ACID properties of a transaction?

• Atomicity – All or nothing
  
  
• Consistency – Valid state maintained
  
  
• Isolation – Transactions are isolated
  
  
• Durability – Committed changes are permanent
  
  

20. How can you update data in MySQL?

UPDATE users

SET name = ‘John Doe’

WHERE id = 5;

21. What is normalization?

Normalization organizes data to reduce redundancy. There are multiple normal forms:

• 1NF: Atomic values
  
  
• 2NF: No partial dependency
  
  
• 3NF: No transitive dependency
  
  

22. What is denormalization?

Denormalization is the process of introducing redundancy to improve read performance by reducing joins.

23. What is the difference between IS NULL and = NULL?

Use IS NULL for null checking.

Incorrect:

SELECT * FROM users WHERE email = NULL;   — Wrong

Correct:

SELECT * FROM users WHERE email IS NULL;

24. What is an AUTO_INCREMENT column?

Automatically generates a unique value for new rows.

Example:

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50)

);

25. What is the difference between IN and EXISTS?

• IN checks values in a list
  
  
• EXISTS checks for row existence in a subquery (more efficient with large data)
  
  

26. What is the default storage engine in MySQL?

As of MySQL 5.5 and above, the default engine is InnoDB (supports transactions and foreign keys).

27. What is a subquery?

A query inside another query.

Example:

SELECT name

FROM users

WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);

28. What is a view in MySQL?

A view is a virtual table based on a query result.

Example:

CREATE VIEW active_users AS

SELECT id, name FROM users WHERE status = ‘active’;

29. What is a trigger in MySQL?

A trigger automatically executes a command when an event (INSERT, UPDATE, DELETE) occurs.

Example:

CREATE TRIGGER before_insert_users

BEFORE INSERT ON users

FOR EACH ROW

SET NEW.created_at = NOW();

30. How do you copy a table structure without data?

CREATE TABLE new_table LIKE old_table;

31. How do you copy both structure and data of a table?

CREATE TABLE new_table AS SELECT * FROM old_table;

32. What is the difference between BETWEEN and IN?

BETWEEN checks for a range:

SELECT * FROM products WHERE price BETWEEN 100 AND 500;

IN checks for specific values:

SELECT * FROM products WHERE category_id IN (1, 2, 3);

33. What is the use of the IFNULL() function?

IFNULL(expr1, expr2) returns expr2 if expr1 is NULL.

Example:

SELECT name, IFNULL(email, ‘N/A’) FROM users;

34. How do you find duplicate rows in MySQL?

SELECT email, COUNT(*)

FROM users

GROUP BY email

HAVING COUNT(*) > 1;

35. What is the difference between NOW(), CURDATE(), and SYSDATE()?

• NOW(): Current date and time
  
  
• CURDATE(): Current date
  
  
• SYSDATE(): Same as NOW() but evaluated at call time (useful in long-running queries)
  
  

36. What is the use of GROUP_CONCAT()?

It combines values from multiple rows into a single string.

Example:

SELECT department, GROUP_CONCAT(name) AS employees

FROM users

GROUP BY department;

37. How do you perform a case-insensitive search in MySQL?

By default, searches are case-insensitive in VARCHAR fields with collation like utf8_general_ci.

To explicitly ignore case:

SELECT * FROM users WHERE LOWER(name) = ‘john’;

38. What is a CASE statement in MySQL?

CASE is used for conditional logic inside queries.

Example:

SELECT name,

  CASE status

    WHEN 1 THEN ‘Active’

    WHEN 0 THEN ‘Inactive’

    ELSE ‘Unknown’

  END AS status_desc

FROM users;

39. How do you limit the number of rows returned by a query?

SELECT * FROM users LIMIT 5;

40. What is a full outer join and how do you simulate it in MySQL?

MySQL doesn’t support full outer join directly. You can simulate it with UNION:

SELECT * FROM A LEFT JOIN B ON A.id = B.id

UNION

SELECT * FROM A RIGHT JOIN B ON A.id = B.id;

41. How do you get the nth highest salary from an employee table?

SELECT DISTINCT salary

FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET N-1;  — For 3rd highest, N = 3

42. What is the use of EXPLAIN in MySQL?

EXPLAIN shows how MySQL executes a query, helping you optimize it.

EXPLAIN SELECT * FROM users WHERE email = ‘a@b.com’;

43. How do you change a column data type?

ALTER TABLE users MODIFY COLUMN age SMALLINT;

44. How do you rename a table in MySQL?

RENAME TABLE old_table TO new_table;

45. How can you retrieve the last inserted ID?

SELECT LAST_INSERT_ID();

46. What is the difference between DELETE and TRUNCATE in terms of performance?

• TRUNCATE is faster (DDL)
  
  
• DELETE is slower but transaction-safe (DML)
  
  

47. What is the purpose of SET SQL_SAFE_UPDATES = 0;?

Allows updates/deletes without a WHERE clause or primary key reference.

48. How do you handle errors in stored procedures?

Use DECLARE … HANDLER

Example:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

SET @error = ‘An error occurred’;

49. How do you find the second highest value in a column?

SELECT MAX(salary) FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

50. How do you export and import a MySQL database?

Export:

mysqldump -u user -p database_name > backup.sql

Import:

mysql -u user -p database_name < backup.sql

Mastering MySQL is not just about learning syntax—it’s about understanding how databases work, how to optimize queries, and how to solve real-world data problems efficiently. This list of the top 50 MySQL interview questions and answers is designed to give you the edge in any technical interview, helping you demonstrate both your theoretical knowledge and practical experience.

Keep practicing, stay curious, and don’t forget to bookmark this page for future reference. If you’re also preparing for related topics like SQL optimization, database design, or NoSQL vs SQL, check out our other interview prep resources linked below.

Ready to land your dream job? Start preparing with confidence—one question at a time.