Wirecard Latest MySQL Interview Questions

What is BLOB?

- BLOB stands for binary large object.
- It that can hold a variable amount of data.

There are four types of BLOB based on the maximum length of values they can hold:

- TINYBLOB
- BLOB
- MEDIUMBLOB
- LONGBLOB

What is TEXT?

TEXT is case-insensitive BLOB. The four types of TEXT are:

- TINYTEXT
- TEXT
- MEDIUMTEXT
- LONGTEXT

What is the difference between BLOB and TEXT?

- In BLOB sorting and comparison is performed in case-sensitive for BLOB values.
- In TEXT types sorting and comparison is performed case-insensitive.

Wirecard Most Frequently Asked Latest MySQL Interview Questions Answers

How is MyISAM table stored?

MyISAM table is stored on disk in three formats.
- ‘.frm’ file – storing the table definition
- ‘.MYD’ (MYData) - data file
- ‘.MYI’ (MYIndex) – index file

Explain advantages of MyISAM over InnoDB?

- MyISAM follows a much more conservative approach to disk space management – storing each MyISAM table in a separate file, which can be further compresses, if required.
- InnoDB stores the tables in tablespace. Further optimization is difficult with them.

How would concatenate strings in MySQL?

With the use of - CONCAT (string1, string2, string3)

How would you get the current date in Mysql?

By using

SELECT CURRENT_DATE();

How would you enter Characters as HEX Numbers?

- To enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X).
- Alternatively, just prefix HEX numbers with (Ox).

How are MySQL timestamps seen to a user?

- MySQL time stamps are seen to a user in a readable format : YYYY-MM-DD HH:MM:SS.

How will you export tables as an XML file in MySQL?

MYSQL’s query browser has a provision called “Export Result Set” which allows the tables to be exported as XML.

What is the use of i-am-a-dummy flag in MySQL?

Using the i-am-dummy flag makes the SQL engine refuse any Updates or deletes to execute if the WHERE clause is not present. It is very useful when using delete statements. Using i-am-dummy flag will not allow the following statement to execute:

Delete from employee;

What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

Mysql_fetch_object returns the result from the database as objects while mysql_fetch_array returns result as an array. This will allow access to the data by the field names.

E.g. using mysql_fetch_object field can be accessed as $result->name and using mysql_fetch_array field can be accessed as $result->[name]. mysql_fetch_row($result):- where $result is the result resource returned from a successful query executed using the mysql_query() function.

Example:

$result = mysql_query(“SELECT * from students");
while($row = mysql_fetch_row($result))
{
        Some statement;
}

What is difference between mysql_connect and mysql_pconnect?

Mysql_connect() opens a new connection to the database while mysql_pconnect() opens a persistent connection to the database. This means that each time the page is loaded mysql_pconnect() does not open the database. Mysql_close() cannot be used to close the persistent connection. Though it can be used to close mysql_connect().

What is MySQL data directory? How to determine the location of the data directory?

MySQL stores its data on the disk on the data dictionary. Each subdirectory under this data dictionary represents a MySQL database, inside those directories. By default the information managed my MySQL = server mysqld is stored in data directory.A default location of data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data..

What you can use Regular Expression for in MySQL? Support your answer with an example.

Regular expressions in MySql are used in queries for searching a pattern in a string.

1. * Matches 0 more instances of the string preceding it.
2. + matches 1 more instances of the string preceding it.
3. ? Matches 0 or 1instances of the string preceding it.
4. . Matches a single character.
5. [abc] matches a or b or z
6. | separates strings
7. ^ anchors the match from the start.

REGEXP can be used to match the input characters with the database.

Example:

The following statement retrieves all rows where column employee_name contains the text 1000 (example salary):

Select employee_name
From employee
Where employee_name REGEXP ‘1000’
Order by employee_name

“.” Can be used to match any single character. “|” can be used to match either of the two strings

How will you export tables as an XML file in MySQL?

From the command prompt type the following statement:

mysql -u test --xml -e 'SELECT * FROM t1' > t1.xml

where ‘–u test‘ is the user name, --xml indicates the type of the file is xml, -e for export

What is the use of i-am-a-dummy flag in MySQL?

The flag i-am-a-dummy flag makes the MySQL engine to deny the UPDATE and DELETE commands unless the WHERE clause is present.

What are the differences between MySQL_fetch_array(), MySQL_fetch_object(), MySQL_fetch_row()?

The mysql_fetch_object() returns the result from the database as an object.

Ex: $result->name

The mysql_fetch_array() returns the result from the database as an associative array or numeric array or both by using mysql_NUM or mysql_ASSOC options.

EX: $result[0] ,$result['name']

The mysql_fetch_row() returns the result from the database as a numeric array.

Ex: $result[0].