30 COMMON MYSQL INTERVIEW QUESTIONS

A. Beginner-Level MySQL Questions:

1. What is MySQL?

   • MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for accessing, managing, and manipulating data stored in databases.

2. What are the major features of MySQL?

   • Open-source, cross-platform support, high performance, support for complex queries, security features, ACID compliance, scalability, replication, and clustering.

3. What is a relational database?

   • A relational database is a type of database that stores data in tables consisting of rows and columns. Each table has a unique key, and relationships between tables are established using foreign keys.

4. What is SQL?

   • SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases, including tasks like querying, inserting, updating, and deleting data.

5. What are the different data types in MySQL?

   • MySQL supports various data types, including:
     • Numeric: INT, FLOAT, DOUBLE, DECIMAL
     • String: VARCHAR, TEXT, CHAR
     • Date and Time: DATE, DATETIME, TIMESTAMP, TIME
     • Binary: BLOB, BINARY

6. What is a primary key?

   • A primary key is a unique identifier for a table record. It ensures that no duplicate values are stored in the primary key column, and each table can have only one primary key.

7. What is a foreign key?

   • A foreign key is a field (or collection of fields) in a table that uniquely identifies a row of another table, establishing a link between the two tables and enforcing referential integrity.

8. What is the difference between CHAR and VARCHAR?

   • CHAR is a fixed-length string, while VARCHAR is a variable-length string. CHAR is used when the length of the string is predictable, while VARCHAR is more space-efficient for varying lengths.

9. What is AUTO_INCREMENT in MySQL?

   • AUTO_INCREMENT is a feature in MySQL that automatically generates a unique identifier for new records in a table, often used for primary key columns.

10. What is the JOIN clause in SQL?

    • JOIN is used to combine rows from two or more tables based on a related column. Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

11. What is INNER JOIN?

    • INNER JOIN returns only those rows that have matching values in both tables being joined.

12. What is LEFT JOIN?

    • LEFT JOIN returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.

13. What is a UNION in SQL?

    • UNION combines the result sets of two or more SELECT queries, removing duplicate rows between the queries. The columns in all SELECT statements must have the same number and data types.

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

    • UNION removes duplicate rows, whereas UNION ALL includes all duplicates from the combined result set.

15. What is the GROUP BY clause?

    • GROUP BY groups rows with the same values into summary rows, often used with aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX().

B. Intermediate-Level MySQL Questions:

16. What is an index in MySQL?

    • An index is a data structure that improves the speed of data retrieval operations on a table. It is used to quickly locate data without scanning the entire table.

17. What are the different types of indexes in MySQL?

    • Common index types in MySQL include:
      • Primary Index: Automatically created for primary keys.
      • Unique Index: Ensures all values in the indexed column are unique.
      • Full-text Index: Used for text searching.
      • Composite Index: Index on multiple columns.

18. What is normalization?

    • Normalization is the process of organizing database tables to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, related tables.

19. What is denormalization?

    • Denormalization is the process of combining tables to reduce the number of joins and improve query performance, often used in read-heavy applications.

20. What is the HAVING clause?

    • HAVING is used to filter records after an aggregation is applied, typically with GROUP BY. It is similar to WHERE, but WHERE is applied before aggregation.

21. What is a stored procedure?

    • A stored procedure is a set of SQL statements that can be executed as a single unit. It is stored in the database and can be called with a specific name, often used to encapsulate complex logic.

22. What is a trigger in MySQL?

    • A trigger is a set of SQL statements that automatically executes when a specified event (INSERT, UPDATE, DELETE) occurs on a table.

23. What is a view in MySQL?

    • A view is a virtual table that is based on the result of a SELECT query. It does not store data physically but provides a way to simplify complex queries.

24. What is a transaction in MySQL?

    • A transaction is a sequence of SQL statements that are executed as a single unit of work. It follows ACID properties (Atomicity, Consistency, Isolation, Durability).

25. What are ACID properties?

    • Atomicity: All operations within a transaction are completed or none are.
    • Consistency: Transactions bring the database from one valid state to another.
    • Isolation: Transactions do not interfere with each other.
    • Durability: Once a transaction is committed, changes are permanent.

C. Advanced-Level MySQL Questions:

26. What is replication in MySQL?

    • Replication is the process of copying data from one MySQL server (master) to one or more servers (slaves) for redundancy and load balancing.

27. What are the different types of replication in MySQL?

    • Master-Slave Replication: Data is written to the master and replicated to slaves.
    • Master-Master Replication: Both servers can act as master and replicate data to each other.
    • Group Replication: Multi-master replication for highly available MySQL clusters.

28. What is the InnoDB storage engine?

    • InnoDB is the default storage engine in MySQL, providing support for ACID-compliant transactions, foreign keys, and crash recovery.

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

    • DELETE: Removes rows from a table based on a condition. It can be rolled back.
    • TRUNCATE: Removes all rows from a table but retains its structure. It cannot be rolled back.
    • DROP: Deletes the entire table, including its structure. It cannot be rolled back.

30. How do you optimize a slow query in MySQL?

    • Techniques include using proper indexing, analyzing query execution plans (EXPLAIN), rewriting queries for efficiency, using JOIN instead of subqueries, avoiding SELECT *, and ensuring hardware resources are adequate.