MySQL Interview Questions

Top 50 common MySQL interview questions and answers, along with examples where applicable:

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that allows users to store and manage data.

2. How do you create a new database in MySQL?

Example:

CREATE DATABASE mydatabase;

3. What is the difference between MyISAM and InnoDB storage engines in MySQL?

MyISAM is non-transactional, while InnoDB is a transactional storage engine with support for foreign keys and ACID compliance.

4. How do you create a new table in MySQL?

Example:

CREATE TABLE employees (

    employee_id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50)

);

5. How do you insert data into a MySQL table?

Example:

INSERT INTO employees (first_name, last_name)

VALUES ('John', 'Doe');

6. What is a primary key in MySQL, and why is it important?

A primary key is a column or a set of columns that uniquely identify each row in a table. It enforces data integrity and ensures uniqueness.

7. How do you create a primary key constraint on a table in MySQL?

Example:

ALTER TABLE employees

ADD PRIMARY KEY (employee_id);

8. Explain the difference between CHAR and VARCHAR data types in MySQL.

CHAR stores fixed-length strings, while VARCHAR stores variable-length strings.

9. How do you retrieve data from a MySQL table?

Example:

SELECT * FROM employees;

10. What is an index in MySQL, and why is it used?

- An index is a data structure that improves the speed of data retrieval operations. It's used to quickly locate rows in a table.

11. How do you create an index on a column in MySQL?

- Example:

sql CREATE INDEX idx_last_name ON employees (last_name);

12. Explain the difference between UNION and UNION ALL in MySQL.

- UNION combines the result sets of two or more SELECT statements, removing duplicate rows. UNION ALL also combines result sets but includes duplicates.

13. What is the purpose of the MySQL LIMIT clause?

- The LIMIT clause is used to restrict the number of rows returned by a query.

- Example:

sql SELECT * FROM employees LIMIT 10;

14. How do you update data in a MySQL table?

- Example:

sql UPDATE employees SET salary = 50000 WHERE employee_id = 101;

15. Explain the COMMIT and ROLLBACK statements in MySQL.

- COMMIT saves all pending changes in a transaction, while ROLLBACK undoes changes made in a transaction.

16. What is a foreign key, and how is it used in MySQL?

- A foreign key is a constraint that enforces referential integrity by ensuring that values in one table's column correspond to values in another table's primary key column.

17. Explain the difference between a LEFT JOIN and INNER JOIN in MySQL.

- A LEFT JOIN returns all records from the left table and the matched records from the right table. An INNER JOIN returns only the matched records from both tables.

18. How do you find the number of rows in a table in MySQL?

- Example:

sql SELECT COUNT(*) FROM table_name;

19. What is a subquery in MySQL, and how is it different from a JOIN?

- A subquery is a query nested inside another query. It can be used to retrieve data for use in the main query. A JOIN is used to combine rows from two or more tables based on a related column.

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

- Example:

sql ALTER TABLE table_name ADD new_column_name data_type;

21. Explain the concept of a trigger in MySQL.

- A trigger is a set of actions that are automatically executed when a specified event occurs in the database.

22. What is the purpose of the MySQL GROUP BY clause?

- The GROUP BY clause is used to group rows that have the same values in specified columns and apply aggregate functions to those groups.

- Example:

sql SELECT department, AVG(salary) FROM employees GROUP BY department;

23. How do you delete data from a table in MySQL?

- Example:

sql DELETE FROM employees WHERE employee_id = 101;

24. Explain the concept of a view in MySQL.

- A view is a virtual table that is based on the result of a SELECT query. It allows you to simplify complex queries and control access to data.

25. What is the purpose of the MySQL HAVING clause?

- The HAVING clause is used to filter the results of a GROUP BY query based on a specified condition.

- Example:

sql SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

26. How do you create a unique constraint on a table in MySQL?

- Example:

sql ALTER TABLE table_name ADD CONSTRAINT uc_column_name UNIQUE (column_name);

27. What is a stored procedure in MySQL?

- A stored procedure is a precompiled set of one or more SQL statements that can be executed as a single unit.

28. How do you create a stored procedure in MySQL?

- Example:

sql DELIMITER // CREATE PROCEDURE GetEmployee(IN emp_id INT) BEGIN SELECT * FROM employees WHERE employee_id = emp_id; END // DELIMITER ;

29. What is the purpose of the MySQL ORDER BY clause?

- The ORDER BY clause is used to sort the result set of a query in ascending or descending order.

- Example:

sql SELECT * FROM employees ORDER BY last_name ASC;

30. What is the difference between a function and a procedure in MySQL?

- A function returns a value, while a procedure does not return a value but can perform actions within the database.

31. How do you create an auto-increment column in MySQL?

- Example:

sql CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );

32. What is the purpose of the MySQL DISTINCT keyword?

- The DISTINCT keyword is used to retrieve unique values from a column