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.

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!