w3resource
MySQL exercises

MySQL JOINS - Exercises, Practice, Solution

MySQL Joins [13 exercises with solution]

1. Write a query to find the addresses (location_id, street_address, city, state_province, country_name) of all the departments. Go to the editor
Hint : Use NATURAL JOIN.

Sample table: locations


Sample table: countries


Click me to see the solution

2. Write a query to find the name (first_name, last name), department ID and name of all the employees. Go to the editor

Sample table: employees


Sample table: departments


Click me to see the solution

3. Write a query to find the name (first_name, last_name), job, department ID and name of the employees who works in London. Go to the editor

Sample table: departments


Sample table: locations


Click me to see the solution

4. Write a query to find the employee id, name (last_name) along with their manager_id and name (last_name). Go to the editor

Sample table: employees


Click me to see the solution

5. Write a query to find the name (first_name, last_name) and hire date of the employees who was hired after 'Jones'. Go to the editor

Sample table: employees


Click me to see the solution

6. Write a query to get the department name and number of employees in the department. Go to the editor

Sample table: employees


Sample table: departments


Click me to see the solution

7. Write a query to find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90. Go to the editor

Sample table: employees


Click me to see the solution

8. Write a query to display the department ID and name and first name of manager. Go to the editor

Sample table: employees


Sample table: departments


Click me to see the solution

9. Write a query to display the department name, manager name, and city. Go to the editor

Sample table: employees


Sample table: departments


Sample table: locations


Click me to see the solution

10. Write a query to display the job title and average salary of employees. Go to the editor

Sample table: employees


Click me to see the solution

11. Write a query to display job title, employee name, and the difference between salary of the employee and minimum salary for the job. Go to the editor

Sample table: employees


Click me to see the solution

12. Write a query to display the job history that were done by any employee who is currently drawing more than 10000 of salary. Go to the editor

Sample table: employees


Sample table: Job_history


Click me to see the solution

13. Write a query to display department name, name (first_name, last_name), hire date, salary of the manager for all managers whose experience is more than 15 years. Go to the editor

Sample table: employees


Sample table: departments


Click me to see the solution

Structure of 'hr' database:

hr database

More.....

MySQL Code Editor:

What is the difficulty level of this exercise?

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.