MySQL Interview Questions for Developers

Back to interview questions

MySQL

MySQL is a popular free and open-source database management system, which makes it accessible to developers and businesses of all sizes. It is known for its reliability, scalability, and ease of use, making it a preferred choice for web applications and software development.

According to the DB-Engines Ranking, MySQL is the second most popular database management system currently in use.

To evaluate the MySQL skills of developers during coding interviews, we’ve provided realistic coding exercises and interview questions below. Additionally, we’ve outlined a set of best practices to ensure that your interview questions accurately assess the candidates’ MySQL skills.

MySQL example coding interview question

Use MySQL and the given schema and dataset to answer these questions about the World Cup

The Data

Data Source: https://www.kaggle.com/datasets/abecklas/fifa-world-cup?select=WorldCupMatches.csv

You can also use commands like show tables and desc world_cup_tournaments.

world_cup_tournaments
+-----------------+---------+
| Year            | int     | <-----+
| Host_Country    | varchar |       |
| Winner          | varchar |       |
| Runners_Up      | varchar |       |
| Third           | varchar |       |
| Fourth          | varchar |       |
| Goals_Scored    | int     |       |
| Qualified_Teams | int     |       |
| Matches_Played  | int     |       |
+-----------------+---------+       |
                                    |
world_cup_matches                   |
+---------------------+---------+   |
| Year                | int     | <-+
| Datetime            | varchar |
| Stage               | varchar |
| Stadium             | varchar |
| City                | varchar |
| Home_Team_Name      | varchar |
| Home_Team_Goals     | int     |  
| Away_Team_Goals     | int     |  
| Away_Team_Name      | varchar |
| Win_Conditions      | varchar |
| Attendance          | int     |
| Half_Time_Home_Goals| int     |
| Half_Time_Away_Goals| int     |
| Referee             | varchar |
| Assistant_1         | varchar |
| Assistant_2         | varchar |
| Round_ID            | int     |
| Match_ID            | int     | <-+
| Home_Team_Initials  | varchar |   |
| Away_Team_Initials  | varchar |   |
+---------------------+---------+   |
                                    |
world_cup_line_ups                  |
+---------------+---------+         |
| Line_up_ID    | int     |         |
| Round_ID      | int     |         |
| Match_ID      | int     | <-------+
| Team_Initials | varchar |
| Coach_Name    | varchar |
| Line_up       | varchar |
| Shirt_Number  | int     |
| Player_Name   | varchar |
+---------------+---------+
Code language: SQL (Structured Query Language) (sql)

The questions:

Question 1: Which FIFA World Cup (year and host country) had the most goals scored (and how many)?

  Table: world_cup_tournaments

  Fields: Year, Host_Country, Goals_Scored

Question 2: Which FIFA World Cup Year and Host Country had the most pitch locations? (i.e. had the most stadiums)

  Tables: world_cup_tournaments, world_cup_matches

  Fields: Year, Host_Country, Stadium

Question 3: Which FIFA World Cup Year was the most exciting (i.e. had the highest average goals scored per match)? Use CTE to answer.

  Tables: world_cup_tournaments

  Fields: Year, Host_Country, Goals_Scored, Matches_Played

Question 4: List the top 10 most popular teams in FIFA World Cup history! (i.e. with the largest total stadium attendance)

  Table: world_cup_matches

  Fields: Match_ID, Home_Team_Name, Away_Team_Name, Attendance

Junior MySQL interview questions

Q: What is SQL and what is it used for?

Answer: SQL stands for Structured Query Language and is a programming language used to manage and manipulate data stored in relational databases. It performs tasks such as querying data, inserting, updating, and deleting records, and creating and modifying database structures.

Q: Explain the difference between SELECT and FROM clauses in SQL.

Answer: The SELECT clause is used to specify which columns to retrieve from a table, while the FROM clause specifies which table or tables to retrieve data from. For example:

SELECT customer_name, order_date FROM ordersCode language: SQL (Structured Query Language) (sql)

This query would retrieve the customer_name and order_date columns from the ‘orders’ table.

Q: Fix the syntax error in the following SQL query:

SELECT customer_name, order_date, order_total FROM orders WHERE order_total > 100;Code language: SQL (Structured Query Language) (sql)

Answer: There is no syntax error in this SQL query.

Q: What is the difference between a WHERE clause and a HAVING clause in SQL?

Answer: The WHERE clause filters rows based on specific conditions, while the HAVING clause filters groups of rows based on specific conditions on aggregate functions, such as COUNT, SUM, AVG, MIN, and MAX.

Q: Write a SQL query to return the number of rows in the ‘customers’ table.

SELECT COUNT(*) FROM customers;Code language: SQL (Structured Query Language) (sql)

Answer: This query would return the total number of rows in the customers table.

Q: Explain the difference between the COUNT and SUM aggregate functions in SQL.

Answer: The COUNT function returns the number of rows that match a specific condition, while the SUM function returns the sum of the values in a specific column that matches a specific condition. For example:

SELECT COUNT(*) FROM orders WHERE customer_id = 1;Code language: SQL (Structured Query Language) (sql)

This query would return the number of orders for the customer with a customer_id of 1.

SELECT SUM(order_total) FROM orders WHERE customer_id = 1;Code language: SQL (Structured Query Language) (sql)

This query would return the total order amount for the customer with a customer_id of 1.

Q: Fix the syntax error in the following SQL query:

SELECT * FROM orders WHERE customer_id = 5 AND order_total > 50Code language: SQL (Structured Query Language) (sql)

Answer: This SQL query has no syntax error, but it may not return any results if there are no orders with a 'customer_id' of 5 and an 'order_total' greater than 50.

Q: Write a SQL query to return the average order total for each customer in the ‘customers’ and ‘orders’ tables.

Answer:

SELECT c.customer_name, AVG(o.order_total) AS avg_order_total FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name;Code language: SQL (Structured Query Language) (sql)

This query would return the average order total for each customer in the 'customers' and ‘orders’ tables.

Q: Explain the difference between a INNER JOIN and a OUTER JOIN in SQL.

Answer: An INNER JOIN returns only the rows with matching values in both tables being joined, while an OUTER JOIN returns all rows from one table and matching rows from the other. There are three types of OUTER JOINs: LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN.

Q: Fix the syntax error in the following SQL query:

SELECT customer_name, COUNT(order_id) AS order_count FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_name;
Code language: SQL (Structured Query Language) (sql)

Answer: There is no syntax error in this SQL query.

Intermediate interview questions

Q: What is a correlated subquery, and how is it different from a regular subquery?

Answer: A correlated subquery is a subquery that refers to a column from the outer query. This is different from a regular subquery, which is self-contained and can be executed independently of the outer query. Correlated subqueries can be slower to execute than regular subqueries because they need to be executed for each row in the outer query.

Q: What is normalization, and why is it important for databases?

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. This is important because it reduces the risk of data inconsistencies and anomalies. Normalization involves splitting large tables into smaller tables and creating relationships between them.

Q: This query below uses a cross join to combine orders and customers is inefficient. Can you improve it using an explicit join?

SELECT * FROM orders, customers;Code language: SQL (Structured Query Language) (sql)

Answer:

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;Code language: SQL (Structured Query Language) (sql)

Q: How can you optimize MySQL queries to reduce execution time?

Answer: There are several ways to optimize MySQL queries to improve performance. Some common techniques include:

• Using indexes on frequently queried columns
• Writing efficient SQL code that avoids unnecessary operations and redundant joins
• Minimizing the size of result sets by using LIMIT and GROUP BY clauses
• Tuning MySQL server configuration settings, such as buffer sizes and cache sizes
• Using EXPLAIN to analyze query execution plans and identify performance bottlenecks
• Caching frequently accessed data in memory or using application-level caching.

Q: What are indexes in MySQL, and how can they improve query performance?

Answer: Indexes are data structures that MySQL uses to quickly look up the location of data in a table. By creating an index on one or more columns, MySQL can use the index to efficiently search for rows that match a query’s WHERE clause. This can greatly improve the performance of queries that filter or sort data based on the indexed columns.

Q: Can you improve the query below for calculating total revenue used a subquery to look up the relevant order items for each customer:

SELECT customers.customer_name,
    (SELECT SUM(quantity * price) FROM order_items WHERE order_items.order_id IN (SELECT order_id FROM orders WHERE orders.customer_id = customers.customer_id)) AS total_revenue
FROM customers;Code language: SQL (Structured Query Language) (sql)

Answer:

SELECT customers.customer_name, SUM(order_items.quantity * order_items.price) AS total_revenue
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY customers.customer_id;Code language: SQL (Structured Query Language) (sql)

Q: Can you improve this query below for finding products that have never been ordered? It currently uses a subquery in the WHERE clause to check for missing order items:

SELECT * FROM products WHERE product_id NOT IN (SELECT product_id FROM order_items);Code language: SQL (Structured Query Language) (sql)

Answer: The query can be improved using a left join:

SELECT products.*
FROM products
LEFT JOIN order_items ON products.product_id = order_items.product_id
WHERE order_items.product_id IS NULL;Code language: SQL (Structured Query Language) (sql)

Q: What are the differences between a left join and a right join in MySQL?

Answer: Both left join and right join are types of outer joins that return all rows from one table and matching rows from another table. The main difference between them is the order in which the tables are specified in the join clause. In a left join, all rows from the left table are returned, along with matching rows from the right table, if any. In a right join, all rows from the right table are returned, along with matching rows from the left table, if any. If there is no match for a row in the right table in a left join, NULL values are returned for the corresponding columns. Similarly, if there is no match for a row in the left table in a right join, NULL values are returned for the corresponding columns.

Q: Fix the following query to return the average number of orders per customer:

SELECT AVG(counts.order_count) AS avg_orders_per_customer
FROM (
  SELECT customers.customer_id, COUNT(orders.order_id) AS order_count
  FROM customers
  JOIN orders ON customers.customer_id = orders.customer_id
  GROUP BY customers.customer_id
) counts;Code language: SQL (Structured Query Language) (sql)

Answer:

The only change that needed to be made was to remove the parentheses around counts.order_count in the AVG function. 

SELECT AVG(counts.order_count) AS avg_orders_per_customer
FROM (
  SELECT customers.customer_id, COUNT(orders.order_id) AS order_count
  FROM customers
  JOIN orders ON customers.customer_id = orders.customer_id
  GROUP BY customers.customer_id
) counts;Code language: SQL (Structured Query Language) (sql)

Q: Write a query to return the top 10 customers by order count, including only those customers who have placed at least 3 orders.

Answer:

SELECT customers.customer_name, COUNT(orders.order_id) AS order_count
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id
HAVING COUNT(orders.order_id) >= 3
ORDER BY order_count DESC
LIMIT 10;Code language: SQL (Structured Query Language) (sql)

More MySQL interview questions resources

For more resources on MySQL interview questions, we have provided links below on MySQL:

• MySQL SHOW TABLES: A Detailed Guide
• MySQL LIKE Operator: A Detailed Guide
• Advanced Stored Procedures In MySQL
• MySQL JOIN Clause: A Detailed Guide
• An Introduction To Stored Procedures In MySQL
• How to Use Indexes to Increase MySQL Database Performance
• How to Optimize MySQL Database Schema for Improved Performance
• How to Optimize Query Performance in MySQL Databases
• Code Challenge: World Cup SQL

Interview best practices for MySQL roles

When carrying out effective MySQL interviews, it is essential to take into account various job role factors, such as the applicant’s experience level and the specific engineering role. To optimize the outcomes of your MySQL interview questions, we suggest implementing the following best practices when interacting with candidates:

• Develop technical questions that correlate with real-world business scenarios in your organization. This approach will not only engage the candidate more effectively but also allow you to better evaluate their fit for your team.
• Promote a culture of inquiry during the interview and nurture a cooperative atmosphere.
• You don’t want someone who is just hacking together queries – make sure you’re assessing your candidate’s ability to write efficient and optimized  queries so you’re not hiring employees who are stressing the databases’ performance. 

Furthermore, adhering to standard interview etiquette is crucial when conducting MySQL interviews. This involves tailoring the complexity of questions to the applicant’s development skill level, offering prompt feedback on their job application status, and giving candidates the opportunity to inquire about the evaluation process or working alongside you and your team.