Thursday, 3 March 2016

Question: What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.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.

Question: 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.

Question: Difference between FLOAT and DOUBLE?1. Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.2. Float takes 4 bytes whereas DOUBLE takes eight bytes.3. FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.4. Float have accuracy up to eight place whereas DOUBLE upto 18 placeS.

Question: 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.

Question: 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$';

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

Question: 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.

Question: What does myisamchk do?
Myisamchk compress the MyISAM tables, which reduces the disk or memory usage.

Question: 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.

Question: 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).

Question: 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.

Question: 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.

Question: How can you list all indexes in a table?
SHOW INDEX FROM user;

Question: What is ISAM?
ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.

Question: 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.

Question: 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

Question: How to insert if record have containing single quotes?
Escape it with Slash like below