SQL Exercise: Jobs which started between two given dates

SQL JOINS on HR Database: Exercise-13 with Solution

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: jobs

Sample table: departments

Sample table: employees

Sample Solution:

-- Selecting specific columns (job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date) from the 'job_history' table
SELECT job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date 

-- Performing an INNER JOIN between the 'job_history' table and the 'jobs' table using the common column 'job_id'
FROM job_history 

JOIN jobs USING (job_id) 

-- Performing another INNER JOIN between the result set and the 'departments' table using the common column 'department_id'
JOIN departments USING (department_id) 

-- Performing another INNER JOIN between the result set and the 'employees' table using the common column 'employee_id'
JOIN employees USING (employee_id) 

-- Filtering rows based on the conditions that the 'start_date' is between '1993-01-01' and '1997-08-31'
WHERE start_date >= '1993-01-01' AND start_date <= '1997-08-31';

Sample Output:

job_title		department_name		employee_name	start_date
Administration Assistant  Executive	 Jennifer Whalen	1995-09-17

Code Explanation:

The said query in SQL that selects the job title, department name, employee name, and start date for all employees who started working in the company between January 1, 1993, and August 31, 1997, inclusive. It joins the 'job_history', 'jobs', 'departments', and 'employees' tables using their respective primary and foreign keys.

Visual Presentation:

Alternative Solutions:

Using INNER JOINs:

SELECT job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date 
FROM job_history 
JOIN jobs ON job_history.job_id = jobs.job_id
JOIN departments ON job_history.department_id = departments.department_id
JOIN employees ON job_history.employee_id = employees.employee_id
WHERE start_date >= '1993-01-01' AND start_date <= '1997-08-31';

Explanation:

This query uses INNER JOINs to connect the 'job_history', 'jobs', 'departments', and 'employees' tables based on their respective IDs. It selects the job title, department name, employee name (concatenated from first and last names), and start date for records within the specified date range.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees who work in the same department as Taylor.
Next SQL Exercise: Salary difference between jobs maximum and employees.