OraFAQ Forum: SQL & PL/SQL » query on emp table

Home » SQL & PL/SQL » SQL & PL/SQL » query on emp table (Oracle, 10g, windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query on emp table [message #334607]

Thu, 17 July 2008 06:18

jyothsna1612
Messages: 68
Registered: June 2008

Member

Hi
I need the HINT for the following query

Display the empno,ename, sal for all employees who earn more than the average sal and who work in a department with any employee

I tried the following:(In select clause the columns are different)

SELECT   SAL,
         COUNT(* )
FROM     EMP
WHERE    SAL >  ANY (SELECT   AVG(SAL)
                     FROM     EMP
                     GROUP BY DEPTNO)
GROUP BY DEPTNO,
         SAL;

I even tried with the > all but none gave correct result..
Please excuse me if this is a newbie question.

Thanks
Jyothsna

Report message to a moderator

Re: query on emp table [message #334611 is a reply to message #334607]

Thu, 17 July 2008 06:35

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Let's analyze the question.

"Display the empno,ename, sal", so the select list must contain these columns.

"who earn more than the average sal ", there it is not clear (given the last part) if the average is the global average or the average of salary in the employee department. Assuming it is the global average, how do you get it?

" who work in a department with any employee", if you select from emp then you can't have data from a department that has no employee but maybe I misunderstood the sentence.

Regards
Michel

Report message to a moderator

Re: query on emp table [message #334616 is a reply to message #334607]

Thu, 17 July 2008 06:43

jyothsna1612
Messages: 68
Registered: June 2008

Member

Hi
There would be a chance to misunderstand the query as this is not much clear for the first time.

Ex:
The avg(sal) for deptno 10 = 2916. If The employees who belong to only dept 10 earns more than 2916, then his details must be displayed..
Like the same approach for dept 20 and 30. also

I think now i am somewhat clear to this query..

Report message to a moderator

Re: query on emp table [message #334617 is a reply to message #334616]

Thu, 17 July 2008 06:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ok, now first build the query to get the average salary for each department, then query from emp the employee which salary is greater than this value.
You query almost does all what you want, you just have to select the correct field in the end.

Regards
Michel

Report message to a moderator

Re: query on emp table [message #334620 is a reply to message #334617]

Thu, 17 July 2008 07:17

jyothsna1612
Messages: 68
Registered: June 2008

Member

My doubt is:

SQL> SELECT   DEPTNO,
         ROUND(AVG(SAL))
FROM     EMP
GROUP BY DEPTNO;

    DEPTNO ROUND(AVG(SAL))
---------- ---------------
        30            1567
        20            2175
        10            2917

Here deptno10's avg(sal) is 2917
But check the o/p of the query i wrote:

SELECT   SAL,deptno,
         COUNT(* )
FROM     EMP
WHERE    SAL >  ANY (SELECT   AVG(SAL)
                     FROM     EMP
                     GROUP BY DEPTNO)
GROUP BY DEPTNO,
         SAL;

       SAL     DEPTNO   COUNT(*)
---------- ---------- ----------
      5000         10          1
      3000         20          2
      2450         10          1
      1600         30          1
      2850         30          1
      2975         20          1

In the above o/p: the salary 2450 for dept10 which is
less than the avg(sal) of deptno10 i.e.,2917 , and is the wrong o/p:

So HINT me to change my query:

Report message to a moderator

Re: query on emp table [message #334621 is a reply to message #334607]

Thu, 17 July 2008 07:22

ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD

Senior Member

This is happend because of Any clause in the sub query.First data will be filtered then the grouping will be taken place.

[Updated on: Thu, 17 July 2008 07:23]

Report message to a moderator

Re: query on emp table [message #334622 is a reply to message #334620]

Thu, 17 July 2008 07:23

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to make a relation between the deptno from outer emp and the deptno from subquery.

Regards
Michel

Report message to a moderator

Re: query on emp table [message #334624 is a reply to message #334621]

Thu, 17 July 2008 07:30

jyothsna1612
Messages: 68
Registered: June 2008

Member

Now i'm getting the required output.

SQL> select sal,deptno,count(*)
  2  from emp
  3  where sal>any(select avg(sal)
  4  from emp)
  5  group by deptno,sal;

       SAL     DEPTNO   COUNT(*)
---------- ---------- ----------
      5000         10          1
      2975         20          1  
      2450         10          1
      3000         20          2
      2850         30          1

Thanks for your support Smile

Report message to a moderator

Re: query on emp table [message #334626 is a reply to message #334624]

Thu, 17 July 2008 07:47

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Note that the query gives the employee with salary greater than the overall average and not the average of employee department.
It may or may not be correct depending on the actual requirement.

Regards
Michel

Report message to a moderator

Re: query on emp table [message #334646 is a reply to message #334607]

Thu, 17 July 2008 09:24

sivanagahari.devarapalli@
Messages: 1
Registered: May 2008

Junior Member

Hi,
First let me express my understanding about question:

===============================================================
Display emp details whose salary is greater than average salary of department in which he is working.
===============================================================

----------------------------------------------------------------
According to this first try to get each department's AVG salary:
----------------------------------------------------------------

SELECT deptno,ROUND(avg(sal)) avg_sal
FROM emp
GROUP BY deptno;

This query results:::

DEPTNO AVG_SAL
--------- ----------
30 1567
20 2175
10 2917

----------------------------------------------------------------
Now try to form final statement using the above statement as part of FROM clause "INLINE VIEW" :
----------------------------------------------------------------
SELECT empno,ename,sal,e.deptno
FROM emp e, (SELECT deptno,ROUND(avg(sal)) avg_sal
FROM emp
GROUP BY deptno) i
WHERE e.deptno=i.deptno AND e.sal >avg_sal;

EMPNO ENAME SAL DEPTNO
-------- ---------- ---------- ----------
7698 BLAKE 2850 30
7499 ALLEN 1600 30
7902 FORD 3000 20
7788 SCOTT 3000 20
7566 JONES 2975 20
7839 KING 5000 10

Excuse me for the format & confusion if any.

thanks & Regards,

Siva Naga Hari

Report message to a moderator

Re: query on emp table [message #334671 is a reply to message #334646]

Thu, 17 July 2008 11:06

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Excuse me for the format

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: query on emp table [message #334780 is a reply to message #334646]

Fri, 18 July 2008 00:19

jyothsna1612
Messages: 68
Registered: June 2008

Member

Hi,
Michel is correct, as my query is taking the avg of all departments not a particular department. Now my confusion is clear
and query written by SIVA is correct according to my requirement.

Thank you again.

Report message to a moderator

Re: query on emp table [message #334781 is a reply to message #334780]

Fri, 18 July 2008 00:32

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I encourage you to rewrite Siva's query the same way you wrote yours (using a subquery instead of an inline view), just as an exercise.

Regards
Michel

Report message to a moderator

Previous Topic:	Deferred Constraint Without Dropping Existing Constraint
Next Topic:	website for PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 19:14:12 CST 2024