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

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:

FieldTypeNullKeyDefaultExtra
department_idintNOPRI
department_namevarchar(30)YES

Data:

department_iddepartment_name
30Sales
50Export
60Marketing
80Audit
90Production

Table: employees

Structure:

FieldTypeNullKeyDefaultExtra
emp_idintNOPRI
emp_namevarchar(30)YES
emp_sexvarchar(1)YES
emp_salaryintYES
emp_departmentintYESMUL

Data:

emp_idemp_nameemp_sexemp_salaryemp_department
100StevenM2400090
101NeenaF1700090
102LexM1700080
103 AlexanderM900060
104BruceM600060
105DavidM480080
106ValliF480060
107DianaF420060
108NancyM12000100
109DanielF9000100
110JohnM8200100
111IsmaelM7700100
112Jose ManuelM7800100
113LuisF6900100
114DenM1100030
115AlexanderM310030
116ShelliF290030
117SigalF280030
133JasonM330050
134MichaelF290050
135KiF240050

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    ');

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: