TOP MySQL Interview Questions and Answers
What is MySQL?
MySQL is an open source DBMS which is built, supported and distributed by MySQL AB (now acquired by Oracle)
What is ISAM?
ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.
How to get current MySQL version?
SELECT VERSION (); is used to get the current version of MySQL.
How can you see all indexes defined for a table?
Indexes are defined for the table by:
SHOW INDEX FROM <tablename>;
What is InnoDB?
lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.
What are the technical features of MySQL?
MySQL database software is a client or server system which includes
- Multithreaded SQL server supporting various client programs and libraries
- Different backend
- Wide range of application programming interfaces and
- Administrative tools.
What storage engines are used in MySQL?
Storage engines are called table types and data is stored in files using various techniques.
- Storage mechanism
- Locking levels
- Capabilities and functions.
What do you mean by % and _ in the LIKE statement?
% corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.
How MySQL Optimizes DISTINCT?
DISTINCT is converted to a GROUP BY on all columns and it will be combined with ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
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.
What are the drivers in MySQL?
Following are the drivers available in MySQL:
- PHP Driver
- JDBC Driver
- ODBC Driver
- C WRAPPER
- PYTHON Driver
- PERL Driver
- RUBY Driver
- CAP11PHP Driver
How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.
How to enter Characters as HEX Numbers?
If you want to enter characters as HEX numbers, you can enter HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (Ox).
A HEX number string will be automatically converted into a character string, if the expression context is a string.
What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
• BLOB or TEXT fields are not allowed
• Only comparison operators can be used =, <,>, = >,=<
• AUTO_INCREMENT is not supported by HEAP tables
• Indexes should be NOT NULL
Give string types available for column?
The string types are:
How can we find out which auto increment was assigned on Last insert?
LAST_INSERT_ID will return the last value assigned by Auto_increment and it is not required to specify the table name.