MySQL Interview Questions and Sample Answers

Updated 12 October 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

MySQL is a popular relational database management system (RDBMS). If you are applying for a database administrator or a similar position, the interviewer would expect you to have a clear understanding of how an RDBMS works in addition to the specific concepts of MySQL. Having a general idea of the type of MySQL questions you may face in a job interview can help you prepare well for it. In this article, we explore some of the common MySQL interview questions for freshers and experienced professionals, along with answering tips and sample answers for several of them.

Basic MySQL Interview Questions For Freshers

Following are some of the beginner-level MySQL interview questions for fresh graduates:

Which programming language does MySQL use?
How does MySQL differ from SQL server?
Describe the technical features of MySQL.
What are the advantages of using MySQL over other database systems?
What are the major differences between MySQL and Oracle?
What is the default port for the MySQL server?
Explain the difference between DOUBLE and FLOAT.
List the different set operations you can perform on a MySQL table.
How is a database different from a table?
What are the different types of tables available in MySQL?
How do you install MySQL?
How do you check the MySQL version?
How do you add columns in a MySQL table?
How do you delete a MySQL table?
How do you change a database name in MySQL?
What is the maximum number of columns we can use in a MySQL table?

Advanced-Level MySQL Interview Questions For Experienced Professionals

Here are some of the in-depth MySQL interview questions for experienced professionals:

What is the difference between Mysql_connect and Mysql_pconnect?
What do you know about ISAM?
When would you use the myisamchk function?
Describe the process for creating a view in MySQL.
Can we drop a primary key in MySQL? If yes, what is the procedure?
How do you reset a user's password?
What are WHITE BOX and BLACK BOX testing of a database?
How do you check the null values in a database?
How do you connect to a MySQL database?
How do you change the MySQL root password?
How do you create a new database in MySQL Workbench?
How do you update a MySQL table?
How do you convert timestamps to date?
How do you create a View in MySQL?
What is a LIKE statement? When do you use a % (percentage) and _ (underscore) in a LIKE statement?
How do you create a Trigger?
Describe the procedure for checking users in MySQL.
What do you know about the SAVEPOINT statement?
What is the difference between Mysql_fetch_object and Mysql_fetch_array?
What functions do you use to encrypt and decrypt data in MySQL?

Common MySQL Interview Questions And Answers

Below are some common MySQL interview questions, along with answering tips and sample answers. If you need to give an example of the coding in your answer, you can write it down on paper or use a laptop to show the example to the interviewer.

1. What is MySQL?

This is a basic question the interviewer may ask towards the beginning of the interview. Focus your answer on the technical aspects rather than the details about the naming of MySQL.

Example: "MySQL is an open-source relational database management system based on structured query language (SQL). It is a multi-threaded platform that supports multiple users. We can use it on embedded and client-server systems. Despite being capable of managing robust databases, MySQL is extremely fast and easy to use."

2. How do you add a foreign key in MySQL?

A foreign key helps us link different tables. You can first describe the concept of a foreign key in a sentence or two and then explain the procedure to add a foreign key.

Example: “A foreign key helps us link a MySQL table with another by creating a parent-child relationship between them. It uses the primary key field to link the tables. We can add a foreign key in a table either by using a CREATE TABLE command or an ALTER TABLE command.

[Show the syntax to the interviewer]

CONSTRAINT constraint_name
FOREIGN KEY [foreign_key_name] (col_name, ...)
REFERENCES parent_tbl_name (col_name,...)
ON DELETE referenceOption
ON UPDATE referenceOption
[Explain the constraints and variables]
Constraint_name specifies a foreign key constraint. Col_name defines the column that would act as a foreign key. Parent_tbl_name specifies the parent table along with the relevant column. Refrence_option defines the referential action to be taken on the delete or update command. We can use any of the five referential actions – cascade, set null, restrict, no action or set default.”

3. How do you import and export databases in MySQL?

Importing and exporting databases is a common task you need to perform as a database administrator. You can either use the phpMyAdmin utility for this or do it through MySQL commands. Explain both the methods to the interviewer.

Example: “We can import and export MySQL database in two ways. One method is to use the phpMyAdmin tool and another one is to use the MySQL command line. To import a database using phpMyAdmin, first log in to phpMyAdmin on the server, create an empty database, click on the import tab, choose the file to import and click on the Go button. Similarly, to export a database, log in to phpMyAdmin, choose the database from the drop-down list, click on the Export tab, choose the relevant options and click on the Go button.

In the command line, we can use the mysqldump to export a database.
[Show the syntax to the interviewer]

· mysqldump -u username -p databasename > dbsample.sql

Similarly, to import a database, we can use this command.

· mysql -u username -p databasename < dbsample.sql”

4. How do you add a user in MySQL?

Describe the procedure for adding a user through the CREATE command. Include sample syntax in your response for added clarity.

Example: “We can add a user in MySQL by using the CREATE command. First, we need to log in to the MySQL account. We can then create a user with the necessary credentials.
[Show the syntax to the interviewer]

CREATE USER ‘newuser' IDENTIFIED BY ‘password';

We can then grant permissions to the new user. For example, we can use GRANT ALL PRIVILEGES command to grant all privileges.
[Show the syntax to the interviewer]

GRANT ALL PRIVILEGES ON * . * TO ‘newuser';”

5. What is the difference between CHAR and VARCHAR in MySQL?

CHAR and VARCHAR are common strings we use to specify the type of data in MySQL. Talk about the major difference between them, namely fixed and variable length of characters.

Example: “CHAR and VARCHAR, both of them define the type of character you want to store. We use CHAR for a fixed-length character type. For example, CHAR(10) would store exactly 10 characters. On the other hand, we use VARCHAR to define a variable length. For example, VARCHAR(10) can store any length of characters up to 10. A CHAR string always uses the same amount of memory irrespective of what we store. VARCHAR is more efficient in using the storage space as it allocates memory dynamically.”

6. Can you store images in MySQL?

Although the general practice is to store images in a separate file system, we can store images in MySQL. In addition to describing the method for storing images in MySQL, you can also discuss why it is advisable to store images separately.

Example: “We can store images in a MySQL database, but that usually puts a heavy load on the database and may slow it down. Hence, it is advisable to store images separately in a directory and store the image location in the database. However, some applications may require images to be in close sync with the database. In such cases, we may need to save images in the database. We can do it by choosing binary large object (BLOB) as the data type.”

7. How do you join two tables in MySQL?

MySQL uses a JOIN clause to connect two or more tables. Describe the four JOIN clauses we use in MySQL.

Example: “We can join two tables using a JOIN clause. It combines two or more tables based on a common column between them. MySQL uses four different JOIN clauses – INNER JOIN, LEFT JOIN, RIGHT JOIN and CROSS JOIN.
An INNER JOIN returns only the matching records in both tables. A LEFT JOIN combines all the records from the left table with the matching records from the right table. A RIGHT JOIN combines all the records from the right table with the matching records from the left table. A FULL JOIN combines all the records of the matching tables.”

8. When do you use a DISTINCT clause in MySQL?

While running a query on a MySQL table, we may often get duplicate rows. The DISTINCT clause helps us remove these duplicate rows.

Example: “We use the DISTINCT clause to avoid duplicate rows from the results while running a query on a MySQL table.”