15 MySQL Interview Questions Every Linux User Should Know

Much of the MySQL interview prep you’ll find online is based on outdated versions that reached end-of-life years ago. If your answer to a connection question still mentions mysql_pconnect(), or you write string comparisons without quotes, an experienced interviewer will immediately know you haven’t worked with a modern MySQL server.

This is the third installment in our MySQL interview series, and every question and example has been verified on a currently supported MySQL release. If you haven’t read the previous parts yet, they’re a great place to start before continuing.

All examples in this article were tested on a MySQL 9.7 LTS server. Whenever a command or behavior is different from MySQL 5.7, we’ll point it out, since those version differences are common interview topics.

To keep things simple, every example uses the same users table, so it’s easier to follow along as you work through the questions.

mysql> SELECT * FROM users;
+----+--------+-------------------+---------+------------+-------+
| id | name   | email             | city    | joined     | posts |
+----+--------+-------------------+---------+------------+-------+
|  1 | Ravi   | [email protected]  | Mumbai  | 2012-06-01 |  3200 |
|  2 | Aaron  | [email protected] | Chennai | 2014-03-11 |   180 |
|  3 | Gunjit | NULL              | Delhi   | 2016-09-23 |    47 |
|  4 | Marin  | [email protected] | Zagreb  | 2018-01-05 |    96 |
|  5 | Sam    | [email protected]   | Pune    | 2021-11-30 |    12 |
+----+--------+-------------------+---------+------------+-------+
5 rows in set (0.00 sec)

1. Find the Server Version and the Currently Selected Database

Two built-in functions can quickly show this information. VERSION() displays the MySQL server version, while DATABASE() shows the database currently selected for your session.

mysql> SELECT VERSION(), DATABASE();
+-----------+------------+
| VERSION() | DATABASE() |
+-----------+------------+
| 9.7.2     | NULL       |
+-----------+------------+
1 row in set (0.00 sec)

The NULL value means you haven’t selected a database yet. Choose one with the USE command, then run the query again.

mysql> USE tecmint;
Database changed

mysql> SELECT VERSION(), DATABASE();
+-----------+------------+
| VERSION() | DATABASE() |
+-----------+------------+
| 9.7.2     | tecmint    |
+-----------+------------+
1 row in set (0.00 sec)

Interviewers may also ask which MySQL versions are currently supported. MySQL now has two release tracks:

• LTS (Long-Term Support): Versions like 8.4 and 9.7 receive five years of Premier Support and focus on stability.
• Innovation: Uses year-based version numbers such as 26.7, with new features released every quarter.

Older releases such as 5.7 and 8.0 have reached end-of-life and no longer receive security updates.

If you need more details about your current MySQL session, such as the connection ID, server version, character set, and socket path, use the \s (status) command.

mysql> \s
--------------
mysql  Ver 9.7.2 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          8
Current database:       tecmint
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         9.7.2 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 12 min 18 sec

Threads: 2  Questions: 45  Slow queries: 0  Opens: 142  Flush tables: 3  Open tables: 61  Queries per second avg: 0.060

This command is useful when troubleshooting connection issues or confirming the server you’re connected to during an interview or while working on a production system.

2. Select Every User Except ‘Sam’ Using the NOT Operator

To exclude a specific value, you can use the != operator (or <>, which works the same way). Since 'Sam' is a string, it must be enclosed in quotes. Without quotes, MySQL assumes Sam is a column name and returns an Unknown column error.

mysql> SELECT * FROM users WHERE name != 'Sam';
+----+--------+-------------------+---------+------------+-------+
| id | name   | email             | city    | joined     | posts |
+----+--------+-------------------+---------+------------+-------+
|  1 | Ravi   | [email protected]  | Mumbai  | 2012-06-01 |  3200 |
|  2 | Aaron  | [email protected] | Chennai | 2014-03-11 |   180 |
|  3 | Gunjit | NULL              | Delhi   | 2016-09-23 |    47 |
|  4 | Marin  | [email protected] | Zagreb  | 2018-01-05 |    96 |
+----+--------+-------------------+---------+------------+-------+
4 rows in set (0.00 sec)

Now let’s run a similar query on the email column, which contains a NULL value.

mysql> SELECT id, name, email FROM users WHERE email != '[email protected]';
+----+-------+-------------------+
| id | name  | email             |
+----+-------+-------------------+
|  1 | Ravi  | [email protected]  |
|  2 | Aaron | [email protected] |
|  4 | Marin | [email protected] |
+----+-------+-------------------+
3 rows in set (0.00 sec)

Notice that Gunjit is missing from the results. That’s because the email value is NULL.

In MySQL, comparing anything with NULL doesn’t return TRUE or FALSE, it returns NULL. Since the WHERE clause only keeps rows where the condition is TRUE, rows containing NULL are filtered out.

If you want NULL values to be treated as comparable values, use the NULL-safe equality operator (<=>) together with NOT.

mysql> SELECT id, name FROM users WHERE NOT (email <=> '[email protected]');
+----+--------+
| id | name   |
+----+--------+
|  1 | Ravi   |
|  2 | Aaron  |
|  3 | Gunjit |
|  4 | Marin  |
+----+--------+
4 rows in set (0.00 sec)

This time, Gunjit appears in the results because NULL <=> '[email protected]' evaluates to FALSE, and NOT FALSE becomes TRUE. This is a common interview question because it tests whether you understand how MySQL handles NULL values in comparisons.

3. Can NOT be Combined With AND?

Yes. The NOT, AND, and OR operators can all be used together in the same WHERE clause.

• NOT reverses the result of a condition.
• AND requires all conditions to be true.
• OR requires at least one condition to be true.

For example, the following query returns every user who is not from Mumbai and does not have more than 1,000 posts.

mysql> SELECT id, name, city, posts FROM users
    -> WHERE NOT (city = 'Mumbai' AND posts > 1000);
+----+--------+---------+-------+
| id | name   | city    | posts |
+----+--------+---------+-------+
|  2 | Aaron  | Chennai |   180 |
|  3 | Gunjit | Delhi   |    47 |
|  4 | Marin  | Zagreb  |    96 |
|  5 | Sam    | Pune    |    12 |
+----+--------+---------+-------+
4 rows in set (0.00 sec)

The condition inside the parentheses matches only Ravi, who is from Mumbai and has more than 1,000 posts. The NOT operator reverses that result, so every other row is returned.

This query can also be written without using NOT by applying De Morgan’s Law.

mysql> SELECT id, name, city, posts FROM users
    -> WHERE city != 'Mumbai' OR posts <= 1000;
+----+--------+---------+-------+
| id | name   | city    | posts |
+----+--------+---------+-------+
|  2 | Aaron  | Chennai |   180 |
|  3 | Gunjit | Delhi   |    47 |
|  4 | Marin  | Zagreb  |    96 |
|  5 | Sam    | Pune    |    12 |
+----+--------+---------+-------+
4 rows in set (0.00 sec)

Both queries return the same four rows. A simple rule to remember is:

• NOT (A AND B) becomes NOT A OR NOT B
• NOT (A OR B) becomes NOT A AND NOT B

When you combine AND and OR in the same query, always use parentheses to make your logic clear. They also help avoid mistakes, especially in more complex queries.

If the De Morgan’s law trick just made your WHERE clauses easier to read, pass it to whoever is prepping for their next round Share this article

4. What Does IFNULL() do, and When do You Use COALESCE() Instead?

The IFNULL() function checks whether a value is NULL.

• If the first argument is not NULL, it returns that value.
• If the first argument is NULL, it returns the second argument instead.

This is commonly used to replace missing values with something more readable in query results.

mysql> SELECT name, IFNULL(email, 'not provided') AS email FROM users;
+--------+-------------------+
| name   | email             |
+--------+-------------------+
| Ravi   | [email protected]  |
| Aaron  | [email protected] |
| Gunjit | not provided      |
| Marin  | [email protected] |
| Sam    | [email protected]   |
+--------+-------------------+
5 rows in set (0.00 sec)

Here, Gunjit’s email is NULL, so IFNULL() replaces it with not provided.

If you need to check more than two values, use COALESCE() instead. It accepts multiple arguments and returns the first value that isn’t NULL.

mysql> SELECT COALESCE(email, city, 'unknown') AS contact FROM users;
+-------------------+
| contact           |
+-------------------+
| [email protected]  |
| [email protected] |
| Delhi             |
| [email protected] |
| [email protected]   |
+-------------------+
5 rows in set (0.00 sec)

In this example, Gunjit's email is NULL, so COALESCE() returns the value from the city column instead. If both email and city were NULL, it would return 'unknown'.

Another related function you’ll often see is NULLIF().

NULLIF(a, b)

It returns NULL if a and b are equal; otherwise, it returns a. It’s commonly used in calculations to avoid divide-by-zero errors.

5. Show Only the First or Last Few Rows of a Result Set

The LIMIT clause controls how many rows a query returns. To make sure you always get the expected rows, use it together with ORDER BY.

For example, to display the earliest user based on the joined date:

mysql> SELECT id, name, joined FROM users ORDER BY joined LIMIT 1;
+----+------+------------+
| id | name | joined     |
+----+------+------------+
|  1 | Ravi | 2012-06-01 |
+----+------+------------+
1 row in set (0.00 sec)

To get the most recently joined users, sort the results in descending order with DESC.

mysql> SELECT id, name, joined FROM users ORDER BY joined DESC LIMIT 2;
+----+-------+------------+
| id | name  | joined     |
+----+-------+------------+
|  5 | Sam   | 2021-11-30 |
|  4 | Marin | 2018-01-05 |
+----+-------+------------+
2 rows in set (0.00 sec)

You can also use OFFSET to skip a number of rows before returning the results. This is commonly used for pagination.

The following query skips the first two rows and returns the next two.

mysql> SELECT id, name FROM users ORDER BY id LIMIT 2 OFFSET 2;
+----+--------+
| id | name   |
+----+--------+
|  3 | Gunjit |
|  4 | Marin  |
+----+--------+
2 rows in set (0.00 sec)

One important thing to remember is that using LIMIT without ORDER BY doesn’t guarantee a consistent result. MySQL can return rows in any order, so always specify how the rows should be sorted before limiting them.

Another common interview question is about pagination performance. While LIMIT with OFFSET works well for small result sets, large offsets become slower because MySQL still has to scan and skip all the preceding rows before returning the requested ones.

For large tables, a better approach is keyset (seek) pagination, where you continue from the last value you retrieved instead of skipping thousands of rows.

mysql> SELECT id, name
    -> FROM users
    -> WHERE id > 40
    -> ORDER BY id
    -> LIMIT 20;

This approach is much more efficient because MySQL can jump directly to the matching rows instead of reading and discarding a large number of records first.

6. MySQL or MariaDB? Which One and Why?

This is a common interview question, especially for Linux administrator and database roles.

MySQL and MariaDB share the same roots, but they’ve evolved into separate database systems over the years. While they still support much of the same SQL syntax, they’re no longer considered drop-in replacements for each other.

Reasons to choose MySQL:

• Developed and maintained by Oracle.
• New InnoDB features are introduced here first.
• Includes Group Replication and InnoDB Cluster for built-in high availability.
• Widely supported by managed cloud database services.
• Offers Long-Term Support (LTS) releases with a defined support lifecycle.

Reasons to choose MariaDB:

• Community-governed under the MariaDB Foundation.
• Supports additional storage engines such as Aria, ColumnStore, and Spider.
• Ships as the default database package in many Linux distributions, including Debian, Ubuntu, and RHEL-based systems.
• Introduces some features, such as temporal tables and sequences, independently of MySQL.

There isn’t a single “best” choice. The right answer depends on your environment and requirements.

In an interview, a good answer is that MySQL and MariaDB have diverged since MySQL 5.5. Although they remain similar in many ways, they have different features, release cycles, and compatibility rules.

Replication isn’t supported in every direction between the two, and moving databases from one to the other may require changes to dump files or application code.

In practice, most organizations choose the database system that’s already supported by their application stack, Linux distribution, or cloud provider.

7. How do You Get the Current Date and Time?

MySQL provides several built-in functions for working with the current date and time. Each one serves a slightly different purpose, so it’s useful to know when to use each.

mysql> SELECT CURDATE(), CURTIME(), NOW(), UTC_TIMESTAMP();
+------------+-----------+---------------------+---------------------+
| CURDATE()  | CURTIME() | NOW()               | UTC_TIMESTAMP()     |
+------------+-----------+---------------------+---------------------+
| 2026-08-06 | 11:42:07  | 2026-08-06 11:42:07 | 2026-08-06 06:12:07 |
+------------+-----------+---------------------+---------------------+
1 row in set (0.00 sec)

Here’s what each function returns:

• CURDATE() returns only the current date.
• CURTIME() returns only the current time.
• NOW() returns the current date and time.
• UTC_TIMESTAMP() returns the current date and time in UTC, regardless of your session time zone.

You’ll also see CURRENT_DATE(), which is simply another name for CURDATE().

One interview question that comes up frequently is the difference between NOW() and SYSDATE().

• NOW() returns the time when the current statement started executing. Multiple calls to NOW() within the same statement always return the same value.
• SYSDATE() returns the actual system time when the function is executed, so multiple calls can return different values if the statement takes time to run.

For example:

mysql> SELECT NOW(), SLEEP(2), NOW(), SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               | SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+---------------------+----------+---------------------+
| 2026-08-06 11:42:07 |        0 | 2026-08-06 11:42:07 | 2026-08-06 11:42:09 |        0 | 2026-08-06 11:42:11 |
+---------------------+----------+---------------------+---------------------+----------+---------------------+
1 row in set (4.00 sec)

Notice that both calls to NOW() return the same timestamp, while each call to SYSDATE() returns the current system time at the moment it is executed.

Because of this behavior, SYSDATE() isn’t considered safe for statement-based replication, while NOW() is.

The difference between NOW() and SYSDATE() has caused unexpected replication issues in real-world deployments. If you found this explanation helpful, share it with someone preparing for a MySQL interview.

8. Export a Table as an XML File

You can export the output of a query as an XML file by combining the MySQL client’s –xml and -e options.

mysql -u root -p --xml -e "SELECT * FROM users" tecmint > users.xml

Here’s what each option does:

• -u root specifies the MySQL user account.
• -p prompts you to enter the password securely.
• --xml formats the query result as XML instead of the default table format.
• -e "SELECT * FROM users" executes the SQL statement and exits immediately.
• tecmint is the database where the query is executed.
• > users.xml saves the output to an XML file.

A common interview misconception is that -e means export. It actually stands for –execute, which simply tells the MySQL client to execute the specified SQL statement and then exit. The XML output comes from the --xml option, not from -e.

If you want to export an entire database in XML format instead of a single query result, use mysqldump.

mysqldump -u root -p --xml tecmint > tecmint.xml

This command exports every table in the tecmint database as XML. You may also be asked about exporting data as JSON. The classic mysql client doesn’t provide a --json option. If you need JSON output, you can either:

• Use MySQL Shell, which supports JSON output modes.
• Generate JSON directly in SQL using functions such as JSON_OBJECT() and JSON_ARRAYAGG().

9. What Replaced mysql_pconnect() for Persistent Connections?

The mysql_* extension, including mysql_pconnect(), is no longer available in modern PHP.

It was deprecated in PHP 5.5 and removed completely in PHP 7.0. That means functions like mysql_connect(), mysql_pconnect(), and mysql_close() don’t exist in any supported PHP version today.

If an interviewer asks about persistent connections, the correct answer is to use either PDO or MySQLi. With PDO, enable persistent connections by setting the PDO::ATTR_PERSISTENT attribute.

$pdo = new PDO(
    'mysql:host=localhost;dbname=tecmint;charset=utf8mb4',
    'user',
    'pass',
    [PDO::ATTR_PERSISTENT => true]
);

With MySQLi, use the p: prefix before the hostname.

$db = new mysqli('p:localhost', 'user', 'pass', 'tecmint');

The idea behind a persistent connection is simple. Instead of opening a new database connection for every request, PHP reuses an existing connection whenever possible. This avoids the overhead of creating a new TCP connection and authenticating with the MySQL server each time.

However, persistent connections also have some drawbacks:

• Session variables can remain from a previous request.
• Temporary tables may still exist if they weren’t cleaned up.
• Uncommitted transactions can carry over.
• Each PHP worker keeps a database connection open, even when it’s idle, which counts toward MySQL’s max_connections limit.

Because of these trade-offs, persistent connections aren’t always the best choice. They’re most useful for applications with high traffic where the benefits of reusing connections outweigh the additional resource usage.

10. Show All Indexes Defined on a Table

To view all indexes on a table, use the SHOW INDEX statement.

mysql> SHOW INDEX FROM users\G
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

This command displays information about every index on the table, including:

• Key_name – the index name.
• Column_name – the indexed column.
• Non_unique – whether duplicate values are allowed.
• Seq_in_index – the position of the column within a multi-column index.
• Cardinality – an estimate of the number of unique values.
• Index_type – the index type, such as BTREE.
• Visible – whether the optimizer can use the index.
• Expression – the expression used for a functional index, if applicable.

Notice the \G at the end of the command. Instead of displaying the output as a wide table, it prints each row vertically, making it much easier to read when there are many columns.

One feature introduced in MySQL 8.0 is invisible indexes. An invisible index is still updated whenever data changes, but the query optimizer ignores it. You can make an index invisible like this:

mysql> ALTER TABLE users ALTER INDEX idx_city INVISIBLE;

This is useful when you want to find out whether an index is actually needed before deleting it. If queries continue to perform well, you can safely remove the index later. If performance drops, simply make the index visible again.

Because the index is still maintained while it’s invisible, changing it back to VISIBLE is almost instant. This is much faster and safer than dropping an index and rebuilding it on a large production table.

If you regularly back up MySQL databases, it’s also worth automating the process. Instead of running mysqldump manually, you can schedule backups with a Bash script, add log rotation, and configure alerts to notify you if a backup fails.

11. What are CSV Tables in MySQL?

This question is about the CSV storage engine, not CSV files in general. When you create a table using ENGINE=CSV, MySQL stores the table data as a plain comma-separated values (CSV) file on disk. Since it’s a regular text file, you can open it with a spreadsheet application or any text editor.

Here’s an example:

mysql> CREATE TABLE reports (
-> id INT NOT NULL,
-> city VARCHAR(30) NOT NULL
-> ) ENGINE=CSV;
Query OK, 0 rows affected (0.02 sec)

When the table is created, MySQL generates two files in the database directory:

• .CSV – stores the table data.
• .CSM – stores table metadata and status information.

The CSV storage engine has several limitations:

• It doesn’t support indexes, so every query performs a full table scan.
• All columns must be defined as NOT NULL.
• It doesn’t support transactions.
• It doesn’t support table partitioning.
• AUTO_INCREMENT columns aren’t allowed.

Because of these limitations, the CSV storage engine is mainly used for data exchange, not for everyday database tables.

If your goal is simply to export data as a CSV file, it’s usually better to keep your table as InnoDB and export the results using SELECT ... INTO OUTFILE.

12. Why Does an Old Client Fail to Connect to a New MySQL Server?

A common reason is that the client doesn’t support the authentication method used by newer MySQL servers. The default authentication plugin has changed over the years:

• MySQL 5.7 used mysql_native_password.
• MySQL 8.0 switched the default to caching_sha2_password.
• MySQL 8.4 LTS and later disabled the old plugin by default, and it has since been removed.

If you’re using an older MySQL client or connector that only supports mysql_native_password, you’ll get an authentication error when connecting to a newer server.

You can check which authentication plugin a user account is using with:

mysql> SELECT user, host, plugin
    -> FROM mysql.user
    -> WHERE user="tecmint";
+---------+-----------+-----------------------+
| user    | host      | plugin                |
+---------+-----------+-----------------------+
| tecmint | localhost | caching_sha2_password |
+---------+-----------+-----------------------+
1 row in set (0.00 sec)

If the account is using caching_sha2_password, the best solution is to upgrade your MySQL client or connector. Downgrading the server or trying to switch back to the old authentication plugin is generally not recommended.

The caching_sha2_password plugin provides stronger security by using either a TLS-encrypted connection or an RSA key exchange during authentication.

Another related change that often appears in interviews is how GRANT works.

Older MySQL versions could create a user automatically when you ran a GRANT statement. Modern MySQL no longer allows this. You must create the user first and then grant the required privileges.

mysql> CREATE USER 'tecmint'@'localhost'
    -> IDENTIFIED BY 'StrongPass!23';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT
    -> ON tecmint.*
    -> TO 'tecmint'@'localhost';
Query OK, 0 rows affected (0.00 sec)

This change helps prevent accidentally creating user accounts with incorrect names or privileges.

Many “MySQL won’t accept my password” problems aren’t caused by an incorrect password at all, they happen because the client doesn’t support the server’s authentication plugin. If this helped you understand the issue, share the article with others preparing for MySQL interviews.

13. What’s the Difference Between utf8 and utf8mb4?

This is a common MySQL interview question because many people assume utf8 supports all Unicode characters but it doesn’t.

The original MySQL utf8 character set stores up to 3 bytes per character, which means it can’t store 4-byte Unicode characters such as many emojis and some less common language characters.

The utf8mb4 character set supports up to 4 bytes per character, allowing it to store the entire Unicode character set.

Starting with MySQL 8.0, utf8mb4 became the default character set, along with the utf8mb4_0900_ai_ci collation. The old utf8 alias now points to utf8mb3, which is deprecated and will be removed in a future MySQL release.

You can check the server’s default character set with:

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.01 sec)

If you have an older table that still uses utf8mb3, you can convert it to utf8mb4 with:

mysql> ALTER TABLE users
    -> CONVERT TO CHARACTER SET utf8mb4
    -> COLLATE utf8mb4_0900_ai_ci;

This command converts all character columns in the table to utf8mb4. Keep in mind that MySQL rebuilds the table during the conversion, so it can take some time for large tables.

One more thing to watch for is index size. Since utf8mb4 uses up to 4 bytes per character, indexed VARCHAR columns require more storage than they did with utf8mb3. In some cases, you may need to shorten the indexed column or use a prefix index after converting the table.

14. A GROUP BY Query Worked on MySQL 5.6 and Now Throws an Error. Why?

This usually happens because the ONLY_FULL_GROUP_BY SQL mode is enabled. Starting with MySQL 5.7, ONLY_FULL_GROUP_BY is enabled by default and it requires every column in the SELECT list to either:

• Be included in the GROUP BY clause, or
• Be wrapped in an aggregate function such as SUM(), COUNT(), MAX(), MIN(), or AVG().

For example, this query fails because name is neither grouped nor aggregated:

mysql> SELECT city, name, SUM(posts) FROM users GROUP BY city;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'tecmint.users.name' which is not
functionally dependent on columns in GROUP BY clause

In older MySQL versions, this query often worked, but the value returned for name was arbitrary and could change depending on the data. Modern MySQL prevents this by reporting an error. The correct solution is to use an aggregate function for the non-grouped column.

mysql> SELECT city,
    -> MAX(name) AS name,
    -> SUM(posts) AS total
    -> FROM users
    -> GROUP BY city;
+---------+--------+-------+
| city    | name   | total |
+---------+--------+-------+
| Chennai | Aaron  |   180 |
| Delhi   | Gunjit |    47 |
| Mumbai  | Ravi   |  3200 |
| Pune    | Sam    |    12 |
| Zagreb  | Marin  |    96 |
+---------+--------+-------+
5 rows in set (0.00 sec)

You can check the current SQL mode with:

mysql> SELECT @@sql_mode;

It’s possible to disable ONLY_FULL_GROUP_BY at the session or server level, but that’s usually not the right solution.

In an interview, the best answer is that you would fix the query, not disable the SQL mode. The check exists to prevent ambiguous queries and ensure the results are correct and predictable.

Still seeing ONLY_FULL_GROUP_BY errors after upgrading MySQL? Before disabling it, understand why it’s happening and fix the query instead. If this saved you some debugging time, share the article with a teammate.

15. Rank Rows Without a Subquery Using a CTE and a Window Function

MySQL 8.0 introduced Common Table Expressions (CTEs) and window functions, making many queries simpler and easier to read. These features are now common interview topics because they replace many of the complex subqueries used in older MySQL versions.

A CTE starts with the WITH keyword and creates a temporary named result set that you can reference in the main query.

mysql> WITH active AS (
    ->   SELECT name, city, posts
    ->   FROM users
    ->   WHERE posts > 40
    -> )
    -> SELECT name,
    ->        city,
    ->        posts,
    ->        RANK() OVER (ORDER BY posts DESC) AS rnk
    -> FROM active;
+--------+---------+-------+-----+
| name   | city    | posts | rnk |
+--------+---------+-------+-----+
| Ravi   | Mumbai  |  3200 |   1 |
| Aaron  | Chennai |   180 |   2 |
| Marin  | Zagreb  |    96 |   3 |
| Gunjit | Delhi   |    47 |   4 |
+--------+---------+-------+-----+
4 rows in set (0.00 sec)

In this example:

• The CTE named active selects users with more than 40 posts.
• The main query reads from that result set.
• The RANK() window function assigns a ranking based on the posts column, with the highest number of posts receiving rank 1.

Unlike GROUP BY, window functions don’t combine rows. They return every row from the query while adding calculated values such as rankings, running totals, or averages.

If you want the ranking to restart for each city, add a PARTITION BY clause inside the OVER() clause.

mysql> SELECT name,
    ->        city,
    ->        posts,
    ->        RANK() OVER (
    ->            PARTITION BY city
    ->            ORDER BY posts DESC
    ->        ) AS rnk
    -> FROM users;

Interviewers also like to ask about the difference between MySQL’s ranking functions:

Knowing when to use each one is a good way to show you’re comfortable writing modern MySQL queries instead of relying on older subquery-based approaches.

Where to Go From Here

Don’t just read these questions—run every query on a local MySQL server before your next interview. The candidates who stand out are the ones who can explain not only what a query does, but also why it works and what happens when it fails. The best way to build that confidence is through hands-on practice.

If you’ve been asked a MySQL interview question that isn’t covered here, share it in the comments along with how you answered it. Your feedback helps shape the next part of this interview series, so other readers can prepare for the questions companies are asking today.

Conclusion

Preparing for a MySQL interview isn’t about memorizing syntax, it’s about understanding how MySQL behaves in real-world situations. Many interview questions are based on features that have changed in recent releases, so practicing on a current MySQL version is just as important as knowing the SQL itself.

The 15 questions in this article covered common topics such as NULL handling, GROUP BY, window functions, authentication, character sets, indexes, and modern MySQL features that interviewers frequently ask about. Spend some time running each example on your own system, experimenting with different inputs, and understanding the output.

The more hands-on experience you have, the easier it becomes to explain your reasoning during an interview and that’s often what makes the difference between simply knowing the answer and landing the job.

If this article helped, share it with someone on your team.