MySQL Interview Questions

Last few days I have been working to compile a question and answer set for MySQL interview questions. These questions and answers are compiled from different online resources.

1) What is the difference between Primary Key and Unique Key?

Ans: Both Primary and Unique Key is implemented for Uniqueness of the column. Primary Key creates a clustered index of column where as an Unique creates unclustered index of column. Moreover, Primary Key doesn’t allow NULL value, however Unique Key does allows one NULL value.

2) How many TRIGGERS are allowed in MySql table?

Ans: MySql table allows following 6 triggers: 

 	
 BEFORE INSERT

 	
 AFTER INSERT

 	
 BEFORE UPDATE

 	
 AFTER UPDATE

 	
 BEFORE DELETE

 	
 AFTER DELETE

3) What does myisamchk do?

Ans: It compresses the MyISAM tables, which reduces their disk or memory usage.

4) How is MyISAM table stored?

Ans: MyISAM table is stored on disk in three formats. 

 	
 ‘.frm’ file: storing the table definition

 	
 ‘.MYD’ (MYData): data file 

 	
 ‘.MYI’ (MYIndex): index file

5) What are Heap tables?

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

6) Differentiate CHAR_LENGTH and LENGTH?

Ans: CHAR_LENGTH  is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

6) How to get current MySQL version?

Ans: 
# mysql -p
# mysql;> SELECT VERSION ();

7) What do you mean by % and _ in the LIKE statement?

Ans: % corresponds to 0 or more characters, _ is exactly one character in the LIKE statement.

8) How can we get the number of rows affected by query?

Ans: Number of rows can be obtained by
# mysql -p
# mysql;> SELECT COUNT (user_id) FROM users;

9) What is ISAM?

Ans: ISAM  is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes.

10) What is InnoDB?

Ans: lnnoDB is a transaction safe storage engine developed by Innobase Oy which is a Oracle Corporation now.

11) How to display top 50 rows?

Ans: In MySql, top 50 rows are displayed by using this following query:
# mysql -p
# mysql;> SELECT * FROM LIMIT 0,50;

12) How many columns can be used for creating Index?

Ans: Maximum of 16 indexed columns can be created for any standard table.

13) How do you return the a hundred books starting from 25th?

Ans: 
# mysql -p
# mysql;> SELECT book_title FROM books LIMIT 25, 100;
The first number in LIMIT is the offset, the second is the number.

14) How would you write a query to select all teams that won either 2, 4, 6 or 8 games?

Ans:
# mysql -p
# mysql;> SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8)

15) How would you select all the users, whose phone number is null?

Ans:
# mysql -p
# mysql;> SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

16) How can you see all indexes defined for a table?

Ans:
# mysql -p
# mysql;> SHOW INDEX FROM techpreparation_questions;

17) What is the difference between CHAR and VARCHAR?

Ans:

 	
 CHAR and VARCHAR are differ in storage and retrieval.

 	
 CHAR column length is fixed while VARCHAR length is variable.

 	
 The maximum no. of character CHAR data type can hold is 255 character while VARCHAR can hold up to 4000 character.

 	
 CHAR is 50% faster than VARCHAR.

 	
 CHAR uses static memory allocation while VARCHAR uses dynamic memory allocation.

18) What does ” i_am_a_dummy flag” do in MySQL?

Ans: The " i_am_a_dummy flag" enables MySQL engine to refuse any UPDATE or DELETE statement to execute if the WHERE clause is not present.

19) What is the difference between Unix timestamps and MySQL timestamps?

Ans: Actually both Unix timestamp and MySQL timestamp are stored as 32-bit integers but MySQL timestamp is represented in readable format of YYYY-MM-DD HH:MM:SS format.

20) What is SQLyog?

Ans: SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.