970_90

Monday, 23 May 2016

MySQL Interview questions and answers for 2 year experience

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?
  1. GRANT the permission
  2. open my.ini search following and comment.
    bind-address = 127.0.0.1
  3. 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;

How to get MySQL tables size for particular database



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');





No comments:

Post a Comment