Mysql Interview Questions and Answers
1 Define SQL
SQL stands for Structured Query Language. SQL is is a special-purpose programming language designed for data held in a relational database management system.
It's originally based upon relational algebra and tuple relational calculus, It's consists of a data definition language(DDL) and a data manipulation language(DML).
2 What is difference between DBMS and RDBMS
.RDBMS (relational database management system) applications store data in a tabular form.
. DBMS store data as files .However there are is tables in DBMS also, but there is no relation between the tables as in RDBMS.
.In DBMS, data is generally stored in either a hierarchical form or a navigational form.
.In RDBMS tables have an identifier called primary key and Data values will be stored in the form of tables. The relationships between these data values will be stored in the form of a table as well. Every value stored in the relational database is accessible.
.RDBMS solution is required by large sets of data whereas small sets of data can be managed by DBMS
3 How to make a database connection to Mysql in PHP
We can create a connection to database by mysql_connect function .
$connection = mysql_connect(‘servername’, ‘username’,’passowrd’);
die(‘Failed to connect to MySQL’);
4 What is difference between ORDER BY and GROUP BY in SQL
Groupby : It is a way to sub-total your results,or perform
some other 'aggregate' functions on them.
e.g : select sum(salary)from salary Groupby employee name.
Orderby : It is a simply a way to sort your results. it display result in assending or dessending order.
e.g : select * from customer where column name="value" orderby column name.
5 What is JOIN in SQL
joins are used for select the relevant data between one or more table.
6 What is difference between FLOAT and DOUBLE
FLOAT and DOUBLE both represents approximate numerical data values. MySql uses four bytes for single precision values and eight bytes for double precision values .
A Float is for single-precision value wheres DOUBLE is for double precision values and DOUBLE allows greater accurcy than FLOAT.
Float saves floating point numbers upto eight places and DOUBLE saves floating point numbers upto 18 places.
7 What is BLOB
A BLOB is Bianry large object tha can hold variable amount of data. There are four types of BLOB
These have differnces in maximum length of the values they can hold.BLOB values stores as binary strings(byte strings).
8 How many types of JOINS in SQL
There are three types of join in sql. that are as following:-
1) Inner join
1.1) equi join
1.2) natural join
2.3) full join
3) Cross join
9 How to get second maximum salary in an organization
"SELECT max(salary_obtained) FROM organization_tbl WHERE salary_obtained <( SELECT max(salary_obtained) FROM organization_tbl)".
10 How to get the two max salary of an organization
"SELECT salary_obtained FROM organization_tbl ORDER BY salary_obtained DESC LIMIT 0,2"
11 How to get the student name got maximum marks in a class
SELECT student_name from students_tbl WHERE marks_obtained = (SELECT max(marks_obtained) FROM students_tbl)
12 What is difference between PRIMARY key and UNIQUE Key in sql
1: UNIQUE key can be a NULL value but PRIMARY key can not take NULL values.
2:A table can have multiple UNIQUE key but can only one PRIMARY key.
13 What is difference between TRUNCATE and DELETE in mysql
1: DELETE is a DML(data manipulation lnguage) command whereas truncate is a DDL(data definition language) command.
2 : Truncate is much faster than Delete.
3 : We can not roll back in truncate but in delete we can rollback.
4 : We can not use where clause in truncate but in delete we can use conditions using where clause.
14 What is MYSQL storage engines
A storage engine is a software module that a database management system uses to create ,read update the data from a database.
15 Which storage engines a mysql support
Below are some storage engines names that a mysql support.
1 : MyISAM.
5 :Merge etc.
16 What is difference between MyISAM and InnoDB storage engines in mysql
1 : InnoDB provides us row level locking while MyISAM provides us table level locking.
2 : InnoDB offers foreign key constraints wheres in MyISAM does not have foreign key constraints.
3 : InnoDB does not have full text search wheres MyISAM provides us full text search.
17 What is difference between CHAR and VARCHAR data types in sql
CHAR used to store fixed length memory storage whereas VARCHAR is used for variable length memory storage .In VARCHAR if we used less space than defined space ,then the remaining space is not wasted , but In CHAR if we use less space than defined space then remaining space is wasted.
CHAR stores only character values whereas VARCHAR stores alphanumeric values
18 Which is the latest stable version of MySQL
The latest version of MySQL is MySQL 5.6.19 released on 31st March 2014
19 What is difference between mysql_connect and mysql_pconnect
1: While using the mysql_pconnect the function would try to find a connection that is already open with same host,username and password ie.(persistant connection) .If connection found an identifier will be returned instead of opening new connection. And with mysql_connect a new connection is always established .
2:While using mysql_pconnect the connection will not closed after the execution of script for future use and in mysql_connect the connection will automatically close when execution of script ends.
3 :mysql_pconnect uses less resources than mysql_connect.
20 What things we keep in mind to write an optimize SQL Query
1 : Instead of using SELECT * we should specify the columns name like SELECT column1,column2 which have use in our script.
2 : We should use indexing of tables for which we are writing queries.
3 : Try to use the LIMIT if we need only a specific number of rows.
21 How to get distinict columns values in MySql
In MySql we have to use DISTINCT statement to get distinct (unique) values .
SELECT DISTINCT column_name FROM table_name ;
22 How many TRIGGERS are possible in MySql
Six triggers are possible to use in MySQL database .
1. Before Insert
2. After Insert
3. Before Update
23 How to get current date in MySql
We can get the current date in MySql by SELECT CURRENT_DATE();
24 How to concatenate two fields in MySql
In MySQL CONCAT function is used to concatenate two strings to form a single string
Synatx : SELECT CONCAT(column1,column2) FROM tblname.
Ex : SELECT CONCAT(first_name,last_name) FROM employee
25 Write a query to find out all records from a user table ordering them first all inactive users, then active users, then pending.
We are using the table users with three columns userid, uname, status
SELECT uname, status FROM users ORDER BY FIELD(status, 'inactive','active','pending');
26 How to get comma seperated values in a given column for each group. If we have a table that contains StudentId and subject ID and we need to get subject id's in comma seperated values for each student Id
IF we have below table
and we need to get result like thiswe need to use GROUP_CONCAT for it and our SQL query is :SELECT studentId,GROUP_CONCAT(SubjectId) as subjects FROM student GROUP BY studentId
27 How to access all employees name who are getting the second highest salary?
We can first get the 2nd highest salary from the table. As salary can be same , so we will find the DISTINCT salary data by using DISTINCT clause or GROUP BY clause.
SELECT salary from employees GROUP BY salary DESC LIMIT 1,1
after we can write complete query to get the employees on this salary.
SELECT name from employees WHERE salary=(SELECT salary from employees GROUP BY salary DESC LIMIT 1,1)
28 How to delete only the repeated records from a USER table ?
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)
29 Find records from employee table who joined between 01-06-2015 and 1-10-2015 where employees name have NULL?
we will use BETWEENclause to find records between dates and the IS Operator to check NULL values. we can not use name = NULL condition as null values can not be determined by the equal to sign.
SELECT * FROM `user` WHERE date BETWEEN '2015-06-01' AND '2015-10-01' AND name is NULL
30 How to get records from first table which does not exist in second table?
First We need to select common record's ids .
SELECT id FROM `user` INNER JOIN `userdata` WHERE user.id = userdata.userid
Then we will match to found records from first table where matching id is not there.
SELECT * FROM USER WHERE id NOT IN(SELECT id FROM `user` INNER JOIN `userdata` WHERE user.id = userdata.userid)
31 How to get employees name and their manager name from an employee table, also dictate the employee as a TOP MANAGER who does not have any manager.
Employee empid name superuid 1 xyz 3 2 xyz2 4 3 xyz3 1 4 xyz4 5 5 xyz5 0
Here we need to show the employee name and their manager name as we can see employee with id 5 whose name is xyz5 does not have any manager so we need to show him as a senior.
SELECT IFNULL(m.name, 'TOP MANAGER') AS 'Manager', e.name As 'employee' from employee e LEFT JOIN employee m ON m.empid = e.superuid ORDER BY Manager
You can skip order by clause here .The resultant table will be:
MANAGER EMPLOYEE xyz xyz3 xyz3 xyz xyz4 xyz2 xyz5 xyz4 TOP MANAGER xyz5
32 What will be the output? "SELECT * FROM table WHERE 1" "SELECT * FROM table WHERE -1" "SELECT * FROM table WHERE 0"
SELECT * FROM table WHERE 1 :: Gives All Rows
SELECT * FROM table WHERE -1 :: Gives All Rows
SELECT * FROM table WHERE 0 :: Gives Nothing
33 Write SQL Query that will interchange the values of gender column Ie. if gender is male then update it to female and if gender is female update to male
UPDATE employee set gender = (case gender when 'female' then 'male'else 'female' end);
34 Which operator is used to select values within a range?1.Range 2. Within 3.Between