Mysql Interview Questions and Answers:

What are the different tables present in MySQL? Which type of table is generated when we are creating a table in the following syntax:

create table employee(eno int(2),ename varchar(10))?

Total 5 types of tables we can create
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM
MyISAM is the default storage engine as of MySQL 3.23. When you did not mentioned storage engine then MyISAM is set table default engine.

How To Create a Table?

If you want to create a table, you can run the CREATE TABLE statement as shown in the following sample script:
include “connection.php”;
$sql =“CREATE TABLE `membership_plan` (
`mem_id` int(11) NOT NULL auto_increment,
`mem_plan_name` varchar(25) NOT NULL,
`mem_cost` int(3) NOT NULL,
`mem_limit` int(3) NOT NULL,
`modified_date` datetime NOT NULL,
PRIMARY KEY (`mem_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;”;
if (mysql_query($sql, $con)) {
print(“Table Membership plan is created.\n”);
} else {
print(“Error accord.\n”);
}
mysql_close($con);
?>
The mysql_query return true and false statement on create table,Above program return the “Table Membership plan is created” or if Unsuccessful then return “error accured” .

What is the purpose of the following files having extensions: frm, myd, and myi? What these files contain?

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type.
The ‘.frm’ file stores the table definition.
The data file has a ‘.MYD’ (MYData) extension.
The index file has a ‘.MYI’ (MYIndex) extension,

What are the differences between DROP a table and TRUNCATE a table?

Drop – deletes the data as well as structure
Truncate – deletes only the data, and resets the auto increment column to 0
Delete – Deletes the selected/all rows from a table, it doest not reset auto increment., here we can delete set of records by specifying the condition in where clause

Note: If Delete is used, the date can be rollback or commit depending on the requirements.

What is the maximum length of a table name, a database name, or a field name in MySQL?

Database name: 64 characters
Table name: 64 characters
Column name: 64 characters

How many values can the SET function of MySQL take?

MySQL SET function can take zero or more values, but at the maximum it can take 64 values.