MySql Interview Questions and Answers

Question: What is MySQL?

MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language, the most popular language for adding, accessing, and processing data in a database. Because it is open source, anyone can download MySQL and tailor it to their needs in accordance with the general public license. MySQL is noted mainly for its speed, reliability, and flexibility.

Question: What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values and that can be used during table creation.

Question: Define REGEXP?

REGEXP is a pattern match in which matches pattern anywhere in the search value.
See REGEXP eg: How to search for exact matched word using MySql Query

Question: How do you get the number of rows in MYSql?

SELECT COUNT (id) FROM items

Question: How do you return the a hundred items starting from 20th position?

SELECT item_name FROM items LIMIT 20, 100.

Where the first number in LIMIT is the offset, the second is the number.

Question: Give string types available for column in MYSql?

Following are the string types in MYSql
* SET
* BLOB
* ENUM
* CHAR
* TEXT
* VARCHAR

Question: What are the disadvantages of MySQL?

* MySQL is not so efficient for large scale databases.
* It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
* Transactions are not handled very efficiently.

Question: How many columns can you create for an index?

You can create maximum of 16 indexed columns for a standard table.

Question: How to get current MySQL version?

SELECT VERSION ();

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

Question: What is the query to display current date and time?

SELECT NOW(); -- Display only current date SELECT CURRENT_DATE();

Question: What is InnoDB?

lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

Question: How can we run batch mode in mysql?

mysql ; mysql mysql.out

Question: What is MySQL data directory?

MySQL data directory is a place where MySQL stores its data. Each subdirectory under this data dictionary represents a MySQL database. By default the information managed my MySQL = server mysqld is stored in data directory.

Question: What is the use of mysql_close()?

it can be used to close connection opened by mysql_connect() function.

Question: Why MySQL is used?

MySQL database server is reliable, fast and very easy to use. This software can be downloaded as freeware and can be downloaded from the internet

Question: In which language MySQL is written?

MySQL is written in C and C++ and its SQL parser is written in yacc.

Question: What are the technical features of MySQL?

MySQL has the following technical features:-
* Flexible structure
* High performance
* Manageable and easy to use
* Replication and high availability
* Security and storage management

Question: What is maximum length of column name, table name and database name?

column name can be upto 64 chars, table name can be upto 64 and database name can be upto 64 chars.