SELECT dno,sal FROM EMP WHERE sal>ANY(SELECT AVG(sal)

i have a table
eno dno sal
1 10 200
2 10 150
3 10 100
4 20 75
5 20 100

i want to get sal which is less than the avg sal of thri dept.

eno dno sal
2 10 150
3 10 100
4 20 75

Question Posted / mohan

12 Answers
20331 Views
IBM, I also Faced
E-Mail Answers

Answer Posted / mohan

SELECT dno,sal
FROM EMP
WHERE sal>ANY(SELECT AVG(sal)
FROM emp
GROUP BY dno)
ORDER BY dno;

Is This Answer Correct ?

0 Yes

2 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

What is a common use of group by in sql?

1100

How to load data with sql*loader?

1112

Why is normalization important?

1084

How to create an array in pl/sql?

1125

What do you mean by field in sql?

1131

What is the difference between sql and t sql?

1110

Advantages and disadvantages of stored procedure?

1172

Write a query to find the names of users that begin with "um" in sql?

1046

What is constant in pl sql?

1065

What is the difference between the conventional and direct path loads? : aql loader

1479

What is trigger point?

1058

What is the purpose of design view?

1078

Explain the difference in execution of triggers and stored procedures?

1066

how to use case expression? : Sql dba

1079

how to decrement dates by 1 in mysql? : Sql dba

1077