w3resource

SQL Challenges-1: Find smart salesperson executed at least 5 transactions

SQL Challenges-1: Exercise-54 with Solution

A 'Smart' salesperson executed at least 5 transactions with a total sale amount is more than Rs.30000. From the following tables write a SQL query to find the smart salespersons. Return salesperson ID, name, number of transactions executed and the total amount of executed transactions.

Input:

Table: sales

Structure:

FieldTypeNullKeyDefaultExtra
TRANSACTION_IDint(5)NOPRI
SALESMAN_IDint(4)NO
SALE_AMOUNTdecimal(8,2)YES

Data:

TRANSACTION_IDSALESMAN_IDSALE_AMOUNT
501185200.00
502505566.00
503388400.00
504438400.00
505119000.00
506425900.00
507137000.00
508336000.00
509418200.00
510114500.00
5115110000.00
512299500.00
513596500.00
514387800.00
515589800.00
5166012000.00
5175813900.00
5182312200.00
519345480.00
520358129.00
521499323.00
522468200.00
523479990.00
5244214000.00
525447890.00
526475990.00
527217770.00
528576645.00
529565125.00
5302510990.00
5312112600.00
532415514.00
5331715600.00
5344415000.00
5351217550.00
5365513000.00
5375816800.00
5382519900.00
539579990.00
540288900.00
5414410200.00
5425718000.00
5433416200.00
5443619998.00
5453013500.00
5463715520.00
5473620000.00
5482019800.00
5492218530.00
5501912523.00
551469885.00
552227100.00
5535417500.00
5541919600.00
5552417500.00
556425900.00
557387926.00
558497548.00
5595619330.00
5602414400.00
5611816700.00
562546420.00
5633118720.00
5642117220.00
5654818880.00
566338882.00
5674419550.00
5682214440.00
5695319500.00
570305300.00
5713010823.00
5723513300.00
5733519100.00
5741817525.00
575608995.00
576539990.00
577217660.00
5782718990.00
5791118200.00
5803012338.00
5813711000.00
5822711980.00
5831812628.00
5845211265.00
5855319990.00
586278125.00
587257128.00
588576760.00
589195985.00
5905217641.00
5915311225.00
5922212200.00
5935916520.00
5943519990.00
5954219741.00
5961915000.00
5975719625.00
598539825.00
5992416745.00
6001214900.00

Table: salesman

Structure:

FieldTypeNullKeyDefaultExtra
SALESMAN_IDint(4)NOPRI
SALESMAN_NAMEvarchar(30)YES

Data:

SALESMAN_IDSALESMAN_NAME
11Jonathan Goodwin
12Adam Hughes
13Mark Davenport
14Jamie Shelley
15Ethan Birkenhead
16Liam Alton
17Josh Day
18Sean Mann
19Evan Blake
20Rhys Emsworth
21Kian Wordsworth
22Frederick Kelsey
23Noah Turner
24Callum Bing
25Harri Wilberforce
26Gabriel Gibson
27Richard York
28Tobias Stratford
29Will Kirby
30Bradley Wright
31Eli Willoughby
32Patrick Riley
33Kieran Freeman
34Toby Scott
35Elliot Clapham
36Lewis Moss
37Joshua Atterton
38Jonathan Reynolds
39David Hill
40Aidan Yeardley
41Dan Astley
42Finlay Dalton
43Toby Rodney
44Ollie Wheatley
45Sean Spalding
46Jason Wilson
47Christopher Wentworth
48Cameron Ansley
49Henry Porter
50Ezra Winterbourne
51Rufus Fleming
52Wallace Dempsey
53Dan McKee
54Marion Caldwell
55Morris Phillips
56Chester Chandler
57Cleveland Klein
58Hubert Bean
59Cleveland Hart
60Marion Gregory

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
TRANSACTION_ID INTEGER(5) NOT NULL,
SALESMAN_ID   INTEGER(4) NOT NULL,
SALE_AMOUNT  decimal(8,2),
PRIMARY KEY (TRANSACTION_ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO sales VALUES(501,18,5200.00),(502,50,5566.00),(503,38,8400.00),(504,43,8400.00),(505,11,9000.00);
INSERT INTO sales VALUES (506,42,5900.00),(507,13,7000.00),(508,33,6000.00),(509,41,8200.00),(510,11,4500.00);
INSERT INTO sales VALUES (511,51,10000.00),(512,29,9500.00),(513,59,6500.00),(514,38,7800.00),(515,58,9800.00);
INSERT INTO sales VALUES (516,60,12000.00),(517,58,13900.00),(518,23,12200.00),(519,34,5480.00),(520,35,8129.00);
INSERT INTO sales VALUES (521,49,9323.00),(522,46,8200.00),(523,47,9990.00),(524,42,14000.00),(525,44,7890.00);
INSERT INTO sales VALUES (526,47,5990.00),(527,21,7770.00),(528,57,6645.00),(529,56,5125.00),(530,25,10990.00);
INSERT INTO sales VALUES (531,21,12600.00),(532,41,5514.00),(533,17,15600.00),(534,44,15000.00),(535,12,17550.00);
INSERT INTO sales VALUES (536,55,13000.00),(537,58,16800.00),(538,25,19900.00),(539,57,9990.00),(540,28,8900.00);
INSERT INTO sales VALUES (541,44,10200.00),(542,57,18000.00),(543,34,16200.00),(544,36,19998.00),(545,30,13500.00);
INSERT INTO sales VALUES (546,37,15520.00),(547,36,20000.00),(548,20,19800.00),(549,22,18530.00),(550,19,12523.00);
INSERT INTO sales VALUES (551,46,9885.00),(552,22,7100.00),(553,54,17500.00),(554,19,19600.00),(555,24,17500.00);
INSERT INTO sales VALUES (556,38,7926.00),(557,49,7548.00),(558,15,9778.00),(559,56,19330.00),(560,24,14400.00);
INSERT INTO sales VALUES (561,18,16700.00),(562,54,6420.00),(563,31,18720.00),(564,21,17220.00),(565,48,18880.00); 
INSERT INTO sales VALUES (566,33,8882.00),(567,44,19550.00),(568,22,14440.00),(569,53,19500.00),(570,30,5300.00);
INSERT INTO sales VALUES (571,30,10823.00),(572,35,13300.00),(573,35,19100.00),(574,18,17525.00),(575,60,8995.00);
INSERT INTO sales VALUES (576,53,9990.00),(577,21,7660.00),(578,27,18990.00),(579,11,18200.00),(580,30,12338.00);
INSERT INTO sales VALUES (581,37,11000.00),(582,27,11980.00),(583,18,12628.00),(584,52,11265.00),(585,53,19990.00);
INSERT INTO sales VALUES (586,27,8125.00),(587,25,7128.00),(588,57,6760.00),(589,19,5985.00),(590,52,17641.00);
INSERT INTO sales VALUES (591,53,11225.00),(592,22,12200.00),(593,59,16520.00),(594,35,19990.00),(595,42,19741.00);
INSERT INTO sales VALUES (596,19,15000.00),(597,57,19625.00),(598,53,9825.00),(599,24,16745.00),(600,12,14900.00);
DROP TABLE IF EXISTS `salesman`;
CREATE TABLE `salesman` (
`SALESMAN_ID` 	            INTEGER(4) NOT NULL,
`SALESMAN_NAME`               varchar(30),
PRIMARY KEY (`SALESMAN_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO salesman VALUES(11	,'Jonathan Goodwin     ');
INSERT INTO salesman VALUES(12	,'Adam Hughes          ');
INSERT INTO salesman VALUES(13	,'Mark Davenport       ');
INSERT INTO salesman VALUES(14	,'Jamie Shelley        ');
INSERT INTO salesman VALUES(15	,'Ethan Birkenhead     ');
INSERT INTO salesman VALUES(16	,'Liam Alton           ');
INSERT INTO salesman VALUES(17	,'Josh Day             ');
INSERT INTO salesman VALUES(18	,'Sean Mann            ');
INSERT INTO salesman VALUES(19	,'Evan Blake           ');
INSERT INTO salesman VALUES(20	,'Rhys Emsworth        ');
INSERT INTO salesman VALUES(21	,'Kian Wordsworth      ');
INSERT INTO salesman VALUES(22	,'Frederick Kelsey     ');
INSERT INTO salesman VALUES(23	,'Noah Turner          ');
INSERT INTO salesman VALUES(24	,'Callum Bing          ');
INSERT INTO salesman VALUES(25	,'Harri Wilberforce    ');
INSERT INTO salesman VALUES(26	,'Gabriel Gibson       ');
INSERT INTO salesman VALUES(27	,'Richard York         ');
INSERT INTO salesman VALUES(28	,'Tobias Stratford     ');
INSERT INTO salesman VALUES(29	,'Will Kirby           ');
INSERT INTO salesman VALUES(30	,'Bradley Wright       ');
INSERT INTO salesman VALUES(31	,'Eli Willoughby       ');
INSERT INTO salesman VALUES(32	,'Patrick Riley        ');
INSERT INTO salesman VALUES(33	,'Kieran Freeman       ');
INSERT INTO salesman VALUES(34	,'Toby Scott           ');
INSERT INTO salesman VALUES(35	,'Elliot Clapham       ');
INSERT INTO salesman VALUES(36	,'Lewis Moss           ');
INSERT INTO salesman VALUES(37	,'Joshua Atterton      ');
INSERT INTO salesman VALUES(38	,'Jonathan Reynolds    ');
INSERT INTO salesman VALUES(39	,'David Hill           ');
INSERT INTO salesman VALUES(40	,'Aidan Yeardley       ');
INSERT INTO salesman VALUES(41	,'Dan Astley           ');
INSERT INTO salesman VALUES(42	,'Finlay Dalton        ');
INSERT INTO salesman VALUES(43	,'Toby Rodney          ');
INSERT INTO salesman VALUES(44	,'Ollie Wheatley       ');
INSERT INTO salesman VALUES(45	,'Sean Spalding        ');
INSERT INTO salesman VALUES(46	,'Jason Wilson         ');
INSERT INTO salesman VALUES(47	,'Christopher Wentworth');
INSERT INTO salesman VALUES(48	,'Cameron Ansley       ');
INSERT INTO salesman VALUES(49	,'Henry Porter         ');
INSERT INTO salesman VALUES(50	,'Ezra Winterbourne    ');
INSERT INTO salesman VALUES(51	,'Rufus Fleming        ');
INSERT INTO salesman VALUES(52	,'Wallace Dempsey      ');
INSERT INTO salesman VALUES(53	,'Dan McKee            ');
INSERT INTO salesman VALUES(54	,'Marion Caldwell      ');
INSERT INTO salesman VALUES(55	,'Morris Phillips      ');
INSERT INTO salesman VALUES(56	,'Chester Chandler     ');
INSERT INTO salesman VALUES(57	,'Cleveland Klein      ');
INSERT INTO salesman VALUES(58	,'Hubert Bean          ');
INSERT INTO salesman VALUES(59	,'Cleveland Hart       ');
INSERT INTO salesman VALUES(60	,'Marion Gregory       ');


SELECT salesman.salesman_id,salesman.salesman_name AS name,
       COUNT(sales.transaction_id) AS order_count,
       sum(sale_amount) AS total_sale_amount
FROM sales
INNER JOIN salesman ON sales.salesman_id = salesman.salesman_id
GROUP BY salesman.salesman_id,
         salesman.salesman_name
HAVING total_sale_amount>30000
AND COUNT(sales.transaction_id) >=5;

Sample Output:

salesman_id|name                 |order_count|total_sale_amount|
-----------+---------------------+-----------+-----------------+
         57|Cleveland Klein      |          5|         61020.00|
         53|Dan McKee            |          5|         70530.00|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Highest difference in total sale of all quarters on a product of many companies.
Next: Find the minimum order amount sold by each salesperson.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/challenges-1/sql-challenges-1-exercise-54.php