MySQL interview exam questions and answers

What is MySQL?
Ans. MySQL is an open-source DBMS that is built, supported and distributed by MySQL AB. It is now owned by Oracle.

Why do we use MySQL?
Ans. MySQL database server is dependable, fast and user-friendly. One can download the software from the internet and as freeware.

What is the default port for MySQL Server?
Ans. The default port for MySQL server is 3306

What is REGEXP?
Ans. REGEXP is a pattern match in which matches pattern anywhere in the search value

What does a TIMESTAMP do on UPDATE CURRENT_TIMESTAMP data type?
Ans. When the table is created, Zero is used for updating the TIMESTAMP column. UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever other fields of the table are changed.

What does myisamchk do?
Ans. It is used for compressing the MyISAM tables thereby reducing their disk or memory usage.

What do you understand by federated tables?
Ans. They let you have access to the tables which are located on other databases on other servers.

How do we find out the auto increment that was assigned on Last insert?
Ans. LAST_INSERT_ID will return the last value assigned by Auto increment and it not mandatory for the table name to be specified.

How do you login to MySQL using Unix shell?
Ans. To login, we can make use of this command:
#[mysql dir]/mysql –h hostname –u<UserName> -p<password>

Differentiate between CHAR_LENGTH and LENGTH
Ans. While CHAR_LENGTH is character count, the LENGTH is a byte count. The numbers are the different for Unicode and other encodings but the same for Latin characters.

How can you see all the indexes defined for a table?
Ans. You can define indexes for the table by:
SHOW INDEX FROM <tablename>;

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

What is InnoDB?
Ans. InnoDB is a transaction safe storage engine developed by Inno Oy, now Oracle Corporation.

How many columns can you use in creating Index?
Ans. You can only create a maximum of 16 indexed columns for any standard table

What do you mean by % and _ in the LIKE statement?
Ans. % corresponds to 0 or more characters. But _ is exactly one character in the LIKE statement.