50 essential MySQL interview questions and answers

 

Here is a comprehensive, production-grade guide 50 essential MySQL interview questions and answers curated for freshers and junior developers.

Part 1: Fundamentals of Database & MySQL Architecture

1. What is MySQL?

MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage, store, and retrieve data. Developed and supported by Oracle, it operates on a client-server architecture, storing data in structured tables linked by defined relationships.

2. What is the difference between SQL and MySQL?

SQL (Structured Query Language): The standard, universal programming language utilized to interact with and manipulate relational databases.

MySQL: A specific database software application (an RDBMS) that implements SQL to manage data. Think of SQL as the language and MySQL as the software system that interprets it.

3. What are the key features of MySQL?

Open-Source & Relational: Highly accessible and structured using tables.

High Performance: Optimized execution engines for rapid queries.

Robust Security: Implements host-based verification and encrypted passwords.

Cross-Platform: Runs smoothly across Windows, Linux, and macOS.

Storage Engine Extensibility: Supports interchangeable storage layers like InnoDB and MyISAM.

4. What is a Relational Database Management System (RDBMS)?

An RDBMS is a data management system that arranges information into columns and rows inside tables. It enforces data integrity, constraints, and relationships across separate tables using keys (e.g., Primary and Foreign keys).

5. What are the different types of SQL commands?

SQL statements are classified into five main sub-languages based on functional roles:

DDL (Data Definition Language): Modifies schema/structure (e.g., CREATE, ALTER, DROP).

DML (Data Manipulation Language): Handles data records (e.g., INSERT, UPDATE, DELETE).

DQL (Data Query Language): Reads data records (SELECT).

DCL (Data Control Language): Manages permissions (GRANT, REVOKE).

TCL (Data Transaction Control Language): Controls runtime tasks (COMMIT, ROLLBACK).

6. What is the difference between the InnoDB and MyISAM storage engines?

InnoDB: The default MySQL storage engine. It fully supports ACID transactions, foreign key constraints, and row-level locking, making it ideal for write-heavy applications.

MyISAM: An older, legacy engine. It does not support transactions or foreign keys and uses table-level locking. It is faster only for read-heavy datasets.

7. What are the ACID properties in MySQL?

ACID ensures transaction safety and database reliability:

Atomicity: Guarantees that the entire transaction completes successfully, or all changes are completely aborted.

Consistency: Ensures data shifts only from one valid state to another, upholding constraints.

Isolation: Ensures concurrent transactions execute independently without bleeding into each other.

Durability: Guarantees that committed modifications remain saved in memory, surviving runtime system crashes.

8. What is the default port number for MySQL?

The default network connection port assigned to MySQL is port 3306.

9. What is a transaction in MySQL?

A transaction is a sequence of one or more SQL operations executed sequentially as a single logical unit of work. If every query passes, the result is saved permanently via COMMIT; if any single query fails, the state is completely wiped using ROLLBACK.

10. How do you view all the databases available in a MySQL instance?

You run the SHOW DATABASES; administrative statement to display every active database instance.

Part 2: Data Types & Schema Structures

11. What are the primary data types available in MySQL?

MySQL groups its data types into four general categories:

Numeric: INT, TINYINT, FLOAT, DOUBLE, DECIMAL.

String/Character: CHAR, VARCHAR, TEXT, BLOB.

Date and Time: DATE, TIME, DATETIME, TIMESTAMP, YEAR.

Structured: JSON (available in modern MySQL 5.7+).

12. What is the difference between CHAR and VARCHAR?

CHAR: A fixed-length character field. If you declare CHAR(10) but insert 4 characters, MySQL pads the remaining space with blanks. It is faster for fixed-length data like country codes.

VARCHAR: A variable-length character field. If you define VARCHAR(10) and write 4 characters, it uses space for only 4 characters (plus 1 extra byte for length recording). It is far more memory-efficient.

13. What is the difference between BLOB and TEXT?

TEXT: Stores large strings of non-binary, human-readable text characters (e.g., articles, descriptions). It respects text collations and case insensitivity.

BLOB (Binary Large Object): Stores raw binary data streams like images, audio files, or compiled program objects.

14. What is the difference between DATETIME and TIMESTAMP?

DATETIME: Stores absolute dates and times from year 1000 to 9999. It remains unchanged regardless of the database server's time zone settings.

TIMESTAMP: Stores values representing seconds passed since the Unix epoch. It converts values from the local time zone to UTC for storage and back again automatically. It has a narrower window (ending in the year 2038).

15. What is the ENUM data type in MySQL?

An ENUM is a specialized string data type that restricts a column's values to a strict list of predefined, static text choices.

CREATE TABLE users (

   id INT AUTO_INCREMENT PRIMARY KEY,

   role ENUM('admin', 'moderator', 'member')

);

Part 3: Tables & Constraints

16. What is a Primary Key?

A Primary Key is a designated column (or combination of columns) that uniquely identifies each row inside a table. It cannot contain NULL values, and duplicate entries are forbidden. A table can have only one primary key.

17. What is a Foreign Key?

A Foreign Key is a column in one table that links to a Primary Key in another table. It establishes and enforces referential integrity across related data schemas.

18. What is the difference between Primary Key and Unique Key constraints?

Primary Key: Identifies a record uniquely, cannot contain NULL values, and is limited to exactly one per table. It automatically generates a clustered index.

Unique Key: Guarantees all data values in the column are distinct, allows NULL values (depending on options), and you can declare multiple unique keys on a single table.

19. What does the AUTO_INCREMENT attribute do?

The AUTO_INCREMENT property automatically generates a sequential integer every time a new row is appended to the table. It is typically attached to the Primary Key.

CREATE TABLE products (

   product_id INT AUTO_INCREMENT PRIMARY KEY,

   product_name VARCHAR(100)

);

20. How do you add a new column to an existing table?

You use the ALTER TABLE DDL statement combined with the ADD command clause.

ALTER TABLE products ADD price DECIMAL(10, 2);

21. How can you remove an entire table structure along with its data?

You execute the DROP TABLE command, which permanently deletes the structural definition, metadata, indexes, and all stored row records.

DROP TABLE products;

Part 4: Data Manipulation & Queries

22. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE: A DML operation that removes specific rows based on a WHERE condition. It logs individual row deletions, triggers execution triggers, and can be rolled back.

TRUNCATE: A DDL operation that wipes all records by dropping and recreating the table structure. It is much faster, skips triggers, and cannot be rolled back.

DROP: A DDL statement that completely removes the table schema, constraints, data, and indexes from memory.

23. What is the purpose of the WHERE clause?

The WHERE clause filters records returned by a query based on specific conditions before any grouping operations occur.

SELECT * FROM employees WHERE department = 'Engineering';

24. What is the purpose of the LIKE operator?

The LIKE operator searches for matching patterns within string columns using wildcards:

%: Matches zero or more characters.

_: Matches a single character.

-- Finds employees whose names start with 'A'

SELECT * FROM employees WHERE first_name LIKE 'A%';

25. Explain the GROUP BY clause.

GROUP BY groups rows that share identical column values. It is typically paired with aggregate functions to generate summarized data reports.

SELECT department, COUNT(*) FROM employees GROUP BY department;

26. What is the difference between the WHERE and HAVING clauses?

WHERE: Filters raw individual data rows before any aggregate calculations or GROUP BY operations execute.

HAVING: Filters consolidated group metrics after GROUP BY aggregates are calculated.

SELECT department, AVG(salary) FROM employees

GROUP BY department

HAVING AVG(salary) > 60000;

27. What is the DISTINCT keyword used for?

DISTINCT filters out duplicate matching row values from a query's output array, ensuring only unique values are returned.

SELECT DISTINCT country FROM clients;

28. How do you sort query results in MySQL?

You sort results using the ORDER BY clause, appending ASC for ascending order (default) or DESC for descending order.

SELECT * FROM employees ORDER BY salary DESC;

29. What is the purpose of the LIMIT clause?

LIMIT restricts the total number of rows returned by a query, which is highly useful for pagination features.

-- Fetches only the top 5 highest-paid employees

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

Part 5: Joins & Subqueries

30. What are Joins in MySQL?

Joins are clauses used to combine records from two or more distinct tables based on a shared, logically related column.

31. Explain the differences between the four primary JOIN types.

INNER JOIN: Returns rows only when the join condition is satisfied in both tables.

LEFT JOIN: Returns all records from the left table, plus any matching records from the right table. Unmatched right-side columns return as NULL.

RIGHT JOIN: Returns all records from the right table, plus matching records from the left table. Unmatched left-side columns return as NULL.

CROSS JOIN: Generates the complete Cartesian product by pairing every row of the first table with every row of the second table.

-- Inner Join Example

SELECT employees.name, departments.dept_name

FROM employees

INNER JOIN departments ON employees.dept_id = departments.id;

32. What is a Self Join?

A Self Join occurs when a table is joined with itself. This requires using table aliases to distinguish the left instance from the right instance during the evaluation.

-- Finds employees and their respective managers within the same table

SELECT e.name AS Employee, m.name AS Manager

FROM employees e

INNER JOIN employees m ON e.manager_id = m.emp_id;

33. What is the difference between UNION and UNION ALL?

UNION: Combines the result sets of two separate SELECT queries into one, automatically filtering out any duplicate rows.

UNION ALL: Combines the two result sets exactly as they are, preserving all duplicate entries. It executes much faster because it skips the sorting/deduplication process.

34. What is a Subquery?

A Subquery (or nested inner query) is a complete SELECT query nested inside another outer parent SQL query.

SELECT name FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

Part 6: Database Optimization & Performance

35. What is an Index in MySQL, and why is it used?

An Index is a structural asset that maps table data references in a sorted order. Instead of scanning every single row in a table (a full table scan), MySQL uses indexes to quickly jump to the exact target records, significantly speeding up queries.

36. What is the difference between a Clustered and a Non-Clustered index?

Clustered Index: Dictates the actual, physical order in which data rows are sorted and stored on disk. A table can have only one clustered index (automatically mapped to the Primary Key in InnoDB).

Non-Clustered Index: Built as a separate look-up structure outside the main data rows. It contains pointers back to the original physical row locations. You can create multiple non-clustered indexes on a single table.

37. How can you analyze the performance of a slow-running query?

You prefix your query with the EXPLAIN keyword. This tells MySQL to display its internal query execution plan, showing which indexes it intends to use, how it intends to join tables, and how many rows it expects to scan.

EXPLAIN SELECT * FROM employees WHERE email = 'test@example.com';

38. What is a View in MySQL?

A View is a virtual, logical window built from a dynamic SQL query execution. It does not store physical data records itself; instead, it looks up data from underlying tables on the fly when queried.

CREATE VIEW engineering_staff AS

SELECT name, email FROM employees WHERE department = 'Engineering';

39. What is Database Normalization?

Normalization is the structural process of organizing database tables to reduce data redundancy and eliminate anomalies (insert, update, delete). It breaks large tables down into smaller, well-defined related tables.

40. Explain the first three normal forms (1NF, 2NF, 3NF).

1NF (First Normal Form): Every table cell must contain atomic (indivisible) values, and columns must contain unique data types.

2NF (Second Normal Form): Must satisfy 1NF, and all non-key columns must depend entirely on the absolute Primary Key (eliminates partial dependencies).

3NF (Third Normal Form): Must satisfy 2NF, and no non-key column can depend on another non-key column (eliminates transitive dependencies).

Part 7: Advanced Concepts & Built-in Tools

41. What is a Stored Procedure?

A Stored Procedure is a collection of SQL statements compiled and saved directly on the database server. Applications can call the procedure by name, reducing network traffic and reusing code.

DELIMITER //

CREATE PROCEDURE GetLowStockItems()

BEGIN

   SELECT * FROM products WHERE inventory < 10;

END //

DELIMITER ;

42. What is a Trigger in MySQL?

A Trigger is a named database object that automatically runs an associated SQL block when a specific DML event (INSERT, UPDATE, or DELETE) occurs on a target table.

43. What is a Database Cursor?

A Cursor is an internal control structure used inside stored programs to iterate through and process a query's result set one row at a time.

44. What are Aggregate Functions? Name the most common ones.

Aggregate functions perform a calculation across a set of row values and return a single summary value:

COUNT(): Returns total row counts matching criteria.

SUM(): Calculates the total arithmetic sum.

AVG(): Evaluates the arithmetic mean value.

MAX() / MIN(): Finds the highest and lowest values in a column.

45. What is the difference between the NOW() and CURRENT_DATE() functions?

NOW(): Returns both the current date and time stamp (YYYY-MM-DD HH:MM:SS).

CURRENT_DATE(): Returns only the current date component (YYYY-MM-DD).

46. What does the COALESCE() function do?

COALESCE() evaluates an ordered list of arguments from left to right and returns the very first non-NULL value it encounters.

SELECT COALESCE(phone_work, phone_home, 'No Phone Provided') FROM contacts;

47. How do you manage text pattern matches using regex configurations?

MySQL provides the REGEXP_LIKE() function (or REGEXP operator) to match strings against regular expressions.

-- Finds products containing any digit in their code name

SELECT * FROM products WHERE product_name REGEXP '[0-9]';

48. How do you find duplicate rows in a table?

You use GROUP BY on the columns you want to check for duplicates, then use a HAVING clause to filter for groups with a count greater than 1.

SELECT email, COUNT(*)

FROM users

GROUP BY email

HAVING COUNT(*) > 1;

49. How do you fetch the Nth highest value (e.g., the 2nd highest salary) from a table?

You sort the values in descending order using ORDER BY and use an offset with the LIMIT clause to skip the top rows.

-- Fetches the 2nd highest salary (skips the first row, returns the next)

SELECT salary FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET 1;

50. How do you handle NULL values when performing comparisons in MySQL?

In SQL, NULL represents an unknown or missing value. You cannot use standard comparison operators like = or != with NULL. Instead, you must use the specialized IS NULL or IS NOT NULL operators.

SELECT * FROM employees WHERE manager_id IS NULL;