w3resource

SQL Challenges-1: Employees who are earning more salary than their managers

SQL Challenges-1: Exercise-68 with Solution

From the following table write a query in SQL to find the employees who are earning more salary than their managers. Return employee ID, name, and salary, manager ID, manager name and the salary of the manager.

Table: employees

Structure:

FieldTypeNullKeyDefaultExtra
employee_idintNOPRI
emp_namevarchar(25)YES
hire_datedateYES
job_idvarchar(25)YES
salarydecimal(10,2)YES
manager_idintYES
department_idintYES

Data:

employee_idemp_namehire_datejob_idsalarymanager_iddepartment_id
100Steven1987-06-17AD_PRES24000.00 0 90
101Neena1987-06-18AD_VP17000.00 100 90
102Lex1987-06-19AD_VP17000.00 100 90
103Alexander1987-06-20IT_PROG9000.00 102 60
104Bruce1987-06-21IT_PROG6000.00 103 60
105David1987-06-22IT_PROG4800.00 103 60
106Valli1987-06-23IT_PROG4800.00 103 60
107Diana1987-06-24IT_PROG4200.00 103 60
108Nancy1987-06-25FI_MGR12000.00 101 100
109Daniel1987-06-26FI_ACCOUNT9000.00 108 100
110John1987-06-27FI_ACCOUNT8200.00 108 100
111Ismael1987-06-28FI_ACCOUNT7700.00 108 100
112Jose Manuel1987-06-29FI_ACCOUNT7800.00 108 100
113Luis1987-06-30FI_ACCOUNT6900.00 108 100
114Den1987-07-01PU_MAN11000.00 100 30
115Alexander1987-07-02PU_CLERK3100.00 114 30
116Shelli1987-07-03PU_CLERK2900.00 114 30
117Sigal1987-07-04PU_CLERK2800.00 114 30
133Jason1987-07-20ST_CLERK3300.00 122 50
134Michael1987-07-21ST_CLERK2900.00 122 50
135Ki1987-07-22ST_CLERK2400.00 122 50
136Hazel1987-07-23ST_CLERK2200.00 122 50
137Renske1987-07-24ST_CLERK3600.00 123 50
138Stephen1987-07-25ST_CLERK3200.00 123 50
139John1987-07-26ST_CLERK2700.00 123 50

Sample Solution:

SQL Code(MySQL):

create table employees (
employee_id integer(4) not null unique,
emp_name varchar(25),
hire_date date,
job_id varchar(25),
salary decimal(10,2),
manager_id integer(4),
department_id integer(4)
);



insert into employees values( 100,'Steven     ','1987-06-17','AD_PRES   ',24000.00,         0,   90);
insert into employees values( 101,'Neena      ','1987-06-18','AD_VP     ',17000.00,       100,   90);
insert into employees values( 102,'Lex        ','1987-06-19','AD_VP     ',17000.00,       100,   90);
insert into employees values( 103,'Alexander  ','1987-06-20','IT_PROG   ', 9000.00,       102,   60);
insert into employees values( 104,'Bruce      ','1987-06-21','IT_PROG   ',11000.00,       103,   60);
insert into employees values( 105,'David      ','1987-06-22','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 106,'Valli      ','1987-06-23','IT_PROG   ', 4800.00,       103,   60);
insert into employees values( 107,'Diana      ','1987-06-24','IT_PROG   ', 4200.00,       103,   60);
insert into employees values( 114,'Den        ','1987-07-01','PU_MAN    ',11000.00,       100,   30);
insert into employees values( 115,'Alexander  ','1987-07-02','PU_CLERK  ', 3100.00,       114,   30);
insert into employees values( 116,'Shelli     ','1987-07-03','PU_CLERK  ', 2900.00,       114,   30);
insert into employees values( 117,'Sigal      ','1987-07-04','PU_CLERK  ', 2800.00,       114,   30);
insert into employees values( 108,'Nancy      ','1987-06-25','FI_MGR    ',12000.00,       101,  100);
insert into employees values( 109,'Daniel     ','1987-06-26','FI_ACCOUNT', 9000.00,       108,  100);
insert into employees values( 110,'John       ','1987-06-27','FI_ACCOUNT', 8200.00,       108,  100);
insert into employees values( 111,'Ismael     ','1987-06-28','FI_ACCOUNT', 7700.00,       108,  100);
insert into employees values( 112,'Jose Manuel','1987-06-29','FI_ACCOUNT', 7800.00,       108,  100);
insert into employees values( 113,'Luis       ','1987-06-30','FI_ACCOUNT', 6900.00,       108,  100);
insert into employees values( 133,'Jason      ','1987-07-20','ST_CLERK  ', 3300.00,       122,   50);
insert into employees values( 134,'Michael    ','1987-07-21','ST_CLERK  ', 2900.00,       122,   50);
insert into employees values( 135,'Ki         ','1987-07-22','ST_CLERK  ', 2400.00,       122,   50);
insert into employees values( 136,'Hazel      ','1987-07-23','ST_CLERK  ', 2200.00,       122,   50);
insert into employees values( 137,'Renske     ','1987-07-24','ST_CLERK  ', 3600.00,       123,   50);
insert into employees values( 138,'Stephen    ','1987-07-25','ST_CLERK  ', 3200.00,       123,   50);
insert into employees values( 139,'John       ','1987-07-26','ST_CLERK  ', 2700.00,       123,   50);



SELECT a.employee_id,
    a.emp_name,
    a.salary,
    a.manager_id,
    b.emp_name AS "Manager Name",
    b.salary AS "Manager Salary"
    FROM
    employees a INNER JOIN employees b
    ON a.manager_id = b.employee_id
WHERE
    a.salary > b.salary;

Sample Output:

mployee_id|emp_name   |salary  |manager_id|Manager Name|Manager Salary|
----------+-----------+--------+----------+------------+--------------+
       104|Bruce      |11000.00|       103|Alexander   |       9000.00|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find the highest salaried employees for each designation.
Next: Find cost percent for each order of total order amount.



Follow us on Facebook and Twitter for latest update.