SQL Exercises, Practice, Solution - SUBQUERIES
SQL [55 exercises with solution]
You may read our SQL Subqueries tutorial before solving the following exercises.
[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 receive a higher salary than the employee with ID 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 .....
2. From the following table, write a SQL query to find out which employees have the same designation 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 .....
3. From the following table, write a SQL query to find those employees whose salary matches the lowest 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 .....
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 .....
5. From the following table, write a SQL query to find those employees who report to 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 .....
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
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
8. From the following table, write a SQL query to find those employees whose ID matches any of the numbers 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
9. From the following table, write a SQL query to find those employees whose salary is in the range of 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 .....
10. From the following table and write a SQL query to find those employees whose salary falls within the range of the 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 .....
11. From the following tables, write a SQL query to find those employees who do not work in the departments where managers’ IDs are between 100 and 200 (Begin and end values are included.). Return all the fields of the employeess. 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 .....
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
13. From the following tables, write a SQL query to find those employees who work in the same department as ‘Clara’. 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 .....
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 the 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 .....
15. From the following tables, write a SQL query to find those employees who earn more than the average salary and work in the same department as an employee whose first name contains the letter '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 .....
16. From the following table, write a SQL query to find those employees whose department is 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
17. From the following table, write a SQL query to find those employees whose salary is lower than that of 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 .....
18. From the following table, write a SQL query to find those employees whose salary is lower than that of 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 .....
19. From the following table, write a SQL query to find those employees whose salary exceeds the salary of all 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 .....
20. From the following table, write a SQL query to find those employees whose salaries are higher than the average for all departments. 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
21. From the following table, write a SQL query to check whether there are any employees with salaries exceeding 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 .....
22. From the following table, write a SQL query to calculate total salary of the 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 .....
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
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
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
26. From the following table, write a SQL query to find all those departments where at least one employee is employed. Return department name. Go to the editor
Sample table: employees
Sample table: departments
Sample Output:
department_name Administration Marketing Purchasing Human Resources Shipping .....
27. From the following tables, write a SQL query to find employees who work in departments located in the 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
28. From the following table, write a SQL query to find out which employees are earning more than the 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
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 .....
30. From the following tables, write a SQL query find the employees who report to a manager based in the United States. 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 .....
31. From the following tables, write a SQL query to find those employees whose salaries exceed 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
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 .....
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 .....
34. From the following table, write a SQL query to search for employees who receive such a salary, which is the maximum salary for salaried employees, hired between 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
35. From the following tables, write a SQL query to find those departments that are located in the city of 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
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
37. From the following table, write a SQL query to find those employees who earn more than the maximum salary for 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
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
39. From the following table, write a SQL query to find employees who work for the department in which employee ID 201 is employed. 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
40. From the following table, write a SQL query to find those employees whose salary matches that of the employee who works in department 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
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
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
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
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
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 Laura (first name) is employed. 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
46. From the following tables, write a SQL query to find all employees whose department is located in 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
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
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
49. From the following tables, write a SQL query to find those departments where the 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
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
51. From the following table, write a SQL query to find employees who have previously worked as 'Sales Representatives'. 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
52. From the following table, write a SQL query to find those employees who earn the 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
53. From the following table, write a SQL query to find the 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
54. From the following table, write a SQL query to find those employees who earn the 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 ....
55. From the following table, write a SQL query to find those employees who have not had a 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 .....
Keep Learning: SQL Subqueries, SQL Single Row Subqueries, SQL Multiple Row and Column Subqueries, SQL Correlated Subqueries, SQL Nested subqueries.
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.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework