w3resource

SQL Exercises, Practice, Solution - SUBQUERIES

SQL [55 exercises with solution]

[An editor is available at the bottom of the page to write and execute the scripts.]

1. From the following table, write a SQL query to find those employees who get higher salary than the employee whose ID is 163. Return first name, last name.  Go to the editor

Sample table: employees


Sample Output:

first_name	last_name
Steven		King
Neena		Kochhar
Lex		De Haan
.....

Click me to see the solution

2. From the following table, write a SQL query to find those employees whose designation is the same as the employee whose ID is 169. Return first name, last name, department ID and job ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary		department_id	job_id
Peter		Tucker		10000.00	80		SA_REP
David		Bernstein	9500.00		80		SA_REP
Peter		Hall		9000.00		80		SA_REP
.....

Click me to see the solution

3. From the following table, write a SQL query to find those employees whose salary matches the smallest salary of any of the departments. Return first name, last name and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Bruce		Ernst		6000.00		60
Diana		Lorentz		4200.00		60
.....

Click me to see the solution

4. From the following table, write a SQL query to find those employees who earn more than the average salary. Return employee ID, first name, last name.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name
100		Steven		King
101		Neena		Kochhar
102		Lex		De Haan
103		Alexander	Hunold
.....

Click me to see the solution

5. From the following table, write a SQL query to find those employees who report that manager whose first name is ‘Payam’. Return first name, last name, employee ID and salary.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	employee_id	salary
Jason		Mallin		133		3300.00
Michael		Rogers		134		2900.00
Ki		Gee		135		2400.00
Hazel		Philtanker	136		2200.00
.....

Click me to see the solution

6. From the following tables, write a SQL query to find all those employees who work in the Finance department. Return department ID, name (first), job ID and department name.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

department_id	first_name	job_id		department_name
100		Nancy		FI_MGR			Finance
100		Daniel		FI_ACCOUNT		Finance
100		John		FI_ACCOUNT		Finance
100		Ismael		FI_ACCOUNT		Finance
100		Jose Manuel	FI_ACCOUNT		Finance
100		Luis		FI_ACCOUNT		Finance

Click me to see the solution

7. From the following table, write a SQL query to find the employee whose salary is 3000 and reporting person’s ID is 121. Return all fields.   Go to the editor

Sample table : employees


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
187		Anthony		Cabrio		ACABRIO	650.509.4876	2007-02-07	SH_CLERK	3000.00	0.00	121		50

Click me to see the solution

8. From the following table, write a SQL query to find those employees whose ID matches any of the number 134, 159 and 183. Return all the fields.    Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	email	phone_number 		hire_date	job_id 	salary commission_pct	manager_id	department_id
134		Michael		Rogers		MROGERS	650.127.1834		2006-08-26	ST_CLERK	2900.00	  0.00		122		50
159		Lindsey		Smith		LSMITH	011.44.1345.729268	2005-03-10	SA_REP		8000.00   0.30		146		80
183		Girard		Geoni		GGEONI	650.507.9879		2008-02-03	SH_CLERK	2800.00	  0.00		120		50

Click me to see the solution

9. From the following table, write a SQL query to find those employees whose salary is in the range 1000, and 3000 (Begin and end values have included.). Return all the fields.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
116		Shelli		Baida		SBAIDA	515.127.4563	2005-12-24	PU_CLERK	2900.00	0.00	114		30
117		Sigal		Tobias		STOBIAS	515.127.4564	2005-07-24	PU_CLERK	2800.00	0.00	114		30
118		Guy		Himuro		GHIMURO	515.127.4565	2006-11-15	PU_CLERK	2600.00	0.00	114		30
119		Karen		Colmenares	KCOLMENA515.127.4566	2007-08-10	PU_CLERK	2500.00	0.00	114		30
.....

Click me to see the solution

10. From the following table and write a SQL query to find those employees whose salary is in the range of smallest salary, and 2500. Return all the fields.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
119		Karen		Colmenares	KCOLMENA515.127.4566	2007-08-10	PU_CLERK	2500.00	0.00	114		30
127		James		Landry		JLANDRY	650.124.1334	2007-01-14	ST_CLERK	2400.00	0.00	120		50
128		Steven		Markle		SMARKLE	650.124.1434	2008-03-08	ST_CLERK	2200.00	0.00	120		50
.....

Click me to see the solution

11. From the following tables, write a SQL query to find those employees who do not work in those departments where manager ids are in the range 100, 200 (Begin and end values are included.) Return all the fields of the employees.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

employee_id	first_name	last_name	email	phone_number		hire_date	job_id	salary	commission_pct	manager_id	department_id
178		Kimberely	Grant		KGRANT	011.44.1644.429263 	2007-05-24	SA_REP	7000.00		0.15	149		0
201		Michael		Hartstein	MHARTSTE515.123.5555		2004-02-17	MK_MAN	13000.00	0.00	100		20
202		Pat		Fay		PFAY	603.123.6666		2005-08-17	MK_REP	6000.00		0.00	201		20
203		Susan		Mavris		SMAVRIS	515.123.7777		2002-06-07	HR_REP	6500.00		0.00	101		40
.....

Click me to see the solution

12. From the following table, write a SQL query to find those employees who get second-highest salary. Return all the fields of the employees.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
101		Neena		Kochhar		NKOCHHAR515.123.4568	2005-09-21	AD_VP	17000.00	0.00	100		90
102		Lex		De Haan		LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	0.00	100		90

Click me to see the solution

13. From the following tables, write a SQL query to find those employees who work in the same department where ‘Clara’ works. Exclude all those records where first name is ‘Clara’. Return first name, last name and hire date.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	hire_date
John		Russell		2004-10-01
Karen		Partners	2005-01-05
Alberto		Errazuriz	2005-03-10
Gerald		Cambrault	2007-10-15
.....

Click me to see the solution

14. From the following tables, write a SQL query to find those employees who work in a department where the employee’s first name contains a letter 'T'. Return employee ID, first name and last name.  Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name
120		Matthew		Weiss
121		Adam		Fripp
122		Payam		Kaufling
123		Shanta		Vollman
124		Kevin		Mourgos
.....

Click me to see the solution

15. From the following tables, write a SQL query to find those employees who earn more than the average salary and work in a department with any employee whose first name contains a character a 'J'. Return employee ID, first name and salary.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	salary
108		Nancy		12000.00
109		Daniel		9000.00
110		John		8200.00
111		Ismael		7700.00
.....

Click me to see the solution

16. From the following table, write a SQL query to find those employees whose department located at 'Toronto'. Return first name, last name, employee ID, job ID.   Go to the editor

Sample table: employees


Sample table: departments


Sample table: locations


Sample Output:

first_name	last_name	employee_id	job_id
Michael		Hartstein	201		MK_MAN
Pat		Fay		202		MK_REP

Click me to see the solution

17. From the following table, write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is ‘MK_MAN’. Return employee ID, first name, last name, job ID.   Go to the editor

Sample table: employees


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
.....

Click me to see the solution

18. From the following table, write a SQL query to find those employees whose salary is lower than any salary of those employees whose job title is 'MK_MAN'. Exclude employees of Job title ‘MK_MAN’. Return employee ID, first name, last name, job ID.   Go to the editor

Sample table: employees


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
.....

Click me to see the solution

19. From the following table, write a SQL query to find those employees whose salary is more than any salary of those employees whose job title is 'PU_MAN'. Exclude job title 'PU_MAN'. Return employee ID, first name, last name, job ID.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES
101		Neena		Kochhar		AD_VP
102		Lex		De Haan		AD_VP
108		Nancy		Greenberg	FI_MGR
.....

Click me to see the solution

20. From the following table, write a SQL query to find those employees whose salary is more than average salary of any department. Return employee ID, first name, last name, job ID.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	job_id
100		Steven		King		AD_PRES

Click me to see the solution

21. From the following table, write a SQL query to find any existence of those employees whose salary exceeds 3700. Return first name, last name and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	department_id
Steven		King		90
Neena		Kochhar		90
Lex		De Haan		90
Alexander	Hunold		60
Bruce		Ernst		60
.....

Click me to see the solution

22. From the following table, write a SQL query to find total salary of those departments where at least one employee works. Return department ID, total salary.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

department_id	total_amt
10		4400.00
20		19000.00
30		24900.00
40		6500.00
.....

Click me to see the solution

23. Write a query to display the employee id, name ( first name and last name ) and the job id column with a modified title SALESMAN for those employees whose job title is ST_MAN and DEVELOPER for whose job title is IT_PROG.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	designation	salary
100		Steven		King		AD_PRES		24000.00
101		Neena		Kochhar		AD_VP		17000.00
102		Lex		De Haan		AD_VP		17000.00
103		Alexander	Hunold		DEVELOPER	9000.00
104		Bruce		Ernst		DEVELOPER	6000.00

Click me to see the solution

24. Write a query to display the employee id, name ( first name and last name ), salary and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	salary	salarystatus
100		Steven		King		24000.00	HIGH
101		Neena		Kochhar		17000.00	HIGH
102		Lex		De Haan		17000.00	HIGH
103		Alexander	Hunold		9000.00		HIGH
104		Bruce		Ernst		6000.00		LOW
105		David		Austin		4800.00		LOW

Click me to see the solution

25. Write a query to display the employee id, name ( first name and last name ), SalaryDrawn, AvgCompare (salary - the average salary of all employees) and the SalaryStatus column with a title HIGH and LOW respectively for those employees whose salary is more than and less than the average salary of all employees.   Go to the editor

Sample table: employees


Sample Output:

employee_id	first_name	last_name	salarydrawn	avgcompare	salarystatus
100		Steven		King		24000.00	17538.32	HIGH
101		Neena		Kochhar		17000.00	10538.32	HIGH
102		Lex		De Haan		17000.00	10538.32	HIGH
103		Alexander	Hunold		9000.00		2538.32		HIGH
104		Bruce		Ernst		6000.00		-461.68		LOW
105		David		Austin		4800.00		-1661.68	LOW

Click me to see the solution

26. From the following table, write a SQL query to find all those departments where at least one or more employees work.Return department name.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

department_name
Administration
Marketing
Purchasing
Human Resources
Shipping
.....

Click me to see the solution

27. From the following tables, write a SQL query to find those employees who work in departments located at 'United Kingdom'. Return first name.   Go to the editor

Sample table: employees


Sample table: departments


Sample table: locations


Sample table: countries


Sample Output:

first_name
Susan

Click me to see the solution

28. From the following table, write a SQL query to find those employees who earn more than average salary and who work in any of the 'IT' departments. Return last name.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

last_name
Hunold

Click me to see the solution

29. From the following table, write a SQL query to find all those employees who earn more than an employee whose last name is 'Ozer'. Sort the result in ascending order by last name. Return first name, last name and salary.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary
Lex		De Haan		17000.00
Alberto		Errazuriz	12000.00
Nancy		Greenberg	12000.00
Michael		Hartstein	13000.00
.....

Click me to see the solution

30. From the following tables, write a SQL query to find those employees who work under a manager based in ‘US’. Return first name, last name.   Go to the editor

Sample table: employees


Sample table: departments


Sample table: locations


Sample Output:

first_name	last_name
Neena		Kochhar
Lex		De Haan
Alexander	Hunold
Bruce		Ernst
David		Austin
.....

Click me to see the solution

31. From the following tables, write a SQL query to find those employees whose salary is greater than 50% of their department's total salary bill. Return first name, last name.   Go to the editor

Sample table : employees


Sample Output:

first_name	last_name
Kimberely	Grant
Jennifer	Whalen
Michael		Hartstein
Susan		Mavris
Hermann		Baer
Shelley		Higgins

Click me to see the solution

32. From the following tables, write a SQL query to find those employees who are managers. Return all the fields of employees table.   Go to the editor

Sample table: employees


Sample table: departments


employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
100		Steven		King		SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	0.00	0		90
103		Alexander	Hunold		AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00		0.00	102		60
108		Nancy		Greenberg	NGREENBE515.124.4569	2002-08-17	FI_MGR	12000.00	0.00	101		100
.....

Click me to see the solution

33. From the following table, write a SQL query to find those employees who manage a department. Return all the fields of employees table.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
100		Steven		King		SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	0.00	0		90
103		Alexander	Hunold		AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00		0.00	102		60
108		Nancy		Greenberg	NGREENBE515.124.4569	2002-08-17	FI_MGR	12000.00	0.00	101		100
.....

Click me to see the solution

34. From the following table, write a SQL query to find those employees who get such a salary, which is the maximum of salaried employee, joining within January 1st, 2002 and December 31st, 2003. Return employee ID, first name, last name, salary, department name and city.   Go to the editor

Sample table: employees


Sample table: departments


Sample table: locations


Sample Output:

employee_id	first_name	last_name	salary	department_name		city
100		Steven		King		24000.00	Executive	Seattle

Click me to see the solution

35. From the following tables, write a SQL query to find those departments, located in the city ‘London’. Return department ID, department name.   Go to the editor

Sample table: departments


Sample table: locations


Sample Output:

department_id	department_name
40		Human Resources

Click me to see the solution

36. From the following table, write a SQL query to find those employees who earn more than the average salary. Sort the result-set in descending order by salary. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Steven		King		24000.00	90
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
John		Russell		14000.00	80

Click me to see the solution

37. From the following table, write a SQL query to find those employees who earn more than the maximum salary of a department of ID 40. Return first name, last name and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Steven		King		24000.00	90
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Alexander	Hunold		9000.00		60

Click me to see the solution

38. From the following table, write a SQL query to find departments for a particular location. The location matches the location of the department of ID 30. Return department name and department ID.  Go to the editor

Sample table: departments


Sample Output:

department_name	department_id
Administration		10
Purchasing		30
Executive		90
Finance			100
Accounting		110

Click me to see the solution

39. From the following table, write a SQL query to find those employees who work in that department where the employee works of ID 201. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Michael		Hartstein	13000.00	20
Pat		Fay		6000.00		20

Click me to see the solution

40. From the following table, write a SQL query to find those employees whose salary matches to the salary of the employee who works in that department of ID 40. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Shanta		Vollman		6500.00		50
Susan		Mavris		6500.00		40

Click me to see the solution

41. From the following table, write a SQL query to find those employees who work in the department 'Marketing'. Return first name, last name and department ID.   Go to the editor

Sample table: employees


Sample table: departments


Sample Output:

first_name	last_name	department_id
Michael		Hartstein	20
Pat		Fay		20

Click me to see the solution

42. From the following table, write a SQL query to find those employees who earn more than the minimum salary of a department of ID 40. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Steven		King		24000.00	90
Neena		Kochhar		17000.00	90
Lex		De Haan		17000.00	90
Alexander	Hunold		9000.00		60

Click me to see the solution

43. From the following table, write a SQL query to find those employees who joined after the employee whose ID is 165. Return first name, last name and hire date.   Go to the editor

Sample table: employees


Sample Output:

full_name	hire_date
Steven Markle	2008-03-08
Sundar Ande	2008-03-24
Amit Banda	2008-04-21
Sundita Kumar	2008-04-21

Click me to see the solution

44. From the following table, write a SQL query to find those employees who earn less than the minimum salary of a department of ID 70. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Alexander	Hunold		9000.00	60
Bruce		Ernst		6000.00	60
David		Austin		4800.00	60
Valli		Pataballa	4800.00	60

Click me to see the solution

45. From the following table, write a SQL query to find those employees who earn less than the average salary, and work at the department where the employee 'Laura' (first name) works. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample Output:

first_name	last_name	salary	department_id
Kevin		Mourgos		5800.00	50
Julia		Nayer		3200.00	50
Irene		Mikkilineni	2700.00	50
James		Landry		2400.00	50

Click me to see the solution

46. From the following tables, write a SQL query to find those employees whose department is located in the city 'London'. Return first name, last name, salary, and department ID.   Go to the editor

Sample table: employees


Sample table: locations


Sample table: departments


Sample Output:

first_name	last_name	salary	department_id
Susan		Mavris		6500.00	40

Click me to see the solution

47. From the following tables, write a SQL query to find the city of the employee of ID 134. Return city.   Go to the editor

Sample table: locations


Sample table: departments


Sample table: employees


Sample Output:

city
South San Francisco

Click me to see the solution

48. From the following tables, write a SQL query to find those departments where maximum salary is 7000 and above. The employees worked in those departments have already completed one or more jobs. Return all the fields of the departments.   Go to the editor

Sample table: departments


Sample table: employees


Sample table: job_history


Sample Output:

department_id	department_name	manager_id	location_id
80		Sales		145		2500
90		Executive	 100	1700

Click me to see the solution

49. From the following tables, write a SQL query to find those departments where starting salary is at least 8000. Return all the fields of departments.   Go to the editor

Sample table: departments


Sample table: employees


Sample Output:

department_id	department_name	manager_id	location_id
70		Public Relations	204		2700
90		Executive		100		1700
110		Accounting		205		1700

Click me to see the solution

50. From the following table, write a SQL query to find those managers who supervise four or more employees. Return manager name, department ID.   Go to the editor

Sample table : employees


Sample Output:

manager_name	department_id
Steven King		90
Neena Kochhar		90
Alexander Hunold	60
Nancy Greenberg		100

Click me to see the solution

51. From the following table, write a SQL query to find those employees who worked as a 'Sales Representative' in the past. Return all the fields of jobs.   Go to the editor

Sample table: jobs


Sample table: employees


Sample table: job_history


Sample Output:

job_id	job_title		min_salary	max_salary
SA_REP	Sales Representative	6000		12000

Click me to see the solution

52. From the following table, write a SQL query to find those employees who earn second-lowest salary of all the employees. Return all the fields of employees.   Go to the editor

Sample table : employees


Sample Output:

employee_id	first_name	last_name	email	phone_number 	hire_date  job_id	salary	commission_pct	manager_id	department_id
128		Steven		Markle		SMARKLE	  650.124.1434	2008-03-08ST_CLERK 	2200.00		0.00	120	 	50
136		Hazel		Philtanker	HPHILTAN  650.127.1634	2008-02-06ST_CLERK 	2200.00		0.00	122		50

Click me to see the solution

53. From the following table, write a SQL query to find those departments managed by ‘Susan’. Return all the fields of departments.  Go to the editor

Sample table: departments


Sample table: employees


Sample Output:

department_id	department_name	manager_id	location_id
40		Human Resources	203 		2400

Click me to see the solution

54. From the following table, write a SQL query to find those employees who earn highest salary in a department. Return department ID, employee name, and salary.   Go to the editor

Sample table: employees


Sample Output:

department_id	employee_name	salary
90		Steven King			24000.00
60		Alexander Hunold	9000.00
100		Nancy Greenberg		12000.00
30		Den Raphaely		11000.00
....

Click me to see the solution

55. From the following table, write a SQL query to find those employees who did not have any job in the past. Return all the fields of employees.   Go to the editor

Sample table: employees


Sample table: job_history


Sample Output:

employee_id	first_name	last_name	email	phone_number	hire_date	job_id	salary	commission_pct	manager_id	department_id
100		Steven	 	King		SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	0.00		0		90
103		Alexander	Hunold		AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00		0.00		102		60
104		Bruce	 	Ernst		BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00		0.00		103		60
105		David	 	Austin		DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00		0.00		103		60
.....

Click me to see the solution

Practice Online


More to Come !

Query visualizations are generated using Postgres Explain Visualizer (pev).

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.