Master MySQL Interview Questions

Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) known for its reliability, performance, and ease of use. Developed by MySQL AB and now owned by Oracle Corporation, MySQL has been essential to modern web applications since its release in 1995. Its robust architecture ensures stability and efficiency, making it a popular choice for developers and organizations. With extensive support for SQL, MySQL integrates seamlessly with various programming languages and platforms, handling everything from small projects to large enterprise environments. Its active community and comprehensive documentation make it accessible and continuously evolving.

Table of Contents

• Junior-Level MySQL Interview Questions
• Mid-Level MySQL Interview Questions
• Expert-Level MySQL Interview Questions
• Proven Strategies and Best Practices for MySQL Interview

Here are some junior-level interview questions for MySQL:

Question 01: What is MySQL?

Answer: MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). It is widely used for managing and organizing data in databases, and is known for its speed, reliability, and ease of use. MySQL supports various storage engines and allows for the creation, deletion, and manipulation of databases and tables, making it a popular choice for web applications, data warehousing, and logging applications.

Developed and maintained by Oracle Corporation, MySQL is commonly used with PHP to build dynamic websites and applications. It supports a range of operating systems and provides features such as data security, scalability, and high performance. MySQL also offers robust transactional support, ACID compliance, and support for stored procedures, triggers, and views, making it a versatile tool for developers and database administrators.

Question 02: Write a SQL query to find all records from a table named employees where the age is greater than 30.

Answer: Below is the SQL query to find all records from a table named employees where the age is greater than 30:

SELECT * FROM employees WHERE age > 30;
 

Question 03: How do you create a new database in MySQL?

Answer: You can create a new database in MySQL using the CREATE DATABASE statement. This command initializes a new database with the specified name. For example:

CREATE DATABASE database_name;

Question 04: What will be the output of the following SQL query?

SELECT CONCAT('Hello', ' ', 'World') AS greeting;

Answer: The output will be:

greeting
----------
Hello World

Question 05: Find the error in the below SQL code:

SELECT * FROM orders WHERE order_date = '2024-07-12;

Answer: The string literal for the date is not properly closed. The corrected code is:

SELECT * FROM orders WHERE order_date = '2024-07-12';

Question 06: What is the purpose of the AUTO_INCREMENT attribute in MySQL?

Answer: The AUTO_INCREMENT attribute is used to generate a unique identity for new rows in a table. When a column is defined with the AUTO_INCREMENT attribute, MySQL automatically increments the value of that column by one each time a new row is inserted.

This is particularly useful for primary key columns where each row needs a unique identifier. The AUTO_INCREMENT attribute simplifies the process of ensuring that each row has a distinct and sequential value, without requiring manual input from the user.

Question 07: Write a SQL query to update the salary of an employee with employee_id = 5 to 60000 in the employees table.

Answer:

UPDATE employees SET salary = 60000 WHERE employee_id = 5;

Question 08: What is a primary key in MySQL, and why is it important?

Answer: A primary key is a unique identifier for a row within a table. It ensures that each row can be uniquely identified and helps enforce entity integrity. Primary keys must contain unique values and cannot contain NULLs. The primary key constraint is crucial for maintaining the integrity and reliability of the database.

It allows for efficient indexing, which improves the speed of data retrieval operations. Additionally, primary keys are often referenced by foreign keys in other tables, enabling the establishment of relationships between tables and ensuring referential integrity within the database.

Question 09: Predict the output of the following SQL query:

SELECT IF(1 < 2, 'True', 'False') AS result;

Answer: The output will be:

result
------
True

Question 10: Explain the difference between INNER JOIN and LEFT JOIN.

Answer: An INNER JOIN returns only the rows from both tables that match the join condition, meaning it excludes rows that do not have matching values in both tables. In contrast, a LEFT JOIN returns all rows from the left table and the matched rows from the right table.

If there are no matches in the right table, the result is NULL for the right table's columns. This difference makes LEFT JOIN useful when you want to include all records from the left table, regardless of whether there are matching rows in the right table.

Here are some mid-level interview questions for MySQL:

Question 01: Explain the concept of a foreign key in MySQL.

Answer: A foreign key is a field (or collection of fields) in one table that uniquely identifies a row in another table. The purpose of the foreign key is to ensure referential integrity between the two tables. It establishes a link between the data in the two tables, enforcing that the values in the foreign key column(s) must match values in the primary key column(s) of the referenced table.

Foreign keys help maintain the consistency and integrity of the data by preventing invalid data entries and ensuring that relationships between tables are preserved.

Question 02: Write a SQL query to retrieve the names of all employees who work in the 'Sales' department and have a salary greater than 50000. Assume there are two tables: employees (with columns employee_id, name, department_id, salary) and departments (with columns department_id, department_name).

Answer:

SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales' AND e.salary > 50000;

Question 03: What is the difference between UNION and UNION ALL in MySQL?

Answer: The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows from the final result set. UNION ALL, on the other hand, also combines the result sets of multiple SELECT statements but does not remove duplicates, returning all rows, including duplicates.

UNION ALL is generally faster than UNION because it does not perform the additional step of removing duplicates.

Question 04: Predict the output of the following SQL query.

SELECT name, COALESCE(middle_name, 'N/A') AS middle_name FROM employees;

Answer: This query selects the name and middle_name columns from the employees table. If the middle_name column is NULL for any row, it will be replaced with 'N/A' in the result set. For example, if an employee's middle name is NULL, the output will show 'N/A' instead.

Question 05: Find the error in the below SQL code.

INSERT INTO employees (employee_id, name, department_id, salary)
VALUES (NULL, 'John Doe', 3, 50000);
    

Answer: If the employee_id column is defined as an AUTO_INCREMENT column, you should not explicitly insert NULL into it. Instead, use the default AUTO_INCREMENT behavior by omitting the employee_id value:

INSERT INTO employees (name, department_id, salary)
VALUES ('John Doe', 3, 50000);

Question 06: Explain the purpose and usage of the GROUP BY clause in MySQL.

Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, like "finding the total sales for each year" or "the number of employees in each department." It is often used in conjunction with aggregate functions (such as COUNT, SUM, AVG, MAX, MIN) to perform calculations on each group of rows. For example, to count the number of employees in each department, you would use:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

Question 07: Write a SQL query to delete all records from the orders table where the order_date is older than January 1, 2022.

Answer:

DELETE FROM orders WHERE order_date < '2022-01-01';
    

Question 08: What is the difference between DELETE and TRUNCATE statements in MySQL?

Answer: Both DELETE and TRUNCATE statements are used to remove records from a table. However, DELETE removes rows one at a time and logs each deletion, allowing for a WHERE clause to specify which rows to remove.

TRUNCATE removes all rows from a table without logging individual row deletions, which makes it faster but also means that it cannot be rolled back in many storage engines. Additionally, TRUNCATE resets any AUTO_INCREMENT counters on the table.

Question 09: What will be the output of the following SQL query?

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Answer: This query calculates the average salary for each department and returns only those departments where the average salary is greater than 60000. The result will include department_id and avg_salary for each qualifying department.

Question 10: Find the error in the following SQL query and provide the corrected version:

SELECT * FROM employees WHERE name LIKE 'A% ORDER BY id;

Answer: The LIKE clause is missing a closing single quote. SQL syntax errors like missing quotes are common and can cause queries to fail.Corrected Version:

SELECT * FROM employees WHERE name LIKE 'A%' ORDER BY id;

Here are some expert-level interview questions for MySQL:

Question 01: Explain the use of WITH RECURSIVE in MySQL and provide an example.

Answer: The WITH RECURSIVE clause in MySQL is used to create Common Table Expressions (CTEs) that can reference themselves, allowing for the processing of hierarchical or tree-structured data. Recursive CTEs are particularly useful for queries involving organizational charts, bill of materials, or any data that has a parent-child relationship.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

Question 02: What are window functions in MySQL, and how do they differ from aggregate functions? Provide an example.

Answer: Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which return a single value for a set of rows, window functions do not cause rows to become grouped into a single output row—the rows retain their separate identities.

SELECT
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS SalaryRank
FROM employees;

Question 03: How would you optimize a query that has performance issues due to a large IN clause?

Answer: Optimizing a query with a large IN clause can involve several strategies:

• Using JOIN instead of IN: Replace the IN clause with a JOIN, which can be more efficient.
• Ensure that the columns involved in the IN clause are indexed.
• Use temporary tables to store the values and join them with the main table.
• If applicable, rewrite the IN clause as a subquery with proper indexing.

Question 04: Describe how you would use EXPLAIN to analyze and optimize a query in MySQL.

Answer: The EXPLAIN statement provides insight into how MySQL executes a query, which can be used to diagnose performance issues. EXPLAIN outputs details such as the order of table reads, join types, possible keys, key lengths, and number of rows examined.

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

Question 05: What is the difference between REPEATABLE READ and SERIALIZABLE isolation levels in MySQL?

Answer: In MySQL, the REPEATABLE READ isolation level ensures that if a transaction reads the same row twice, it will receive the same values each time, thus preventing non-repeatable reads and dirty reads. However, it does not prevent other transactions from inserting new rows that match the WHERE clause of the query, leading to potential phantom reads.

On the other hand, the SERIALIZABLE isolation level is the highest isolation level, ensuring complete isolation from other transactions. It achieves this by locking the entire range of rows that any query touches, thus preventing phantom reads, dirty reads, and non-repeatable reads. However, this level of isolation can lead to significant performance overhead due to increased locking.

Question 06: What will be the output of the following query and why?

SELECT COALESCE(NULL, 2, NULL, 3) AS Result;
        

Answer: The COALESCE function returns the first non-NULL value in the list of arguments. In this case, it returns 2 because it is the first non-NULL value encountered.

Result
------
2    

Question 07: What is the purpose of foreign key constraints in MySQL, and how do you create them?

Answer: Foreign key constraints enforce referential integrity between tables. They ensure that a value in one table corresponds to a valid value in another table, preventing orphaned records and maintaining data consistency.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Question 08: How can you handle large data migrations in MySQL to minimize downtime?

Answer: Handling large data migrations in MySQL requires strategies to minimize downtime and ensure data consistency. Some techniques include:

• Perform the migration in small batches to minimize impact on the database.
• Use replication to create a copy of the database and perform the migration on the replica, then switch over.
• Use tools like pt-online-schema-change from Percona or gh-ost from GitHub to perform schema changes without downtime.
• Implement techniques like blue-green deployment or canary releases to ensure continuous availability.

Question 09: Explain how to perform a full-text search in MySQL.

Answer: Full-text search in MySQL allows for sophisticated text searching within text columns. It is particularly useful for searching large volumes of textual data. Full-text indexes can be created on CHAR, VARCHAR, or TEXT columns, and the MATCH and AGAINST operators are used for searching.

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
);

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('database optimization');        

Question 10: Describe how MySQL handles transactions and the role of the InnoDB storage engine in ensuring ACID compliance.

Answer: In MySQL, a transaction is a sequence of SQL operations executed as a single unit of work, ensuring that either all operations succeed or none do, thus preserving data integrity and consistency. If any operation fails, the entire transaction can be rolled back to maintain a consistent database state.

The InnoDB storage engine supports transactions and ensures ACID compliance. It guarantees Atomicity by committing or rolling back all operations as a unit, Consistency by keeping the database in a valid state, Isolation by ensuring transactions do not interfere with each other, and Durability by making sure committed transactions persist through system failures.