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.]
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 .....
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 .....
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 .....
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 .....
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
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 .....
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
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 .....
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 .....
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 .....
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 .....
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 .....
13. From the following tables, write a SQL query to find those employees who joined between 1st January 1993 and 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
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 .....
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 .....
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 .....
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 .....
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 .....
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 .....
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
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
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
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
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
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
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 .....
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_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| ..........
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.
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