w3resource

SQL Challenges-1: Display own salary and average salary for their department

SQL Challenges-1: Exercise-66 with Solution

From the following table write a SQL query to show the salary of each employee along with the average salary for their department. Return job ID,name,salary and average salary.

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   ', 6000.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 
e.job_id, emp_name, salary, Average_Salary
FROM employees e
JOIN
(SELECT job_id, AVG(salary) Average_Salary
   FROM employees GROUP BY  job_id) avgsal
ON e.job_id=avgsal.job_id;

Sample Output:

job_id    |emp_name   |salary  |Average_Salary|
----------+-----------+--------+--------------+
AD_PRES   |Steven     |24000.00|  24000.000000|
AD_VP     |Neena      |17000.00|  17000.000000|
AD_VP     |Lex        |17000.00|  17000.000000|
IT_PROG   |Alexander  | 9000.00|   5760.000000|
IT_PROG   |Bruce      | 6000.00|   5760.000000|
IT_PROG   |David      | 4800.00|   5760.000000|
IT_PROG   |Valli      | 4800.00|   5760.000000|
IT_PROG   |Diana      | 4200.00|   5760.000000|
FI_MGR    |Nancy      |12000.00|  12000.000000|
FI_ACCOUNT|Daniel     | 9000.00|   7920.000000|
FI_ACCOUNT|John       | 8200.00|   7920.000000|
FI_ACCOUNT|Ismael     | 7700.00|   7920.000000|
FI_ACCOUNT|Jose Manuel| 7800.00|   7920.000000|
FI_ACCOUNT|Luis       | 6900.00|   7920.000000|
PU_MAN    |Den        |11000.00|  11000.000000|
PU_CLERK  |Alexander  | 3100.00|   2933.333333|
PU_CLERK  |Shelli     | 2900.00|   2933.333333|
PU_CLERK  |Sigal      | 2800.00|   2933.333333|
ST_CLERK  |Jason      | 3300.00|   2900.000000|
ST_CLERK  |Michael    | 2900.00|   2900.000000|
ST_CLERK  |Ki         | 2400.00|   2900.000000|
ST_CLERK  |Hazel      | 2200.00|   2900.000000|
ST_CLERK  |Renske     | 3600.00|   2900.000000|
ST_CLERK  |Stephen    | 3200.00|   2900.000000|
ST_CLERK  |John       | 2700.00|   2900.000000|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find total order amount for each customer in September 2008.
Next: Find the highest salaried employees for each designation.



Follow us on Facebook and Twitter for latest update.