Top 30 MySQL Interview Questions and Answers

This article will cover the top 30 MySQL interview questions and answers. These are the top MySQL interview questions asked by hiring managers.

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that stores and manages data. It is one of the most popular databases today and is commonly used for web applications.

2. What is a database schema in MySQL?

A database schema is a logical container for objects such as tables, views, indexes, and procedures. It defines the structure of the database and how the data is stored.

3. How do you create a table in MySQL?

To create a table in MySQL, you can use the following SQL statement:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
);

Example of creating a table named "users"

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

4. What is a primary key in MySQL?

A primary key is a column or a combination of columns that uniquely identifies each row in a table.

It is used to enforce data integrity and to establish relationships between tables.

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

To add a new column to an existing table in MySQL, you can use the following SQL statement:

ALTER TABLE table_name
ADD column_name datatype;

For example - if you want to alter the table users

ALTER TABLE users
ADD phone VARCHAR(20);

6. What is a foreign key in MySQL?

A foreign key is a column or a combination of columns that references a primary key in another table.

It is used to establish relationships between tables and enforce referential integrity.

7. How do you create a foreign key in MySQL?

To create a foreign key in MySQL, you can use the following SQL statement:

ALTER TABLE child_table
ADD CONSTRAINT foreign_key_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

For example: To add foreign key for table "orders" -> user_id will be the foreign key

ALTER TABLE orders
ADD CONSTRAINT fk_user_id
FOREIGN KEY (user_id)
REFERENCES users (id);

8. What is a trigger in MySQL?

A trigger is a special type of stored program that is automatically executed in response to certain events, such as INSERT, UPDATE, or DELETE operations on a table.

9. How do you create a trigger in MySQL?

To create a trigger in MySQL, you can use the following SQL statement:

For example, to create a trigger called “update_order_count” that updates the “order_count” column in the “users” table after a new order is inserted into the “orders” table:

CREATE TRIGGER update_order_count
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE users
    SET order_count = order_count + 1
    WHERE id = NEW.user_id;
END;

10. What is a view in MySQL?

A view is a virtual table that is based on the result of a SELECT statement. It can be used to simplify complex queries or to restrict access to sensitive data.

11. How do you create a view in MySQL?

To create a view in MySQL, you can use the following SQL statement.

For example, to create a view called “top_customers” that shows the top 10 customers by total order amount:

CREATE VIEW top_customers AS
SELECT customers.id, customers.name, SUM(orders.amount) as total_amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_amount DESC
LIMIT 10;

12. What is a stored procedure in MySQL?

A stored procedure is a named set of SQL statements that can be executed as a single unit.

It can be used to encapsulate complex business logic and improve performance by reducing network traffic.

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

To create a stored procedure in MySQL, you can use the following SQL statement.

For example, to create a stored procedure called “get_customer_orders” that returns all orders for a given customer ID:

CREATE PROCEDURE get_customer_orders (IN customer_id INT)
BEGIN
    SELECT *
    FROM orders
    WHERE customer_id = customer_id;
END;

14. What is a transaction in MySQL?

A transaction is a sequence of SQL statements that are executed as a single unit of work.

It ensures data integrity by guaranteeing that either all of the statements are executed or none of them are.

15. How do you start a transaction in MySQL?

To start a transaction in MySQL, you can use the following SQL statement:

START TRANSACTION;

16. How do you commit a transaction in MySQL?

To commit a transaction in MySQL, you can use the following SQL statement:

COMMIT;

17. How do you rollback a transaction in MySQL?

To rollback a transaction in MySQL, you can use the following SQL statement:

ROLLBACK;

18. What is a join in MySQL?

A join is a method for combining data from two or more tables based on a common column.

It can be used to query data from multiple tables in a single query.

19. What are the different types of joins in MySQL?

There are four types of joins in MySQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

20. What is an inner join in MySQL?

An inner join returns only the rows that have matching values in both tables.

21. How do you perform an inner join in MySQL?

To perform an inner join in MySQL, you can use the following SQL statement:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

For example, to join the “orders” and “customers” tables on the “customer_id” column:

SELECT *
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.id;

22. What is a left join in MySQL?

A left join returns all the rows from the left table and the matching rows from the right table, or NULL values if there is no match.

23. How do you perform a left join in MySQL?

To perform a left join in MySQL, you can use the following SQL statement:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

For example, to join the “orders” and “customers” tables and include all customers, even those with no orders:

SELECT *
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

24. What is a right join in MySQL?

A right join returns all the rows from the right table and the matching rows from the left table, or NULL values if there is no match.

25. How do you perform a right join in MySQL?

To perform a right join in MySQL, you can use the following SQL statement:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

For example, to join the “orders” and “customers” tables and include all orders, even those with no customers:

SELECT *
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.id;

26. What is a full outer join in MySQL?

A full outer join returns all the rows from both tables, including those with no matches in the other table.

27. How do you perform a full outer join in MySQL?

MySQL does not support a full outer join directly, but it can be emulated using a union of a left join and a right join:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column
WHERE table1.column IS NULL;

28. What is a subquery in MySQL?

A subquery is a query that is nested inside another query.

It can be used to retrieve data for a specific condition or to perform a calculation on a subset of data.

29. How do you use a subquery in MySQL?

To use a subquery in MySQL, you can include it within parentheses and use it as a value in another query.

For example, to find all orders that are greater than the average order amount:

SELECT *
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

30. What is a trigger in MySQL?

A trigger is a named set of SQL statements that are automatically executed when a specified event occurs.

It can be used to enforce business rules, log changes to a table, or perform other actions based on data changes.

31. What is a view in MySQL, and how is it different from a table?

A view in MySQL is a virtual table that is based on the result of a SQL statement. It contains no data of its own but instead retrieves data from one or more tables.

A view can be used to simplify complex queries or to provide a simplified view of a table.

Unlike a table, a view does not have its own storage and is dependent on the underlying tables.

32. Can you modify data through a view in MySQL?

In most cases, you can modify data through a view in MySQL.

However, there are some restrictions.

For example, you cannot modify data that is based on a GROUP BY clause, or data that is derived from multiple tables.

To modify data through a view, you can use the same SQL statements as you would for a table.