-2

Which MONTH (i.e. JAN or FEB, or JUN, etc…) in the hiring date of all employees across all departments can be found in all three departments in the employees table (dept 50, 60, 80)?

Hint: the month of JANUARY is a month where employees were hired into depts 50 and 60 and 80.

The data in the table maybe something like this

Hiredate                Department_id
29-JAN-97                   50
03-JAN-90                   60
29-JAN-00                   80

The output of the SQL you would write should produce something like the following: 

Month Name
------------------
January

I really don't know where to begin. Please help!

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Liana
  • 7
  • 1
  • 3

2 Answers2

0

To get the MONTH NAME from the hiredate column for a list of departments, use TO_CHAR with Month format.

Please read documentation to know more about Format models and datetime format elements.

MONTH -- Name of month, padded with blanks to length of 9 characters.

For example,

SELECT DISTINCT TO_CHAR(hiredate, 'Month') Month_name, department_id dept
   FROM employees
WHERE department_id IN (50, 60, 80);
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0
select mth "Month" 
  from (
    select distinct department_id, to_char(hire_date, 'Month') mth
      from employees
      where department_id in (50, 60, 80) ) 
  group by mth 
  having count(1)=3

SQLFiddle

Inner query selects distinct months in each department. Now you only need to count these months and show rows with count = 3. This is done with group by and having in outer query.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24