Basics - create a query that will display the total no.of employees and, of that total, the no.of employees h

GeekInterview.com > Interview Questions > Oracle > Basics

Go To First | Previous Question | Next Question

Basics | Question 29 of 49

create a query that will display the total no.of employees and, of that total, the no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings.

Total Answers and Comments: 11

Last Update: July 08, 2009 Asked by: adarsh_sp

Sponsored Links

Best Rated Answer

—

Submitted by: rampratap409

Dear Anil,

Your query is correct but it will give the out put in multiple line. To get the result in one line you have to follow above query.

Above answer was rated as good by the following members:
baridbej, subajay

Sorting Options

Page 1 of 2

« First

Last »

October 05, 2006 07:55:46

mamatakalbande

Member Since: October 2006 Contribution: 1

RE: create a query that will display the total no.of e...

select Count(*) as NumberOfEmployees

EmployeesHiredIn1995_1996_1997_1998

(select count(*) from employees

where hiredate like ' 1995 '

or hiredate like ' 1996 '

or hiredate like ' 1997 '

or hiredate like ' 1998 ')

from employees

Is this answer useful? Yes | No

October 10, 2006 08:34:20

adarsh_sp

Member Since: October 2006 Contribution: 13

RE: create a query that will display the total no.of e...

Thank You mamatakalbande

Is this answer useful? Yes | No

October 11, 2006 01:25:36

Sneha Atre

RE: create a query that will display the total no.of e...

select count(*)

from employees

where hiredate in(1995 1996 1997 1998);

Is this answer useful? Yes | No

1 0

Overall Rating: +1

October 19, 2006 05:20:30

Manjunath

RE: create a query that will display the total no.of e...

Check this......

select count(*) Count to_char(hiredate 'YYYY') YEAR from emp group by rollup( to_char(hiredate 'YYYY'))

Is this answer useful? Yes | No

October 19, 2006 11:39:47

Sachin

RE: create a query that will display the total no.of e...

Select

count(emp_code)

count(case when TO_NUMBER(to_CHAR(hire_date 'YYYY')) 1995 then (emp_code) end) as hire_1995

count(case when TO_NUMBER(to_CHAR(hire_date 'YYYY')) 1996 then (emp_code) end) as hire_1996

from employees

--similary the query will continue. remember that the fields are from one table and you are not doing any dimension querying. only the fact info is being queried

Is this answer useful? Yes | No

December 04, 2006 07:56:07

rampratap409

Member Since: September 2006 Contribution: 111

RE: create a query that will display the total no.of e...

select

count(decode(to_char(hiredate 'yyyy') '1995' hiredate null)) hired_in_1995
count(decode(to_char(hiredate 'yyyy') '1996' hiredate null)) hired_in_1996

count(decode(to_char(hiredate 'yyyy') '1997' hiredate null)) hired_in_1997

count(decode(to_char(hiredate 'yyyy') '1998' hiredate null)) hired_in_1998
count(*) total_emp
from emp;

Is this answer useful? Yes | No

December 10, 2006 06:56:46

Anil Patra

RE: create a query that will display the total no.of e...

Check this out.

select to_char(PADATEOFJOININGD 'YYYY') count(*)

from PAEMPLOYEE_M

group by (to_char(PADATEOFJOININGD 'YYYY'))

union

select 'total emplyee' count(*)

from PAEMPLOYEE_M

Is this answer useful? Yes | No

December 11, 2006 05:16:04

rampratap409

Member Since: September 2006 Contribution: 111

RE: create a query that will display the total no.of e...

Dear Anil

Your query is correct but it will give the out put in multiple line. To get the result in one line you have to follow above query.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

May 24, 2008 03:16:12

baridbej

Member Since: May 2008 Contribution: 1

RE: create a query that will display the total no.of employees and, of that total, the no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings.

SELECT COUNT(*) TOTAL_EMP
SUM(CASE WHEN INSTR(HIREDATE '81') > 0 THEN 1 ELSE 0 END)HIRED_81
SUM(CASE WHEN INSTR(HIREDATE '80') > 0 THEN 1 ELSE 0 END)HIRED_80
SUM(CASE WHEN INSTR(HIREDATE '87') > 0 THEN 1 ELSE 0 END)HIRED_87
SUM(CASE WHEN INSTR(HIREDATE '82') > 0 THEN 1 ELSE 0 END)HIRED_82
FROM EMP
/

This query is as per ORACLE 9I EMP table that I have in my home.

Is this answer useful? Yes | No

September 12, 2008 02:11:27

#10

lanka_satya

Member Since: January 2008 Contribution: 16

RE: create a query that will display the total no.of employees and, of that total, the no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings.

select count(*) Count to_char(hiredate 'YYYY') YEAR from emp group by to_char(hiredate 'YYYY') having substr(to_char(hiredate 'yyyy') -1) in(5 6 7 8)

Is this answer useful? Yes | No

Page 1 of 2

« First

Last »

Go To Top

User Name		Remember Me?
Password

Question Bank