MySQL Interview Questions and Answers

What is MySQL?
  • MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage data in tables. It is widely used in web applications, particularly with PHP.
What are the key features of MySQL?
  • Open-source and free to use
  • Support for multiple storage engines
  • ACID compliant transactions
  • High performance and scalability
  • Supports replication and clustering
What is the difference between MySQL and SQL?
  • SQL (Structured Query Language) is a standard language for managing databases. MySQL is a specific relational database management system that uses SQL as its query language.
What are the different types of joins in MySQL?
  • INNER JOIN: Returns rows when there is a match in both tables.
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table.
  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table.
  • FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table.
What is normalization in MySQL?
  • Normalization is the process of organizing data in a database to avoid redundancy and dependency by dividing large tables into smaller tables and establishing relationships between them.
What is denormalization in MySQL?
  • Denormalization is the process of merging tables that were previously split in normalization to improve query performance by reducing the number of joins.
What is a foreign key in MySQL?
  • A foreign key is a column or group of columns in one table that uniquely identifies a row of another table. It is used to establish and enforce a link between the data in two tables.
What is the difference between `CHAR` and `VARCHAR` in MySQL?
  • `CHAR` is a fixed-length string, while `VARCHAR` is a variable-length string. `CHAR` is more efficient when the length of data is known to be fixed, while `VARCHAR` saves space when data lengths vary.
What is an index in MySQL?
  • An index is a database object used to speed up data retrieval operations on a table. It can be created on one or more columns in a table to improve the speed of SELECT queries.
What is a primary key in MySQL?
  • A primary key is a column or set of columns in a table that uniquely identifies each row in the table. It must contain unique values and cannot be NULL.
What is a UNIQUE constraint in MySQL?
  • The `UNIQUE` constraint ensures that all values in a column or a set of columns are unique across the table, preventing duplicate entries.
What is the difference between `DELETE` and `TRUNCATE` in MySQL?
  • `DELETE` removes rows from a table based on a condition and can be rolled back. `TRUNCATE` removes all rows from a table, but it cannot be rolled back.
What is the `AUTO_INCREMENT` feature in MySQL?
  • The `AUTO_INCREMENT` attribute is used for generating a unique number automatically when a new record is inserted into a table. It is commonly used for primary keys.
What is a subquery in MySQL?
  • A subquery is a query nested inside another query, typically used to retrieve data that will be used in the main query. It can be used in SELECT, INSERT, UPDATE, and DELETE statements.
What is a stored procedure in MySQL?
  • A stored procedure is a set of SQL statements stored in the database that can be executed as a single unit. It is used to perform repetitive tasks or complex operations on the database.
What is a trigger in MySQL?
  • A trigger is a set of SQL statements that automatically execute in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE.
What is a view in MySQL?
  • A view is a virtual table that consists of a SELECT query. It allows you to simplify complex queries and present data in a more convenient form.
What is the difference between `HAVING` and `WHERE` in MySQL?
  • `WHERE` filters rows before grouping, while `HAVING` filters rows after grouping in a `GROUP BY` statement. `HAVING` is used with aggregate functions like COUNT, SUM, etc.
What is the `GROUP BY` clause in MySQL?
  • The `GROUP BY` clause is used in a SELECT query to group rows that have the same values into summary rows, often used with aggregate functions like COUNT, SUM, AVG, etc.
What is the `LIMIT` clause in MySQL?
  • The `LIMIT` clause is used to specify the number of rows to return in a result set. It is often used to restrict the output of a query to a subset of results.
What is the difference between `INNER JOIN` and `OUTER JOIN`?
  • An `INNER JOIN` returns only the rows that match in both tables, while an `OUTER JOIN` returns all rows from one table and the matching rows from the other table, or NULL if there is no match.
What is a composite key in MySQL?
  • A composite key is a primary key that consists of two or more columns to uniquely identify rows in a table.
What are temporary tables in MySQL?
  • Temporary tables are tables that are created and used temporarily during a session. They are automatically dropped when the session ends.
What is the purpose of `EXPLAIN` in MySQL?
  • The `EXPLAIN` keyword is used to obtain the execution plan of a query, showing how MySQL will execute it. It helps in optimizing queries by understanding how tables are accessed and joined.
What is a JOIN operation in MySQL?
  • A JOIN operation is used to combine rows from two or more tables based on a related column. It can be used to retrieve data that is distributed across multiple tables.
What are the different types of indexes in MySQL?
  • PRIMARY KEY: A unique identifier for a table.
  • UNIQUE INDEX: Ensures that all values in a column are unique.
  • FULLTEXT INDEX: Used for full-text searches.
  • INDEX: A general-purpose index for speeding up queries.
What is the difference between `TRUNCATE` and `DROP`?
  • `TRUNCATE` deletes all rows in a table, but keeps the table structure, while `DROP` deletes both the table and its data permanently.
What are the types of storage engines in MySQL?
  • InnoDB (default): Supports ACID transactions and foreign keys.
  • MyISAM: A non-transactional engine.
  • Memory: Stores data in memory for fast access.
  • CSV: Stores data in CSV files.
  • Archive: Used for storing large amounts of data with minimal overhead.
What is a `LEFT JOIN` in MySQL?
  • A `LEFT JOIN` returns all rows from the left table and the matched rows from the right table. If no match is found, NULL is returned for columns from the right table.
What is a `RIGHT JOIN` in MySQL?
  • A `RIGHT JOIN` returns all rows from the right table and the matched rows from the left table. If no match is found, NULL is returned for columns from the left table.
What are the advantages of using indexes in MySQL?
  • Indexes improve query performance by reducing the amount of data scanned. They make SELECT queries faster, particularly for large tables.
What are the different types of `ORDER BY` clauses in MySQL?
  • ASC (ascending): Sorts data in ascending order.
  • DESC (descending): Sorts data in descending order.
What is the `INSERT INTO` statement in MySQL?
  • The `INSERT INTO` statement is used to insert new records into a table in MySQL.
What is the `UPDATE` statement in MySQL?
  • The `UPDATE` statement is used to modify existing records in a table.
What is the `DELETE` statement in MySQL?
  • The `DELETE` statement is used to remove rows from a table based on a specified condition.
What is the purpose of `DISTINCT` in MySQL?
  • The `DISTINCT` keyword is used to remove duplicate rows from the result set.
What is the difference between `NULL` and `NOT NULL` in MySQL?
  • `NULL` represents the absence of a value, while `NOT NULL` ensures that a column must contain a value.