w3resource

SQL Exercises, Practice, Solution - JOINS on HR Database

SQL JOINS [27 exercises with solution]

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

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

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.  Go to the editor

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.  Go to the editor

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.  Go to the editor

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 a 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 table, write a SQL query to find all departments including those without any employee. Return first name, last name, department ID, department name.  Go to the editor

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 those employees who earn less than the employee of ID 182. Return first name, last name and salary.  Go to the editor

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.  Go to the editor

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.  Go to the editor

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 those employees who have or not any department. Return first name, last name, department ID, department name.  Go to the editor

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. These managers do not work under any manager. Return the first name of the employee and manager.  Go to the editor

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 those employees who work in a department where the employee of last name 'Taylor' works. Return first name, last name and department ID.  Go to the editor

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 those employees who joined on 1st January 1993 and leave on or before 31 August 1997. Return job title, department name, employee name, and joining date of the job.  Go to the editor

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 find the difference between maximum salary of the job and salary of the employees. Return job title, employee name, and salary difference.  Go to the editor

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 compute the average salary, number of employees received commission in that department. Return department name, average salary and number of employees.  Go to the editor

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 compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.  Go to the editor

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.  Go to the editor

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.  Go to the editor

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 compute the average salary of employees for each job title.  Go to the editor

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 those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.  Go to the editor

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 those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments.  Go to the editor

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.  Go to the editor

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 compute the number of days worked by employees in a department of ID 80. Return employee ID, job title, number of days worked.  Go to the editor

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 those employees who work in any department located in 'London' city.  Go to the editor

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, starting and ending date of last jobs of employees who worked without a commission percentage.  Go to the editor

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.  Go to the editor

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 the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.  Go to the editor

Sample table: countries


Sample table: locations


Sample table: employees


Sample table: departments


Sample Output:

employee_id	job_title	days
176	Sales Manager	364
176	Sales Representative	282

Click me to see the solution

More to Come !

Contribute your code and comments through Disqus.

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.