Essential MySQL Interview Fundamentals: 2025 Edition Quiz

Explore core MySQL interview questions focusing on storage engines, SQL queries, indexing, ACID properties, and more. Perfect for beginners and job seekers aiming to understand key MySQL concepts, commands, and best practices for 2025.

1. Understanding SQL Joins

   Which SQL JOIN returns only rows that have matching values in both joined tables, such as getting customers who have placed orders?

   1. INNER JOIN
   2. LEFT JOIN
   3. RIGHT JOIN
   4. FULL JOIN

   Explanation: INNER JOIN returns rows with matching values in both tables, making it ideal when you only want common data like customers who have orders. LEFT JOIN includes all rows from the left table, even if there's no match. RIGHT JOIN does the same for the right table. FULL JOIN is not standard in MySQL and would try to return all rows from both tables.

2. Comparing Storage Engines

   Which MySQL storage engine supports transactions and foreign keys, making it suitable for applications requiring strong data integrity?

   1. InnoDB
   2. MyISAM
   3. Memory
   4. CSV

   Explanation: InnoDB supports transactions, row-level locking, and foreign keys, ensuring strong data integrity. MyISAM does not support transactions or foreign keys. Memory stores data in RAM and also lacks these features. CSV is used for storing data in comma-separated format and is not intended for transactional operations.

3. Understanding UNION and UNION ALL

   If you want to combine the results of two SELECT queries and include duplicate rows, which operator should you use?

   1. UNION ALL
   2. UNION
   3. JOIN
   4. GROUP BY

   Explanation: UNION ALL combines all results from two SELECT queries, including duplicates. UNION removes any duplicate rows between the result sets. JOIN combines columns from tables rather than stacking rows. GROUP BY is used to group rows by a column and does not combine result sets this way.

4. MySQL Index Types

   Which of the following is NOT a recognized type of index in MySQL?

   1. Temporal Index
   2. Primary Index
   3. Full-text Index
   4. Spatial Index

   Explanation: While MySQL supports primary, full-text, and spatial indexes, there is no 'Temporal Index' in MySQL. Primary Index uniquely identifies each record. Full-text Index is used for text searching, and Spatial Index is for spatial data types. 'Temporal Index' is not a standard MySQL index type.

5. AUTOINCREMENT Columns

   How does the AUTO_INCREMENT attribute work when adding new rows to a MySQL table with a primary key?

   1. It automatically generates a unique sequential value for each new row
   2. It duplicates the last used value
   3. It requires manual value assignment for each row
   4. It fills the column with zeros

   Explanation: AUTO_INCREMENT automatically assigns a unique and sequential value to each new row, usually for primary keys. It does not duplicate values, require manual entry, or fill with zeros. This feature simplifies record identification and uniqueness.

6. CHAR vs VARCHAR Differences

   What is the major difference between the MySQL data types CHAR(10) and VARCHAR(10) when storing text?

   1. CHAR is fixed-length and padded, VARCHAR is variable-length
   2. VARCHAR stores encrypted values, CHAR does not
   3. CHAR is always faster than VARCHAR for any data length
   4. VARCHAR can only store numbers, not text

   Explanation: CHAR stores values in a fixed length and pads them with spaces, making storage predictable. VARCHAR stores only the actual text and uses variable length, saving space. VARCHAR can store text, not just numbers. The speed benefit for CHAR mainly applies to consistently short string data.

7. EXPLAIN Statement Usage

   What is the main purpose of the EXPLAIN keyword in a MySQL SELECT query?

   1. To display the query execution plan
   2. To create a table
   3. To display the column comments
   4. To grant privileges to a user

   Explanation: EXPLAIN reveals how MySQL will execute a SELECT query, including join order and indexes, helping with performance optimization. It does not create tables, show comments, or manage privileges. Understanding query plans aids in optimizing slow or complex queries.

8. Transactional Properties

   Which of the following properties is NOT part of ACID compliance in MySQL transactions?

   1. Adaptability
   2. Atomicity
   3. Consistency
   4. Durability

   Explanation: ACID stands for Atomicity, Consistency, Isolation, and Durability. Adaptability is not part of this set. Atomicity ensures all or nothing changes, Consistency keeps data valid, and Durability guarantees changes persist. Isolation prevents interference between concurrent transactions.

9. WHERE vs HAVING Clause

   In a MySQL query, which clause should be used to filter results after aggregation with GROUP BY?

   1. HAVING
   2. WHERE
   3. ORDER BY
   4. LIMIT

   Explanation: The HAVING clause is used to filter results after GROUP BY has performed aggregation. WHERE filters rows before aggregation occurs. ORDER BY sorts results, while LIMIT reduces the number of returned rows but is not a filter for aggregates.

10. Foreign Key Constraints

    What is the main function of a foreign key constraint in a MySQL table?

    1. To enforce referential integrity between related tables
    2. To increase query speed on non-indexed columns
    3. To restrict the type of data stored in a column
    4. To create a backup of the table automatically

    Explanation: A foreign key constraint creates a relationship between two tables, maintaining referential integrity by ensuring values in one table correspond to primary keys in another. It does not directly affect query speed, restrict data types (that’s data types and constraints), or create backups.