w3resource
SQL exercises

SQL JOINS on HR Database: Display full name, job title, starting and ending date of last jobs for those employees worked without a commission percentage

SQL JOINS on HR Database: Exercise-25 with Solution

25. Write a query in SQL to display full name(first and last name), job title, starting and ending date of last jobs for those employees with worked without a commission percentage.

Sample table: jobs


Sample table: job_history


Sample table: employees


Sample Solution:

SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name,
       j.job_title,
       h.*
FROM employees e
JOIN
  (SELECT MAX(start_date),
          MAX(end_date),
          employee_id
   FROM job_history
   GROUP BY employee_id) h ON e.employee_id=h.employee_id
JOIN jobs j ON j.job_id=e.job_id
WHERE e.commission_pct = 0;

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

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display full name, job title, starting and ending date of last jobs for those employees worked without a commission percentage - Duration

Rows:

Query visualization of Display full name, job title, starting and ending date of last jobs for those employees worked without a commission percentage - Rows

Cost:

Query visualization of Display full name, job title, starting and ending date of last jobs for those employees worked without a commission percentage - Cost

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

Previous: Write a query in SQL to display the full name (first and last name), and salary of those employees who working in any department located in London.
Next: Write a query in SQL to display the department name and number of employees in each of the department.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming