MySql queries interview questions and answers - MySql Database Tutorial

MySql queries Interview Questions & Answers
In this section, I have selected advanced MYSQL queries interview questions and answers for all levels of candidates(entry level, advanced level, expert level).Last few months I have been working to select best MYSQL queries for MYSQL interview questions.

This article introduces MYsql Database queries:

Suppose we have to tables 
1.Employee
2.Employee Salary

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `photo` varchar(100) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `gender` varchar(11) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `mobile` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `active` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

employees

id   name 
1    Admin
2    Employee1
3    Employee2
4    Employee3
5    Employee4

CREATE TABLE `employee_salary` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) DEFAULT NULL,
  `salary` varchar(50) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `created_by` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=32 DEFAULT CHARSET=latin1

id employee_id  salary
1    1          10000
2    2          20000
3    3          10000
4    4          40000
5    5          30000 

1. Find Second highest paid employees withou using LIMIT function.

   Query:

   SELECT MAX(salary) FROM  employee_salary WHERE salary <(SELECT MAX(salary) FROM  employee_salary)

2.Skip repeating values

  Query:
  SELECT salary FROM  employee_salary GROUP BY salary

3.Reset Auto-increment next value

  Query:
  ALTER TABLE tablel SET AUTO_INCREMENT=val;

4.Find the Size of database

Query:
SELECT 
s.schema_name AS 'database',
SUM(t.data_length) AS DATA,
SUM( t.index_length ) AS INDEXES,
SUM(t.data_length) + SUM(t.index_length) AS 'Mb Used',
IF(SUM(t.data_free)=0,'',SUM(t.data_free)) AS 'Mb Free',
COUNT(table_name) AS TABLES
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name  

5.First and Last Date of Month

Last day of next month:

SELECT LAST_DAY (DATE_ADD(CURDATE(), INTERVAL 30 DAY))

Last day of Previous month:

SELECT LAST_DAY (DATE_SUB(CURDATE(), INTERVAL 30 DAY))

First day of next month

SELECT  CONCAT(LEFT(CURDATE() + INTERVAL 1 MONTH, 8), '01'); 


6.Display every Nth row

Query:
SELECT id
FROM employees
GROUP BY id 
HAVING MOD(id, N) = 0; 

7.Age in years

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS('1991-07-01')), '%Y') + 0;

8.Difference between two dates

SELECT DATEDIFF('2002-07-15','2001-07-15')

9. Update Table using Join

UPDATE employee e LEFT JOIN employee_salary s ON e.id=s.employee_id 
SET s.salary='50000'
where e.first_name='Admin'

10. DELETE Table using Join 

Delete employee_salary FROM employee_salary  LEFT JOIN employee ON employee.id=employee_salary.employee_id 
WHERE  employee.first_name='Admin'