MySQL - Interview Questions and Answers

81) How do you determine the location of MySQL data directory?

The default location of MySQL data directory in windows is C:\mysql\data or C:\Program Files\MySQL\MySQL Server 5.0 \data.

82) What is the usage of regular expressions in MySQL?

In MySQL, regular expressions are used in queries for searching a pattern in a string.

* Matches 0 more instances of the string preceding it.
+ matches one more instances of the string preceding it.
? Matches 0 or 1 instances of the string preceding it.
. Matches a single character.
[abc] matches a or b or z
| separates strings
^ anchors the match from the start.
"." Can be used to match any single character. "|" can be used to match either of the two strings
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   

83) What is the usage of the "i-am-a-dummy" flag in MySQL?

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

84) Which command is used to view the content of the table in MySQL?

The SELECT command is used to view the content of the table in MySQL.

Explain Access Control Lists.

An ACL is a list of permissions that are associated with an object. MySQL keeps the Access Control Lists cached in memory, and whenever the user tries to authenticate or execute a command, MySQL checks the permission required for the object, and if the permissions are available, then execution completes successfully.

85) What is InnoDB?

InnoDB is a storage database for SQL. The ACID-transactions are also provided in InnoDB and also includes support for the foreign key. Initially owned by InnobaseOY now belongs to Oracle Corporation after it acquired the latter since 2005.

86) What is ISAM?

It is a system for file management developed by IBM, which allows records to access sequentially or even randomly.

87) How can we run batch mode in MySQL?

To perform batch mode in MySQL, we use the following command:

88) What are federated tables?

Federated tables are tables that point to the tables located on other databases on some other server.

89) What is the difference between primary key and candidate key?

To identify each row of a table, we will use a primary key. For a table, there exists only one primary key.

A candidate key is a column or a set of columns, which can be used to uniquely identify any record in the database without having to reference any other data.

90)

What are the drivers in MySQL?

Following are the drivers available in MySQL:

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxz

Interview :: MySQL