SQL Exercise: Employees who are SALESMAN and have an experience

Last update on December 20 2024 11:59:41 (UTC/GMT +8 hours)

[An editor is available at the bottom of the page to write and execute the scripts.]

35. From the following table, write a SQL query to identify those employees who have been working as a SALESMAN and month portion of the experience is more than 10. Return complete information about the employees.

Sample table: employees

Pictorial Presentation:

Sample Solution:

SELECT *
FROM employees
WHERE job_name = 'SALESMAN'
  AND EXTRACT(MONTH
              FROM age(CURRENT_DATE, hire_date)) > 10;

Sample Output:

 emp_id | emp_name | job_name | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+----------+------------+------------+---------+------------+--------
  64989 | ADELYN   | SALESMAN |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
(2 rows)

Explanation:

The given query in SQL that retrieves all columns from the table 'employees' where the job_name value is 'SALESMAN' and the number of months between the hire_date value and the current date is greater than 10 months.

The EXTRACT function extracts a number of months from an interval value. The age function calculates the difference between two dates as an interval value.

The WHERE clause filters the result set for the extract number of months between the current date and the hire_date value is greater than 10.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees joined in company before 19th of the month.
Next SQL Exercise: Employees of department id 3001 or 1001 joined in 1991.