Top 20 MySQL Interview Questions Answered

MySQL is a multi-user SQL database management system founded by MySQL AB. Currently, Oracle owns MYSQL. It is the world’s 2nd most used & popular open-source database, i.e., free to use. A significant factor behind its popularity is its compatibility with different languages (like C, C++, JAVA, PHP, PERL, etc.), notably PHP. PHP is mostly used in web development accompanied by MySQL.

Being popular, interviewers expect a candidate to be thorough with MySQL. Here are Top MySQL Interview Questions, which might be pretty helpful to you.

MySQL Interview Questions

1) What are the technical specifications of MySQL?

Ans:

• High Efficiency
• Easy to Use & Manageable
• Flexible Structure
• Replication
• Portable
• Security & Storage Management

2) Is there any difference between MySQL & SQL? If yes, then what?

Ans:

SQL or Structured Query Language is a database language used for creation, deletion, retrieving or modifying, etc. of the database. Meanwhile, MySQL is a database that stores different types of data safely. So MySQL is a database & SQL is a database language which works on MySQL.

3) MySQL supports large databases up to which extent?

Ans:

MySQL supports up to 50 million rows or tables. And the table limit is up to 4 GB. However, you can increase these limits according to your operating system’s strength. Theoretically, the size of the table can extend up to 8 million terabytes. 

4) List all categories of datatypes of MySQL.

Ans:

Type of data declared is termed as “Datatype.” Each data type has a fixed amount of space which will be allotted if declared. Example: declaration of int will create 2 bits. MySQL has three categories of data types; Numeric, Time & String. 

There are eight datatypes categorized in Numeric, five in the Time & eight in the String. 

5) What are the different tables in MySQL?

Ans:

There are five different tables in MySQL, out of which MyISAM is the default database engine. 

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

6) Name the triggers allowed in MySQL.

Ans: There are six triggers allowed in MySQL as follows:

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete 

7) What are the differences between the heap table & temporary table?

Ans:

Heap Table:

• Found in memory. It works as storage temporarily. 
• BLOB & TEXT fields aren’t allowed
• Indexes should be “NOT NULL”
• Doesn’t supports “AUTO_INCREMENT”
• Can be shared among clients
• Only comparison operators can be used (=,<,>,>=, <=)

Temporary Table:

• Used to store provisional data
• Temporarily stored data is deleted after client session ends
• Aren’t shared among clients
• Special syntax is used; “create temporary table”

8) What is the difference between MySQL_connect & MySQL_pconnect?

Ans:

MySQL_connect:

• Opens a new connection to the database
• Depending on request you need to open & close a database connection
• Opens page whenever loaded

MySQL_pconnect:

• In MySQL_pconnect, “p” stands for “Persistent” so its persistent connection
• A database cannot be closed
• No need to open & close thus suitable for sites with huge traffic

9) How to display nth highest salary from a table in MySQL?

Ans: 

• nth highest salary:
  • Select distinct(salary) from the employee order by salary desc limit (n-1),1
• 3rd highest salary:
  • Select distinct(salary) from employee order by salary desc limit 2,1
• 6th highest salary:
  • Select distinct(salary) from employee order by salary desc limit 5,1

10) What is BLOB & TEXT in MySQL?

Ans: 

BLOB:

• BLOB or Binary Large Object stores the variable amount of data. 
• There are four types of BLOB:
  • TINYBLOB
  • BLOB(size)
  • MEDIUMBLOB
  • LONGBLOB

TEXT:

• Case-insensitive BLOB
• Values are a character string or non-binary string
• Have a character set
• Four types of TEXT:
• TINYTEXT
• TEXT     
• MEDIUM TEXT
• LONGTEXT

The factor that makes BLOB & TEXT different is “sorting & comparison.” It is performed case sensitive for BLOB & case-insensitive for TEXT values.

11) What are the differences between CHAR & VARCHAR?

12) What is SQLyog?

Ans:

SQLyog program is a GUI tool for admin. It’s a popular MySQL manager & admin tool. It’s an integration of MySQL administration, phpMyAdmin & other MySQL GUI tools.

13) How is the MyISAM table stored?

Ans:

You can store MyISAM table in the following format on disk:

• ‘.frm‘ : File
• ‘.MYI‘: (MY Index) : Index file
• ‘.MYD‘: (MYData) : Data File

14) How can we make a conversion between UNIX & MySQL timestamp?

Ans: 

• UNIX_TIMESTAMP is the command used to convert MySQL timestamp to Unix timestamp
• FROM_TIMESTAMP is the command used to convert Unix timestamp to MySQL timestamp

15) What is the difference between UNIX & MySQL timestamp?

Ans:

You can store UNIX & MySQL timestamp in 32-bit integers. However, MySQL is represented in “YYYY-MM-DD HH:MM:SS” format.

16) How to use “Like Condition”?

Ans:

LIKE condition or pattern patch has three primary rules for use:

• Used in the pattern to match anyone character
• “%” corresponds to zero or more characters
• “_” is exactly one character
• ESCAPE used to provide ESCAPE character in the pattern

17) What are Regular Expressions? State their uses.

Ans:

Regular Expressions are for complex search in a string. It’s a way of specifying patterns for search. 

18) For what we use “i-am-a-dummy flag” in MySQL?

Ans:

 “i-am-a-dummy” flag is used at places where we want to deny or stop “UPDATE & DELETE” commands unless there’s a WHERE clause present.

19) How to add a new column & delete an existing column in a table?

Ans: 

• Add a new column:
  • ALTER TABLE ADD COLUMN column_name datatype(size);
• Delete an existing column:
  • ALTER TABLE DROP COLUMN column_name;

20) What are the common MySQL functions?

Ans: 

Conclusion:

These were Top MySQL Interview Questions commonly asked by interviewers. The purpose is to make you equipped for it. I hope you liked it!