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 .
  e.g :
  <?PHP

  
  $connection = mysql_connect(‘servername’, ‘username’,’passowrd’);
  If(! $connection)
  {
  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
    1. TINYBLOB
    2. BLOB
    3. MEDIUMBLOB
    4. LONGBLOB
  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)Outer join 

          2.1)left join
          2.2)right 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.
  2 :InnoDB
  3 :Memory
  4 :CSV
  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 .
  
  e.g :
  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
   4.After Update
   5.Before Delete
   6.After Delete

• 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 

  studentId	SubjectId
  1	4
  1	5
  1	9
  3	6
  2	90
  1	40
  3	55
  2	66

  and we need to get result like this  

  studentId	subjects
  1	4,5,9,40
  2	90,66
  3	6,55

   

   

   

  we  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"

   OUTPUT:

  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

  Between

• 35 What is view

  A view is a virtual table that is based on the result-set of an SQL statement. It contains rows and columns like a real table. The fields in a view are from one or more tables in the DB.

  CREATE VIEW view_name AS
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

• 36 Find records from one table that not exists in other table.If we have product table and order table and we need to get those products which have not ordered by any customer.

  SELECT p.id FROM product as p LEFT JOIN orders as o ON p.id = o.product_id WHERE o.product_id IS NULL