SQL Exercise: Employees worked without a commission percentage

SQL JOINS on HR Database: Exercise-25 with Solution

25. From the following tables, write a SQL query to find full name (first and last name), job title, start and end date of last jobs of employees who did not receive commissions.

Sample table: jobs

Sample table: job_history

Sample table: employees

Sample Solution:

-- Selecting specific columns (CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, j.job_title, h.*) from the 'employees' table, aliased as 'e', and performing joins with other tables
SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name,
       j.job_title,
       h.*

-- Joining the 'employees' table (aliased as 'e') with a subquery (aliased as 'h') that calculates the MAX(start_date), MAX(end_date), and employee_id grouped by employee_id from the 'job_history' table
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

-- Joining the result set with the 'jobs' table (aliased as 'j') based on the common column 'job_id'
JOIN jobs j ON j.job_id = e.job_id

-- Filtering rows based on the condition that 'commission_pct' is equal to 0
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

Code Explanation:

The said query in SQL which will return a list of employee names, job titles, and job history details for employees who do not have a commission percentage, based on data from the employees, jobs, and job_history tables.
The JOIN clause joins the employees table with a subquery that selects the latest job history record for each employee.
The subquery groups the job history records by employee ID using the GROUP BY clause. It ensures that only the latest job history records are returned for each employee.
After joining the result of the subquery with the employees table, the query joins the jobs table with the job ID.
The WHERE clause filters the results to only include employees who do not have a commission percentage.

Alternative Solutions:

Using Subquery with MAX() in WHERE Clause:

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

Explanation:

In this query, the subquery finds the maximum start date, maximum end date, and employee ID from the 'job_history' table. The main query then joins the relevant tables and uses this subquery in the WHERE clause to filter the results.

Using Subquery with EXISTS Clause:

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

Explanation:

This query uses an EXISTS subquery to check if there exists another record in job_history for the same employee with the same start and end dates. If such a record exists, it includes the current record in the result set.

Using Self-Join and Subquery:

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

Explanation:

This query performs a self-join on the job_history table to find the records with maximum start and end dates for each employee. It then joins the relevant tables and filters the results to include only records with a commission percentage of 0.

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 any department located in London.
Next SQL Exercise: Number of employees in each of the department.