Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
azimuth73
Starting Member
3 Posts |
 Posted - 2007-06-07 : 10:51:27
|
| /* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/The task is:Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.The table: emp Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY What i came up with:select empno, firstname, lastnamefrom empwhere avg(salary)>any( select avg(salary) from emp where sex='M' )group by sexhaving sex = 'F' |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-07 : 11:05:08
|
| Sounds like homework - but you've had a go and seem to wnat to learn rather than have someone give you the answer so:Your subquery to get the average male salary returns a single value so you don't need the "any".You want to compare the salary of the female employee not the avg salary (theres only one so the avg salary will be the same but take it out).You want a list of the female employees not a single value so get rid of the group by.the having clause whould be in the where claue (that's the case even if you needed the group by as you are filtering on the group field).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
azimuth73
Starting Member
3 Posts |
Posted - 2007-06-07 : 13:08:52
|
| you're a legend. thanks. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-09 : 06:51:41
|
| <<select empno, firstname, lastnamefrom empwhere avg(salary)>any( select avg(salary)from empwhere sex='M' )group by sexhaving sex = 'F'>>Did you try executing that?Also you need to learn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Noob needs help!