Categories
Programming

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
Data Type SyntaxMaximum Size
TINYBLOBMaximum size of 255 Bytes
BLOB(size)Maximum size of 65,535 Bytes
MEDIUMBLOBMaximum size of 16,777,215 Bytes

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
Data Type SyntaxMaximum Size
TINYTEXTMaximum Size of 255 characters
TEXT(size)Maximum Size of 65,535 characters
MEDIUMTEXTMaximum Size of 16,777,215 characters
LONGTEXTMaximum Size of 4 GB or 4,294,967,295 characters

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?

CHARVARCHAR
Column Length is fixedColumn Length isn’t fixed
Maximum characters holding capacity: 255 charactersMaximum characters holding capacity: 4000 characters
Faster than VARCHARLittle slower than CHAR
Uses Static Memory AllocationUses Dynamic Memory Allocation

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. 

PatternWhat it matches
*Zero instance or more instances of the preceding element
^Beginning of String
$End of String
+One instance or more instances of the preceding element
.Any Single Character
[…]Characters are written/listed between square brackets
[^…]Characters not written/listed between square brackets
{n}N instances of preceding element
{m,n}M through to n instances of preceding element
|Matches any one of the pattern
Example: p1|p2|p3 means p1,p2 or p3
?0 or 1 instance of the preceding element
  REGEXPMatches input character with database
Example: REGEXP ‘abc’ or REGEXP ‘10’

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: 

FUNCTIONUSED FOR 
CONCATAdds two or more strings/fields together to form one
CONCAT_WSSimilar like CONCAT but has a separator
INSERTInserts a string within a string at a specific position
REVERSEReverses a string 
STRCMPCompares two strings
LOWER & UPPERConverts string into lowercase & uppercase respectively
FORMATFormats a specified position from A to B in “#”
Example: “###IA”
CURRDATE() & CURRTIME()Returns current date & time
SUBSTRExtracts substring from a specific position of a string
REPLACEReplaces values of a substring within a string with a new substring 

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!