w3resource
SQL exercises

SQL Subquery Exercises: Display the employee number, name and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN

SQL SUBQUERY: Exercise-18 with Solution

Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN.

Sample table: employees


Sample Solution:

SELECT employee_id,first_name,last_name, job_id 
FROM employees 
WHERE salary < ANY 
( SELECT salary 
FROM employees 
WHERE job_id = 'MK_MAN' ) 
AND job_id <> 'MK_MAN' ;

Sample Output:

employee_id	first_name	last_name	job_id
103		Alexander	Hunold		IT_PROG
104		Bruce		Ernst		IT_PROG
105		David		Austin		IT_PROG
106		Valli		Pataballa	IT_PROG
107		Diana		Lorentz		IT_PROG
108		Nancy		Greenberg	FI_MGR
109		Daniel		Faviet		FI_ACCOUNT
110		John		Chen		FI_ACCOUNT
111		Ismael		Sciarra		FI_ACCOUNT
112		Jose 	Manuel	Urman		FI_ACCOUNT
113		Luis		Popp		FI_ACCOUNT
114		Den		Raphaely	PU_MAN
115		Alexander	Khoo		PU_CLERK
116		Shelli		Baida		PU_CLERK
117		Sigal		Tobias		PU_CLERK
118		Guy		Himuro		PU_CLERK
119		Karen		Colmenares	PU_CLERK
120		Matthew		Weiss		ST_MAN
121		Adam		Fripp		ST_MAN
122		Payam		Kaufling	ST_MAN
123		Shanta		Vollman		ST_MAN
124		Kevin		Mourgos		ST_MAN
125		Julia		Nayer		ST_CLERK
126		Irene		Mikkilineni	ST_CLERK
127		James		Landry		ST_CLERK
128		Steven		Markle		ST_CLERK
129		Laura		Bissot		ST_CLERK
130		Mozhe		Atkinson	ST_CLERK
131		James		Marlow		ST_CLERK
132		TJ		Olson		ST_CLERK
133		Jason		Mallin		ST_CLERK
134		Michael		Rogers		ST_CLERK
135		Ki		Gee		ST_CLERK
136		Hazel		Philtanker	ST_CLERK
137		Renske		Ladwig		ST_CLERK
138		Stephen		Stiles		ST_CLERK
139		John		Seo		ST_CLERK
140		Joshua		Patel		ST_CLERK
141		Trenna		Rajs		ST_CLERK
142		Curtis		Davies		ST_CLERK
143		Randall		Matos		ST_CLERK
144		Peter		Vargas		ST_CLERK
147		Alberto		Errazuriz	SA_MAN
148		Gerald		Cambrault	SA_MAN
149		Eleni		Zlotkey		SA_MAN
150		Peter		Tucker		SA_REP
151		David		Bernstein	SA_REP
152		Peter		Hall		SA_REP
153		Christopher	Olsen		SA_REP
154		Nanette		Cambrault	SA_REP
155		Oliver		Tuvault		SA_REP
156		Janette		King		SA_REP
157		Patrick		Sully		SA_REP
158		Allan		McEwen		SA_REP
159		Lindsey		Smith		SA_REP
160		Louise		Doran		SA_REP
161		Sarath		Sewall		SA_REP
162		Clara		Vishney		SA_REP
163		Danielle	Greene		SA_REP
164		Mattea		Marvins		SA_REP
165		David		Lee		SA_REP
166		Sundar		Ande		SA_REP
167		Amit		Banda		SA_REP
168		Lisa		Ozer		SA_REP
169		Harrison	Bloom		SA_REP
170		Tayler		Fox		SA_REP
171		William		Smith		SA_REP
172		Elizabeth	Bates		SA_REP
173		Sundita		Kumar		SA_REP
174		Ellen		Abel		SA_REP
175		Alyssa		Hutton		SA_REP
176		Jonathon	Taylor		SA_REP
177		Jack		Livingston	SA_REP
178		Kimberely	Grant		SA_REP
179		Charles		Johnson		SA_REP
180		Winston		Taylor		SH_CLERK
181		Jean		Fleaur		SH_CLERK
182		Martha		Sullivan	SH_CLERK
183		Girard		Geoni		SH_CLERK
184		Nandita		Sarchand	SH_CLERK
185		Alexis		Bull		SH_CLERK
186		Julia		Dellinger	SH_CLERK
187		Anthony		Cabrio		SH_CLERK
188		Kelly		Chung		SH_CLERK
189		Jennifer	Dilly		SH_CLERK
190		Timothy		Gates		SH_CLERK
191		Randall		Perkins		SH_CLERK
192		Sarah		Bell		SH_CLERK
193		Britney		Everett		SH_CLERK
194		Samuel		McCain		SH_CLERK
195		Vance		Jones		SH_CLERK
196		Alana		Walsh		SH_CLERK
197		Kevin		Feeney		SH_CLERK
198		Donald		OConnell	SH_CLERK
199		Douglas		Grant		SH_CLERK
200		Jennifer	Whalen		AD_ASST
202		Pat		Fay		MK_REP
203		Susan		Mavris		HR_REP
204		Hermann		Baer		PR_REP
205		Shelley		Higgins		AC_MGR
206		William		Gietz		AC_ACCOUNT

Practice Online


Query Visualization:

Duration:

Query visualization of Display the employee number, name and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN - Duration

Rows:

Query visualization of Display the employee number, name and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN - Rows

Cost:

Query visualization of Display the employee number, name and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN. Exclude Job title MK_MAN - Cost

Contribute your code and comments through Disqus.

Previous: Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is smaller than any salary of those employees whose job title is MK_MAN.
Next: Write a query to display the employee number, name( first name and last name ) and job title for all employees whose salary is more than any salary of those employees whose job title is PU_MAN. Exclude job title PU_MAN.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming