w3resource

SQL Exercises, Practice, Solution - JOINS on HR Database

SQL JOINS [27 exercises with solution]

You may read our SQL Joins, SQL Left Join, SQL Right Join tutorial before solving the following exercises.

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

1. From the following tables, write a SQL query to find the first name, last name, department number, and department name for each employee.

Sample table: departments


Sample table: employees


Sample Output:

first_name	last_name	department_id	department_name
Steven		King		90		Executive
Neena		Kochhar		90		Executive
Lex		De Haan		90		Executive
Alexander	Hunold		60		IT
.....

Click me to see the solution

2. From the following tables, write a SQL query to find the first name, last name, department, city, and state province for each employee.

Sample table: departments


Sample table: employees


Sample table: locations


Sample Output:

first_name	last_name	department_name	city	state_province
Steven		King		Executive	Seattle		Washington
Neena		Kochhar		Executive	Seattle		Washington
Lex		De Haan		Executive	Seattle		Washington
Alexander	Hunold		IT		Southlake	Texas
.....

Click me to see the solution

3. From the following table, write a SQL query to find the first name, last name, salary, and job grade for all employees.

Sample table: employees


Sample table: job_grades


Sample Output:

first_name	last_name	salary	grade_level
Shelli		Baida		2900.00		A
Sigal		Tobias		2800.00		A
Guy		Himuro		2600.00		A
Karen		Colmenares	2500.00		A
.....

Click me to see the solution

4. From the following tables, write a SQL query to find all those employees who work in department ID 80 or 40. Return first name, last name, department number and department name.

Sample table: departments


Sample table: employees


Sample Output:

first_name	last_name	department_id	department_name
Ellen		Abel		80		Sales
Sundar		Ande		80		Sales
Amit		Banda		80		Sales
Elizabeth	Bates		80		Sales
.....

Click me to see the solution

5. From the following tables, write a SQL query to find those employees whose first name contains the letter ‘z’. Return first name, last name, department, city, and state province.

Sample table: departments


Sample table: employees


Sample table: locations


Sample Output:

first_name	last_name	department_name	city		   state_province
Mozhe		Atkinson	Shipping	South San Francisco	California
Hazel		Philtanker	Shipping	South San Francisco	California
Elizabeth	Bates		Sales		OX9 9ZB			Oxford

Click me to see the solution

6. From the following tables, write a SQL query to find all departments, including those without employees. Return first name, last name, department ID, department name.

Sample table: departments


Sample table: employees


Sample Output:

first_name  |  last_name  | department_id |   department_name    
-------------+-------------+---------------+----------------------
 Steven      | King        |            90 | Executive
 Neena       | Kochhar     |            90 | Executive
 Lex         | De Haan     |            90 | Executive
 Alexander   | Hunold      |            60 | IT
 Bruce       | Ernst       |            60 | IT
 .....
 

Click me to see the solution

7. From the following table, write a SQL query to find the employees who earn less than the employee of ID 182. Return first name, last name and salary.

Sample table: employees


Sample Output:

first_name	last_name	salary
James		Landry		2400.00
Steven		Markle		2200.00
TJ		Olson		2100.00
Ki		Gee		2400.00
Hazel		Philtanker	2200.00

Click me to see the solution

8. From the following table, write a SQL query to find the employees and their managers. Return the first name of the employee and manager.

Sample table: employees


Sample Output:

Employee Name	Manager
Neena		Steven
Lex		Steven
Alexander	Lex
Bruce		Alexander
David		Alexander
.....

Click me to see the solution

9. From the following tables, write a SQL query to display the department name, city, and state province for each department.

Sample table: departments


Sample table: locations


Sample Output:

department_name		city	state_province
Administration		Seattle	Washington
Marketing		Toronto	Ontario
Purchasing		Seattle	Washington
Human 	Resources	London	
.....

Click me to see the solution

10. From the following tables, write a SQL query to find out which employees have or do not have a department. Return first name, last name, department ID, department name.

Sample table: departments


Sample table: employees


Sample Output:

first_name	last_name	department_id	department_name
Steven		King		90		Executive
Neena		Kochhar		90		Executive
Lex		De Haan		90		Executive
Alexander	Hunold		60		IT
.....

Click me to see the solution

11. From the following table, write a SQL query to find the employees and their managers. Those managers do not work under any manager also appear in the list. Return the first name of the employee and manager.

Sample table: employees


Sample Output:

Employee Name	Manager
Steven	
Neena		Steven
Lex		Steven
Alexander	Lex
Bruce		Alexander
.....

Click me to see the solution

12. From the following tables, write a SQL query to find the employees who work in the same department as the employee with the last name Taylor. Return first name, last name and department ID.

Sample table: employees


Sample Output:

first_name	last_name	department_id
Matthew		Weiss		50
Adam		Fripp		50
Payam		Kaufling	50
Shanta		Vollman		50
.....

Click me to see the solution

13. From the following tables, write a SQL query to find all employees who joined on or after 1st January 1993 and on or before 31 August 1997. Return job title, department name, employee name, and joining date of the job.

Sample table: job_history


Sample table: employees


Sample table: jobs


Sample table: departments


Sample Output:

job_title		department_name		employee_name	start_date
Administration Assistant  Executive	 Jennifer Whalen	1995-09-17

Click me to see the solution

14. From the following tables, write a SQL query to calculate the difference between the maximum salary of the job and the employee's salary. Return job title, employee name, and salary difference.

Sample table: employees


Sample table: jobs


Sample Output:

job_title			employee_name		salary_difference
President			Steven King	 		16000.00
Administration Vice President   Neena Kochhar			13000.00
Administration Vice President   Lex De Haan	 		13000.00
Programmer			Alexander Hunold 		1000.00
.....

Click me to see the solution

15. From the following table, write a SQL query to calculate the average salary, the number of employees receiving commissions in that department. Return department name, average salary and number of employees.

Sample table: employees


Sample table : departments


Sample Output:

department_name		avg		count
Shipping	3475.5555555555555556	45
Sales		8955.8823529411764706	34
IT		5760.0000000000000000	5
Administration	4400.0000000000000000	1
.....

Click me to see the solution

16. From the following tables, write a SQL query to calculate the difference between the maximum salary and the salary of all the employees who work in the department of ID 80. Return job title, employee name and salary difference.

Sample table: employees


Sample table: jobs


Sample Output:

job_title		employee_name		salary_difference
Sales Manager		John Russell		6000.00
Sales Manager		Karen Partners		6500.00
Sales Manager		Alberto Errazuriz	8000.00
Sales Manager		Gerald Cambrault	9000.00
.....

Click me to see the solution

17. From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.

Sample table: countries


Sample table: locations


Sample table: departments


Sample Output:

country_name			city		department_name
Canada				Toronto		Marketing
Germany				Munich		Public Relations
United Kingdom			London		Human Resources
United States of America	Seattle		Payroll
.....

Click me to see the solution

18. From the following tables, write a SQL query to find the department name and the full name (first and last name) of the manager.

Sample table: departments


Sample table: employees


Sample Output:

department_name	name_of_manager
Executive	Steven King
IT		Alexander Hunold
Finance		Nancy Greenberg
Purchasing	Den Raphaely
.....

Click me to see the solution

19. From the following table, write a SQL query to calculate the average salary of employees for each job title.

Sample table: employees


Sample table: jobs


Sample Output:

job_title			avg
Marketing Manager		13000.0000000000000000
Marketing Representative	6000.0000000000000000
Finance Manager			12000.0000000000000000
Shipping Clerk			3215.0000000000000000
.....

Click me to see the solution

20. From the following table, write a SQL query to find the employees who earn $12000 or more. Return employee ID, starting date, end date, job ID and department ID.

Sample table: employees

Sample table: job_history


Sample Output:

employee_id	start_date	end_date	job_id	department_id
101		1997-09-21	2001-10-27	AC_ACCOUNT	110
101		2001-10-28	2005-03-15	AC_MGR		110
102		2001-01-13	2006-07-24	IT_PROG		60
201		2004-02-17	2007-12-19	MK_REP		20

Click me to see the solution

21. From the following tables, write a SQL query to find out which departments have at least two employees. Group the result set on country name and city. Return country name, city, and number.

Sample table: countries


Sample table: locations


Sample table: employees


Sample table: departments


Sample Output:

country_name			city			count
United States of America 	South San Francisco	1
Canada				Toronto			1
United States of America	Seattle			4
United States of America	Southlake		1

Click me to see the solution

22. From the following tables, write a SQL query to find the department name, full name (first and last name) of the manager and their city.

Sample table: employees


Sample table: departments


Sample table: locations


Sample Output:

department_name		name_of_manager		city
Executive		Steven King		Seattle
IT			Alexander Hunold	Southlake
Finance			Nancy Greenberg		Seattle
Purchasing		Den Raphaely		Seattle

Click me to see the solution

23. From the following tables, write a SQL query to calculate the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.

Sample table: jobs


Sample table: job_history


Sample Output:

employee_id		job_title		days
176			Sales Manager		364
176			Sales Representative 	282

Click me to see the solution

24. From the following tables, write a SQL query to find full name (first and last name), and salary of all employees working in any department in the city of London.

Sample table: departments


Sample table: locations


Sample table: employees


Sample Output:

employee_name	salary
Susan Mavris	6500.00

Click me to see the solution

25. From the following tables, write a SQL query to find full name (first and last name), job title, start and end date of last jobs of employees who did not receive commissions.

Sample table: jobs


Sample table: job_history


Sample table: employees


Sample Output:

employee_name		job_title			starting_date	ending_date	employee_id
Neena Kochhar		Administration Vice President	2001-10-28	2005-03-15	101
Lex De Haan		Administration Vice President	2001-01-13	2006-07-24	102
Den Raphaely		Purchasing Manager		2006-03-24	2007-12-31	114
Payam Kaufling		Stock Manager			2007-01-01	2007-12-31	122
Jennifer Whalen		Administration Assistant	2002-07-01	2006-12-31	200
Michael Hartstein	Marketing Manager		2004-02-17	2007-12-19	201

Click me to see the solution

26. From the following tables, write a SQL query to find the department name, department ID, and number of employees in each department.

Sample table: departments


Sample table: employees


Sample Output:

department_name		no_of_employees	department_id
Administration		1		10
Marketing		2		20
Purchasing		6		30
Human Resources		1		40
.....

Click me to see the solution

27. From the following tables, write a SQL query to find out the full name (first and last name) of the employee with an ID and the name of the country where he/she is currently employed.

Sample table: countries


Sample table: locations


Sample table: employees


Sample table: departments


Sample Output:

employee_name    |employee_id|country_name            |
-----------------|-----------|------------------------|
Steven King      |        100|United States of America|
Neena Kochhar    |        101|United States of America|
Lex De Haan      |        102|United States of America|
Alexander Hunold |        103|United States of America|
Bruce Ernst      |        104|United States of America|
..........

Click me to see the solution

More to Come !

Contribute your code and comments through Disqus.

Keep Learning: SQL Joins, SQL Left Join, SQL Right Join, SQL Equi Join, SQL Non Equi Join, SQL Inner Join, SQL Natural Join, SQL Cross Join, SQL Outer Join, SQL Full Outer Join, SQL Self Join.

Practice Online


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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/joins-hr/index.php