I want to display the employees who have joined in last two
months. (It should be executed randomly means If I execute
the query in March it should display Jan and Feb joined
employees. Same query if i execute in Feb, 2007 it should
display dec, 2006 and jan 2007 joined employees.




Answers were Sorted based on User's Feedback



I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / sdp

select * from emp
where trunc(months_between(sysdate, hiredate))=2;

Is This Answer Correct ?    34 Yes 10 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / suneel

select * from af where months_between(sysdate,hiredate)<=2

Is This Answer Correct ?    15 Yes 2 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / raji_4u

select *
from emp
where hiredate
between add_months(trunc(sysdate,'Month'),-2)
and trunc(sysdate,'Month')-1

This works for sure. 100 %

Is This Answer Correct ?    13 Yes 1 No




I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / avi007

select * from employee where (sysdate-hiredate) <=60

Is This Answer Correct ?    6 Yes 2 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / venkat

select *from emp where floor(months_between
(sysdate,hiredate))=2;

Is This Answer Correct ?    6 Yes 5 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / arijit

take Mon(like sep,oct,mar etc)
and year( like 2006,2007,1980 etc) as input...
and execute this query..

select empno,ename,hiredate from emp1
where hiredate between
add_months(to_date('01-'|| upper('&Mon')||'-'||&yr,'DD-MON-
YYYY'),-2) and last_day(add_months(to_date('01-'|| upper
('&Mon')||'-'||&yr,'DD-MON-YYYY'),-1))

Is This Answer Correct ?    2 Yes 1 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / kesava

100% sure execute

select * from emp
where hiredate between
trunc(add_months(last_day(sysdate),-3)+1) and
trunc(add_months(last_day(sysdate),-1))

Is This Answer Correct ?    1 Yes 0 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / balaji s.t

select convert(varchar(10),
dateadd(dd,-(day(dateadd(mm,1,getdate()))-1), dateadd(mm,-2,getdate())), 120),

convert(varchar(10),dateadd(dd,-(day(getdate())),getdate()),120)

Is This Answer Correct ?    1 Yes 1 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / satish

select * from emp where
round(months_between(sysdate,hire_date) in(1,2);

Is This Answer Correct ?    0 Yes 1 No

I want to display the employees who have joined in last two months. (It should be executed randomly..

Answer / pracheth

select * from temployees where to_char(hiredate,
'month') in
(to_char(trunc(sysdate,'month')-30,'month'),to_char(trunc(sysdate,'month')-60,'month'))

Is This Answer Correct ?    0 Yes 1 No

Post New Answer



More SQL PLSQL Interview Questions

What is difference between CHAR and VARCHAR2?What is the maximum SIZE allowed for each type?

6 Answers   Saama Tech,


what is global table

7 Answers   iFlex,


what is the order of pre-defined exceptions.

6 Answers   TCS,


What is mutating trigger?How to avoid it??

3 Answers   Tech Mahindra,


select top 3 sal from each dept?

17 Answers   TCS, IBM,






I am creating an index on Emp table Empno column,if u using this indexed column in ur SELECT stmt. where clause,then how do u know that yr index will be working or nor? Thanks Advance...

3 Answers  


I have following column in the table. col1 1 a b c 2 3 d and I want to display it as num chars 1 a 2 b 3 c 4 d numbers in one column and letters in another column.

1 Answers   IBM, Saama Tech,


How can we schedule the procedure to run automatically ?

3 Answers  


I have 2 table A and B. In A 1 lakh record is present. In b 20 thousand data is present. To get the unique data from table A and B which join we should prefer left inner join or right outer join. Please answer.

4 Answers   GE,


can i use global variables in stored procedure or function

1 Answers   L&T,


How to improve the performance of a pl/sq stored procedures or functions or triggers and packages ?

11 Answers   TCS, IBM, UHD,


Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers  






Categories