PHP and Mysql Interview Questions with example Answer - Mostlikers

03 April, 2017

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 from that tutorial, That tutorial has only PHP section interview question. 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 with 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 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 exist in the database.

5)Write the MySQL query for 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 Total Team score in the match.
  2. Write SQL query for Which player have 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?

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

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 function 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 is the bit data stored by the browser. It sends everytime 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?

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 a comment section some intersting interview questions. That may helpful my readers. The above question and answers we referred by Stackoverflow.com.

No comments:

Post a Comment