w3resource

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

SQL Challenges-1: Exercise-74 with Solution

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:

FieldTypeNullKeyDefaultExtra
emp_idintNOPRI
emp_namevarchar(30)YES
emp_sexvarchar(1)YES
depart_namevarchar(25)YES
emp_salaryintYES
qtr1intYES
qtr2intYES
qtr3intYES
qtr4intYES
emp_departmentintYES

Data:

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

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.



Follow us on Facebook and Twitter for latest update.