SQL - Display the emploee records who joins the department before their manager?

GeekInterview.com > Interview Questions > Oracle > SQL

Go To First | Previous Question | Next Question

SQL | Question 141 of 171

Display the emploee records who joins the department before their manager?

Total Answers and Comments: 8

Last Update: September 12, 2008 Asked by: kowmudiswarna

Sponsored Links

Best Rated Answer

—

Submitted by: nandk.sharma

We should use less than operator instead of greater than , given in the previous response

select t.empno,t.hiredate,t.mgr,t1.empno,t1.hiredate
from emp t,emp t1
where t.mgr=t1.empno
and t.hiredate < t1.hiredate

Above answer was rated as good by the following members:
srikanth velichala

September 26, 2007 15:22:42

siva rama srinivas

RE: Display the emploee records who joins the departme...

ans)
select e.last_name m.last_name e.hire_date m.hire_date
from employees e employees m
where m.employee_id e.manager_id and e.hire_date<m.hire_date;

Is this answer useful? Yes | No

September 27, 2007 01:05:49

kowmudiswarna

Member Since: September 2007 Contribution: 21

RE: Display the emploee records who joins the departme...

Hi Rama Srinivas

Can u please explain the query imean what is manager_id is it seperate column?

Actually my table has columns like empno ename job sal comm hiredate mgr.

Suppose if the table is like that what is manager_id u was mentioned in the ans.

Can u please clarify my doubt as early as possible.

Thanks
Swarna.k

Is this answer useful? Yes | No

January 29, 2008 05:29:05

patilpravin_1981

Member Since: December 2007 Contribution: 29

RE: Display the emploee records who joins the department before their manager?

select * from emp
where hire_date<(select hire_date from emp where emp.emp_id emp.mgr_id);

Is this answer useful? Yes | No

May 25, 2008 16:15:29

M.REHMAN

Member Since: May 2008 Contribution: 3

RE: Display the emploee records who joins the department before their manager?

select e.employee_id e.last_name m.manager_id
m.last_name e.hire_date employee m.hire_date manager
from employees e employees m
where m.employee_id e.manager_id
and e.hire_date < m.hire_date

Is this answer useful? Yes | No

May 25, 2008 16:20:07

M.REHMAN

Member Since: May 2008 Contribution: 3

RE: Display the emploee records who joins the department before their manager?

select e.employee_id e.last_name m.manager_id m.last_name e.hire_date employee m.hire_date manager from employees e employees m where m.employee_id e.manager_id and e.hire_date < m.hire_date

Is this answer useful? Yes | No

July 01, 2008 20:14:43

chaitanya61

Member Since: July 2008 Contribution: 3

RE: Display the emploee records who joins the department before their manager?

Try this

select s.empno s.ename
from emp s (select a.empno a.hiredate from emp a
where a.empno in (select mgr from emp b
start with mgr is null connect by prior empno mgr)) m
where s.mgr m.empno
and s.hiredate < m.hiredate;

Is this answer useful? Yes | No

July 02, 2008 04:17:12

nandk.sharma

Member Since: June 2008 Contribution: 10

RE: Display the emploee records who joins the department before their manager?

We should use less than operator instead of greater than given in the previous response

select t.empno t.hiredate t.mgr t1.empno t1.hiredate
from emp t emp t1
where t.mgr t1.empno
and t.hiredate < t1.hiredate

Is this answer useful? Yes | No

1 1

Overall Rating: -N/A-

September 12, 2008 04:21:05

mahato.neelima

Member Since: September 2008 Contribution: 2

RE: Display the emploee records who joins the department before their manager?

This means that the employee does not have any manager.

Select e.empno e.ename From emp e emp m where m.empno(+) e.mgrid;

or

Select e.empno e.ename From emp e left outer join on emp m where m.empno e.mgrid;

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank