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

Last update on December 20 2024 11:59:13 (UTC/GMT +8 hours)

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:

Field	Type	Null	Key	Default	Extra
department_id	int	NO	PRI
department_name	varchar(30)	YES

Data:

department_id	department_name
30	Sales
50	Export
60	Marketing
80	Audit
90	Production
100	Administration

Table: employees

Structure:

Field	Type	Null	Key
emp_id	int	NO	PRI
emp_name	varchar(30)	YES
emp_sex	varchar(1)	YES
emp_salary	int	YES
emp_department	int	YES	MUL

Data:

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

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.