Basics - create a matrix query to display the job, the salary for that job based on department number and the

GeekInterview.com > Interview Questions > Oracle > Basics

Go To First | Previous Question | Next Question

Basics | Question 28 of 49

create a matrix query to display the job, the salary for that job based on department number and the total salary for that job, for departments 20,50,80, and 90,giving each column and appropriate heading.

Total Answers and Comments: 9

Last Update: July 23, 2009 Asked by: zecar

Sponsored Links

Best Rated Answer

—

Submitted by: Pallavi50953

select * from (select job,sum(decode(deptno,20,sal)) dept20,
                           sum(decode(deptno,50,sal)) dept50,
                           sum(decode(deptno,80,sal)) dept80,
                           sum(decode(deptno,90,sal)) dept90,
                           sum(sal) total_sal
        from emp group by job) order by 1

Above answer was rated as good by the following members:
Annapoorna Meyyappan

September 19, 2006 00:48:15

atit shah

RE: create a matrix query to display the job, the sala...

hello sir

Question: create a matrix query to display the job the salary for that job based on department number and the total salary for that job for departments 20 50 80 and 90 giving each column and appropriate heading.

plz give me ans.

thank you

Is this answer useful? Yes | No

September 24, 2006 11:25:21

vssandilya

RE: create a matrix query to display the job, the sala...

dear friend

try this code.

select department_id job_id sum(salary) from employees where

department_id in(20 50 80 9) group by rollup(department_id job_id)

then reply me.

sandilya

Is this answer useful? Yes | No

October 03, 2006 04:08:34

adarsh_sp

Member Since: October 2006 Contribution: 13

RE: create a matrix query to display the job, the sala...

select department job sum(salary) from emp where

department in(20 50 80 9) group by rollup(department job)

Is this answer useful? Yes | No

July 06, 2007 01:21:20

Vijayalakshmi

RE: create a matrix query to display the job, the sala...

SELECT DISTINCT job
SUM(CASE deptno WHEN 20 THEN sal END) "Dept 20"
SUM(CASE deptno WHEN 50 THEN sal END) "Dept 50"
SUM(CASE deptno WHEN 80 THEN sal END) "Dept 80"
SUM(sal) "Total"
FROM emp
GROUP BY job;

Is this answer useful? Yes | No

October 10, 2007 10:42:44

geethika

RE: create a matrix query to display the job, the sala...

select distinct job
sum(decode(deptno 20 sal) ) "20"
sum(decode(deptno 50 sal) )"50"
sum(decode(deptno 80 sal) ) "80"
sum(decode(deptno 90 sal) )"90"
sum(sal) "total"
from emp
group by job;

Is this answer useful? Yes | No

December 13, 2007 07:33:20

Pallavi50953

Member Since: December 2007 Contribution: 3

RE: create a matrix query to display the job, the salary for that job based on department number and the total salary for that job, for departments 20,50,80, and 90,giving each column and appropriate heading.

select * from (select job sum(decode(deptno 20 sal)) dept20
sum(decode(deptno 50 sal)) dept50
sum(decode(deptno 80 sal)) dept80
sum(decode(deptno 90 sal)) dept90
sum(sal) total_sal
from emp group by job) order by 1

Is this answer useful? Yes | No

1 0

Overall Rating: +1

January 27, 2008 17:19:41

madhukar.charla

Member Since: January 2008 Contribution: 1

how to access an exel sheet in to win runner

the date is given in the exceel sheet how do we import in to winrunner plz answer my question

Is this answer useful? Yes | No

August 08, 2008 05:41:07

shyamvarmatra

Member Since: August 2008 Contribution: 1

select job_id salary department_id avg(salary) "Average Salary"
from employees
where department_id in(20 80 90 50)
group by job_id salary department_id
order by job_id

Is this answer useful? Yes | No

July 23, 2009 11:54:07

pchandna

Member Since: April 2009 Contribution: 3

select job deptno salary

sum(salary) over( partition by deptno job) dep_sum_sal

from emp

Is this answer useful? Yes | No

Go To Top

User Name		Remember Me?
Password

Question Bank