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:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
TRANSACTION_ID | int(5) | NO | PRI | ||
SALESMAN_ID | int(4) | NO | |||
SALE_AMOUNT | decimal(8,2) | YES |
Data:
TRANSACTION_ID | SALESMAN_ID | SALE_AMOUNT |
---|---|---|
501 | 18 | 5200.00 |
502 | 50 | 5566.00 |
503 | 38 | 8400.00 |
504 | 43 | 8400.00 |
505 | 11 | 9000.00 |
506 | 42 | 5900.00 |
507 | 13 | 7000.00 |
508 | 33 | 6000.00 |
509 | 41 | 8200.00 |
510 | 11 | 4500.00 |
511 | 51 | 10000.00 |
512 | 29 | 9500.00 |
513 | 59 | 6500.00 |
514 | 38 | 7800.00 |
515 | 58 | 9800.00 |
516 | 60 | 12000.00 |
517 | 58 | 13900.00 |
518 | 23 | 12200.00 |
519 | 34 | 5480.00 |
520 | 35 | 8129.00 |
521 | 49 | 9323.00 |
522 | 46 | 8200.00 |
523 | 47 | 9990.00 |
524 | 42 | 14000.00 |
525 | 44 | 7890.00 |
526 | 47 | 5990.00 |
527 | 21 | 7770.00 |
528 | 57 | 6645.00 |
529 | 56 | 5125.00 |
530 | 25 | 10990.00 |
531 | 21 | 12600.00 |
532 | 41 | 5514.00 |
533 | 17 | 15600.00 |
534 | 44 | 15000.00 |
535 | 12 | 17550.00 |
536 | 55 | 13000.00 |
537 | 58 | 16800.00 |
538 | 25 | 19900.00 |
539 | 57 | 9990.00 |
540 | 28 | 8900.00 |
541 | 44 | 10200.00 |
542 | 57 | 18000.00 |
543 | 34 | 16200.00 |
544 | 36 | 19998.00 |
545 | 30 | 13500.00 |
546 | 37 | 15520.00 |
547 | 36 | 20000.00 |
548 | 20 | 19800.00 |
549 | 22 | 18530.00 |
550 | 19 | 12523.00 |
551 | 46 | 9885.00 |
552 | 22 | 7100.00 |
553 | 54 | 17500.00 |
554 | 19 | 19600.00 |
555 | 24 | 17500.00 |
556 | 42 | 5900.00 |
557 | 38 | 7926.00 |
558 | 49 | 7548.00 |
559 | 56 | 19330.00 |
560 | 24 | 14400.00 |
561 | 18 | 16700.00 |
562 | 54 | 6420.00 |
563 | 31 | 18720.00 |
564 | 21 | 17220.00 |
565 | 48 | 18880.00 |
566 | 33 | 8882.00 |
567 | 44 | 19550.00 |
568 | 22 | 14440.00 |
569 | 53 | 19500.00 |
570 | 30 | 5300.00 |
571 | 30 | 10823.00 |
572 | 35 | 13300.00 |
573 | 35 | 19100.00 |
574 | 18 | 17525.00 |
575 | 60 | 8995.00 |
576 | 53 | 9990.00 |
577 | 21 | 7660.00 |
578 | 27 | 18990.00 |
579 | 11 | 18200.00 |
580 | 30 | 12338.00 |
581 | 37 | 11000.00 |
582 | 27 | 11980.00 |
583 | 18 | 12628.00 |
584 | 52 | 11265.00 |
585 | 53 | 19990.00 |
586 | 27 | 8125.00 |
587 | 25 | 7128.00 |
588 | 57 | 6760.00 |
589 | 19 | 5985.00 |
590 | 52 | 17641.00 |
591 | 53 | 11225.00 |
592 | 22 | 12200.00 |
593 | 59 | 16520.00 |
594 | 35 | 19990.00 |
595 | 42 | 19741.00 |
596 | 19 | 15000.00 |
597 | 57 | 19625.00 |
598 | 53 | 9825.00 |
599 | 24 | 16745.00 |
600 | 12 | 14900.00 |
Table: salesman
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
SALESMAN_ID | int(4) | NO | PRI | ||
SALESMAN_NAME | varchar(30) | YES |
Data:
SALESMAN_ID | SALESMAN_NAME |
---|---|
11 | Jonathan Goodwin |
12 | Adam Hughes |
13 | Mark Davenport |
14 | Jamie Shelley |
15 | Ethan Birkenhead |
16 | Liam Alton |
17 | Josh Day |
18 | Sean Mann |
19 | Evan Blake |
20 | Rhys Emsworth |
21 | Kian Wordsworth |
22 | Frederick Kelsey |
23 | Noah Turner |
24 | Callum Bing |
25 | Harri Wilberforce |
26 | Gabriel Gibson |
27 | Richard York |
28 | Tobias Stratford |
29 | Will Kirby |
30 | Bradley Wright |
31 | Eli Willoughby |
32 | Patrick Riley |
33 | Kieran Freeman |
34 | Toby Scott |
35 | Elliot Clapham |
36 | Lewis Moss |
37 | Joshua Atterton |
38 | Jonathan Reynolds |
39 | David Hill |
40 | Aidan Yeardley |
41 | Dan Astley |
42 | Finlay Dalton |
43 | Toby Rodney |
44 | Ollie Wheatley |
45 | Sean Spalding |
46 | Jason Wilson |
47 | Christopher Wentworth |
48 | Cameron Ansley |
49 | Henry Porter |
50 | Ezra Winterbourne |
51 | Rufus Fleming |
52 | Wallace Dempsey |
53 | Dan McKee |
54 | Marion Caldwell |
55 | Morris Phillips |
56 | Chester Chandler |
57 | Cleveland Klein |
58 | Hubert Bean |
59 | Cleveland Hart |
60 | Marion 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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics