MySQL Interview Questions and Answers for 2025

Question: What is a subquery in MySQL?

Answer:

A subquery in MySQL is a query nested inside another query. It allows you to perform a query within the context of another query, often to retrieve data that will be used in the outer query. Subqueries can be used in the SELECT, INSERT, UPDATE, or DELETE statements and can help with complex queries where a direct comparison or condition is needed.

A subquery is typically used to:

• Filter records based on the results of another query.
• Calculate aggregate values or perform operations that require a subquery’s result.
• Select values that will be used in a comparison or as a field in the outer query.

Types of Subqueries:

1. Single-Row Subqueries: Returns a single value (one row and one column).

   • These subqueries are used in situations where you expect a single value to be returned (e.g., comparing a column with a value).

2. Multiple-Row Subqueries: Returns multiple rows but only one column.

   • These are used when the subquery returns a list of values that can be compared with the outer query.

3. Multiple-Column Subqueries: Returns multiple rows and multiple columns.

   • Used when the subquery returns a result set with multiple columns, and the outer query needs to process all of them.

4. Correlated Subqueries: A subquery that depends on the outer query and references columns from the outer query. The subquery is executed once for each row selected by the outer query.

5. Uncorrelated Subqueries: A subquery that is independent of the outer query and can be executed on its own.

Syntax for Subqueries:

• In WHERE Clause: Used to filter records based on the result of a subquery.
• In FROM Clause: Used to provide a temporary table for the outer query.
• In SELECT Clause: Used to calculate an aggregated value or a derived column.

Examples:

1. Single-Row Subquery (Used in WHERE clause to compare a single value):

SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

• In this example, the inner query retrieves the department_id for the ‘Sales’ department, and the outer query uses that value to filter employees in the same department.

2. Multiple-Row Subquery (Used with IN):

SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

• The inner query returns multiple department_id values, and the outer query selects employees working in those departments.

3. Multiple-Column Subquery (Used in WHERE clause):

SELECT first_name, last_name
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

• The inner query returns multiple columns (department_id and the maximum salary per department), and the outer query selects employees who match both the department and the highest salary.

4. Correlated Subquery:

SELECT first_name, last_name
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

• In this example, the inner query is dependent on the outer query’s department_id column. The subquery is re-executed for each row selected by the outer query to compare the employee’s salary with the average salary in their department.

5. Subquery in FROM Clause (Derived Table):

SELECT temp.department_id, AVG(temp.salary) AS avg_salary
FROM (SELECT department_id, salary FROM employees WHERE salary > 50000) AS temp
GROUP BY temp.department_id;

• Here, the inner query selects employees with a salary greater than 50,000. The outer query calculates the average salary per department for this subset of employees.

Performance Considerations:

• Subqueries can be less efficient than joins, especially correlated subqueries, because the subquery is executed for each row selected by the outer query.
• Using JOIN instead of subqueries can often improve performance, particularly when the subquery is complex or involves large datasets.
• MySQL optimizes certain types of subqueries. In some cases, MySQL might rewrite subqueries as joins or merge operations for better performance.

Summary:

• A subquery is a query inside another query, used to retrieve or filter data that will be used by the outer query.
• Subqueries can be single-row, multiple-row, multiple-column, correlated, or uncorrelated depending on how they interact with the outer query.
• Subqueries are commonly used in the SELECT, WHERE, FROM, and HAVING clauses to perform complex operations or filter data dynamically.

Question: What are stored procedures and triggers in MySQL?

Answer:

Stored Procedures and Triggers are both database objects in MySQL that allow you to encapsulate business logic and automate certain tasks. They both involve executing SQL statements in response to specific events but differ in how and when they are executed.

---

Stored Procedures:

A Stored Procedure is a set of SQL statements that are precompiled and stored in the database. Once created, a stored procedure can be invoked (called) by an application or a user. Stored procedures allow for reusable code, better performance, and a centralized location for business logic.

Key Features of Stored Procedures:

• Encapsulation of Logic: Stored procedures can encapsulate complex SQL queries and operations, so they can be reused without needing to rewrite the SQL code each time.
• Parameters: They can accept input parameters, which makes them flexible for different use cases. You can also define output parameters.
• Improved Performance: Stored procedures are precompiled, meaning the query execution plan is cached, leading to faster execution for repetitive queries.
• Security: You can grant permissions on stored procedures, allowing users to execute certain operations without giving them direct access to the underlying tables.
• Transaction Management: Stored procedures can manage transactions (using BEGIN, COMMIT, and ROLLBACK), ensuring data consistency.

Syntax:

CREATE PROCEDURE procedure_name (parameters)
BEGIN
   -- SQL statements
END;

Example:

A stored procedure to insert a new employee:

CREATE PROCEDURE AddEmployee(IN emp_name VARCHAR(100), IN emp_salary DECIMAL(10, 2))
BEGIN
    INSERT INTO employees (name, salary) VALUES (emp_name, emp_salary);
END;

To call the stored procedure:

CALL AddEmployee('John Doe', 50000);

Benefits:

• Code Reusability: Once created, you can call the stored procedure multiple times.
• Modularization: Organizes logic into modules that can be maintained more easily.
• Error Handling: You can handle errors and exceptions within stored procedures.

---

Triggers:

A Trigger is a type of stored procedure that automatically executes (or “fires”) when a specific event occurs on a table or view. Triggers are defined to respond to insert, update, or delete operations on a table, and they can enforce business rules, data integrity, or other automatic behavior.

Key Features of Triggers:

• Automatic Execution: Triggers are executed automatically when a specified event occurs, without needing to be explicitly called.
• Event-Driven: Triggers are tied to specific events: INSERT, UPDATE, and DELETE. They can be set to execute before or after the event.
• Data Integrity: Triggers can be used to enforce complex data integrity rules or automatically update related data when changes are made to a table.
• Cannot Accept Parameters: Unlike stored procedures, triggers do not accept input parameters and are tied directly to events on a table.

Types of Triggers:

1. BEFORE Trigger: Executes before the event (e.g., INSERT, UPDATE, DELETE).
2. AFTER Trigger: Executes after the event.

Syntax:

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
BEGIN
   -- SQL statements
END;

Example:

A trigger that automatically updates the last_modified column when an employee’s record is updated:

CREATE TRIGGER update_employee_timestamp
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   UPDATE employees
   SET last_modified = NOW()
   WHERE employee_id = OLD.employee_id;
END;

• This trigger fires after an UPDATE on the employees table and updates the last_modified column to the current timestamp.

Benefits:

• Automatic Actions: Triggers automate repetitive tasks or enforce data consistency without needing to explicitly call them.
• Data Validation: Triggers can validate or modify data before or after an operation on a table.
• Auditing: You can create triggers to log changes to records for auditing purposes.

---

Key Differences Between Stored Procedures and Triggers:

Feature	Stored Procedures	Triggers
Execution	Explicitly called by users or applications.	Automatically executed based on events (INSERT, UPDATE, DELETE).
Parameters	Can accept input parameters and return output parameters.	Cannot accept parameters.
Use Case	For encapsulating logic that needs to be executed on demand.	For automating actions in response to changes in data (e.g., validation, auditing).
Event-Driven	Not event-driven. Needs to be called manually.	Event-driven. Automatically triggers on table events.
Complexity	Can contain complex logic, loops, and conditions.	Limited in complexity, designed for simple operations on data.
Error Handling	Supports explicit error handling using DECLARE and HANDLER.	Errors during trigger execution can lead to a failed operation but cannot be explicitly handled inside the trigger.

---

Use Case Examples:

1. Stored Procedure for Reusable Logic:

• A company wants to add employees in multiple departments with the same salary range. A stored procedure can be written to handle this repetitive insertion task.

2. Trigger for Enforcing Data Integrity:

• A BEFORE INSERT trigger can be used to ensure that no employee is inserted into the employees table with a salary lower than the minimum required value, thus enforcing a rule for valid data.

Example:

CREATE TRIGGER check_min_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   IF NEW.salary < 30000 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be less than 30,000';
   END IF;
END;

3. Trigger for Auditing:

• An AFTER DELETE trigger can be set up to log deleted records into an audit_log table, maintaining a history of changes made to the data.

Example:

CREATE TRIGGER log_deletion
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_log (action, employee_id, old_data, timestamp)
   VALUES ('DELETE', OLD.employee_id, CONCAT('Name: ', OLD.name, ', Salary: ', OLD.salary), NOW());
END;

Summary:

• Stored Procedures** are reusable blocks of SQL code that can be called manually, allowing encapsulation of logic, parameters, and complex operations. They are suitable for complex tasks and repetitive operations.
• Triggers are automatic SQL procedures that execute in response to certain database events, ensuring that certain actions are taken (like validation or logging) without user intervention. They are ideal for maintaining data integrity or automating tasks based on changes to a table.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as MySQL interview questions, MySQL interview experiences, and details about various MySQL job positions. Click here to check it out.