1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) based on Structured Query Language (SQL). It is one of the most popular databases in the world, often used with PHP and other server-side languages.

2. What are the key features of MySQL?

Relational database
Open-source
High performance
Scalability and flexibility
Strong data security
Support for large databases

3. What is the difference between MySQL and SQL?

SQL: The standard language used to interact with a database.
MySQL: A specific database management system that uses SQL.

4. What is a "Storage Engine" in MySQL?

A software module that the database management system uses to create, read, and update data from a database. MySQL supports several storage engines like InnoDB and MyISAM.

5. What is the difference between InnoDB and MyISAM?

InnoDB: Supports transactions, row-level locking, and foreign keys. It is the default engine.
MyISAM: Does not support transactions or foreign keys; uses table-level locking. It is faster for read-heavy operations.

6. What is the "Primary Key" in MySQL?

A unique identifier for each record in a table. It cannot be null and must be unique.

7. What is "Auto Increment"?

A feature that allows a unique number to be generated automatically when a new record is inserted into a table (used mainly for primary keys).

8. What is a "Foreign Key"?

A constraint that establishes a link between data in two tables. It ensures referential integrity.

9. What is a "Join" in MySQL?

Used to query data from multiple tables based on a relationship between them. (Inner Join, Left Join, Right Join, Cross Join).

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

DELETE: Removes specific rows; can be filtered with WHERE; logged.
TRUNCATE: Removes all rows from a table; faster; not logged per row.
DROP: Removes the entire table structure and data from the database.

11. What is a "View" in MySQL?

A virtual table that displays data from one or more tables based on a stored query.

12. What is a "Trigger"?

A database object that is automatically executed in response to certain events (like INSERT, UPDATE, or DELETE) on a particular table.

13. What is a "Stored Procedure"?

A collection of SQL statements that are stored in the database and can be called by name to perform a specific task.

14. What is a "Stored Function"?

Similar to a stored procedure but it returns a single value and can be used in SQL statements like SELECT.

15. What are "Indexes" in MySQL?

Data structures that help retrieve data from a table more quickly. Types include PRIMARY, UNIQUE, INDEX (non-unique), and FULLTEXT.

16. What is a "Composite Index"?

An index on multiple columns of a table. It is useful when queries filter or sort by multiple columns.

17. What is "Database Normalization"?

The process of organizing data to minimize redundancy and avoid data anomalies (1NF, 2NF, 3NF).

18. What is the `LIMIT` clause?

Used to specify the number of records to return in a query (e.g., SELECT * FROM table LIMIT 10).

19. What is the `OFFSET` clause?

Used with LIMIT to skip a specific number of records (e.g., LIMIT 10 OFFSET 20 returns records 21-30).

20. What is the `GROUP BY` clause?

Used to group rows that have the same values into summary rows.

21. What is the `HAVING` clause?

Used to filter groups created by the GROUP BY clause. It's like WHERE but for groups.

22. What is the difference between `WHERE` and `HAVING`?

WHERE: Filters rows before grouping.
HAVING: Filters groups after grouping.

23. What is the `UNION` operator?

Combines the result-set of two or more SELECT statements (removes duplicates).

24. What is the `UNION ALL` operator?

Combines the result-set of two or more SELECT statements (includes duplicates).

25. What is "Database Transaction"?

A sequence of operations performed as a single logical unit of work. It follows ACID properties.

26. What are the "ACID" properties in MySQL?

Atomicity: All or nothing.
Consistency: The database remains in a valid state.
Isolation: Concurrent transactions don't interfere.
Durability: Once committed, changes are permanent.

27. What is "Explain" statement?

Used to analyze how MySQL executes a query, providing information about table access, joins, and index usage.

28. What is the "Slow Query Log"?

A log that records queries that take longer than a specified amount of time to execute, helping in performance tuning.

29. What is the `LIKE` operator?

Used for pattern matching in a WHERE clause (using % for any number of characters and _ for a single character).

30. What is a "Subquery"?

A query nested inside another query (e.g., in SELECT, FROM, or WHERE).

31. What is the difference between `CHAR` and `VARCHAR`?

CHAR: Fixed-length string; pads with spaces.
VARCHAR: Variable-length string; only uses necessary space.

32. What is the `BLOB` data type?

Binary Large Object; used for storing large binary data like images or files.

33. What is the `TEXT` data type?

Used for storing large amounts of string data.

34. What is the `ENUM` data type?

A string object with a value chosen from a list of permitted values specified at table creation.

35. What is the `SET` data type?

A string object that can have zero or more values, chosen from a list of permitted values.

36. What is "Referential Integrity"?

A condition where every foreign key value in a table points to an existing primary key value in the related table.

37. What is `ON DELETE CASCADE`?

A foreign key constraint that automatically deletes child records when the corresponding parent record is deleted.

38. What is `ON UPDATE CASCADE`?

A foreign key constraint that automatically updates child records when the corresponding parent record's primary key is updated.

39. What is "Full-Text Search"?

A feature that allows searching for words or phrases within large text columns using the MATCH() and AGAINST() functions.

40. What is "Replication" in MySQL?

The process of copying data from one MySQL server (source) to one or more servers (replicas).

41. What is "Master-Slave" (Source-Replica) Replication?

A setup where one server handles all writes (Source) and other servers (Replicas) mirror the source for read operations.

42. What is "Binary Log" (Binlog)?

A log that contains all events that describe database changes (e.g., inserts, updates). It's crucial for replication and recovery.

43. What is "MySQL Workbench"?

A visual tool for database architects, developers, and DBAs to design, develop, and manage MySQL databases.

44. What is "SQL Injection" and how to prevent it?

A security flaw where attackers can inject malicious SQL code. Prevent it using prepared statements and parameterized queries.

45. What is the `NOW()` function?

Returns the current date and time.

46. What is the `CONCAT()` function?

Used to join two or more strings together into a single string.

47. What is the `ORDER BY` clause?

Used to sort the result-set in ascending or descending order.

48. What is the `DISTINCT` keyword?

Used to return only different (unique) values.

49. What is "Database Sharding" in MySQL?

A technique of splitting a large database into smaller, more manageable pieces (shards) across multiple servers.

50. What is the purpose of the `GRANT` and `REVOKE` commands?

GRANT: Gives specific permissions to a user.
REVOKE: Removes specific permissions from a user.

MySQL Interview Questions

Table of Contents

What is MySQL?

What are the key features of MySQL?

What is the difference between MySQL and SQL?

What is a "Storage Engine" in MySQL?

What is the difference between InnoDB and MyISAM?

What is the "Primary Key" in MySQL?

What is "Auto Increment"?

What is a "Foreign Key"?

What is a "Join" in MySQL?

What is the difference between <code>DELETE</code>, <code>TRUNCATE</code>, and <code>DROP</code>?

What is a "View" in MySQL?

What is a "Trigger"?

What is a "Stored Procedure"?

What is a "Stored Function"?

What are "Indexes" in MySQL?

What is a "Composite Index"?

What is "Database Normalization"?

What is the <code>LIMIT</code> clause?

What is the <code>OFFSET</code> clause?

What is the <code>GROUP BY</code> clause?

What is the <code>HAVING</code> clause?

What is the difference between <code>WHERE</code> and <code>HAVING</code>?

What is the <code>UNION</code> operator?

What is the <code>UNION ALL</code> operator?

What is "Database Transaction"?

What are the "ACID" properties in MySQL?

What is "Explain" statement?

What is the "Slow Query Log"?

What is the <code>LIKE</code> operator?

What is a "Subquery"?

What is the difference between <code>CHAR</code> and <code>VARCHAR</code>?

What is the <code>BLOB</code> data type?

What is the <code>TEXT</code> data type?

What is the <code>ENUM</code> data type?

What is the <code>SET</code> data type?

What is "Referential Integrity"?

What is <code>ON DELETE CASCADE</code>?

What is <code>ON UPDATE CASCADE</code>?

What is "Full-Text Search"?

What is "Replication" in MySQL?

What is "Master-Slave" (Source-Replica) Replication?

What is "Binary Log" (Binlog)?

What is "MySQL Workbench"?

What is "SQL Injection" and how to prevent it?

What is the <code>NOW()</code> function?

What is the <code>CONCAT()</code> function?

What is the <code>ORDER BY</code> clause?

What is the <code>DISTINCT</code> keyword?

What is "Database Sharding" in MySQL?

What is the purpose of the <code>GRANT</code> and <code>REVOKE</code> commands?