MySQL interview questions

17 questions from the MySQL Fundamentals: SQL, Joins and Transactions course, each with the correct answer and an explanation. Try to answer before you open the answer.

Module 1: Getting Started

1. What does `USE shop;` do?

  1. Creates a new database
  2. Deletes the `shop` database
  3. Makes `shop` the active database for following queries
  4. Lists every table in the server
Show answer

Answer: Makes `shop` the active database for following queries

`USE` just switches context — no data is created or removed.

Lesson: Databases vs Tables

Module 3: Basic Queries

2. Why insert multiple rows in a single statement?

  1. It's the only way MySQL allows inserts
  2. It skips the NOT NULL constraints
  3. It encrypts the inserted data
  4. It's generally faster than one INSERT per row
Show answer

Answer: It's generally faster than one INSERT per row

Batching reduces round trips to the server.

Lesson: INSERT

Module 4: Modifying Data

3. What happens if you run `DELETE FROM customers;` with no WHERE?

  1. Every row in the table is deleted
  2. Nothing, MySQL requires a WHERE clause
  3. Only the first row is deleted
  4. Only rows created today are deleted
Show answer

Answer: Every row in the table is deleted

MySQL doesn't require WHERE — omitting it deletes all rows.

Lesson: DELETE

Module 6: Aggregation

4. Which counts only non-NULL values in a column?

  1. COUNT(*)
  2. COUNT(column_name)
  3. SUM(column_name)
  4. MAX(column_name)
Show answer

Answer: COUNT(column_name)

COUNT(column) ignores NULLs in that column; COUNT(*) counts every row.

Lesson: Aggregate Functions

Module 7: Advanced Queries

5. What's the key difference between UNION and UNION ALL?

  1. UNION ALL sorts the results
  2. They require the same table name
  3. UNION removes duplicate rows, UNION ALL keeps them
  4. UNION is only for numbers
Show answer

Answer: UNION removes duplicate rows, UNION ALL keeps them

UNION dedupes (extra work); UNION ALL doesn't, so it's faster.

Lesson: UNION

6. How does a window function differ from GROUP BY?

  1. It keeps every row while adding a computed value
  2. It deletes duplicate rows
  3. It creates a new table
  4. It only works on text
Show answer

Answer: It keeps every row while adding a computed value

Window functions calculate over a window of rows without collapsing them.

Lesson: Window Functions & CTEs (MySQL 8)

Module 9: Performance

7. In EXPLAIN output, what does `type: ALL` usually indicate?

  1. The query used an index perfectly
  2. The table has no rows
  3. The query will always be fast
  4. A full table scan happened
Show answer

Answer: A full table scan happened

ALL means MySQL had to read every row — often fixable with an index.

Lesson: EXPLAIN at a Glance

Module 12: Beyond Basics

8. What's the main benefit of a stored procedure?

  1. It saves reusable SQL logic on the server under a callable name
  2. It replaces the need for indexes
  3. It automatically backs up the database
  4. It makes every query use an index
Show answer

Answer: It saves reusable SQL logic on the server under a callable name

Procedures package logic server-side so apps can just CALL it.

Lesson: Stored Procedures at a Glance

9. Which key uniquely identifies each row?

  1. Foreign key
  2. Index only
  3. Primary key
  4. View
Show answer

Answer: Primary key

A primary key has no duplicates and no NULLs.

Lesson: Backup & Restore

10. Which clause filters groups after aggregation?

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

Answer: HAVING

WHERE runs before grouping; HAVING runs after.

Lesson: Backup & Restore

11. Which join keeps all rows from the left table?

  1. LEFT JOIN
  2. INNER JOIN
  3. CROSS JOIN only
  4. UNION
Show answer

Answer: LEFT JOIN

Unmatched right-side columns become NULL.

Lesson: Backup & Restore

12. Which data type is best for money?

  1. FLOAT
  2. DECIMAL
  3. TEXT
  4. BOOLEAN
Show answer

Answer: DECIMAL

DECIMAL stores exact values without rounding errors.

Lesson: Backup & Restore

13. What does ROLLBACK do?

  1. Commits changes
  2. Creates an index
  3. Undoes changes since the transaction started
  4. Deletes the table
Show answer

Answer: Undoes changes since the transaction started

It restores the state before START TRANSACTION.

Lesson: Backup & Restore

14. What does EXPLAIN type: ALL usually signal?

  1. A perfect index use
  2. An empty table
  3. A syntax error
  4. A full table scan
Show answer

Answer: A full table scan

It often means a useful index is missing.

Lesson: Backup & Restore

15. Why avoid using root for an application?

  1. It has far more privileges than needed
  2. It is slower
  3. It cannot connect remotely
  4. It lacks SELECT
Show answer

Answer: It has far more privileges than needed

Least privilege limits the damage of a compromise.

Lesson: Backup & Restore

16. Which tool creates a logical backup of a MySQL database?

  1. mysqlcheck only
  2. mysqldump
  3. ls
  4. git
Show answer

Answer: mysqldump

mysqldump writes schema and data as SQL.

Lesson: Backup & Restore

17. Which practice best prevents SQL injection?

  1. Parameterised queries
  2. Concatenating strings
  3. Using root everywhere
  4. Disabling indexes
Show answer

Answer: Parameterised queries

Parameters keep user input separate from SQL code.

Lesson: Users, Privileges & Security

← Back to the course