Department wise employees having salary greater than average salary

Correlation subqueries

 Let us consider an employee table and department table. For readers simplicity we are just giving minimum fields in the table/

Table employees

/*Table structure for table `tbl_employee` */

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)

/*Data for the table `tbl_employee` */

insert  into `tbl_employee`(`id`,`name`,`salary`,`department_id`) values (1,'Sachin',2500,1),(2,'Dravid',9000,1),(3,'Ganguly',7000,1),(4,'Azaruddin',10000,1),(5,'Jadeja',8000,2),(6,'Sreenath',9500,2),(7,'Rohit',7800,2),(8,'Kambli',4000,2),(9,'Kapil',10001,3),(10,'Kohli',2300,2),(11,'Pant',3400,1);

Table Employees

Table department

/*Table structure for table `tbl_departments` */


CREATE TABLE `tbl_departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


/*Data for the table `tbl_departments` */

insert  into `tbl_departments`(`id`,`name`) values (1,'ODI'),(2,'TEST'),(3,'T20'),(4,'IPL');

Table department

Query 1 : Select all employees whose salary is greater than average salary of all departments

select * from tbl_employee where salary > (select avg(salary) from tbl_employee)

will return all the employees having the salary greater than the average salary ie 6681.91

Query 2 : Select employees whose salary is greater than their departments average salary

SELECT * FROM tbl_employee e1 where salary > (select AVG(salary) from tbl_employee e2 where e2.department_id = e1.department_id)

will returns the  department wise employees having the salary greater than the average salary

Query 3 : Select top two employees having higher salaries in their department

SELECT * FROM tbl_employee e1 WHERE 2 >  (SELECT Count(DISTINCT salary) FROM tbl_employee e2 WHERE e2.Salary > e1.Salary  AND e1.department_id = e2.department_id );

Top 2 salaries in each department

department employees having their higher salaries

Thanking you