MySQL Interview Questions

Beginner MySQL Interview Questions

1. What is MySQL?

MySQL is an open-source relational database management system. It stores data in tables made of rows and columns. It uses SQL to create, read, update, and delete data. MySQL is commonly used with applications built in PHP, Java, Python, Node.js, and other languages.

2. What is the difference between SQL and MySQL?

SQL is a language. It is used to work with relational databases. MySQL is a database management system that understands SQL. In simple words, SQL is what you write, and MySQL is the software that runs it.

3. What are tables, rows, and columns in MySQL?

A table stores related data. A row is one record in that table. A column is a field that stores a specific type of data. For example, in a student's table, student_id, name, and email can be columns. One student’s complete data is one row.

4. What are primary keys and foreign keys?

A primary key uniquely identifies each row in a table. It cannot be NULL. A foreign key connects one table to another table. It usually points to the primary key of another table.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY,
  student_id INT,
  course_name VARCHAR(100),
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. What are common MySQL data types?

Common MySQL data types include:

• INT for numbers
• VARCHAR for variable-length text
• TEXT for long text
• DATE for dates
• DATETIME for date and time
• DECIMAL for exact values like price
• JSON for JSON documents

6. What is the difference between CHAR and VARCHAR?

CHAR stores fixed-length text. VARCHAR stores variable-length text. Use CHAR when the length is always fixed, such as a country code. Use VARCHAR when the length can change, such as a name or email address.

Get the Coding Skills You Need to SucceedAI-Powered Full Stack Developer ProgramExplore Program

Intermediate MySQL Interview Questions

7. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters grouped results after GROUP BY.

SELECT department, COUNT(*) AS total_employees
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) > 10;

Here, WHERE filters only active employees. HAVING returns only departments with more than 10 active employees.

8. What are joins in MySQL?

Joins combine data from two or more tables. Common joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN, and SELF JOIN.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

This returns employees along with their department names.

9. What is normalization?

Normalization is the process of organizing data to reduce duplication and improve data integrity. For example, instead of storing department names repeatedly in an employee table, you can store departments in a separate table and connect them using a foreign key.

10. What is denormalization?

Denormalization means adding some duplicate data to improve read performance. It is often used in reporting systems. It can make queries faster, but it may increase storage and update complexity.

11. What is an index in MySQL?

An index helps MySQL find rows faster. It works like an index in a book. Indexes are useful on columns used in WHERE, JOIN, ORDER BY, and GROUP BY. The MySQL documentation notes that indexes are one of the best ways to improve SELECT performance.

CREATE INDEX idx_employee_email
ON employees(email);

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

DELETE removes selected rows and can use a WHERE clause. TRUNCATE removes all rows from a table quickly. DROP removes the complete table structure and data.

DELETE FROM employees WHERE employee_id = 10;
TRUNCATE TABLE employees;
DROP TABLE employees;

Advanced MySQL Interview Questions

13. What is a stored procedure?

A stored procedure is a saved set of SQL statements. It can be reused whenever needed. It helps reduce repeated code.

DELIMITER //
CREATE PROCEDURE GetActiveEmployees()
BEGIN
  SELECT * FROM employees WHERE status = 'Active';
END //
DELIMITER ;
CALL GetActiveEmployees();

14. What is a trigger?

A trigger is SQL code that runs automatically when an event happens on a table. The event can be INSERT, UPDATE, or DELETE.

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();

This trigger appends the current timestamp to a new employee record before it is inserted.

15. What is a view in MySQL?

A view is a virtual table created using a query. It does not usually store data itself. It shows data from one or more tables.

CREATE VIEW active_employees AS
SELECT employee_id, name, department_id
FROM employees
WHERE status = 'Active';

Views can simplify complex queries and limit access to sensitive columns.

16. What is a transaction?

A transaction is a group of SQL statements that run as one unit. If all statements succeed, the transaction is committed. If something fails, it can be rolled back.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

Transactions help maintain data consistency.

17. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transaction processing. InnoDB, the default MySQL storage engine, supports transactions and ACID compliance.

18. What is the difference between MyISAM and InnoDB?

InnoDB supports transactions, row-level locking, and foreign keys. MyISAM does not support transactions or foreign keys. In most modern MySQL applications, InnoDB is preferred for its better support of data integrity and concurrent writes.

Learn 45+ in-demand full-stack development skills and tools, including Frontend Development, Backend Development, Version Control and Collaboration, Database Management, and AI-Assisted Development, with our AI-Powered Full Stack Developer Course.

MySQL Query-Based Interview Questions

19. Write a query to fetch all employees from the Sales department.

SELECT e.employee_id, e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

20. Write a query using aggregate functions.

Aggregate functions perform calculations on a group of rows. Common examples are COUNT(), SUM(), AVG(), MIN(), and MAX().

SELECT department_id,
       COUNT(*) AS total_employees,
       AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

21. Write a query to find the second-highest salary.

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

This first uses a subquery to find the highest salary, then the next lower salary.

22. Write a query using a subquery.

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

This returns employees whose salary is higher than the company average.

23. Write a query using LEFT JOIN.

SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

This returns all customers, even those who have not placed any orders.

24. Write a query to find duplicate emails.

SELECT email, COUNT(*) AS count_email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

This query groups users by email and returns only emails that appear more than once.

25. Write a query using ranking functions.

MySQL 8 supports window functions such as ROW_NUMBER(), RANK(), and DENSE_RANK(). The official MySQL window function documentation explains that these functions perform calculations across related rows while still returning each row.

SELECT employee_id,
       name,
       department_id,
       salary,
       RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

This ranks employees by salary within each department.

Software engineering remains one of the most versatile and in-demand careers in tech. Explore this Software Engineer roadmap to understand the skills, tools, salary potential, and career progression from entry-level developer to senior engineering roles.

MySQL DBA Interview Questions

26. How do you take a backup in MySQL?

A common logical backup method is mysqldump.

mysqldump -u root -p company_db > company_db_backup.sql

To restore it:

mysql -u root -p company_db < company_db_backup.sql

The official MySQL backup and recovery guide covers logical and physical backups, full and incremental backups, and point-in-time and full-table recovery.

27. What is point-in-time recovery?

Point-in-time recovery restores a database to a specific time before an error occurred. It usually uses a full backup plus binary logs. This is useful when someone accidentally deletes data or applies an incorrect update.

28. What is MySQL replication?

Replication copies data from one MySQL server to one or more replica servers. According to the MySQL replication documentation, replication is asynchronous by default. It is used for read scaling, backup, reporting, and high availability.

Ship AI Apps, Agents, & Workflows in 8 WeeksWith the AI Accelerator ProgramExplore Now

29. How do you monitor MySQL performance?

You can monitor MySQL using:

• SHOW PROCESSLIST
• Slow query log
• EXPLAIN
• Performance Schema
• MySQL Workbench
• Third-party monitoring tools

The MySQL Performance Schema helps monitor server execution at a low level.

SHOW PROCESSLIST;
EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

30. How do you manage user privileges?

MySQL uses accounts and privileges to control access. The MySQL access control documentation explains that the privilege system authenticates users and associates them with allowed operations.

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT SELECT ON company_db.* TO 'report_user'@'localhost';
SHOW GRANTS FOR 'report_user'@'localhost';
REVOKE SELECT ON company_db.* FROM 'report_user'@'localhost';

Scenario-Based MySQL Interview Questions

31. A query is running slowly. What will you do?

First, use EXPLAIN to check the execution plan. Then check whether the query is using indexes. Avoid SELECT * if you only need a few columns. Review joins, filters, and subqueries. Also, check the slow query log.

EXPLAIN
SELECT customer_id, order_date
FROM orders
WHERE order_date >= '2026-01-01';
If the query often filters by order_date, an index may help.
CREATE INDEX idx_order_date ON orders(order_date);

32. A table has millions of rows. How will you improve performance?

Use proper indexing. Archive old data if it is not needed often. Partition large tables when suitable. Avoid unnecessary columns in queries. Use pagination with LIMIT. Also review schema design and query patterns.

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 50;

33. A user deleted important records by mistake. What will you do?

Stop writing if possible. Check the latest backup. Use binary logs if point-in-time recovery is enabled. Restore data to a test server first. Verify the restored data. Then move the corrected records back to production.

34. Your application is getting too many database connections. What will you check?

Check connection pooling, long-running queries, idle connections, and application retry logic. Also, check the MySQL max_connections setting. Increasing the limit may help for a short time, but the real fix is usually better query and connection management.

Boost Your Coding Skills. Nail Your Next InterviewAI-Powered Full Stack Developer ProgramExplore Program

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';

SELECT order_id,
       customer_id,
       order_amount,
       SUM(order_amount) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
       ) AS running_total
FROM orders;

WITH high_salary AS (
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 80000
)
SELECT *
FROM high_salary;

WITH RECURSIVE employee_tree AS (
  SELECT employee_id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id
  FROM employees e
  JOIN employee_tree et
  ON e.manager_id = et.employee_id
)
SELECT *
FROM employee_tree;

SELECT *
FROM JSON_TABLE(
  '[{"name":"Asha","score":90},{"name":"Ravi","score":85}]',
  '$[*]' COLUMNS (
    name VARCHAR(50) PATH '$.name',
    score INT PATH '$.score'
  )
) AS jt;

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;