w3resource

SQL JOINS on HR Database: Display the job title, department name, full name, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997

SQL JOINS on HR Database: Exercise-13 with Solution

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

SELECT job_title, department_name, first_name || ' ' || last_name AS Employee_name, start_date 
	FROM job_history 
		JOIN jobs USING (job_id) 
			JOIN departments USING (department_id) 
				JOIN  employees USING (employee_id) 
					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

Pictorial Presentation:

SQL Exercises: Display the job title, department name, full name, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the job title, department name, full name, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997 - Duration

Rows:

Query visualization of Display the job title, department name, full name, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997 - Rows

Cost:

Query visualization of Display the job title, department name, full name, and starting date for all the jobs which started on or after 1st January, 1993 and ending with on or before 31 August, 1997 - Cost

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

Previous: 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.
Next: 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.

What is the difficulty level of this exercise?