﻿ SQL Challenges-1: Minimum Priced Orders sold by a salesperson - w3resource

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:

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
13Mark Davenport
14Jamie Shelley
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
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 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|
13|Mark Davenport       |            7000.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|
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: