w3resource

SQL Exercise: List the employees who joined in 90's

SQL employee Database: Exercise-48 with Solution

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

48. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees.

Sample table: employees


Pictorial Presentation:

SQL exercises on employee Database: List the employees who joined in 90's

Sample Solution:

SELECT *
FROM employees
WHERE to_char(hire_date,'YY') BETWEEN '90' AND '99';

OR

SELECT *
FROM employees
WHERE to_char(hire_date,'YY') >= '90'
  AND to_char(hire_date,'YY') < '99';

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
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001
(14 rows)

Explanation:

The said query in SQL that selects all the columns and rows from the 'employees' table where the year portion of the "hire_date" column falls between 1990 and 1999.

The "to_char" function converts the "hire_date" column to a string with a format of "YY", which can then be compared to the strings '90' and '99'.

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 who joined after the month of JUNE in 1991.
Next SQL Exercise: List the managers of department 1001 or 2001.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/employee-database-exercise/sql-employee-database-exercise-48.php