w3resource

SQL Challenges-1: Find salespersons who not yet made any sale transaction

SQL Challenges-1: Exercise-73 with Solution

From the following tables write a query in SQL to find the salespersons who not yet made any sale transaction. Return salesperson ID and salesperson's name. Arranged the resultset in ascending order on salesman ID.

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
529565125.00
5302510990.00
5312112600.00
532415514
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
556387926.00
557497548.00
558159778.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 * FROM salesman
WHERE salesman_id NOT IN(
SELECT DISTINCT salesman_id FROM sales
) ORDER BY salesman_id;

Sample Output:

SALESMAN_ID|SALESMAN_NAME        |
-----------+---------------------+
         14|Jamie Shelley        |
         16|Liam Alton           |
         26|Gabriel Gibson       |
         32|Patrick Riley        |
         39|David Hill           |
         40|Aidan Yeardley       |
         45|Sean Spalding        |

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find highest three unique salaries of each department.
Next: Employees resolve highest number of cases in all quarters.



Follow us on Facebook and Twitter for latest update.