LTI MySQL Most Frequently Asked Latest Interview Questions Answers

What are the column comparisons operators?

The = , <>, <=, <, >=, >,<<,>>, <=>, AND, OR, or LIKE operators are used in column comparisons in SELECT statements.

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.mxj

What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?

TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to  current time whenever there is a change in other fields of the table.

What is the difference between primary key and candidate key?

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

LTI MySQL Most Frequently Asked Latest Interview Questions Answers

How do you login to MySql using Unix shell?

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

How do you control the max size of a HEAP table?

Maximum size of Heal table can be controlled by MySQL config variable called max_heap_table_size.

How can we get the number of rows affected by query?

Number of rows can be obtained by

SELECT COUNT (user_id) FROM users;
1
SELECT COUNT (user_id) FROM users;

Is Mysql query is case sensitive?

No.

SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;
1
2
3
SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;


All these examples are same. It is not case sensitive.

What is the difference between the LIKE and REGEXP operators?

LIKE and REGEXP operators are used to express with ^ and %.


SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";
1
2
SELECT * FROM employee WHERE emp_name REGEXP "^b";
SELECT * FROM employee WHERE emp_name LIKE "%b";

What is the difference between BLOB AND TEXT?

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –

TINYBLOB
BLOB
MEDIUMBLOB and
LONGBLOB
They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types

TINYTEXT
TEXT
MEDIUMTEXT and
LONGTEXT
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.

The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

How MySQL Optimizes DISTINCT?

DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.


SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
1
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

How to enter Characters as HEX Numbers?

If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).

A HEX number string will be automatically converted into a character string, if the expression context is a string.

What does myisamchk do?

- It compresses the MyISAM tables, which reduces their disk or memory usage

How can we convert between Unix & MySQL timestamps?

- MySQL timestamp can be converted into Unix timestamp using the command UNIX_TIMESTAMP.
- Unix timestamp can be converted into MySQL timestamp using the command FROM_UNIXTIME.

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.

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..