1. What is MySQL?
MySQL is an open source relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL).

2. Difference between UNION and UNION ALL?
- UNION: Combines the results of two or more SELECT statements and removes duplicate rows from the result set.
- UNION ALL: Also combines the results of two or more SELECT statements but includes all rows, including duplicates, in the result set.

3. What is the purpose of the AUTO_INCREMENT attribute?
The AUTO_INCREMENT attribute is used to automatically generate a unique value for a column whenever a new row is inserted into the table.

4. How can you optimize a MySQL query?
Query optimization can be achieved through proper indexing, avoiding SELECT *, optimizing joins, and using EXPLAIN to analyze query performance.

5. What is a primary key in MySQL?
A primary key is a unique identifier for each row in a table. It ensures that each row in a table is uniquely identifiable.

6. What is a foreign key in MySQL?
A foreign key is a field in a table that refers to the primary key of another table. It establishes a relationship between two tables.

7. How do you handle NULL values in MySQL?
NULL values can be handled using functions like IS NULL, IS NOT NULL, COALESCE, or by setting appropriate default values.

8. What is the difference between CHAR and VARCHAR data types?
CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type.

9. Which are the storage engines?
- MYISAM: It is an older storage engine that is known for its simplicity and high-speed performance, especially for read-heavy workloads. However, it does not support transactions or foreign keys, making it less suitable for applications requiring data integrity and concurrency.

- InnoDB: It provides ACID (Atomicity, Consistency, Isolation, Durability) transaction support, row-level locking, foreign key constraints, and crash recovery capabilities. It is well-suited for high-performance OLTP (Online Transaction Processing) applications.

- Memory (Heap): This storage engine stores data in memory and uses hash indexes, providing extremely fast read and write access. However, data in memory tables is not persistent and is lost when the MySQL server restarts. It is suitable for temporary tables and caching purposes.

- Merge: This storage engine allows multiple MyISAM tables to be logically grouped together and queried as a single table. It simplifies data partitioning and management for large datasets.

- Archive: This storage engine is optimized for storing large amounts of data with minimal space requirements. It achieves compression by storing rows in a compressed format, making it suitable for storing historical data or log files.

- CSV: This storage engine stores data in Comma-Separated Values (CSV) format files. It is useful for importing and exporting data between MySQL tables and external applications that support CSV format.

- Federated: This storage engine allows a MySQL server to access data from remote MySQL servers as if it were local. It facilitates data integration and distributed query processing across multiple servers.

- Blackhole: This storage engine discards all data written to it and always returns an empty result set when queried. It is typically used for replication or auditing purposes where data is captured but not stored locally.

10. What is MySQL indexes?
MySQL indexes are special kind of additional information which stores sorted data as per mentioned column(s) with pointer-position. So by using indexes, database access becomes faster.

11. What are types of Indexes?
- Unique Index: It is one in which all column values must be unique.

- Primary Key Unique Index: It is a unique index in which no value can be null.

- Simple/Regular/Normal Index: It is index where the values don't need to be unique and they can be null.

- Fulltext Index: It is index used for fulltext searches.

- Descending Index: It is index (regular index) stored in the reversed order.

12. What is BLOB?
BLOB, which stands for a Binary Large Object, is a MySQL data type that can store images, PDF files, multimedia and other types of binary data.

13. What is a trigger?
A trigger is a special type of stored-procedure that is invoked automatically in response to certain events on a particular table.

14. How many Triggers are available in MySQL?
There are six Triggers available to use in the MySQL database:
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete

15. Explain the difference between Truncate and Delete commands.
- Truncate removes all rows from table. Delete removes rows from table according to the where condition.

- Truncate is DDL command. Delete is DML command.

- Truncate can not be rollback. Delete can be rollback.

- Truncate resets auto-increment counter. Delete does not reset auto-increment counter.

16. Explain types of relationships in MySQL.
- One-to-One Relationship: Each record in one table is related to only one record in another table.

- One-to-Many Relationship: Each record in one table can be related to multiple records in another table.

- Many-to-One Relationship: Multiple records in one table are related to only one record in another table.

- Many-to-Many Relationship: Multiple records in one table can be related to multiple records in another table.

17. What are the different types of joins?
- Inner Join: It returns all rows from multiple tables where the join condition is satisfied

- Left Join: It returns all rows from the left table specified in the ON condition and only those rows from the other table where the join condition is fulfilled

- Right Join: It returns all rows from the right table specified in the ON condition and only those rows from the other table where the join condition is fulfilled

- Cross Join: It returns all records from both tables, by making cartesian product of rows from the both tables

18. Explain the concept of Normalization.
Normalization is the process of organizing data in a database efficiently. It involves breaking down large tables into smaller, more manageable tables and defining relationships between them to reduce redundancy and dependency. This helps improve data integrity, minimize data duplication, and enhance database performance. Overall, normalization aims to ensure that each piece of data is stored in a logical and structured manner, making it easier to maintain and query the database.

19. What is a stored procedure?
A stored procedure is a collection of pre-compiled SQL statements, grouped together in a function that can be called on-demand with specific input parameters.

20. How can you find duplicate rows in a MySQL table?
Duplicate rows can be found using the DISTINCT, GROUP BY, HAVING clauses or by using a self-join.

21. What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows that have the same values into summary rows, typically used with aggregate functions like SUM, COUNT, AVG, etc.

22. What is a Super key in MySQL?
In MySQL, a super key is a set of one or more columns that uniquely identifies each row in a table. It is a broader concept than a primary key and can include more columns than necessary to uniquely identify rows. All primary keys are super keys, but not all super keys are primary keys. Additionally, candidate keys and composite keys are also types of super keys.

23. Different types of normalization forms in MySQL?
- First Normal Form (1NF): Eliminates repeating groups by ensuring that each column contains atomic values, and there are no repeating groups or arrays.

- Second Normal Form (2NF): Ensures that non-key attributes are fully functional dependent on the entire primary key, eliminating partial dependencies.

- Third Normal Form (3NF): Removes transitive dependencies by ensuring that non-key attributes are only dependent on the primary key and not on other non-key attributes.

- Boyce-Codd Normal Form (BCNF): A stricter version of 3NF, ensuring that every determinant in a table is a candidate key.

24. What is the default MySQL port number?
MySQL’s default port is 3306.

25. Explain the purpose of the LIMIT clause in MySQL.
The LIMIT clause is used to restrict the number of rows returned by a query, which is useful for pagination or limiting the result set size.

26. What is a View?
A view is a virtual table based on the result-set of a stored SELECT query. It can be used to simplify complex queries or provide restricted access to data.

27. What is the significance of HAVING clause in MySQL?
The HAVING clause in MySQL is used to filter rows returned by a GROUP BY clause based on specified conditions. It is similar to the WHERE clause but is applied after grouping and aggregation functions.

28. How can you prevent SQL injection in MySQL?
- Use Parameterized Queries (Prepared Statements)
- Input Validation
- Escape User Input
- Least Privilege Principle
- Whitelist Input Filtering
- Disable Dynamic SQL
- Regular Security Audits

29. How can you backup a MySQL database?
MySQL databases can be backed up using tools like mysqldump or by using MySQL Workbench.

30. How can you monitor MySQL performance?
MySQL performance can be monitored using tools like MySQL Enterprise Monitor, performance schema, EXPLAIN, and third-party monitoring tools.