This tutorial will show some MySQL interview questions with the solutions. If you are applying for the role of MySQL developer, you need to go through these questions before going to the interview. This article compiles all the common questions that you should know. Let’s hop onto the question-answers directly.
1. What is the use of the SELECT statement in MySQL?
The purpose of the SELECT statement in MySQL is to obtain data from one or more database tables. This statement allows us to fetch records from all the fields or specified fields that meet the provided criteria.
Here are the primary uses of SELECT in MySQL:
- Data Retrieval: The SELECT statement lets you get information from one or more tables in your database. This allows users to fetch specific data they want.
- Filtering: The SELECT statement lets you narrow down the data you get back with the WHERE clause. This allows users to show the data that meets certain conditions.
- Sorting: The SELECT statement lets you organize your data using the ORDER BY clause. This allows users to arrange the results in a specific order.
- Joining Tables: The SELECT statement allows combining information from different tables using the JOIN clause. This allows users to combine data from different tables based on related columns.
2. What is the MySQL binary log, and how is it used?
MySQL binary log keeps track of all the modifications made to a MySQL database at the binary level, such as UPDATE, DELETE and INSERT statements, and schema changes like ALTER TABLE statements. The binary log can be used for various purposes, such as point-in-time recovery, logging format, enabling binary logging, replication, and debugging. To utilize the binary log, enable it and configure its settings.
3. What sorts of relationships are used in MySQL?
There are three types of relationships in MySQL:
- One-to-One: When two items have a one-to-one relationship, we often include them as columns in the same table.
- One-to-Many: One-to-many (or many-to-one) relationships arise when a single row in one table is connected to a number of rows in another table.
- Many-to-Many: A many-to-many relationship connects many rows in one table to many rows in another table. To build this relationship, we add a third table with the same key column as each of the other tables.
4. Write a query to add a column in MySQL
To add a new column in an existing table in MySQL, you can use the ‘ALTER TABLE’ statement.
Syntax:
ALTER TABLE table_name
ADD column_name column_definitions;Code language: SQL (Structured Query Language) (sql)Example:
Let’s say we have a table named ’employeees’, and we want to add a new column named ’email’ of type VARCHAR(255).
ALTER TABLE employeees
ADD gmail VARCHAR(255);Code language: SQL (Structured Query Language) (sql)Now, update the new column with data from the existing column using ‘UPDATE’.
UPDATE employees
SET gmail = CONCAT(username, '@example.com');Code language: SQL (Structured Query Language) (sql)
5. How can we delete a row or a column in MySQL?
In MySQL, we can delete a row using the DELETE statement and delete a column using the ALTER TABLE statement. Here’s how we can do it:
Delete a Row
To delete a row from the table, use the DELETE statement followed by the WHERE clause, which tells the condition that identifies the row to be deleted. If you skip the WHERE clause, all rows in the table will be deleted.
Syntax:
DELETE FROM table_name
WHERE condition;Code language: SQL (Structured Query Language) (sql)Delete a Column
To delete a column from the table, use the ALTER TABLE statement followed by the DROP COLUMN clause, specifying the name of the table you want to delete.
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;Code language: SQL (Structured Query Language) (sql)6. What are the different types of JOINs in MySQL?
The main types of joins in MySQL are:
1. INNER JOIN: It fetches all rows from various tables in which the join condition is met.
Syntax:
SELECT column_name
FROM table01
INNER JOIN table02
ON table01.column_name = table02.column_name;Code language: SQL (Structured Query Language) (sql)2. RIGHT OUTER JOIN: It fetches all rows from the right table and matches rows from the left table. The result set will be null if there is no common value in the left table.
Syntax:
SELECT * FROM table01
RIGHT JOIN table02
ON table01.column_name = table02.column_name;Code language: SQL (Structured Query Language) (sql)3. LEFT OUTER JOIN: It fetches all rows from the left table and matches rows from the right table. The result set will be null if there is no common value in the right table.
Syntax:
SELECT * FROM table01
LEFT JOIN table02
ON table01.column_name = table02.column_name;Code language: SQL (Structured Query Language) (sql)4. CROSS JOIN: It returns the Cartesian product of the two tables, i.e., all possible combinations of rows from both tables.
Syntax:
SELECT * FROM table01
CROSS JOIN table2;5. SELF JOIN: It is a special type of join operation where a table is joined with itself.
Syntax:
SELECT t1.column1, t1.column2, ..., t2.column1, t2.column2, ...
FROM table_name t1
INNER JOIN table_name t2 ON t1.common_column = t2.common_column;Code language: SQL (Structured Query Language) (sql)7. What are VARCHAR and CHAR in MySQL?
VARCHAR and CHAR are both defined as a string. The main difference is that VARCHAR is a variable length, whereas CHAR is a fixed length. For example, If we define VARCHAR(10), it takes at most ten characters, whereas if we define CHAR(10), it needs exactly ten characters. VARCHAR can be claimed to be more efficient in memory utilization since it supports dynamic memory allocations.
8. How many Triggers are possible in MySQL?
Six triggers are allowed to be used in MySQL, they are:
- Before Insert
- After Insert
- Before Update
- After Update
- Before Delete
- After Delete
Summary
In this tutorial, we have covered eight questions. These questions are essential to prepare for before your test or interview. They cover fundamental concepts and explore comparisons and differences between various techniques. Many interviewers directly ask these questions in SQL developer job interviews. We hope you find this article informative and enjoyable.