w3resource

SQL Exercise: Employees who are SALESMAN and have an experience

SQL employee Database: Exercise-35 with Solution

[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:

SQL exercises on employee Database: List the employees who are SALESMAN and gathered an experience which month portion is more than 10

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

employee database structure

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

MySQL select 10 random rows from 600K rows fast:

SELECT name
  FROM random AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

Ref: https://bit.ly/3GdCTM3

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook