Part 1 – MySql Interview Questions and Answers

in this tutorial, i have list down most popular mysql interview questions with answer.This is first part of MySQL interview question series article.i will later post second part of mysql interview questions and answers.Thease mysql interview questions will help to find jobs of fresher,junior developer and web developer post.

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:
[code type=mysql]
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 occurred” .

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