MySQL Interview Questions and Answers for Freshers and Experienced Developers
MySQL is one of the most popular open-source relational database management systems (RDBMS) used worldwide for developing dynamic and data-driven web applications. Whether you are a fresher preparing for your first database-related interview or an experienced professional aiming to refresh your skills, understanding MySQL Interview Questions and Answers concepts is essential. Below are some of the most commonly asked MySQL interview questions and detailed answers to help you prepare effectively.
MySQL is an open-source relational database management system developed by Oracle. It uses Structured Query Language (SQL) to manage and manipulate data stored in tables. MySQL is widely used for web-based applications, especially those built with PHP, Laravel, and WordPress.
2. What are the advantages of using MySQL?
Open-source and free to use.
High performance and scalability.
Secure and reliable with data encryption options.
Large community support and extensive documentation.
A database is an organized collection of data that can be easily accessed, managed, and updated. MySQL stores data in tables, which consist of rows and columns.
4. What is a primary key?
A primary key is a unique identifier for each record in a table. It ensures that no two rows have the same value in the primary key column.
Example:CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT );
5. What is a foreign key?
A foreign key is used to link two tables together. It enforces referential integrity by ensuring that the value in one table matches the value in another.
Example:CREATE TABLE orders ( order_id INT PRIMARY KEY, student_id INT, FOREIGN KEY (student_id) REFERENCES students(id) );
6. What is normalization?
Normalization is the process of organizing data in a database to reduce redundancy and improve efficiency. The main normal forms include:
1NF: Eliminate repeating groups.
2NF: Remove partial dependencies.
3NF: Remove transitive dependencies.
7. What is denormalization?
Denormalization is the process of combining normalized tables to improve query performance. It sacrifices some data redundancy to speed up data retrieval.
8. What is the difference between CHAR and VARCHAR?
CHAR stores fixed-length strings.
VARCHAR stores variable-length strings.
Example: CHAR(10) will always store 10 characters, while VARCHAR(10) can store up to 10 characters.
9. What is the difference between DELETE, TRUNCATE, and DROP?
DELETE: Removes rows from a table based on a condition.
TRUNCATE: Removes all rows but keeps the table structure.
DROP: Removes the entire table structure from the database.
10. What are joins in MySQL?
A JOIN is used to combine rows from two or more tables based on a related column.
Types of Joins:
FULL JOIN (simulated in MySQL using UNION)
Example:SELECT students.name, orders.order_id FROM students INNER JOIN orders ON students.id = orders.student_id;
11. What is the difference between WHERE and HAVING clauses?
WHERE is used to filter rows before grouping.
HAVING is used to filter records after grouping (with aggregate functions).
Example:SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
12. What are indexes in MySQL?
Indexes are used to speed up the retrieval of data from tables. However, they may slow down insert or update operations because the index must also be updated.
Example:CREATE INDEX idx_name ON students(name);
13. What is a stored procedure?
A stored procedure is a set of SQL statements stored in the database that can be executed as a single unit.
Example:DELIMITER // CREATE PROCEDURE GetStudents() BEGIN SELECT * FROM students; END // DELIMITER ;
14. What is a trigger in MySQL?
A trigger is an automatic action executed in response to certain database events (INSERT, UPDATE, DELETE).
Example:CREATE TRIGGER before_student_insert BEFORE INSERT ON students FOR EACH ROW SET NEW.name = UPPER(NEW.name);
15. What is the difference between MyISAM and InnoDB?
MyISAM: Does not support transactions or foreign keys, faster for read-heavy operations.
InnoDB: Supports transactions, foreign keys, and row-level locking; preferred for most modern applications.
16. How to find duplicate records in MySQL?
SELECT name, COUNT(*) FROM students GROUP BY name HAVING COUNT(*) > 1;
17. What is a transaction in MySQL?
A transaction is a sequence of SQL operations performed as a single unit.
ACID properties: Atomicity, Consistency, Isolation, Durability.
Example:START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
18. What are views in MySQL?
A view is a virtual table created from the result of an SQL query. It does not store data but displays data from one or more tables.
Example:CREATE VIEW student_orders AS SELECT s.name, o.order_id FROM students s JOIN orders o ON s.id = o.student_id;
19. What is the default port number for MySQL?
The default port number is 3306.
20. How can you optimize a MySQL query?
Analyze query execution with EXPLAIN.
Limit the use of subqueries; use joins instead.
Normalize tables to eliminate redundancy.
MySQL is a fundamental skill for any backend or full-stack developer. Preparing for interviews with these commonly asked MySQL questions and answers will help you understand the core concepts and perform confidently in technical rounds.
Contact Address:
๐ G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India
๐ง [email protected]
๐ +91-9599086977
