Top 20 MySQL Interview Questions with Answers

Hello guys, if you are preparing for MySQL and Database interview or a Java developer role with MySQL experience and looking for frequently asked MySQL interview questions and answers then you have come to the right place. Earlier, I have shared popular SQL Interview Questions and common SQL queries from Interviews and in this article, I will share frequently asked MySQL database interview questions with answers. MySQL is very vital as far as web application is concerned. Therefore, this is an area that cannot be taken for granted. This simply means that various questions regarding MySQL have to be asked whenever you are seeking a job. 

Most of the questions are obvious and so you do not need to panic. All you need is to equip yourself with the correct answer. In this article I have mentioned the top 20 questions with answers that you cannot miss in MySQL interview.

These questions are very relevant and you cannot ignore them if indeed you want to excel in your interview. The only choice you have is to go through the questions and answers, master all that you can and put it into use on the day of the interview.

If you know MySQL and used MySQL in past then you can easily answer these MySQL interview question but if you struggle them then I suggest you to first go through these MySQL online courses to learn and revise key MySQL concepts. 

20 MySQL Interview Questions with Answers
You do not need to look anywhere else in order to get MySQL interview questions and answers, you have arrived at the the right place just at the right time. Mastering all these questions with the answers will be an added advantage when you face the interview panel. 

I have keenly prepared this article keeping in mind how your success in the interview is vital to you. Do not be in a hurry but clearly analyze these questions with answers and grasp the answers for use during the interview. Take a look below and you will be sorted.

1. What is MySQL?

Answer: MySQL is an open-source relational database management system.

2. What are the features of MySQL?

Answer: MySQL has a number of features as mentioned below;

• High flexibility – its flexibility is derived from the various applications it is able to support.
• Speed – it is among the very fast database languages.
• Scalable – supports multi-threading and can be able to handle over 50 million rows of data.
• Memory efficiency – memory leakage problem is very minimal hence it increases efficiency.
• Partitioning -
• Dual password support (current and secondary password)
• Easy to use – simple knowledge is required in order to use it.
• Security – has a solid data security layer that ensures sensitive data is protected from unauthorized persons.

3. What is the default port number of MySQL?

Answer: the default port number of MySQL is 3306.

4. What are the advantages of using MySQL?

Answer: Using MySQL comes with its advantages which are as follows;

• Easy maintenance
• High speed
• High security
• Reliable

5. What are the disadvantages of using MySQL?

Answer: MySQL also has its disadvantages which are listed below;

• Limited uses of stored routines and triggers.
• Not easy to make it scalable
• Unsuitable for very large database.
• Does not support SQL check constraints.

6. How can you know the version of the installed MySQL?

Answer: When you want to know the installed version simply go to MySQL workbench then log to the server. Under management, you will see a field called Server Status, click on it and you will see the version.

 

7. Why use MySQL?

Answer: MySQL database comes with various advantages which are; reliable, easy to use and also fast. You can easily access this software by downloading it from the internet.

 

8. What are the drivers available in MySQL?

Answer: There are a couple of drivers available in MySQL. They include; PERL Driver, RUBY Driver, PYTHON Driver, ODBC Driver, PHP Driver, C WRAPPER, JDBC Driver, Ado.net5.mxj and CAP11PHP Driver.

 

9. What are the storage engines used in MySQL?

Answer: MySQL has two types of storage engines; transactional and non-transactional. Supported storage engines in MySQL include;

• MyISAM – this is the original storage engine.
• Memory – creates tables in memory.
• InnoDB – this is the most widely used storage engine.
• CSV- it stores data in CSV files.
• Archive – optimized for high speed inserting.
• Merge – operates on underlying MyISAM tables.
• Blackhole – accepts but does not store data.
• Federated – it enables the separation of MySQL servers in order to create one logical database from many physical servers.
• Example

10. What are federated tables?

Answer: Federated tables are referred to as tables that point to the tables located on other databases on other server.

 

11. What is the difference between BLOB AND TEXT?

Answer: A BLOB is a binary large object that can hold a variable amount of data. Examples are;

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

A TEXT on the other hand is simply a case-sensitive BLOB. Examples are;

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

The main difference between BLOB and TEXT types is that in BLOB values, sorting and comparison is case-sensitive while in TEXT values it is case-insensitive.

12. What is the difference between FLOAT and DOUBLE?

Answer: One main difference between FLOAT and DOUBLE is that, FLOAT stores floating-point numbers with accuracy up to 8 places and allocates 4 bytes while DOUBLE stores floating-point numbers with accuracy up to 18 places and allocates 8 bytes.

 

13. What is ENUMs used for in MySQL?

Answer: ENUM is defined as a string object that is simply used to specify set of predefined values. It can also be used during creation of a table.

 

14. How many TRIGGERS are allowed in MySQL table?

Answer: In MySQL, only six triggers are allowed in MySQL table. The following are the allowed triggers;

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

15. What are the various tables found in MySQL?

Answer: Five types of tables are present in MySQL. These tables are;

• Heap
• Merge
• MyISAM
• INNO DB
• ISAM

16. What is a join in MySQL?

Answer: A join is the combining of rows from two or more tables in relation to related column between them. It helps to retrieve data from multiple tables. Examples of MySQL joins are MySQL INNER JOIN and MySQL LEFT OUTER JOIN.

17. What are the common functions of MySQL?

Answer: MySQL has three types of functions namely; String, Numeric and User-Defined. Some of String functions include;

• ASCII
• CHAR_LENGTH
• CONCAT
• FIELD
• FORMAT
• INSERT
• INSTR
• LCASE
• LEFT
• LENGTH
• LOCATE
• LPAD
• LOCATE
• LTRIM
• MID
• REPEAT
• REPLACE
• REVERSE
• RIGHT
• SPACE
• UCASE
• UPPER

Some of Numeric functions include;

• ABS
• ACOS
• ASIN
• ATAN
• AVG
• CEIL
• CEILING
• COS
• COT
• DEGREES
• DIV
• EXP
• FLOOR
• GREATEST
• LEAST
• LN
• LOG10
• LOG2
• MAX
• MIN
• MOD
• POW
• POWER
• RAND
• ROUND
• SQRT
• SUM
• TAN
• TRUNCATE

18. Differentiate between DELETE and TRUNCATE in MySQL? (answer)

Answer: The following are differences between DELETE and TRUNCATE;

• DELETE is a DML command while TRUNCATE is a DDL command.
• Indexed views are not used in TRUNCATE while in DELETE they can be used.
• DELETE command deletes only rows of data while TRUNCATE command deletes every row permanently from a given table.
• Where command cannot be used with TRUNCATE, whereas it can be used with DELETE.

19. What is cardinality in MySQL?

Answer: In MySQL, cardinality refers to the uniqueness of data values that are contained in a given column in a database table.

20. Is MySQL case-sensitive?

Answer: MySQL is not case sensitive. Its case sensitivity only depends on the kind of operating system in place.

That's all about the frequently asked MySQL Interview Questions with Answers for 1 to 3 years experienced. Lastly, I will advice you to take a close look at these questions and am sure you will be sorted as far as MySQL interview is concerned. These are genuine questions that you will never miss in a MySQL interview no matter what. 

Pay close attention to the questions and answers and you will not stop smiling during your interview. One last thing that you should remember is to be confident and articulate and the answers you have found here will surely take care of your situation. Good luck to you as you prepare.