MySQL Database Interview Question and Answer

1. What is default database engine for MySQL?

INODB

2. What are the character data types available in MYSQL?

Char, Varchar, Text

3. What happens if a table has one column defined as TIMESTAMP?

That field gets the current timestamp whenever the row gets altered.

4. What are the optimization techniques for Query optimization performance issues and faster execution of queries

1. litmit
2. keys
3. indexing

5. What are the differences between drop a table and truncate a table?

Drop - will Delete the table date as well as the table structure.

Truncate - Will Delete the table data only. The table structure will remains.

6. How can we change the name and data type of a column of a table?

Alter Table <Table Name> Modify <ColumnName> <DataType>;

7. If the value in the column is repeatable, how do you find out the unique values?

Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

8. How do you convert between Unix timestamps and MySQL timestamps?

UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

9. How are ENUMs and SETs represented internally?

As unique integers representing the powers of two, due to storage optimizations.

10. How do you start and stop MySQL on Windows?

net start MySQL, net stop MySQL

11. What does tee command do in MySQL?

tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.

12. Can you save your connection settings to a conf file?

Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.

13. How do you change a password for an existing user via mysqladmin?

mysqladmin -u root -p password "newpassword"

14. What are some good ideas regarding user security in MySQL?

There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

15. Explain the difference between MyISAM Static and MyISAM Dynamic.

In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

16. What are HEAP tables in MySQL?

HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

17. What are CSV tables?

Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.

18. Explain the difference between FLOAT, DOUBLE and REAL.

FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.

If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?

999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.