45 MySQL Interview Questions in 2024 [Basic to Advanced]

My SQL holds a strong second position with a score of 1111.49 in the most popular relational database management system (RDBMS) worldwide. It was originally founded by MySQL AB as an open-source solution and was later acquired by Oracle Corporation. Despite being under the same umbrella, MySQL retains its open-source nature and separate product line from Oracle’s commercial database offerings.

Given the widespread use of MySQL by organizations like GitHub, Airbnb, and Uber for their data-related solutions, you’ll likely encounter questions about MySQL during data and tech interview rounds.

As a candidate in the data science and analytics field, you’ve come to the right place to find SQL and MySQL-based questions from beginner to advanced. We’ve compiled a list of 45 MySQL interview questions and answers we believe you’ll find intriguing.

Here are a few basic MySQL interview questions to warm you up:

1. What is the difference between a DBMS and an RDBMS?

“A DBMS (database management system) is software that manages databases. An RDBMS (relational database management system) is a type of DBMS that uses a relational model to organize data into tables with rows and columns and supports SQL for database access.”

2. Explain the ACID properties of a transaction in a database.

“ACID stands for atomicity, consistency, isolation, and durability. Atomicity ensures all operations within a transaction are completed; if not, the transaction is aborted. Consistency ensures the database remains in a valid state before and after the transaction. Isolation ensures transactions are securely and independently processed without interference. Durability ensures that once a transaction is committed, it remains so, even in the event of a system failure.”

3. Describe the difference between a primary key and a unique constraint.

“A primary key uniquely identifies each record in a table and cannot be NULL, ensuring entity integrity. A unique constraint also ensures all values in a column or a set of columns are distinct across rows, but unlike the primary key, it allows one NULL value.”

4. You’re getting a “Lost connection to MySQL server” error. What steps would you take to diagnose the issue?

*“I would start by checking the MySQL server status to ensure it’s running. Then, I would examine the MySQL error logs for any clues. Next, I would verify the network connectivity and firewall settings between the client and server. I would then review the MySQL configuration parameters such as max_allowed_packet and wait_timeout to ensure they are set appropriately.”*

5. What are the different types of SQL joins (e.g., INNER JOIN, LEFT JOIN, etc.)? Briefly explain their functionalities.

“INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right table; if no match, NULLs are returned for columns from the right table. RIGHT JOIN is the opposite of LEFT JOIN, returning all rows from the right table and matched rows from the left table. FULL OUTER JOIN returns rows when there is a match in one of the tables, returning NULLs for non-matching rows from either table.”

6. How would you optimize a slow SELECT query in MySQL? Explain your approach.

“To optimize a slow MySQL query, I’d first analyze the query itself and its execution plan using EXPLAIN. This helps identify bottlenecks like missing indexes or inefficient WHERE clauses. Then, I can optimize with targeted actions like creating indexes or refining the query to leverage existing ones. Finally, I’d test and monitor to ensure the optimization holds under the real workload.”

7. Explain the benefits of using indexes in MySQL and how they improve performance.

“Indexes significantly enhance query performance by reducing the amount of data the database engine needs to scan to find the results. They allow faster data retrieval using binary search or other efficient lookup algorithms. Proper indexing can lead to reduced I/O operations, lower CPU usage, and faster query execution times.”

8. How can you secure a MySQL database against unauthorized access? Discuss different methods.

“Securing a MySQL database involves several measures:

1. Setting strong passwords for all MySQL users.
2. Configuring the MySQL server to listen only on localhost if remote access isn’t required.
3. Using SSL/TLS for encrypted connections.
4. Implementing firewall rules to restrict access.
5. Regularly updating MySQL to patch security vulnerabilities.
6. Granting minimal privileges necessary for each user using the principle of least privilege.
7. *Enabling MySQL’s built-in security features like the mysql_secure_installation script.”*

9. Differentiate between the SELECT * and specifying column names in a SELECT statement.

*“Using SELECT * retrieves all columns from a table, which can lead to inefficiencies if only a subset of columns is needed. Specifying column names in a SELECT statement retrieves only the required data, improving performance and reducing network load.”*

10. Explain the concept of a subquery in SQL.

“A subquery, also known as an inner query or nested query, is a query within another SQL query. It is used to perform operations in multiple steps, often providing a result set that the outer query can use for filtering, comparison, or aggregation.”

11. Describe stored procedures in MySQL and their advantages over regular SQL statements.

“Stored procedures in MySQL are precompiled SQL codes stored in the database, which can be executed with a single call. Advantages include improved performance through precompilation, reduced network traffic by executing multiple statements in one call, enhanced security by encapsulating business logic, and maintainability through modularization of repetitive tasks.”

12. What are some security best practices to prevent SQL injection attacks?

“To prevent SQL injection attacks, employ parameterized queries or prepared statements to separate SQL code from user input. Validate and sanitize input data to ensure it conforms to expected formats. Use the least privilege principles by granting minimal database permissions to users. Regularly update and patch database software to mitigate known vulnerabilities. Implement web application firewalls (WAFs) and input validation techniques to filter out malicious inputs.”

13. Describe the functionalities of the WHERE and HAVING clauses in a SELECT statement.

“The WHERE clause filters rows based on specified criteria before the query retrieves data from the database. It restricts the result set to only those rows that meet the conditions specified. The HAVING clause, on the other hand, filters rows based on aggregated values, such as those returned by GROUP BY clauses. It applies conditions to groups created by the GROUP BY clause and filters the groups based on the specified criteria.”

14. What is the difference between DELETE and TRUNCATE statements?

“The DELETE statement removes rows from a table based on specified conditions, allowing for selective deletion. It also generates transaction logs and triggers, providing the possibility of rolling back changes. The TRUNCATE statement removes all rows from a table, resetting auto-incremented columns and deallocating storage space. Unlike DELETE, TRUNCATE is not transactional and cannot be rolled back. Additionally, it resets identity columns to their seed value.”

15. What is MySQL replication, and how can it be used for disaster recovery?

“MySQL replication is a process where data from one MySQL server (master) is copied to one or more MySQL servers (slaves). It supports disaster recovery by providing real-time data redundancy, allowing failover to a replica if the master fails, and enabling load balancing for read operations across multiple servers.”

Intermediate MySQL Interview Questions and Answers

Let’s now move on to the intermediate-level MySQL questions designed exclusively for data science domain candidates.

16. Let’s say you work at Uber. The rides table contains information about the trips of Uber users across America.

Write a query to get the average commute time (in minutes) for each commuter in New York (NY) and the average commute time (in minutes) across all commuters in New York.

Example:

Input:

**rides** table

Column	Type
id	INTEGER
commuter_id	INTEGER
start_dt	DATETIME
end_dt	DATETIME
city	VARCHAR

Output:

Column	Type
commuter_id	INTEGER
avg_commuter_time	FLOAT
avg_time	FLOAT

Display results like:

commuter_id	avg_commuter_time	avg_time
11	27	45
22	97	45
33	11	45

Answer:

SELECT
  a.commuter_id,
  a.avg_commuter_time,
  b.avg_time
FROM (
  SELECT
    commuter_id,
    city,
    FLOOR(AVG(TIMESTAMPDIFF(MINUTE, start_dt, end_dt))) AS avg_commuter_time
  FROM rides
  WHERE city = 'NY'
  GROUP BY commuter_id
) a
LEFT JOIN (
  SELECT
    FLOOR(AVG(TIMESTAMPDIFF(MINUTE, start_dt, end_dt))) AS avg_time,
    city
  FROM rides
  WHERE city = 'NY'
) b
ON a.city = b.city

17. Suppose we have a table of transactions that happened during 2023, with each transaction belonging to different departments within a company. We want to calculate the total spend for IT, HR, and Marketing, and also have a total for Other departments, grouped by fiscal quarters.

Write a query to display this result.

Note: Display only quarters where at least one transaction occurred. Quarter names should be Q1, Q2, Q3 and Q4. Q1 is from January to March.

Example:

Input:

**transactions** table

Column	Type
transaction_id	INTEGER
department	VARCHAR
amount	FLOAT
transaction_date	DATE

Output:

Column	Type
quarter	VARCHAR
it_spending	FLOAT
hr_spending	FLOAT
marketing_spending	FLOAT
other_spending	FLOAT

Answer:

SELECT
  CASE
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 10 AND 12 THEN 'Q4'
  END AS quarter,
  SUM(CASE WHEN department = 'IT' THEN amount ELSE 0 END) AS it_spending,
  SUM(CASE WHEN department = 'HR' THEN amount ELSE 0 END) AS hr_spending,
  SUM(CASE WHEN department = 'Marketing' THEN amount ELSE 0 END) AS marketing_spending,
  SUM(CASE WHEN department NOT IN ('IT', 'HR', 'Marketing') THEN amount ELSE 0 END) AS other_spending
FROM
  transactions
GROUP BY
  Quarter;

18. The HR department in your organization wants to calculate employees’ earnings. Write a query to report the sum of regular salaries, overtime pay, and total compensations for each role.

Example:

Input:

**employees** table

Column	Type
employee_id	INTEGER
job_title	VARCHAR
salary	FLOAT
overtime_hours	INTEGER
overtime_rate	FLOAT

Output:

Column	Type
job_title	VARCHAR
total_salaries	FLOAT
total_overtime_payments	FLOAT
total_compensation	FLOAT

Answer:

SELECT
  job_title,
  SUM(salary) AS total_salaries,
  SUM(overtime_hours * overtime_rate) AS total_overtime_payments,
  SUM(salary + (overtime_hours * overtime_rate)) AS total_compensation
FROM
  employees
GROUP BY
  job_title;

19. Given a table called user_experiences, write a query to determine the percentage of users who held the title of “data analyst” immediately before holding the title “data scientist.” Immediate is defined as the user holding no other titles between the “data analyst” and “data scientist” roles.

Example:

Input:

**user_experiences** table

Column	Type
id	INTEGER
position_name	VARCHAR
start_date	DATETIME
end_date	DATETIME
user_id	INTEGER

Output:

Column	Type
percentage	FLOAT

Answer:

WITH added_previous_role AS (
  SELECT user_id, position_name,
  LAG (position_name)
  OVER (PARTITION BY user_id)
  AS previous_role
  FROM user_experiences
),

experienced_subset AS (
  SELECT *
  FROM added_previous_role
  WHERE position_name = 'Data Scientist'
    AND previous_role = 'Data Analyst'
)

SELECT COUNT(DISTINCT experienced_subset.user_id)/
     COUNT(DISTINCT user_experiences.user_id)
AS percentage
FROM user_experiences
LEFT JOIN experienced_subset
    ON user_experiences.user_id = experienced_subset.user_id

20. Given the transactions table below, write a query that finds the third purchase of every user.

Note: Sort the results by the **user_id** in ascending order. If a user purchases two products at the same time, the lower **id** field is used to determine which is the first purchase.

Example:

Input:

**transactions** table

Columns	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Output:

Columns	Type
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

Answer:

SELECT user_id, created_at, product_id, quantity
FROM (
    SELECT
        user_id
        , created_at
        , product_id
        , quantity
        , RANK() OVER (
            PARTITION BY user_id
            ORDER BY created_at ASC, id ASC
        ) AS rank_value
    FROM transactions
) AS t
WHERE rank_value = 3
ORDER BY user_id ASC

21. Write a query to show the number of users, number of transactions placed, and total order amount per month in the year 2020. Assume that we are only interested in the monthly reports for a single year (January–December).

Example:

Input:

**transactions** table

Column	Type
id	INTEGER
user_id	INTEGER
created_at	DATETIME
product_id	INTEGER
quantity	INTEGER

**products** table

Column	Type
id	INTEGER
name	VARCHAR
price	FLOAT

**users** table

Column	Type
id	INTEGER
name	VARCHAR
sex	VARCHAR

Output:

Column	Type
month	INTEGER
num_customers	INTEGER
num_orders	INTEGER
order_amt	INTEGER

Answer:

SELECT MONTH(t.created_at) AS month,
COUNT(DISTINCT t.user_id) AS num_customers,
COUNT(t.id) AS num_orders,
SUM(t.quantity * p.price) AS order_amt
FROM transactions t
JOIN products p
ON t.product_id = p.id
WHERE YEAR(created_at) ='2020'
GROUP BY 1

22. Given tables employees, employee_projects, and projects, find the 3 lowest-paid employees that have completed at least 2 projects.

Note: incomplete projects will have an end date of **NULL** in the projects table.

Example:

Input:

**employees** table

Column	Type
id	INTEGER
first_name	VARCHAR
last_name	VARCHAR
salary	INTEGER
department_id	INTEGER

**employee_projects** table

Column	Type
employee_id	INTEGER
project_id	INTEGER

**projects** table

Column	Type
id	INTEGER
title	VARCHAR
start_date	DATE
end_date	DATE
budget	INTEGER

Output:

Column	Type
employee_id	INTEGER
salary	INTEGER
completed_projects	INTEGER

Answer:

SELECT  ep.employee_id
, e.salary
, COUNT(p.id) AS completed_projects

FROM employee_projects AS ep

JOIN employees AS e
ON e.id = ep.employee_id

JOIN projects AS p
ON  ep.project_id = p.id

WHERE p.end_date IS NOT NULL
GROUP BY 1
HAVING completed_projects > 1
ORDER BY 2
LIMIT 3

23. How can ACLs be used to control access to specific database objects in MySQL?

“Access control lists (ACLs) in MySQL manage permissions for users by defining what actions they can perform on specific database objects. ACLs can grant or revoke privileges, such as SELECT, INSERT, UPDATE, DELETE, and EXECUTE, on databases, tables, columns, and stored procedures. They provide granular control, allowing different permissions for different users and roles.”

24. Explain the difference between character set and collation in MySQL and how they impact data storage and retrieval.

“A character set in MySQL defines the set of characters that can be stored in a database. Collation is a set of rules for comparing characters within a character set, dictating how text is sorted and compared. Character sets impact storage by determining the byte representation of characters, while collation impacts retrieval by defining how strings are ordered and compared, affecting operations like sorting and searching.”

25. Describe different types of locking mechanisms used by MySQL to ensure data consistency during concurrent access.

“MySQL employs various locking mechanisms such as table-level locks, row-level locks, and page-level locks to maintain data consistency during concurrent access. Table-level locks lock the entire table, row-level locks lock specific rows, and page-level locks lock groups of rows within a table to prevent conflicting modifications by concurrent transactions.”

26. How would you write a MySQL query that checks for errors and returns user-friendly messages instead of technical error codes?

“To write a MySQL query that checks for errors and returns user-friendly messages, I would utilize the MySQL error handling mechanism, specifically the TRY…CATCH block in stored procedures. Within the TRY block, I’d execute the query, and the CATCH block would handle any errors, providing customized error messages to the user.”

27. What are some strategies for optimizing performance when querying large tables in MySQL?

“Strategies for optimizing performance when querying large tables in MySQL include indexing frequently queried columns, partitioning the table based on usage patterns, optimizing queries by using appropriate joins and WHERE clauses, denormalizing data to reduce join complexity, and utilizing caching mechanisms such as query caching and memcached for frequently accessed data.”

28. Explain how self-joins can be used in MySQL to identify relationships within a single table. Provide an example scenario where a self-join might be useful.

“Self-joins in MySQL are used to establish relationships within a single table by joining it with itself. An example scenario where a self-join might be useful is in a hierarchical structure such as an organizational chart. For instance, to retrieve the names of all employees along with their managers’ names, a self-join on the employee table can be performed based on the manager’s ID.”

29. Explain the concept of federated tables in MySQL and their limitations compared to traditional tables.

“Federated tables in MySQL allow access to data from remote databases as if they are local tables. However, they come with limitations such as lack of support for transactions, limited functionality compared to native storage engines, and potential performance overhead due to network latency and data transfer.”

30. You are given a slow UPDATE query. Describe different techniques you might use to analyze and optimize the query performance in MySQL.

“To optimize the performance of a slow UPDATE query in MySQL, I would begin by analyzing the query execution plan using EXPLAIN to identify potential bottlenecks. Then, I might consider optimizing indexes, rewriting the query to minimize the number of rows updated, breaking down the query into smaller transactions, using temporary tables for complex calculations, or optimizing disk I/O by tuning buffer pool sizes and disk configuration.”

Advanced MySQL Interview Questions and Answers

This section is strictly for interviews at companies that actively use MySQL to manage their databases. Here are a few recurring MySQL questions that are asked in senior data science role interviews:

31. The schema below is for a retail online shopping company consisting of two tables, attribution, and user_sessions.

• The attribution table logs a session visit for each row.
• If **conversion** is **true**, then the user converted to buying on that session.
• The **channel** column represents which advertising platform the user was attributed to for that specific session.
• Lastly, the **user_sessions** table maps many to one session visits back to one user.

First touch attribution is defined as the channel with which the converted user was associated when they first discovered the website.

Calculate the first touch attribution for each **user_id** that is converted.

Example:

Input:

**attribution** table

Column	Type
session_id	INTEGER
channel	VARCHAR
conversion	BOOLEAN

**user_sessions** table

column	type
session_id	INTEGER
created_at	DATETIME
user_id	INTEGER

Example output:

user_id	channel
123	facebook
145	google
153	facebook
172	organic
173	email

Answer:

WITH conv AS (
    SELECT us.user_id
    FROM attribution AS a
    INNER JOIN user_sessions AS us
        ON a.session_id = us.session_id
    WHERE conversion = 1
    GROUP BY 1 -- group by to get distinct user_ids
),

-- get the first session by user_id and created_at time.
first_session AS (
    SELECT
        min(us.created_at) AS min_created_at
        , conv.user_id
    FROM user_sessions AS us
    INNER JOIN conv
        ON us.user_id = conv.user_id
    INNER JOIN attribution AS a
        ON a.session_id = us.session_id
    GROUP BY conv.user_id
)

-- join user_id and created_at time back to the original table.
SELECT us.user_id, channel
FROM attribution
JOIN user_sessions AS us
    ON attribution.session_id = us.session_id
-- now join the first session to get a single row for each user_id
JOIN first_session
-- double join
    ON first_session.min_created_at = us.created_at
        AND first_session.user_id = us.user_id

32. There are two tables. One table is called swipes that holds a row for every Tinder swipe and contains a Boolean column that determines if the swipe was a right or left swipe called is_right_swipe. The second is a table named variants that determine which user has which variant of an AB test.

Write an SQL query to output the average number of right swipes for two different variants of a feed ranking algorithm by comparing users that have swiped 10, 50, and 100 swipes in a **feed_change** experiment.

Note: Users have to have swiped at least 10 times to be included in the subset of users to analyze the mean number of right swipes.

Example:

Input:

**variants** table

Columns	Type
id	INTEGER
experiment	VARCHAR
variant	VARCHAR
user_id	INTEGER

**swipes** table

Columns	Type
id	INTEGER
user_id	INTEGER
swiped_user_id	INTEGER
created_at	DATETIME
is_right_swipe	BOOLEAN

Output:

Columns	Type
variant	VARCHAR
mean_right_swipes	FLOAT
swipe_threshold	INTEGER
num_users	INTEGER

Answer:

WITH swipe_ranks AS (
    SELECT
        swipes.user_id
        , variant
        , RANK() OVER (
            PARTITION BY user_id ORDER BY created_at