PHP and Mysql Interview Questions with example Answer

Hi Guys, Recently I have shared PHP Logical Interview Questions And Answers topic. I got a good response, For a 2-3 year base level of experience, it would be fine.  For preparing logical interview need to know about the MySQL. Today we are going to see MySQL interview question with the answer. All the questions and related to PHP and MySQL, Who have less than 3-year experience read the below questions and improve your interview skills.

1) What is Difference between mysql_connect(), mysql_pconect() and mysql_ping().

• mysql_connect() - When we are using the mysql_connect() function, every time it will connect opening and closing the database connection, depending on the server request.
• mysql_pconnect() - First, when connecting, the function would try to find a (persistent) connection that's already open with the same host, username and password.
• mysql_ping() - When there is no connection on the server. Ping a server connection or reconnect the database connection.

2) How do you get the maximum and minimum price in the sales table? 

• SELECT min(price),max(price) FROM sales 
• min() - Min return the minimum value of the table column.
• max - MAX returns the maximum value of the table column.

3) Split each word from this text 'Hellow world!'.

Answer

$str = "Hello Friend";

$arr1 = str_split($str);
echo '<pre>'; print_r($arr1);

Output

Array
(
    [0] => H
    [1] => e
    [2] => l
    [3] => l
    [4] => o
    [5] => w
    [6] =>  
    [7] => w
    [8] => o
    [9] => r
    [10] => l
    [11] => d
    [12] => !
)

4)What is the difference between drop and truncate for tables in MySQL?

• Drop - it's a statement of data definition language. this statement is used to delete or remove a table from a database.
• Truncate - the statement is used to delete/remove all records from a table. in case all the records have deleted also table structure exists in the database.

5)Write the MySQL query for the below questions. Table name 'team_score'

id	Player_name	Player_role	Score
1	Gautam Gambhir	Batsman	23
2	Virender Sehwag	Batsman	40
3	Sachin Tendulkar	Batsman	80
4	Virat Kohli	Batsman	12
5	Mahendra Singh Dhoni	Batsman	98
6	Yuvraj Singh	All-rounder	56
7	Suresh Raina	Batsman	37
8	Harbhajan Singh	Bowler	4
9	Ravichandran Ashwin	Bowler	41
10	Zaheer Khan	Bowler	2
11	Ashish Nehra	Bowler	0

1. Write the query of the Total Team score in the match.
2. Write SQL query for Which player has the highest score declared that player 'Man of the Match'.
3. Write the query player Lowest score in this Match.
4. Write SQL query to Find the Second highest score. Without using Subquery
5. Write a query for Total score of (Batsman, All-rounder, Bowler)

Answers

1)Use Find MySQL sum row values 

SELECT sum(Score) as Total_score FROM `team_score`

// output Total_score => 393

2) Use Find maximum value logic in MySQL

SELECT Player_name, Score FROM team_score WHERE score = (select max(Score) from team_score)

// OUTPUT
Player_name => Mahendra Singh Dhoni, Score => 98

3.Find minimum value in Mysql

SELECT Player_name, Score FROM team_score WHERE score = (select min(Score) from team_score)


// OUTPUT
Player_name => Ashish Nehra, Score => 0

4.Find Second highest value in Mysql

SELECT Player_name, Score FROM team_score order by score DESC limit 1,1

// OUTPUT
Player_name => Sachin Tendulkar Score => 80

5.Use Group by name Group_by(column name')

SELECT Player_role,sum(Score) as Total_score FROM `team_score` GROUP by Player_role

//OUTPUT
All rounder => 56
Batsman => 290
Bowler => 47

6) How to Remove duplicate row in MySQL

DISTINCT() - It will the duplicate rows based on the column value.

SELECT DISTINCT id FROM table 

// Use id,email any unquiue colunm

7) Fetch category table Hierarchical Data in MySQL

id	Category	p_id
1	Mobile	0
2	Moto X	1
3	Laptop	0
4	Dell	3
5	Bike	0
6	Honda	5

Mysql Query

SELECT c1.category AS parent, c2.category as child
FROM category AS c1
LEFT JOIN category AS c2 ON c2.p_id = c1.id

The query output

+-------------+----------------+
| parent      | child          |
+-------------+----------------+
| Mobile      | Moto X         |
| Laptop      | Dell           |
| Bike        | Honda          |
+-------------+----------------+

8)Write the LEFT JOIN, RIGHT JOIN, INNER JOIN MYSQL Query following table structure.

course_id	course_name
1	Engineering
2	Diploma
3	Arts
4	Science
5	Medical
6	Animation
7	Nursing

branch_id	course_id	branch
1	1	Information Technology
2	1	Electronics and Communication
3	1	Electrical Electronics Engineering
4	2	Information Technology
5	2	Electronics and Communication
6	2	Electrical Electronics Engineering

Left join

SELECT c.course_name AS course, b.branch as branch
FROM category AS c
LEFT JOIN branch AS b ON b.course_id = c.course_id

Right join

SELECT c.course_name AS course, b.branch as branch
FROM category AS c
RIGHT JOIN branch AS b ON c.course_id = b.course_id

INNER JOIN

SELECT c.course_name AS course, b.branch as branch
FROM category AS c
JOIN branch AS b ON b.course_id = c.course_id

8)What is the storage engine for MySQL?

The database management system uses to create, read, update data from a database. There are a few types of the stored engines like transaction tables and non-transactional tables store the information. 

More read: http://stackoverflow.com/questions/4233816/what-are-mysql-database-engines

9)What is the use of index in MySql

Index using to helps speed up the retrieval of data from tables. When you retrieve the user information from the table, query scan all the rows from the table. In case you have set the index column query, It will fetch speed and extract column value insides of checking all the rows.

Example users table

CREATE TABLE users (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

10) What are the main differences between InnoDB and MyISAM?

• InnoDB has row-level locking, MyISAM can only do full table-level locking.
• InnoDB has a better crash recovery.
• MyISAM has FULLTEXT search indexes
• InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

11) Difference between require, include and require_once?

• This function all functions used to include files into another file except that it handles errors differently.
• In the case of files missed the include() function generates a warning, but the script will continue execution. 
•  In the case of files missed The require() generates a fatal error, and the script will stop.
• require_once() this statement identical. if the file has already been included, and if so, not include it again. 

12) What is the difference between Sessions and Cookies in PHP?

• Cookies - Cookies are the bit of data stored by the browser. It sends every time to the server request. Ex: - Password reminder, Form auto-suggestion text.
• Session  - A session is a collection of data stored on the server. Ex: - Store users secure data like user-id

13) How does database indexing work?

• I personally recommend read this stack overflow answer http://stackoverflow.com/questions/1108/how-does-database-indexing-work

14) What is the difference between Split and Explode? 

• Split() and explode() both functions used to split a string.
• The split() function splits the string into an array using a regular expression.
• The explode() function splits the group of the array to string by string.

15) What are the differences between the BLOB and TEXT datatypes in MySQL?

• BLOB is used for storing binary data while Text is used to store the large string.
• BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.
• TEXT values are treated as nonbinary strings (character strings). They have a character set and write values are sorted and compared based on the collation of the character set.

16) Write a query Select where count of one field is greater than 10

SELECT * 
  FROM table 
HAVING COUNT(colunm) > 10

17) what is the difference between unset and unlink in php?

• unset() - unset function used to destroying the value Ex:- unset($_SESSION['some']);
• unlink() - unlink file used to delete the file Ex:- unlink(uploads/logo.png);

Drop your comment or share some interesting interview questions with us. It will help some people. The above question and answers we referred by Stackoverflow.com.