Common MySql Interview Questions and Answers For Experienced/Freshers

Some Common MySql Interview Questions and Answers For Experienced + Freshers Which mostly asked my interviewer during interview session, As you know MySql is highly popular relational database and good compatible with open source languages like PHP.

Following list of basic Mysql question and answer surely help developers for getting new jobs.

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: 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.
 
Question: How to start and stop MYSql service?
Start mysql service:
service mysqld start

Stop mysql service:
service mysqld stop

Question: What is the default port for MySQL Server?
The default port for MySQL server is 3306
 
Question: What is the difference between MySQL and SQL?
SQL is known as standard query language. It is used to interact with the database like MySQL. MySQL is a database that stores various types of data and keeps it safe.
 
Question: 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
 
Question: Differentiate between FLOAT and DOUBLE in MYSql?
* Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.
* Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.
 
Question: What are the Types of storage engine in MySQL?
Following are the types of storage engine in MYSql
* MyISAM
* Heap
* Merge
* INNO DB
* ISAM

Question: Differentiate CHAR_LENGTH and LENGTH?
CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.
 
Question: What is the difference between CHAR and VARCHAR?
* CHAR and VARCHAR types are different in storage and retrieval.
* When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.
* CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255.
 
Question: What is DDL, DML and DCL?
DDL (Data Definition Language) deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML (Data Manipulation Language) deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT etc. DCL (Data Control Language) includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.
 
Question: How to represent ENUMs and SETs internally?
ENUMs and SETs are used to represent powers of two because of storage optimizations.
 
Question: What is a trigger in MySQL?
A trigger is a set of codes that executes in response to some events.

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: How many Triggers are possible in MySQL?Following are the possible triggers in MYSql.
* Before Insert
* After Insert
* Before Update
* After Update
* Before Delete
* After Delete
 
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