SQL Exercise: Employees of department id 3001 or 1001 joined in 1991

SQL employee Database: Exercise-36 with Solution

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

36. From the following table, write a SQL query to find those employees of department id 3001 or 1001 and joined in the year 1991. Return complete information about the employees.

Sample table: employees

Pictorial Presentation:

Sample Solution:

SELECT *
FROM employees
WHERE to_char(hire_date,'YYYY') = '1991'
  AND (dep_id =3001
       OR dep_id =1001) ;

OR

SELECT *
FROM employees
WHERE to_char (hire_date,'YYYY') IN ('1991')
  AND (dep_id = 3001
       OR dep_id =1001) ;

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
  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
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001
(8 rows)

Explanation:

The given 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 'YYYY', is equal to '1991' and the dep_id value is either '3001' or '1001'.

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

The WHERE clause includes those rows based on the values of the condition with the check that the year of the hire_date value is equal to '1991' and another condition with the check that is based on whether the dep_id value is '3001' or '1001'.

Practice Online

Sample Database: employee

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

Previous SQL Exercise: Employees who are SALESMAN and have an experience.
Next SQL Exercise: List the Employees who work for department 1001 or 2001.