Web Technology Experts Notes

Showing posts with label MYSQL Interview Questions. Show all posts

Tuesday 24 May 2016

MySQL Interview Questions and Answers for 3 year experience

Question: How can I enable Slow MySQL's query log?
Just set the global variable as following:

SET GLOBAL slow_query_log = 'ON';

Question: How to trim the white space from values?

 UPDATE tablename set name = TRIM(name);

Question: How to add more values in enum field?

ALTER TABLE tablename MODIFY COLUMN country ENUM('india','usa','uk','china');

Question: How do I check if an index exists on a table field in MySQL?

SHOW INDEX FROM tablename

Question: What is the maximum length of data I can put in a BLOB column in MySQL?
A BLOB - 65535 bytes maximum
A MEDIUMBLOB - 16777215 bytes maximum
A LONGBLOB - 4294967295 bytes maximum

Question: How to check empty field in Mysql

select * from where 1 AND (email != "" AND email IS NOT NULL);

Question: How to display current connection info?

SELECT USER();

Question: How to display List of database with current connection?

SELECT DATABASE();

Question: How to get count of distinct record?

 select count(distinct email) from users

Question: How to get count of distinct record?

 UPDATE users SET   email = NULL WHERE  id = 1000;

Question: How to copy data from one table to another table?

INSERT INTO table2 (id,uid,changed,status,assign_status) SELECT id,uid,now(),'Pending','Assigned' FROM table1

Question: How to get a list of MySQL views?

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

Question: How to Copy table without copying data?

CREATE TABLE users_bck SELECT * FROM users WHERE 1=0;

Question: How to Repair all tables?

mysqlcheck -A --auto-repair;

Question: How to increase MySQL connection timeout?
Open MySQL file and increase the following

ini_set('mysql.connect_timeout', 600);//20 min
ini_set('default_socket_timeout', 600); //20 MIn

Question: Which MySQL datatype for an IP address?
As per me, It should be Int(11) unsigned.

Question: How to get last 30 days record?

SELECT  * FROM    users WHERE   created_date < DATE_ADD(NOW(), INTERVAL +1 MONTH);

Here created_date id datetime field in which we store the insertion date.

Question: Differences between utf8 and latin1?
In latin1 each character is exactly one byte long.
In utf8 a character can consist of more than one byte.

Question: How to convert the data type from one to another?
Convert String to DateTime

SELECT CONVERT('2017-04-06 08:25:57', DATETIME); //'2017-04-06 08:25:57'

Convert String to Time

SELECT CONVERT('06:07:58', TIME); //06:07:58

Convert Number to String

SELECT CONVERT(125, CHAR);  //125

Question: How to convert the Convert Character Sets from one to another?

SELECT CONVERT('hello user, how are you?' USING utf8); //

Question: How to get datetime when it was in another format?

SELECT STR_TO_DATE('18,05,2017','%d,%m,%Y'); //2017-05-18

Monday 23 May 2016

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

Thursday 3 March 2016

Advanced MySQL Interview Questions and Answers for Experienced

Question: What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
Check Important Points about Heap Tables?

BLOB or TEXT fields are not allowed.
Only comparison operators can be used like =, <,>, = >,=<.
AUTO_INCREMENT is not supported by HEAP tables.
Indexes should be NOT NULL.

Question: What are the advantages of MySQL over Oracle?

MySQL is open source software whereas Oracle is not.
MySQL is easily portable.
Administration is supported using MySQL Query Browser
MySQL is lightweight applications as compare to Oracle.

Question: Difference between FLOAT and DOUBLE?
1. Floating point numbers are stored in FLOAT whereas Double are stored in DOUBLE.
2. Float takes 4 bytes whereas DOUBLE takes eight bytes.
3. FLOAT is for single-precision whereas DOUBLE is for double-precision numbers.
4. Float have accuracy up to eight place whereas DOUBLE upto 18 placeS.

Question: What is the usage of ENUMs in MySQL?
ENUM is a string object used to specify set of predefined values. When we add/update record(s), Enum field will save only single value from predefined values.

Question: What is REGEXP in MySQL?
It is regular expression is a used for complex search using pattern.
See Example

SELECT * FROM users WHERE name REGEXP '^[aeiou]|ok$';

Question: What are the drivers in MySQL?

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxj

Question: What is the difference between primary key and candidate key?
Primary Key:
Every row of a table is identified uniquely by primary key and there is only one primary key fore each table.

Candidate keys:
These are those keys which is candidate for primary key of a table means a key which full fill all the requirements of primary key.

Question: What does myisamchk do?
Myisamchk compress the MyISAM tables, which reduces the disk or memory usage.

Question: What is the difference between MyISAM Static and MyISAM Dynamic?
MyISAM static will have fixed width for all the fields. Also Its easier to restore in case of corruption.
MyISAM Dynamic will have variable width like TEXT,BLOB.

Question: What are Federated tables?
A Federated Table is a table which points to a table in an other MySQL database instance (Might be on same OR Different server).

Question: What is timestamp meaning in MySQL?
timestamp is datatype in MySQL. If we create a filed with timestamp datatype, it will auto-update with current date/time when record is added/updated.

Question: What happens when auto_increment on integer column reaches the max_value in databases?
It stops incrementing anf through following error.
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine.

Question: How can you list all indexes in a table?
SHOW INDEX FROM user;

Question: What is ISAM?
ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.

Question: What is the different between NOW() and CURRENT_DATE()?
NOW () is used to show current year,month,date, hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.

Question: How many triggers are allowed in MySQL table?
Following are the triggers which are allowed in MySQL table.

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE and
AFTER DELETE

Question: How to insert if record have containing single quotes?
Escape it with Slash like below

INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');

Question: How to get Last insertID in MYSQL ?
Use LAST_INSERT_ID() MySQL Function

INSERT INTO `users` (`id`,`name`) VALUES (NULL,  'this is test\'s message');
SELECT LAST_INSERT_ID();

Question: How many columns can we create for index?
16

Question: What is Query to delete a Index?

ALTER TABLE table_name DROP INDEX index_name.

Question: How to update auto increment value to 1000?

ALTER TABLE tbl_name AUTO_INCREMENT = 1000;

Question: What is command to check table is exist?

CHECK TABLE table_name;

Question: What is command to display all databases?

SHOW DATABASES;

Question: What is command to display current Date and Time ?

SELECT NOW();

Question: What are the objects you can use with CREATE statement?

DATABASE
TABLE
TRIGGER
USER
VIEW
EVENT
FUNCTION
INDEX
PROCEDURE.

Question: What are the nonstandard string types?
TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT

Question: What is SQLyog?
The SQLyog program is Software which provide GUI tool for MySQL.

Question: What are string datatype in MySQL?

CHAR
VARCHAR
TINY TEXT
TEXT
MEDIUM TEXT
LONG TEXT
BINARY
VARBINARY
TINYBLOB
MEDIUMBLOG
BLOB
LONGBLOB
ENUM
SET

Question: How to get the next auto-increment id in mysql?
Use LAST_INSERT_ID() from your MySQL query.

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 reset the autoincrement?

ALTER TABLE tablename AUTO_INCREMENT = 1

Thursday 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"

If you want to make your SQL Query as case sensitive, We have following 3 ways.

Do at MySQL Server Level
Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)
```
character-set-server=utf8
collation-server=utf8_bin
```

Set the column as Case sensitive,For Example

ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

Set the Query as case sensitive, For Example

SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'

Wednesday 3 June 2015

PHP Mysql Interview Questions and Answers for fresher and experienced

Question: How many ways we can retrieve the data in the result set of MySQL using PHP?
Following are different ways

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_assoc($sqlQuery)) {
    //$row have results
}

$sqlQuery = mysql_query("SELECT id, name FROM users");
while ($row = mysql_fetch_object($sqlQuery)) {
   //$row have results
}

Question: How can we create a database using PHP and MySQL?

mysql_create_db('db_name');

Question: How can we repair a MySQL table?
Use any of below as per requirement:

REPAIR tablename; 
REPAIR tablename quick;
REPAIR tablename extended;

Question: How can we find the number of rows in a result set using PHP?

$sqlQuery = mysql_query("SELECT id, name FROM users");
$num = mysql_num_rows($sqlQuery);

Question: How to update auto increment value for 100000?

ALTER TABLE table_name AUTO_INCREMENT = 100000;

Question: How to get the current version number of MYSQL?

SELECT VERSION();

Question: What is difference between between NOW() and CURRENT_DATE()?
Now gives the current date, hour and minutes

select NOW()
//2015-06-03 19:17:23

CURRENT_DATE gives the current date only.

select CURRENT_DATE()
//2015-06-03

Question: List all databases?

SHOW DATABASES

Question: What is mysql query to show the first 100 records?

SELECT * FROM user LIMIT 0,100

Question: How many TRIGGERS allows per table in mysql?
Following are 6 triggers in mysql for table.
1. BEFORE INSERT,
2. AFTER INSERT,
3. BEFORE UPDATE,
4. AFTER UPDATE,
5. BEFORE DELETE
6. AFTER DELETE

Question: What is difference between COMMIT and ROLLBACK?
COMMIT: Mostly it is used in transaction and commit means all process are completed successfully. Once commit done you can not revert.
ROLLBACK: Mostly it is used in transaction and ROLLBACK means all process are NOT completed successfully. So revert the db changes automatically.

Question: What is SAVEPOINT?
The SAVEPOINT statement is used to set a savepoint with a name in transaction. used for roll back the transaction to the named savepoint specified instead of all the changes.

Question: How to find the number of days between two dates?

$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60*24));

Question: How to find the number of hours between two dates?

$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60*60));

Question: How to find the number of minutes between two dates?

$now = time(); // or your date as well
$newDate = strtotime("2010-01-01");
$datediff = $now - $$newDate;
echo floor($datediff/(60));

Question: How do I get random item from an array?

$array = array('','s','d');
echo $array[array_rand($array)];

Question: How to update the max_allowed_packet mysql variable?
You can check the value of max_allowed_packet with following query.

SHOW VARIABLES LIKE 'max_allowed_packet';

For Update, You have to change in configuration mysql.
File Location in My Wamp Server: D:\wamp\bin\mysql\mysql5.6.17\my.ini
Now search with max_allowed_packet and update, as per requirement.

Question: List all the tables whose engine is InnoDB?

SELECT table_name FROM INFORMATION_SCHEMA.TABLES  WHERE ENGINE = 'InnoDB'

Question: How to update the column where NULL value set?

update `users` set phone='000000000' where  phone is NULL

Subscribe to: Posts ( Atom )

Tuesday 24 May 2016

MySQL Interview Questions and Answers for 3 year experience

Best Related Posts are Following:

Monday 23 May 2016

MySQL Interview questions and answers for 2 year experience

Best Related Posts are Following:

Thursday 3 March 2016

Advanced MySQL Interview Questions and Answers for Experienced

Best Related Posts are Following:

Thursday 18 February 2016

How can I make SQL Query case sensitive string comparison on MySQL?

If you want to make your SQL Query as case sensitive, We have following 3 ways.

Best Related Posts are Following:

Wednesday 3 June 2015

PHP Mysql Interview Questions and Answers for fresher and experienced

Best Related Posts are Following: