TOP 10 : MySQL Interview Questions For Experienced

Posted on By Dhananjay No Comments on TOP 10 : MySQL Interview Questions For Experienced
DBMS, MySQL, MySQL Interview Questions

When we are going for an Interview we should know the Advanced part of the Subject. If this is MySQL then you should know the Answers of these questions. Although these questions are also asked in Fresher Interview , but best suited for mid level job interview.

What are the storage engines used by MySQL?

Storage engines supported by MySQL are as follows:

    1. InnoDB
    2. MyISAM
  1. Memory
  2. CSV
  3. Merge
  4. Archive
  5. Federated
  6. Blackhole

Which one is the default storage Engine in MySQL?

The default engine is InnoDB.

What is a database engine?

Database Engine is the basic building blocks of storage systems , used by Database Management Systems to run CRUD (INSERT,SELECT,UPDATE,DELETE)instructions.

What is difference between InnoDB and MyISAM?

InnoDB is featured with ACID property of transactions, but MyISAM does not support transaction. MyISAM supports FULLTEXT search index where as InnoDB does not.

What are HEAP tables in MySQL?

HEAP tables are the existing tables in memory.They also known as memory tables and use Indexes instead of BLOB and TEXT.

What are Stored Procedures in MySQL?

Stored Procedure is Batch of SQL statements and saved in database. It also accepts parameters and can be triggered explicitly. It improves the traffic control and gives a modular structure to implement maintainability.

CREATE PROCEDURE show_employee
AS
SELECT FirstName, LastName
FROM employee;
EXEC show_employee;

What is Trigger?

Auto executable sets of SQL statements stored in database are known as Triggers. They are fired when some event CRUD event occurs.When we do some CRUD operations on table the trigger linked with that table fires. Trigger is behaves like a stored procedure but it invoked automatically. It adds a layer of security in database.

What are differences between Trigger and Stored Procedure?

We can execute procedure explicitly whenever we would like , but trigger can only be invoked on an event fire.We can call a Procedure inside another Procedure but we can not invoke a trigger directly inside another trigger. We can use trigger for transaction purpose but we can not use procedure for transactions.

What are the CONSTRAINTs in MySQL?

MySQL CONSTRAINTs used to define the rules for data restrictions. MySQL provides various types of constraints as:
NOT NULL– does not allow NULL value
UNIQUE – does not allow duplicate entry in column
PRIMARY KEY – uniquely identify a rows in a table
FOREIGN KEY – Primary Key of another table is being as a reference in another table
CHECK – Check for the valid data to be saved in the database
DEFAULT – set the default value for the column, in case of no value passed to SQL statement.
We can declare MySQL CONSTRAINT at the time of table creation or we can alter table to apply CONSTRAINT.

What do you mean by index in a table?

MySQL Indexing is the indexing of data as keys to make the accessibility fast and easy. Rather than searching all over the data indexing allows to get directly to the indexed values. It Eliminates the unnecessary iteration because the indexes are stored in the form of B.Tree.

Leave a Reply