Thursday, 3 March 2016

Advanced MySQL Interview Questions and Answers for Experienced

Advanced MySQL Interview Questions and Answers for Experienced

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
INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');




Question: How to get Last insertID in MYSQL ?
Use LAST_INSERT_ID() MySQL Function
INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');
SELECT LAST_INSERT_ID();




Question: How many columns can we create for index?
16


Question: What is Query to delete a Index?
ALTER TABLE table_name DROP INDEX index_name.


Question: How to update auto increment value to 1000?
ALTER TABLE tbl_name AUTO_INCREMENT = 1000;


Question: What is command to check table is exist?
CHECK TABLE table_name;


Question: What is command to display all databases?
SHOW DATABASES;


Question: What is command to display current Date and Time ?
SELECT NOW();


Question: What are the objects you can use with CREATE statement?
  • DATABASE
  • TABLE
  • TRIGGER
  • USER
  • VIEW
  • EVENT
  • FUNCTION
  • INDEX 
  • PROCEDURE.



Question: What are the nonstandard string types?
TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT


Question: What is SQLyog?
The SQLyog program is Software which provide GUI tool for MySQL.


Question: What are string datatype in MySQL?
  • CHAR
  • VARCHAR
  • TINY TEXT
  • TEXT
  • MEDIUM TEXT
  • LONG TEXT
  • BINARY
  • VARBINARY
  • TINYBLOB
  • MEDIUMBLOG
  • BLOB
  • LONGBLOB
  • ENUM
  • SET



Question: How to get the next auto-increment id in mysql?
Use LAST_INSERT_ID() from your MySQL query.

Question: How to update the column where NULL value set?
update `users` set phone='000000000' where  phone is NULL




Question: How to get number of days difference between two date?
SELECT DATEDIFF('2010-10-22', '2010-10-19');



Question: How to reset the autoincrement?
ALTER TABLE tablename AUTO_INCREMENT = 1




Best Related Posts are Following:

  1. MySQL Interview Questions and Answers for 3 year experience.
  2. MySQL Interview questions and answers for 2 year experience.
  3. Advanced MySQL Interview Questions and Answers for Experienced.
  4. How can I make SQL Query case sensitive string comparison on MySQL?.
  5. PHP Mysql Interview Questions and Answers for fresher and experienced.
Labels: