Top 25 MySQL Interview Questions and Answers (Detailed)

 

Introduction

MySQL is one of the most widely used open-source relational database systems, trusted by web developers, data engineers, and enterprises around the world. Whether you're preparing for an interview or enhancing your MySQL skills, mastering these key concepts will help you build secure, scalable, and high-performing applications. Below are the most commonly asked MySQL questions with clear and concise answers to help you understand the fundamentals and advanced features of the database.

1. What is normalization in MySQL, and why is it important?

Answer:
Normalization is a database design technique that organizes data into multiple related tables to reduce data redundancy and enhance data integrity. It aims to divide a large, complex table into smaller, well-structured tables by eliminating duplicate data. This process typically involves applying rules known as normal forms (First Normal Form, Second Normal Form, etc.).

Benefits of Normalization:

·         Reduces data duplication

·         Enhances data consistency

·         Improves query performance

·         Simplifies data management and updates

For example, instead of storing a customer's details repeatedly in every order record, normalization would place customer data in a separate table and link it to the orders via a foreign key.

---

2. What is a foreign key in MySQL, and how is it used?

Answer:
A foreign key in MySQL is a field (or collection of fields) in one table that uniquely identifies a row in another table. The foreign key establishes a link between the data in two tables. It is used to enforce referential integrity, ensuring that a record in one table corresponds to an existing record in another.

Usage Example:
In an Orders table, a customer_id column can act as a foreign key referencing the id column in a Customers table. This ensures that no order can be placed for a non-existent customer.

sql

CopyEdit

ALTER TABLE Orders

ADD CONSTRAINT fk_customer

FOREIGN KEY (customer_id) REFERENCES Customers(id);

Benefits:

·         Maintains consistent and accurate relationships

·         Prevents orphaned records

·         Simplifies data joins and queries

---

3. What is the difference between a stored procedure and a function in MySQL?

Answer:
Both stored procedures and functions are sets of SQL statements stored in the database and used to encapsulate logic.

Stored Procedure:

·         May or may not return a value.

·         Supports multiple OUT parameters.

·         Typically used for executing business logic, data manipulation, or complex operations.

·         Cannot be used in SQL expressions like SELECT.

Stored Function:

·         Must return a single value using the RETURN keyword.

·         Can be used in SQL statements such as SELECT, WHERE, or HAVING.

·         Commonly used for calculations or data transformations.

Example of Stored Function:

sql

CopyEdit

CREATE FUNCTION get_total_price(price DECIMAL, qty INT)

RETURNS DECIMAL

DETERMINISTIC

BEGIN

  RETURN price * qty;

END;

---

4. What is a transaction in MySQL, and how is it used?

Answer:
A transaction in MySQL is a group of SQL statements executed together so that they either all succeed or all fail. Transactions are used to ensure data consistency, especially in scenarios involving multiple operations.

Transaction Commands:

·         START TRANSACTION – begins a new transaction

·         COMMIT – saves all changes

·         ROLLBACK – undoes all changes since the last START TRANSACTION

Example:

sql

CopyEdit

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If one of the updates fails, you can call ROLLBACK to undo all changes, ensuring the system remains in a consistent state.

---

5. What is the difference between MyISAM and InnoDB storage engines in MySQL?

Answer:

Feature	MyISAM	InnoDB
Transactions	Not supported	Fully supported
Foreign Keys	Not supported	Supported
Locking	Table-level	Row-level
Crash Recovery	Limited	Automatic
Speed (Read-heavy)	Faster	Slightly slower

·         MyISAM is best suited for read-heavy applications like reporting or archival systems.

·         InnoDB is ideal for transactional applications such as banking or e-commerce, where consistency, integrity, and concurrency are important.

---

Would you like me to continue writing all 25 in this detailed format? I can deliver it in segments or provide the full document.

6. What is a trigger in MySQL, and how is it used?

Answer:
A trigger in MySQL is a predefined SQL block that automatically executes (or "fires") in response to specific events on a table, such as an INSERT, UPDATE, or DELETE operation.

Types of Triggers:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

Use Cases:

• Enforcing business rules
• Automatically logging changes (auditing)
• Validating or modifying data before changes are committed

Example:

sql

CopyEdit

CREATE TRIGGER before_insert_user

BEFORE INSERT ON users

FOR EACH ROW

SET NEW.created_at = NOW();

This trigger automatically sets the created_at timestamp before inserting a new user.

---

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

Answer:
A subquery is a query nested inside another SQL query. It is used to return data that will be used by the main query to filter, compare, or calculate values.

Types:

• Scalar subquery (returns a single value)
• Row subquery (returns a single row)
• Table subquery (returns multiple rows and columns)

Example:

sql

CopyEdit

SELECT name FROM employees

WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

The inner subquery returns the ID of the Sales department, and the outer query returns all employees in that department.

---

8. What is a join in MySQL, and how is it used?

Answer:
A JOIN in MySQL is used to retrieve data from two or more related tables based on a common column.

Common Types of Joins:

• INNER JOIN: Returns only matching rows from both tables.
• LEFT JOIN: Returns all rows from the left table and matched rows from the right.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left.
• FULL OUTER JOIN: Not natively supported by MySQL but can be simulated.

Example:

sql

CopyEdit

SELECT orders.id, customers.name

FROM orders

INNER JOIN customers ON orders.customer_id = customers.id;

This retrieves all orders with the corresponding customer names.

---

9. What is indexing in MySQL, and how is it used?

Answer:
An index is a performance-optimization feature that improves the speed of data retrieval. It works like a lookup table that allows MySQL to find rows faster without scanning the entire table.

Common Index Types:

• Single-column index
• Multi-column (composite) index
• Unique index
• Full-text index

Example:

sql

CopyEdit

CREATE INDEX idx_customer_name ON customers(name);

This creates an index on the name column, speeding up queries that search or sort by name.

Benefits:

• Faster SELECT queries
• Better JOIN performance
• Enforced uniqueness with unique indexes

---

10. What is a cursor in MySQL, and how is it used?

Answer:
A cursor is a database object used to retrieve and manipulate query results row by row. It's mostly used in stored procedures where row-by-row operations are required.

Steps to Use a Cursor:

1. Declare the cursor with a SQL SELECT statement.
2. Open the cursor.
3. Fetch rows from the cursor.
4. Close the cursor.

Example:

sql

CopyEdit

DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT id FROM users;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

OPEN cur;

read_loop: LOOP

  FETCH cur INTO user_id;

  IF done THEN

    LEAVE read_loop;

  END IF;

  -- process each user_id here

END LOOP;

CLOSE cur;

Note: Cursors should be used sparingly due to performance considerations.

11. What is a temporary table in MySQL, and how is it used?

Answer:
A temporary table is a special kind of table that exists only for the duration of a database session or a transaction. Once the session ends or the connection closes, the temporary table is automatically dropped.

Use Cases:

• Storing intermediate results during complex calculations or data processing
• Simplifying queries by breaking them down into manageable steps
• Reducing locking and concurrency issues on large base tables

How to Create:

sql

CopyEdit

CREATE TEMPORARY TABLE temp_sales AS

SELECT * FROM sales WHERE sale_date > '2025-01-01';

This creates a temporary table temp_sales which can be used in subsequent queries within the same session.

---

12. What is the difference between a view and a table in MySQL?

Answer:

• Table: A physical database object that stores data permanently on disk. Tables hold actual data and support INSERT, UPDATE, DELETE, and SELECT operations.
• View: A virtual table defined by a SELECT query. Views do not store data themselves but present data dynamically from underlying tables.

Key Differences:

• Views simplify complex queries by hiding the underlying join or filter logic.
• Views can restrict access to sensitive columns or rows.
• Views are generally read-only, although updatable views exist under certain conditions.
• Tables occupy disk space, whereas views do not.

Example of creating a view:

sql

CopyEdit

CREATE VIEW active_customers AS

SELECT id, name, email FROM customers WHERE status = 'active';

---

13. What is a deadlock in MySQL, and how is it resolved?

Answer:
A deadlock occurs when two or more transactions block each other by holding locks on resources the others need. This situation causes a standstill where neither transaction can proceed.

How Deadlocks Happen:

• Transaction A locks row 1 and wants row 2.
• Transaction B locks row 2 and wants row 1.
• Both transactions wait indefinitely for each other.

Resolution:

• MySQL automatically detects deadlocks and rolls back one of the conflicting transactions to break the deadlock.
• Developers can minimize deadlocks by:
• Accessing tables and rows in a consistent order
• Keeping transactions short and efficient
• Using appropriate isolation levels

---

14. What is a full-text search in MySQL, and how is it used?

Answer:
Full-text search allows searching text columns for words or phrases with relevance ranking, rather than exact matching like the LIKE operator.

Features:

• Supports natural language searching and Boolean operators (AND, OR, NOT).
• Uses indexes optimized for fast text retrieval.

How to Use:

• Create a full-text index on one or more text columns:

sql

CopyEdit

CREATE FULLTEXT INDEX ft_index ON articles(content);

• Query using MATCH() and AGAINST():

sql

CopyEdit

SELECT * FROM articles WHERE MATCH(content) AGAINST('database optimization');

Full-text searches return results ranked by relevance score, allowing more meaningful search results.

---

15. What is a materialized view in MySQL, and how is it used?

Answer:
A materialized view is a physical copy of a query result stored like a table, which can be refreshed periodically. Unlike regular views, materialized views store data, so queries run faster at the expense of storage and the need for refreshing data.

Use Cases:

• Optimizing complex queries or reports that are costly to run repeatedly
• Caching aggregated or joined data for quick access

Note:
As of now, MySQL doesn’t natively support materialized views, but they can be simulated by creating tables and refreshing them with scheduled events or triggers.

Example Approach:

sql

CopyEdit

CREATE TABLE sales_summary AS

SELECT product_id, SUM(amount) AS total_sales

FROM sales

GROUP BY product_id;

 

-- Refresh the table periodically via a scheduled event or application logic

 

16. What is the difference between MyISAM and InnoDB storage engines in MySQL?

Answer:
MyISAM and InnoDB are two widely used storage engines in MySQL, each with its strengths and weaknesses.

• MyISAM:
• Default storage engine in older MySQL versions.
• Does not support transactions or foreign keys.
• Uses table-level locking, which can reduce concurrency on write-heavy operations.
• Supports full-text indexing, making it suitable for read-heavy applications like data warehousing or simple websites.
• Faster for read operations but less reliable for critical data integrity.
• InnoDB:
• Default storage engine in modern MySQL versions.
• Supports transactions with ACID compliance (Atomicity, Consistency, Isolation, Durability).
• Supports foreign keys and referential integrity.
• Uses row-level locking, improving performance in high-concurrency environments.
• Provides crash recovery and better data integrity.
• Slightly slower than MyISAM on simple read queries but overall more robust.

Choosing between them depends on your application's needs: use InnoDB for transaction-heavy or data-critical apps and MyISAM for simple, read-heavy workloads.

---

17. What is a stored procedure in MySQL, and how is it used?

Answer:
A stored procedure is a precompiled set of SQL statements stored in the database. It can be executed as a single call, allowing complex operations to be encapsulated and reused.

Benefits:

• Improves performance by reducing network traffic between client and server.
• Encapsulates business logic inside the database.
• Enhances security by restricting direct access to tables.
• Supports input and output parameters for dynamic processing.

Example:

sql

CopyEdit

CREATE PROCEDURE GetCustomerOrders(IN customerId INT)

BEGIN

  SELECT * FROM orders WHERE customer_id = customerId;

END;

You can call this procedure using:

sql

CopyEdit

CALL GetCustomerOrders(101);

---

18. What is the purpose of the EXPLAIN command in MySQL, and how is it used?

Answer:
The EXPLAIN command helps analyze how MySQL executes a query. It provides a detailed execution plan showing:

• The order tables are accessed.
• The types of joins used.
• Which indexes are utilized.
• The estimated number of rows examined.

This insight helps developers optimize queries by identifying bottlenecks like full table scans or missing indexes.

Usage Example:

sql

CopyEdit

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

The output includes columns like type, key, and rows, which indicate how MySQL plans to execute the query.

---

19. What is a database transaction in MySQL, and why is it important?

Answer:
A transaction is a sequence of one or more SQL statements executed as a single atomic unit. It guarantees that all operations succeed or none do, ensuring data consistency.

Key Properties (ACID):

• Atomicity: All changes happen or none.
• Consistency: Data remains valid after the transaction.
• Isolation: Transactions don't interfere with each other.
• Durability: Once committed, changes persist even after failures.

Use Case:
When transferring money between accounts, transactions ensure debit and credit operations either both succeed or both fail, preventing inconsistent states.

Basic Transaction Commands:

sql

CopyEdit

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If an error occurs, you can rollback:

sql

CopyEdit

ROLLBACK;

---

20. What is a foreign key in MySQL, and how is it used?

Answer:
A foreign key is a constraint that enforces a relationship between columns in two tables. It ensures that the value in the child table matches a valid value in the parent table's primary key.

Purpose:

• Maintains referential integrity by preventing orphan records.
• Ensures consistency between related tables.
• Helps enforce business rules at the database level.

Example:

sql

CopyEdit

CREATE TABLE orders (

  order_id INT PRIMARY KEY,

  customer_id INT,

  FOREIGN KEY (customer_id) REFERENCES customers(id)

);

Here, customer_id in orders must exist in customers.id. If you try to insert an order with a non-existent customer, MySQL will reject it.

21. What are transactions in MySQL, and how are they used?

Answer:
Transactions in MySQL are a sequence of one or more SQL operations executed as a single, indivisible unit of work. They ensure data integrity by guaranteeing that either all operations within the transaction succeed (commit) or none do (rollback).

Why use transactions?

• To maintain consistency in complex operations, such as transferring funds or updating multiple related tables.
• To handle errors gracefully by rolling back incomplete operations.
• To ensure the database adheres to the ACID properties.

Usage example:

sql

CopyEdit

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

If something goes wrong, use:

sql

CopyEdit

ROLLBACK;

This prevents partial data changes that could corrupt your data.

---

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

Answer:
A subquery is a query nested inside another SQL query. It retrieves data that the outer query uses for filtering, comparison, or computation.

Types of subqueries:

• Scalar subquery: Returns a single value.
• Row subquery: Returns a single row.
• Table subquery: Returns multiple rows and columns.

Where are subqueries used?

• In SELECT, WHERE, and FROM clauses.
• To filter results based on data from another table.
• To calculate aggregates or check existence.

Example:

sql

CopyEdit

SELECT name FROM customers

WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

This returns customers who have orders with totals over 1000.

---

23. What is replication in MySQL, and how is it used?

Answer:
Replication is the process of copying data from one MySQL server (master) to one or more servers (slaves) in real-time.

Benefits:

• Improves data availability and fault tolerance.
• Enhances scalability by offloading read operations to slave servers.
• Supports backup and disaster recovery strategies.

How it works:

• The master logs all data changes in a binary log.
• Slaves read this log and apply changes to their copies.
• Replication can be asynchronous or semi-synchronous.

Use cases:

• Load balancing reads.
• Creating reporting or backup servers.
• Geo-distributed databases.

---

24. What is a stored procedure in MySQL, and how is it used?

Answer:
A stored procedure is a precompiled collection of SQL statements stored in the database. It allows for modular programming, encapsulating complex logic that can be reused multiple times.

Advantages:

• Reduces client-server communication.
• Improves performance by precompiling SQL.
• Enhances security by restricting direct table access.
• Supports input/output parameters for flexibility.

Example:

sql

CopyEdit

CREATE PROCEDURE AddNewCustomer(IN name VARCHAR(50), IN email VARCHAR(50))

BEGIN

  INSERT INTO customers (name, email) VALUES (name, email);

END;

You can call it with:

sql

CopyEdit

CALL AddNewCustomer('John Doe', 'john@example.com');

---

25. What is a view in MySQL, and how is it used?

Answer:
A view is a virtual table based on the result of a SELECT query. It doesn’t store data physically but presents data from one or more tables as a single, simplified interface.

Uses:

• Simplifies complex queries by hiding joins and filters.
• Provides controlled access to sensitive data.
• Offers consistent, reusable query logic.
• Can aggregate or format data without altering underlying tables.

Example:

sql

CopyEdit

CREATE VIEW ActiveCustomers AS

SELECT id, name, email FROM customers WHERE status = 'active';

You can query the view as if it were a table:

sql

CopyEdit

SELECT * FROM ActiveCustomers;