SQL Challenges-1: Find the minimum order amount sold by each salesperson

SQL Challenges-1: Exercise-55 with Solution

From the following tables write a SQL query to find the minimum order amount sold by each salesperson. Return the salesperson ID, name and the minimum order amount they sold.

Input:

Table: sales

Structure:

Data:

Table: salesman

Structure:

Data:

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 s.salesman_id, s.salesman_name ,minimum_sale_amount 
FROM ( SELECT min(sale_amount) as minimum_sale_amount,
salesman_id FROM sales ss 
GROUP BY salesman_id ) sl 
INNER JOIN salesman s ON s.salesman_id=sl.salesman_id 
ORDER BY salesman_id;

Sample Output:

salesman_id|salesman_name        |minimum_sale_amount|
-----------+---------------------+-------------------+
         11|Jonathan Goodwin     |            4500.00|
         12|Adam Hughes          |           14900.00|
         13|Mark Davenport       |            7000.00|
         15|Ethan Birkenhead     |            9778.00|
         17|Josh Day             |           15600.00|
         18|Sean Mann            |            5200.00|
         19|Evan Blake           |            5985.00|
         20|Rhys Emsworth        |           19800.00|
         21|Kian Wordsworth      |            7660.00|
         22|Frederick Kelsey     |            7100.00|
         23|Noah Turner          |           12200.00|
         24|Callum Bing          |           14400.00|
         25|Harri Wilberforce    |            7128.00|
         27|Richard York         |            8125.00|
         28|Tobias Stratford     |            8900.00|
         29|Will Kirby           |            9500.00|
         30|Bradley Wright       |            5300.00|
         31|Eli Willoughby       |           18720.00|
         33|Kieran Freeman       |            6000.00|
         34|Toby Scott           |            5480.00|
         35|Elliot Clapham       |            8129.00|
         36|Lewis Moss           |           19998.00|
         37|Joshua Atterton      |           11000.00|
         38|Jonathan Reynolds    |            7800.00|
         41|Dan Astley           |            5514.00|
         42|Finlay Dalton        |            5900.00|
         43|Toby Rodney          |            8400.00|
         44|Ollie Wheatley       |            7890.00|
         46|Jason Wilson         |            8200.00|
         47|Christopher Wentworth|            5990.00|
         48|Cameron Ansley       |           18880.00|
         49|Henry Porter         |            7548.00|
         50|Ezra Winterbourne    |            5566.00|
         51|Rufus Fleming        |           10000.00|
         52|Wallace Dempsey      |           11265.00|
         53|Dan McKee            |            9825.00|
         54|Marion Caldwell      |            6420.00|
         55|Morris Phillips      |           13000.00|
         56|Chester Chandler     |            5125.00|
         57|Cleveland Klein      |            6645.00|
         58|Hubert Bean          |            9800.00|
         59|Cleveland Hart       |            6500.00|
         60|Marion Gregory       |            8995.00|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Find smart salesperson executed at least 5 transactions.
Next: Department wise number of male and female employees.