SQL Challenges-1: Department where highest salaried employee(s) are working

SQL Challenges-1: Exercise-75 with Solution

From the following tables write a query in SQL to find the department where the highest salaried employee(s) are working. Return department name and highest salary to this department.

Table: departments

Structure:

Data:

Table: employees

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table departments (
department_id integer(4) not null unique,
department_name varchar(30));


 insert into departments values(30,'Sales');
 insert into departments values( 50,'Export        ');
 insert into departments values( 60,'Marketing     ');
 insert into departments values( 80,'Audit         ');
 insert into departments values( 90,'Production    ');
 insert into departments values(100,'Administration');



create table employees (
emp_id integer(4) not null unique,
emp_name varchar(30),
emp_sex varchar(1),
emp_salary int(6),
emp_department int(3),
foreign key(emp_department) references departments(department_id));



insert into employees values(100,'Steven     ','M',24000,  90);
insert into employees values(101,'Neena      ','F',17000,  90);
insert into employees values(102,'Lex        ','M',17000,  80);
insert into employees values(103,'Alexander  ','M',9000, 60);
insert into employees values(104,'Bruce      ','M',6000,  60);
insert into employees values(105,'David      ','M',4800,  80);
insert into employees values(106,'Valli      ','F',4800,  60);
insert into employees values(107,'Diana      ','F',4200,  60);
insert into employees values(114,'Den        ','M',11000,  30);
insert into employees values(115,'Alexander  ','M',3100,  30);
insert into employees values(116,'Shelli     ','F',2900,  30);
insert into employees values(117,'Sigal      ','F',2800,  30);
insert into employees values(108,'Nancy      ','M',12000, 100);
insert into employees values(109,'Daniel     ','F',9000, 100);
insert into employees values(110,'John       ','M',8200, 100);
insert into employees values(111,'Ismael     ','M',7700, 100);
insert into employees values(112,'Jose Manuel','M',7800, 100);
insert into employees values(113,'Luis       ','F',6900, 100);
insert into employees values(133,'Jason      ','M',3300,  50);
insert into employees values(134,'Michael    ','F',2900,  50);
insert into employees values(135,'Ki         ','F',2400,  50);


SELECT departments.department_name,employees.emp_salary  
FROM employees
JOIN departments
ON employees.emp_department = departments.department_id
WHERE employees.emp_salary IN 
(SELECT MAX(employees.emp_salary) FROM employees);

Sample Output:

department_name|emp_salary|
---------------+----------+
Production     |     24000|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Employees resolve highest number of cases in all quarters.
Next: Find the 2nd highest salary among employees.