MySQL Joins Exercises: Find the name and hire date of the employees who was hired after 'Jones'
MySQL Joins: Exercise-5 with Solution
Write a MySQL query to find the name (first_name, last_name) and hire date of the employees who was hired after 'Jones'.
Sample table: employees
Code:
-- This SQL query selects specific columns from the 'employees' table to retrieve information about employees hired after an employee with the last name 'Jones'.
SELECT
e.first_name, -- Selecting the 'first_name' column from the 'employees' table.
e.last_name, -- Selecting the 'last_name' column from the 'employees' table.
e.hire_date -- Selecting the 'hire_date' column from the 'employees' table.
FROM
employees e -- Specifying the 'employees' table and aliasing it as 'e'.
JOIN
employees davies -- Joining the 'employees' table again and aliasing it as 'davies'.
ON
(davies.last_name = 'Jones') -- Performing a join based on the condition where the last name in 'davies' is 'Jones'.
WHERE
davies.hire_date < e.hire_date; -- Filtering the result to include only employees hired after the employee with the last name 'Jones'.
Explanation:
- This SQL query retrieves specific columns ('first_name', 'last_name', 'hire_date') from the 'employees' table.
- The 'employees' table is referenced twice in the query, once as 'e' for the general employees and once as 'davies'.
- It performs a join based on the condition where the last name in the 'davies' table is 'Jones'.
- The WHERE clause filters the result to include only employees hired after the employee with the last name 'Jones', comparing their hire dates.
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
first_name last_name hire_date Alana Walsh 1987-09-21T04:00:00.000Z Kevin Feeney 1987-09-22T04:00:00.000Z Donald OConnell 1987-09-23T04:00:00.000Z Douglas Grant 1987-09-24T04:00:00.000Z Jennifer Whalen 1987-09-25T04:00:00.000Z Michael Hartstein 1987-09-26T04:00:00.000Z Pat Fay 1987-09-27T04:00:00.000Z Susan Mavris 1987-09-28T04:00:00.000Z Hermann Baer 1987-09-29T04:00:00.000Z Shelley Higgins 1987-09-30T04:00:00.000Z William Gietz 1987-10-01T04:00:00.000Z
MySQL Code Editor:
Structure of 'hr' database:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous:Write a MySQL query to find the employee id, name (last_name) along with their manager_id and name (last_name).
Next:Write a MySQL query to get the department name and number of employees in the department.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics