MySQL Interview questions and answers for 2 year experience

Question: What is current stable version of MySQL
Version: 5.7.12 / Dated: 11 April 2016

Question: How to change MySQL data directory? Wamp Server Directory: E:\wamp\www
MySQL Directory Path: E:\wamp\bin\mysql\mysql5.5.24
Open mysql configuration file. (In My Case file: my.ini in E:\wamp\bin\mysql\mysql5.5.24).
Search for "datadir" Replace

datadir=E:/wamp/bin/mysql/mysql5.5.24/data

with

datadir=F:/wamp/bin/mysql/mysql5.5.24/data

Question: From where I can change the MySQL setting?
open my.ini in E:\wamp\bin\mysql\mysql5.5.24

Question: How to allow remote connection to mysql?

GRANT the permission
open my.ini search following and comment.
```
bind-address = 127.0.0.1
```
restart the MySQL server

Question: How to get MySQL Results as comma separated?

SELECT GROUP_CONCAT(id) as city_comma FROM `cities` where country_code='US';
/*1,3,7,15,19,21,30,31,32,51,54,60,61,65,67,70,73,7*/

Question: How to get MySQL tables size for particular database?

SELECT table_name "Table Name",sum( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",sum( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES  where table_schema='mydb' GROUP BY table_name;

Question: Can we join same table twice ?
Yes, We can do.

SELECT t.phone1, t.phone2, 
   t1.SomeOtherFieldForPhone1, t2.someOtherFieldForPhone2
FROM Table1 t
JOIN Table2 t1 ON t1.phone = t.phone1
JOIN Table2 t2 ON t2.phone = t.phone2

Question: How to change the collation of column, table and database?
Change the collation of column

ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Change the collation of Table

ALTER TABLE `mydb`.`hotel`  ENGINE=INNODB AUTO_INCREMENT=8 COMMENT='' ROW_FORMAT=DEFAULT CHARSET=utf8 COLLATE=utf8_icelandic_ci;

Change the collation of Database

ALTER DATABASE `mydb` CHARACTER SET latin1 COLLATE latin1_general_ci;

Question: How to declare a variable in MySQL?
How to set variable

set @start = 1, @finish = 10;

How to use static variables

select @start, @finish

Question: How to get records with max value for each group of grouped results

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;

Question: How to write case-insensitive query ?
Make the both in uppercase and search like below

select * from users where upper(name) = upper('Hello');

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 replace null with 0 in MySQL?

SELECT COALESCE(column_name, 0) AS column_name FROM table_name;

IF column_name have NULL value, It will return 0
IF column_name have Non-NULL value, It will return column value.

Question: Why we should not use mysql_connect functions?
You should not use mysql_* functions like mysql_query(), mysql_connect() or mysql_real_escape_string(). Because Its already deprecated in PHP5.5 and not in active development.
Instead of this, you can use mysqli OR PDO functions.

Question: How to get a list of MySQL user accounts?

SELECT User FROM mysql.user;

Question: Which MySQL data type to use for storing boolean values?
You can use bit.

Question: How to concatenate multiple rows column into one?
You can use GROUP_CONCAT

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')FROM hobbies GROUP BY person_id

Output

1 cricket, footbal
1 volleyball, footbal, wrestling
1 cricket, volleyball

Web Technology Experts Notes

Monday 23 May 2016