1.What are Heap tables?

HEAP tables are present in memory and they are used for high speed storage on temporary basis.

2.Check Important Points about Heap Tables?

• BLOB or TEXT fields are not allowed.
• Only comparison operators can be used like =, <,>, = >,=<.
• AUTO_INCREMENT is not supported by HEAP tables.
• Indexes should be NOT NULL.

3.What are the advantages of MySQL over Oracle?

• MySQL is open source software whereas Oracle is not.
• MySQL is easily portable.
• Administration is supported using MySQL Query Browser
• MySQL is lightweight applications as compare to Oracle.

4.Difference between FLOAT and DOUBLE?

• Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.
• Float takes 4 bytes whereas DOUBLE takes eight bytes.
• FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.
• Float have accuracy up to eight place whereas DOUBLE upto 18 placeS.

5.What is the usage of ENUMs in MySQL?

ENUM is a string object used to specify set of predefined values. When we add/update record(s), Enum field will save only single value from predefined values.

6.What is REGEXP in MySQL?

It is regular expression is a used for complex search using pattern.
See Example
SELECT * FROM users WHERE name REGEXP '^[aeiou]|ok$';

7.Question: What are the drivers in MySQL?

• PHP Driver
• JDBC Driver
• ODBC Driver
• C WRAPPER
• PYTHON Driver
• PERL Driver
• RUBY Driver
• CAP11PHP Driver
• Ado.net5.mxj

8.What is the difference between primary key and candidate key?

Primary Key:
Every row of a table is identified uniquely by primary key and there is only one primary key fore each table.
Candidate keys:
These are those keys which is candidate for primary key of a table means a key which full fill all the requirements of primary key.

9.What does myisamchk do?

Myisamchk compress the MyISAM tables, which reduces the disk or memory usage.

10. What is the difference between MyISAM Static and MyISAM Dynamic?

MyISAM static will have fixed width for all the fields. Also Its easier to restore in case of corruption.
MyISAM Dynamic will have variable width like TEXT,BLOB.

11.What are Federated tables?

A Federated Table is a table which points to a table in an other MySQL database instance (Might be on same OR Different server).

12. What is timestamp meaning in MySQL?

timestamp is datatype in MySQL. If we create a filed with timestamp datatype, it will auto-update with current date/time when record is added/updated.

13.What happens when auto_increment on integer column reaches the max_value in databases?

It stops incrementing anf through following error.
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine.

14.How can you list all indexes in a table?

SHOW INDEX FROM user;

15.What is ISAM?

ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.

16.What is the different between NOW() and CURRENT_DATE()?

NOW () is used to show current year,month,date, hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

17.How many triggers are allowed in MySQL table?

Following are the triggers which are allowed in MySQL table.

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE and
• AFTER DELETE

18.What is Query to delete a Index?

ALTER TABLE table_name DROP INDEX index_name.

19.How to update auto increment value to 1000?

ALTER TABLE tbl_name AUTO_INCREMENT = 1000;

20.What is command to check table is exist?

CHECK TABLE table_name;