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;

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

Best Related Posts are Following:

1. MySQL Interview Questions and Answers for 3 year experience.
2. MySQL Interview questions and answers for 2 year experience.
3. Advanced MySQL Interview Questions and Answers for Experienced.
4. How can I make SQL Query case sensitive string comparison on MySQL?.
5. PHP Mysql Interview Questions and Answers for fresher and experienced.
6. Differences between Stored Procedures and Functions in MYSQL.
7. PHP MySQL interview questions and answers for experienced.

PHP Interview Questions for 3 year Experience

Question: How to explode a string using multiple delimiters("," and "|" )?

$string='php, interview | questions , and | answers ';
$output = preg_split( "/(\,|\|)/", $string );
print_r($output );
/*
Array ( [0] => php [1] => interview [2] => questions [3] => and [4] => answers ) 
*/

Question: What is Late static bindings in PHP?
Late static bindings comes in PHP 5.3.0. It is used to reference the called class in the context of static inheritance.


Question: Why cause $_FILES may be empty when uploading files to PHP?

Following reason may be for empty of $_FILES

1. multipart/form-data is missing in form tag. <form action="vanilla-upload.php" enctype="multipart/form-data" method="POST"> </form>
2. /tmp folder does not have enough permission. In this folder uploaded file moved temporary.
3. You have upload heavy files more than allowd in php.ini (post_max_size and upload_max_filesize)
4. file_uploads is off in php.ini

Question: How to convert a UNIX Timestamp to Formatted Date?

$timestamp='1463721834';
echo date("Y-m-d h:i:s A", $timestamp);//2016-05-20 05:23:54 AM

Question: How can we remove a key and value from an associative array?

$arrayData = array(0=>'one',1=>'two',3=>'three'); 
print_r($arrayData);
unset($arrayData[1]);
/*
Array ( [0] => one [3] => three ) 
 */

Question: How can we remove a value from an associative array?

$arrayData = array(0=>'one',1=>'two',3=>'three'); 
$arrayData[1]='';
print_r($arrayData);
/*
Array ( [0] => one [1] =>  [3] => three ) 
 */

Question: What is meaning of ampersand(&) before the function name?
If any function start with ampersand(&), It means its call by Reference function. It will return a reference to a variable instead of the value.


Question: How to detect request is from Search Engine Bots?

if(strstr(strtolower($_SERVER['HTTP_USER_AGENT']), "googlebot"))
{
    /*This request is from search engine bots */
}

Question: List all files/directory in one directory?

if ($handle = opendir('D:\softwares')) {

    while (false !== ($entry = readdir($handle))) {

        if ($entry != "." && $entry != "..") {

            echo "$entry";
            
        }
    }

    closedir($handle);
}

Question: What is mod_php?
mod_php means PHP installed as a module in Apache.


Question: How to Add 1 Day in timestamp?

$timestamp='1463721834';//2016-05-20 05:23:54 AM
//Add 1 Day
$timestamp=strtotime('+1 day', $timestamp);
echo date("Y-m-d h:i:s A", $timestamp);//2016-05-21 05:23:54 AM

Question: How to re-index an array?

$arrayData = array(0=>'one',1=>'two',3=>'three',66=>'six six',7=>'Seven'); 

$newArray = array_values($arrayData);
print_r($arrayData);
/*
 * Array ( [0] => one [1] => two [3] => three [4] => six six [5] => Seven ) 
 */

Question: How to restrict the function to be accept only array?
We can define the argument data-type, while defining the function. For Example

function testmsg(array $data) {
    print_r($data);
}

testmsg('string');

It will throw following error.

Catchable fatal error: Argument 1 passed to testmsg() must be of the type array, none given

Best Related Posts are Following:

1. Advanced PHP Interview Questions and Answers.
2. OOP Interview Questions and Answers.
3. Top 10 PHP Interview Questions and Answers.
4. PHP Questions and Answers for experienced.
5. PHP Difficult Interview Questions and Answers.
6. How to Detect Request type (GET OR Post ) in PHP.
7. PHP Basic questions and answers for fresher and experienced.
8. PHP Mysql Interview Questions and Answers for fresher and experienced.
9. PHP interview questions and answers for 5 year experienced.
10. PHP Frequently asked Questions and Answer 2015.