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.
Answer
Output
2) Use Find maximum value logic in MySQL
3.Find minimum value in Mysql
4.Find Second highest value in Mysql
5.Use Group by name Group_by(column name')
Mysql Query
The query output
8)Write the LEFT JOIN, RIGHT JOIN, INNER JOIN MYSQL Query following table structure.
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 |
- Write the query of the Total Team score in the match.
- Write SQL query for Which player has the highest score declared that player 'Man of the Match'.
- Write the query player Lowest score in this Match.
- Write SQL query to Find the Second highest score. Without using Subquery
- Write a query for Total score of (Batsman, All-rounder, Bowler)
1)Use Find MySQL sum row values
SELECT sum(Score) as Total_score FROM `team_score` // output Total_score => 393
SELECT Player_name, Score FROM team_score WHERE score = (select max(Score) from team_score) // OUTPUT Player_name => Mahendra Singh Dhoni, Score => 98
SELECT Player_name, Score FROM team_score WHERE score = (select min(Score) from team_score)
// OUTPUTPlayer_name => Ashish Nehra, Score => 0
SELECT Player_name, Score FROM team_score order by score DESC limit 1,1
// OUTPUTPlayer_name => Sachin Tendulkar Score => 80
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.
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.
No comments:
Post a Comment