SQL Exercise: Employees joined in company before 19th of the month

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

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

34. From the following table, write a SQL query to find out which employees joined the company before the 19th of the month. Return complete information about the employees.

Sample table: employees

Pictorial Presentation:

SQL exercises on employee Database: List the employees those who joined in company before 19th of the month

Sample Solution:

SELECT *
FROM employees
WHERE to_char(hire_date,'DD') < '19';

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id
--------+----------+-----------+------------+------------+---------+------------+--------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
(8 rows)

Explanation:

The said query in SQL that selects all columns from the table 'employees' where the hire_date value, after being converted to a character string with the format mask 'DD', is less than '19'.

The to_char function converts the "hire_date" column to a character string with the format mask 'DD', which means that only the day of the month will be displayed.

The WHERE clause includes only those rows for the day of the hire_date value is less than the 19th day of the month.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: List the employees who joined in the month of APRIL.
Next SQL Exercise: Employees who are SALESMAN and have an experience.