MySQL interview Question & Answer

MYSQL Interview Questions and Answers

Top 100 MY SQL Interview Questions for Freshers

MySQL is a widely used relational database management system (RDBMS) that enables professionals to efficiently store, retrieve, and manage structured data. Mastering MySQL allows developers and database administrators to optimize queries, design scalable databases, and ensure data integrity using advanced indexing, transactions, and security mechanisms.
Candidates should be well-prepared to tackle both the MySQL Online Assessment and the Technical Interview Round at IDM TechPark. To help you succeed, we have compiled a comprehensive list of the Top 100 MySQL Interview Questions along with their answers. Mastering these concepts will give you a strong edge in securing a MySQL-related role and excelling in the field of database administration and backend development.

1. What is MySQL?

Answer: MySQL is an open-source relational database management system (RDBMS) that allows users to store, manage, and retrieve structured data efficiently.

2. What is a database?

Answer: A database is a structured collection of data that can be easily accessed, managed, and updated.

3. What are tables in MySQL?

Answer: Tables are database objects that store data in rows and columns, allowing for efficient data organization.

4. What is a primary key?

Answer: A primary key is a unique identifier for each record in a table, ensuring that no two records have the same key.

5. What is a foreign key?

Answer: A foreign key is a column that establishes a relationship between two tables by referencing the primary key of another table.

6. What are the different data types in MySQL?

Answer: MySQL supports various data types, including INT, VARCHAR, TEXT, DATE, BOOLEAN, FLOAT, and BLOB.

7. What is a query?

Answer: A query is a request made to a database to retrieve, update, insert, or delete data.

8. What is SQL?

Answer: SQL (Structured Query Language) is a standard language used for managing and querying relational databases.

9. How do you create a database in MySQL?

Answer:

CREATE DATABASE database_name;

10. How do you create a table in MySQL?

Answer:

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100) );

11. How do you insert data into a MySQL table?

Answer:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

12. How do you retrieve all records from a table?

Answer:

SELECT * FROM users;

13. How do you update data in a MySQL table?

Answer:

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

14. How do you delete a record from a table?

Answer:

DELETE FROM users WHERE id = 1;

15. What is the difference between DELETE and TRUNCATE?

Answer: DELETE removes specific records, whereas TRUNCATE removes all records but keeps the table structure.

16. What is an index in MySQL?

Answer: An index improves database performance by speeding up search queries.

17. How do you create an index in MySQL?

Answer:

CREATE INDEX idx_name ON users (name);

18. What is the difference between CHAR and VARCHAR?

Answer: CHAR has a fixed length, while VARCHAR can store variable-length strings.

19. What is the default port number of MySQL?

Answer: The default port number is 3306.

20. What is MySQL Workbench?

Answer: MySQL Workbench is a visual database design tool that allows users to manage databases graphically.

21. What is normalization?

Answer: Normalization is the process of structuring a database to reduce redundancy and improve data integrity.

22. What are the different MySQL storage engines?

Answer: Common storage engines include InnoDB (supports transactions) and MyISAM (faster but lacks transactions).

23. What is ACID in databases?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties that ensure reliable transactions.

24. How do you back up a MySQL database?

Answer:

mysqldump -u root -p database_name > backup.sql

25. How do you restore a MySQL database from a backup?

Answer:

mysql -u root -p database_name < backup.sql

1. What is the difference between MySQL and SQL?

Answer: SQL is a standard language for managing relational databases, while MySQL is a database management system that implements SQL.

2. What are MySQL constraints?

Answer: Constraints are rules applied to table columns to ensure data integrity. Examples include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

3. What is the difference between INNER JOIN and OUTER JOIN?

Answer:

INNER JOIN returns only matching records from both tables.
OUTER JOIN (LEFT or RIGHT) returns all records from one table and matching records from the other.

4. How do you use a LEFT JOIN in MySQL?

Answer:

SELECT users.name, orders.order_id FROM users LEFT JOIN orders ON users.id = orders.user_id;

5. What is a self-join?

Answer: A self-join is a join where a table is joined with itself to compare rows.

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

Answer:

UNION combines results and removes duplicates.
UNION ALL combines results but retains duplicates.

7. How do you find duplicate records in a MySQL table?

Answer:

SELECT name, COUNT(*) FROM users GROUP BY name HAVING COUNT(*) > 1;

8. What is a subquery in MySQL?

Answer: A subquery is a query inside another query, used to retrieve data for the main query.

9. How do you use a subquery in MySQL?

Answer:

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

10. What is the difference between EXISTS and IN?

Answer:

EXISTS returns true if a subquery contains results.
IN checks if a value exists in a list of values.

11. What is a stored procedure in MySQL?

Answer: A stored procedure is a precompiled SQL code block that can be executed multiple times.

12. How do you create a stored procedure in MySQL?

Answer:

DELIMITER // CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END // DELIMITER ;

13. How do you execute a stored procedure?

Answer:

CALL GetUsers();

14. What is a trigger in MySQL?

Answer: A trigger is a predefined SQL script that executes automatically when an event occurs in a table.

15. How do you create a trigger in MySQL?

Answer:

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

16. What is a transaction in MySQL?

Answer: A transaction is a sequence of SQL queries that are executed as a single unit.

17. How do you use transactions in MySQL?

Answer:

START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

18. What is the difference between COMMIT and ROLLBACK?

Answer:

COMMIT saves the changes permanently.
ROLLBACK undoes the changes if an error occurs.

19. What is indexing in MySQL?

Answer: Indexing improves query performance by enabling faster data retrieval.

20. How do you create an index in MySQL?

Answer:

CREATE INDEX idx_email ON users (email);

21. What is the difference between clustered and non-clustered indexes?

Answer:

Clustered index sorts data rows physically in the table.
Non-clustered index creates a separate structure for fast searching.

22. How do you optimize a MySQL query?

Answer: Use indexing, avoid SELECT *, use proper joins, and use EXPLAIN to analyze queries.

23. What is the MySQL query cache?

Answer: Query cache stores query results to speed up repeated queries.

24. How do you enable MySQL query cache?

Answer:

SET GLOBAL query_cache_size = 1048576; SET GLOBAL query_cache_type = ON;

25. How do you check the execution time of a query in MySQL?

Answer:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

1. What is the difference between MyISAM and InnoDB storage engines?

Answer:

MyISAM: Does not support transactions or foreign keys, faster for read-heavy applications.
InnoDB: Supports transactions and foreign keys, uses row-level locking, better for high-concurrency applications.

2. What are the ACID properties in MySQL?

Answer:

Atomicity: A transaction is all or nothing.
Consistency: Ensures database integrity before and after transactions.
Isolation: Transactions execute independently.
Durability: Ensures completed transactions are saved permanently.

3. How do you handle deadlocks in MySQL?

Answer:

Use transactions carefully.
Use LOCK IN SHARE MODE or FOR UPDATE.
Avoid long-running transactions.
Use deadlock detection and retry mechanisms.

4. What is a foreign key in MySQL?

Answer: A foreign key is a constraint that links a column in one table to the primary key of another table.

5. How do you create a foreign key in MySQL?

Answer:

ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);

6. What is the difference between CHAR and VARCHAR in MySQL?

Answer:

CHAR has a fixed length (faster for fixed-length data).
VARCHAR has a variable length (saves space but slightly slower).

7. How do you handle duplicate records in MySQL?

Answer:

DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email );

8. How do you implement full-text search in MySQL?

Answer:

ALTER TABLE articles ADD FULLTEXT(title, content); SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL optimization');

9. What is the difference between BLOB and TEXT in MySQL?

Answer:

BLOB stores binary data (images, videos).
TEXT stores character data (long text).

10. What is sharding in MySQL?

Answer: Sharding is the process of splitting large databases into smaller, more manageable pieces to improve performance and scalability.

11. How do you perform a database partitioning in MySQL?

Answer:

CREATE TABLE orders ( id INT NOT NULL, order_date DATE NOT NULL, PRIMARY KEY (id, order_date) ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p0 VALUES LESS THAN (2023), PARTITION p1 VALUES LESS THAN (2024) );

12. What is the purpose of MySQL replication?

Answer: Replication allows data from one MySQL server (master) to be copied to another (slave) for backup, load balancing, or failover.

13. How do you enable binary logging in MySQL?

Answer:
Edit my.cnf:

[mysqld] log-bin=mysql-bin server-id=1

14. What is MySQL slow query log?

Answer: A log that records queries taking longer than a specified time to execute.

15. How do you enable the slow query log?

Answer:

SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 2;

16. How do you prevent SQL injection in MySQL?

Answer:

Use prepared statements.
Validate user input.
Use parameterized queries.

17. How do you use a CTE (Common Table Expression) in MySQL?

Answer:

WITH UserOrders AS ( SELECT user_id, COUNT(*) AS total_orders FROM orders GROUP BY user_id ) SELECT * FROM UserOrders WHERE total_orders > 5;

18. How do you improve MySQL query performance?

Answer:

Use indexes.
Optimize joins.
Avoid **SELECT ***.
Use EXPLAIN ANALYZE to debug queries.

19. What is the difference between a PRIMARY KEY and a UNIQUE KEY?

Answer:

PRIMARY KEY uniquely identifies each row and does not allow NULL values.
UNIQUE KEY ensures uniqueness but allows NULL values.

20. What is a covering index in MySQL?

Answer: A covering index is an index that contains all the columns required by a query, reducing disk access.

21. How do you create a JSON column in MySQL?

Answer:

CREATE TABLE users ( id INT PRIMARY KEY, details JSON );

22. How do you extract data from a JSON column?

Answer:

SELECT details->'$.address.city' AS city FROM users;

23. What is a MySQL stored function?

Answer: A stored function is a reusable SQL function that returns a value.

24. How do you create a stored function in MySQL?

Answer:

DELIMITER // CREATE FUNCTION get_total_orders(userId INT) RETURNS INT DETERMINISTIC BEGIN DECLARE total INT; SELECT COUNT(*) INTO total FROM orders WHERE user_id = userId; RETURN total; END // DELIMITER ;

25. How do you execute a stored function?

Answer:

SELECT get_total_orders(1);

1. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

Answer:

INNER JOIN: Returns only matching records from both tables.
LEFT JOIN: Returns all records from the left table and matching records from the right table.
RIGHT JOIN: Returns all records from the right table and matching records from the left table.
FULL JOIN: Returns all records from both tables (not available in MySQL, but can be simulated using UNION).

2. How does indexing improve MySQL query performance?

Answer:
Indexes speed up search queries by creating a sorted structure for quick lookups instead of scanning all rows.

3. How do you create an index in MySQL?

Answer:

CREATE INDEX idx_user_email ON users(email);

4. What is the difference between clustered and non-clustered indexes?

Answer:

Clustered Index: Determines the physical order of rows (MySQL’s PRIMARY KEY is clustered).
Non-Clustered Index: A separate structure that stores pointers to actual data.

5. How do you check the execution plan of a MySQL query?

Answer:
Use EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

6. What is a stored procedure in MySQL?

Answer:
A stored procedure is a reusable block of SQL that performs a specific task.

7. How do you create a stored procedure in MySQL?

Answer:

DELIMITER // CREATE PROCEDURE getUserOrders(IN userId INT) BEGIN SELECT * FROM orders WHERE user_id = userId; END // DELIMITER ;

8. How do you call a stored procedure?

Answer:

CALL getUserOrders(1);

9. What are MySQL triggers?

Answer:
Triggers are automatic actions executed before or after database events like INSERT, UPDATE, or DELETE.

10. How do you create a trigger in MySQL?

Answer:

DELIMITER // CREATE TRIGGER before_insert_orders BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; END IF; END // DELIMITER ;

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

Answer:

UNION: Combines results from multiple queries but removes duplicates.
UNION ALL: Combines results without removing duplicates (faster).

12. How do you perform pagination in MySQL?

Answer:

SELECT * FROM products LIMIT 10 OFFSET 20;

This fetches 10 rows, skipping the first 20.

13. How do you detect duplicate records in a MySQL table?

Answer:

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

14. How do you delete duplicate rows but keep one in MySQL?

Answer:

DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email );

15. How do you optimize a large table in MySQL?

Answer:

Use indexes.
Use partitioning.
Run OPTIMIZE TABLE.
Use ARCHIVE storage for historical data.

16. What is a subquery, and how is it used in MySQL?

Answer:
A subquery is a query inside another query. Example:

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

17. What is the difference between DELETE, TRUNCATE, and DROP in MySQL?

Answer:

DELETE: Removes specific rows with a WHERE clause, but logs transactions.
TRUNCATE: Removes all rows but keeps table structure, faster than DELETE.
DROP: Deletes the entire table structure and data.

18. How do you handle transactions in MySQL?

Answer:

START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

19. What is the purpose of MySQL views?

Answer:
A view is a virtual table representing a saved query.

20. How do you create a view in MySQL?

Answer:

CREATE VIEW ActiveUsers AS SELECT id, name FROM users WHERE status = 'active';

21. What is MySQL partitioning, and how does it work?

Answer:
Partitioning divides a large table into smaller, more manageable pieces based on a key.

22. What is a MySQL cursor, and when would you use it?

Answer:
A cursor allows row-by-row processing in stored procedures.

23. How do you create a MySQL cursor?

Answer:

DECLARE user_cursor CURSOR FOR SELECT name FROM users;

24. What are MySQL user-defined functions (UDFs)?

Answer:
UDFs allow custom functions in SQL.

25. What is MySQL XA Transactions?

Answer:
XA Transactions allow distributed transactions across multiple databases.

"Deep Concepts to Elevate Your Career"

This guide provides 100+ My Sql Interview questions along with in-depth concepts to strengthen your expertise.

Download

MYSQL Interview Questions and Answers

Top 100 MY SQL Interview Questions for Freshers

"Deep Concepts to Elevate Your Career"

This guide provides 100+ My Sql Interview questions along with in-depth concepts to strengthen your expertise.

Contact Us

IDM Techpark Erode
1st floor, 33/15 vasavi complex, nalli
hospital road, annamalai layout, near bus
stand, erode 638011

+91-95853-05700

idmtechpark@gmail.com

Software Courses

Full Stack Developer

MYSQL Interview Questions and Answers

Top 100 MY SQL Interview Questions for Freshers

"Deep Concepts to Elevate Your Career"

This guide provides 100+ My Sql Interview questions along with in-depth concepts to strengthen your expertise.

Contact Us

IDM Techpark Erode 1st floor, 33/15 vasavi complex, nalli hospital road, annamalai layout, near bus stand, erode 638011

+91-95853-05700

idmtechpark@gmail.com

Software Courses

Full Stack Developer

IDM Techpark Erode
1st floor, 33/15 vasavi complex, nalli
hospital road, annamalai layout, near bus
stand, erode 638011