Basic & Advanced MySQL Interview Questions with Answers

• 2017-11-20
• 151

• Posted by Avi

MySQL Questions with Answers

Question : How can we know the total number of elements of Array?
Answer :
sizeof($array_var)
count($array_var)
If we just pass a simple var instead of a an array it will return 1.

Question : Different Types of Tables in Mysql?
Answer : There are Five Types Tables in Mysql
1)INNODB
2)MYISAM
3)MERGE
4)HEAP
5)ISAM

Question : What is the difference between the functions unlink and unset?
Answer :
unlink() deletes the given file from the file system.
unset() makes a variable undefined.

Question : In how many ways we can retrieve the data in the result set of MySQL using PHP?
Answer : You can do it by 4 Ways
1. mysql_fetch_row.
2. mysql_fetch_array
3. mysql_fetch_object
4. mysql_fetch_assoc


Question : What do DDL, DML, and DCL stand for?
Answer : DDL is the abbreviation for Data Definition Language dealing with database schemas as well as the description of how data resides in the database. An example is CREATE TABLE command. DML denotes Data Manipulation Language such as SELECT, INSERT etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc.

Question : What are the different types of strings in Database columns in MySQL?
Answer : Different types of strings that can be used for database columns are SET, BLOB, VARCHAR, TEX, ENUM, and CHAR.

Question : Is there an object oriented version of MySQL library functions?
Answer : MySQLi is the object oriented version of MySQL and it interfaces in PHP.

Question : How to display Nth highest salary from a table in a MySQL query?
Answer : Let us take a table named employee.

To find Nth highest salary is:
select distinct(salary) from employee order by salary desc limit n-1,1  

If you want to find 3rd largest salary:
select distinct(salary) from employee order by salary desc limit 2,1

Question : What is the difference between mysql_connect and mysql_pconnect?
Answer : Mysql_connect() is used to open a new connection to the database while mysql_pconnect() is used to open a persistent connection to the database. It specifies that each time the page is loaded mysql_pconnect() does not open the database.

Question : How many TRIGGERS are possible in MySql
Answer : Six triggers are possible to use in MySQL database .
1.Before Insert
2.After Insert
3.Before Update
4.After Update
5.Before Delete
6.After Delete

Question : How to delete only the repeated records from a USER table ?
Answer : First we need to select only those records which are repeated giving a constraint :count should be greater than one to make the signle record deletion work. Suppose repeating of record is considered by column name.

SELECT * FROM(SELECT id FROM user GROUP BY name HAVING COUNT(*) > 1) AS A

Then will apply the deletion on those records.
DELETE FROM user WHERE id IN(SELECT * FROM(SELECT id FROM user GROUP BY name HAVING COUNT(*) > 1) AS A)

• Php
• Mysql