SQL Exercise: Employees who earn a salary over 12000

SQL JOINS on HR Database: Exercise-20 with Solution

20. From the following table, write a SQL query to find the employees who earn $12000 or more. Return employee ID, starting date, end date, job ID and department ID.

Sample table: employees

Sample table: job_history

Sample Solution:

-- Selecting all columns (a.*) from the 'job_history' table, aliased as 'a'
SELECT a.*

-- Performing an INNER JOIN between the 'job_history' table (aliased as 'a') and the 'employees' table (aliased as 'm') based on the condition that 'a.employee_id' is equal to 'm.employee_id'
FROM job_history a 

JOIN employees m 
  ON (a.employee_id = m.employee_id)

-- Filtering rows based on the condition that 'salary' is greater than or equal to 12000
WHERE salary >= 12000;

Sample Output:

employee_id	start_date	end_date	job_id	department_id
101		1997-09-21	2001-10-27	AC_ACCOUNT	110
101		2001-10-28	2005-03-15	AC_MGR		110
102		2001-01-13	2006-07-24	IT_PROG		60
201		2004-02-17	2007-12-19	MK_REP		20

Code Explanation:

The said query in SQL that retrieves all columns from the job_history table for employees who currently earn a salary greater than or equal to 12000, and who have a corresponding record in the employees table.
The JOIN clause joins the job_history table with the employees table on their common column(s). In this case, it joins on the employee_id column in both tables.
The WHERE clause filters the result set to only include rows where the salary of the employee is greater than or equal to 12000.

Visual Presentation:

Alternative Solutions:

Using INNER JOIN with Explicit Column Names and WHERE Clause:

SELECT job_history.*
FROM job_history
JOIN employees ON job_history.employee_id = employees.employee_id
WHERE employees.salary >= 12000;

Explanation:

This query uses an INNER JOIN and explicitly specifies column names to combine the job_history and employees tables based on matching employee_id. It then applies a WHERE clause to filter the results to include only records where the salary is greater than or equal to 12000.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display the job title and average salary of employees.
Next SQL Exercise: Departments where at least 2 employees are working.