SQL Challenges-1: Employees resolve highest number of cases in all quarters

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

From the following table write a SQL query find the employee who resolve the highest number of cases in all quarters. Return employee name and total number of cases resolved.

Table: employees

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



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

SELECT emp_name, (qtr1+qtr2+qtr3+qtr4) AS "Target achieved in all Qtrs."
FROM employees e
WHERE (e.qtr1+e.qtr2+e.qtr3+e.qtr4) = (
SELECT MAX((qtr1+qtr2+qtr3+qtr4)) FROM employees e2
);

Sample Output:

emp_name   |Target achieved in all Qtrs.|
-----------+----------------------------+
Valli      |                        1300|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find salespersons who not yet made any sale transaction.
Next: Department where highest salaried employee(s) are working.