SQL Challenges-1: Resolve highest complaint and nearest manager id is 114

SQL Challenges-1: Exercise-70 with Solution

From the following table write a query in SQL to find those employees who resolve the highest complaint in all quarter and works under the supervision of that manager, holding id 114. Returns employee name and the number of complaint resolved.

Table: orders

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

create table employees (
emp_id integer(4) not null unique,
emp_name varchar(30),
emp_sex varchar(1),
depart_name varchar(25),
emp_salary int(6),
qtr1 int(4),
qtr2 int(4),
qtr3 int(4),
qtr4 int(4),
emp_department int(3),
manager_id int(4)
);



insert into employees values(100,'Steven     ','M','Production',24000,240,310,275,300,  90,0);       
insert into employees values(101,'Neena      ','F','Production',17000,270,300,275,290,  90,100);     
insert into employees values(102,'Lex        ','M','Audit',17000,300,290,285,310,  80,100);          
insert into employees values(103,'Alexander  ','M','Marketing', 9000,25,270,260,280,  60,102);       
insert into employees values(104,'Bruce      ','M','Marketing', 6000,300,280,275,290,  60,103);      
insert into employees values(105,'David      ','M','Audit', 4800,200,220,250,270,  80,103);          
insert into employees values(106,'Valli      ','F','Marketing', 4800,300,320,330,350,  60,103);      
insert into employees values(107,'Diana      ','F','Marketing', 4200,280,270,310,320,  60,103);      
insert into employees values(114,'Den        ','M','Sales',11000,280,290,300,320 ,  30,101);         
insert into employees values(115,'Alexander  ','M','Sales', 3100,310,300,320,340,  30,108);          
insert into employees values(116,'Shelli     ','F','Sales', 2900,245,260,280,300,  30,108);          
insert into employees values(117,'Sigal      ','F','Sales', 2800,250,370,290,320,  30,108);          
insert into employees values(108,'Nancy      ','M','Administration',12000,260,280,300,310, 100,108); 
insert into employees values(109,'Daniel     ','F','Administration', 9000,220,210,240,260, 100,108); 
insert into employees values(110,'John       ','M','Administration', 8200,300,290,280,320, 100,100); 
insert into employees values(111,'Ismael     ','M','Administration', 7700,280,300,270,310, 100,114); 
insert into employees values(112,'Jose Manuel','M','Administration', 7800,250,280,260,300, 100,114); 
insert into employees values(113,'Luis       ','F','Administration', 6900,300,280,270,310, 100,114); 
insert into employees values(133,'Jason      ','M','Export', 3300,280,270,300,290,  50,122);         
insert into employees values(134,'Michael    ','F','Export', 2900,260,280,290,300,  50,122);         
insert into employees values(135,'Ki         ','F','Export', 2400,240,260,270,290,  50,122);         



SELECT emp_name, (qtr1+qtr2+qtr3+qtr4) as "Case Resolved"
FROM employees
WHERE manager_id = 114 
AND qtr1+qtr2+qtr3+qtr4 IN 
(SELECT MAX(qtr1+qtr2+qtr3+qtr4) FROM employees 
WHERE manager_id = 114);

Sample Output:

emp_name   |Case Resolved|
-----------+-------------+
Ismael     |         1160|
Luis       |         1160|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find cost percent for each order of total order amount.
Next: Number of customers, orders, and total order amount for each city.