MySQL Interview Questions

Ques: What is difference between VARCHAR and TEXT ?

Ans: Indexing can not be done on TEXT column. So if you have to index, you have to use VARCHAR as type.

Ques: What is size of INT and TINYINT ?

Ans: INT Size – 4 bytes
TINYINT Size – 1 byte

Ques: What is stored procedure ?

Ans: It is a segment of SQL statements stored in MySQL Server. After creating stored procedure, you can directly call it via CALL() function.

Syntax –
CREATE PROCEDURE procedure_name (parameter)
BEGIN
statements
END //

Calling –
CALL procedure_name(arguments);

For example –
Query – Select * from users. – This query will get data of all users.
Create Stored Procedure of it –
DELIMITER //
CREATE PROCEDURE Getallusers()
BEGIN
SELECT * FROM users;
END //
DELIMITER;

To call it –
CALL Getallusers ();

Ques: Write a query to get 3rd highest salary without using limit and offset.

Ans: SELECT name. salary from employee e1 where n-1 = (SELECT COUNT(DISTINCT salary) from employee e2 WHERE e2.salary > e1.salary);

Replace n with highest salaray. In above question to find the 3rd highest salary query wiil be –
SELECT name. salary from employee e1 where 3-1 = (SELECT COUNT(DISTINCT salary) from employee e2 WHERE e2.salary > e1.salary);

Ques: Write a single query to delete duplicate rows ?

Ans: DELETE t1 from table t1 INNER JOIN table t2 WHERE t1.primary_key_column > t2. primary_key_column and t1.duplicate_column_name = t2.duplicate_column_name ;

For Example – We have users table in which some users info. is stored(i.e, ID (Primary Key), Name and Address).

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	India
2	Shyam	test@gmail.com	India
3	Bunty	bunty@gmail.com	India
4	Joni	bunty@gmail.com	India

Query to deleted duplicate email ids(except one) will be –

DELETE u1 from users u1 INNER JOIN users u2 WHERE u1.ID>u2. ID and u1.email_id = t2.email_id ;

Ques: What is default size of MySQL Database ?

Ans: 256 TB

Ques: Tell about GROUP_CONCAT function.

Ans: GROUP_CONCAT function combines(concatenates) the data of multiple rows in a single row. Let us take an example for explanation –

There is a table say customers table.

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	Australia
2	Ram	test@gmail.com	India
3	Bunty	bunty@gmail.com	India
4	Joni	joni@gmail.com	India

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	Australia, India
3	Bunty	bunty@gmail.com	India
4	Joni	joni@gmail.com	India

By default GORUP_CONCAT will add comma(,) as separator between the values. To use the different syntax like(;), you have to use SEPARATOR with separator syntax.

SELECT Name, Email, GROUP_CONCAT(DISTINCT Address SEPARATOR ‘;’) from customers.

Output –

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	Australia; India
3	Bunty	bunty@gmail.com	India
4	Joni	joni@gmail.com	India

Ques: Tell about DUPLICATE ON in MySQL.

Ans: Duplicate On is mostly used with the unique key.  For example, if we have unique key in the table and after that if we will insert the same reocrd, MySQL will give error.

For example – There is a table users with email as unqiue id.

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	India
2	Bunty	bunty@gmail.com	India

If we try to insert the record again with email id test@gmail.com, it will give error. 

To prevent error –

INSERT into users(Name, email, address) VALUES(‘test@gmail.com’) ON DUPLICATE KEY UPDATE email = ‘test_old@gmail.com’ ;

Ques: What is difference between MyISAM and InnoDB ?

Ans: 1.) InnoDB supports transactions while MyISAM does not.
2.) InnoDB has row-level locking while MyISAM has table-locking.
3.) MyISAM supports FULLTEXT Index while InnoDB does not.
4.) Performance speed of InnoDB is less as compared to MyISAM.
5.) InnoDB supports FOREIGN KEY constraints while MyISAM does not.

MyISAM is best for small projects but InnoDB is better option when you have to deal with large database as it supports transactions.

Ques: Write a query to update gender which are male to female and female to male.

Ans: Query –
UPDATE users SET gender = CASE WHEN gender = ‘male’ THEN ‘female’ WHEN gender = ‘female’ THEN ‘male’ ELSE ” END;

 

SELECT Name, Email, GROUP_CONCAT(DISTINCT Address) from customers.

Output –

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	Australia, India
3	Bunty	bunty@gmail.com	India
4	Joni	joni@gmail.com	India

By default GORUP_CONCAT will add comma(,) as separator between the values. To use the different syntax like(;), you have to use SEPARATOR with separator syntax.

SELECT Name, Email, GROUP_CONCAT(DISTINCT Address SEPARATOR ‘;’) from customers.

Output –

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	Australia; India
3	Bunty	bunty@gmail.com	India
4	Joni	joni@gmail.com	India

Ques: Tell about DUPLICATE ON in MySQL.

Ans: Duplicate On is mostly used with the unique key.  For example, if we have unique key in the table and after that if we will insert the same reocrd, MySQL will give error.

For example – There is a table users with email as unqiue id.

ID (Primary Key)	Name	Email ID	Address
1	Ram	test@gmail.com	India
2	Bunty	bunty@gmail.com	India

If we try to insert the record again with email id test@gmail.com, it will give error. 

To prevent error –

INSERT into users(Name, email, address) VALUES(‘test@gmail.com’) ON DUPLICATE KEY UPDATE email = ‘test_old@gmail.com’ ;

Ques: What is difference between MyISAM and InnoDB ?

Ans: 1.) InnoDB supports transactions while MyISAM does not.
2.) InnoDB has row-level locking while MyISAM has table-locking.
3.) MyISAM supports FULLTEXT Index while InnoDB does not.
4.) Performance speed of InnoDB is less as compared to MyISAM.
5.) InnoDB supports FOREIGN KEY constraints while MyISAM does not.

MyISAM is best for small projects but InnoDB is better option when you have to deal with large database as it supports transactions.

Ques: Write a query to update gender which are male to female and female to male.

Ans: Query –
UPDATE users SET gender = CASE WHEN gender = ‘male’ THEN ‘female’ WHEN gender = ‘female’ THEN ‘male’ ELSE ” END;