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 .....
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 .....
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 .....
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 .....
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
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 .....
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
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 .....
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 .....
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 .....
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 .....
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 .....
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
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 .....
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 .....
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 .....
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 .....
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 .....
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 .....
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
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
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
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
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
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
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 .....
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| ..........
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics