Topic: help! SQL assignment due tomorrow... @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / IT技术讨论 / help! SQL assignment due tomorrow... -ely(Ely); 2001-2-26 {814} (#28803@0)

好长时间不用了select * from table_name where salary > (select average(salary) from table_name) -guest:guest; 2001-2-26 {80} (#28808@0)

You mean WHERE EMPSALARY>(SELECT AVG(EMPSALARY) FROM EMP GROUP BY DEPTNAME)? wrong.... -ely(Ely); 2001-2-26 (#28809@0)
you have to compare the salary with the avg in the same dept. -ely(Ely); 2001-2-26 (#28810@0)

不好意思，没看到下半截这样行不行？
select name, salary, dept d from tb where salary > (select avg(salary) from tb where dept = d)
如果不行，可以用stored procedure. -guest:guest; 2001-2-26 {143} (#28819@0)

有错了这样呢？
select name, salary, dept d from tb where salary > (select avg(salary) from tb group by d) -guest:guest; 2001-2-26 {100} (#28822@0)
别傻了，不是这样的，不要用store procedure来干这种简单的工作. -tuantuan(团团); 2001-2-26 (#28834@0)

very simple select * from employee t1 where t1.empsalary > (select avg(empsalary) from employee t2 where t2.depname = t1.depname ) -tuantuan(团团); 2001-2-26 (#28820@0)

(select avg(empsalary) from employee t2 where t2.depname = t1.depname ) doesn't work at all, there is no t1 inside braket.. -ely(Ely); 2001-2-26 (#28823@0)

please try the whole comment, statement in () can inherit those alias like "t1"defined outside, I guess you are a new guy, and havn't got the sql sever here, but I do, and try the statement already. Trust me. -tuantuan(团团); 2001-2-26 {1063} (#28841@0)

ic, thanks!!! -ely(Ely); 2001-2-26 (#28843@0)
I remember that avg should have group ! -guest:guest; 2001-2-26 (#28850@0)
the whole question is for each department, get the average salary of emloyees who have higher salaries than the average salary of the departmentso the answer should be:

SELECT A.DEPTNAME, AVG(A.EMPSALARY)
FROM EMP A
WHERE A.EMPSALARY > (SELECT AVG(B.EMPSALARY)
FROM EMPLOYEE B
GROUP BY B.DEPTNAME
WHERE B.DEPTNAME = A.DEPTNAME )
GROUP BY A.DEPTNAME

IS IT RIGHT?
i HAVE NO SQL AT HOME... -ely(Ely); 2001-2-27 {257} (#28853@0)
顽固分子们，group by 只是对结果进行分类，只是显示顺序不一样，对结果没有影响的。 -tuantuan(团团); 2001-2-27 {681} (#28863@0)

看到你的帖子，真是有很多感慨！自从去年6月递表之后，开始从技术管理转向开发——苦！先把ASP捡了起来；七月MS出了.NET，ASPNG在WIN2000下的确不错，就是要用C#或VB7，一怒之下转向JAVA，在WINDOWS平台上的APPLICATION SERVER找不到感觉，现在回头去装solaris——这可是我四年前干的活呀。早知如此，何必当初呢。 -feather(feather); 2001-2-27 {210} (#28855@0)

Not specific to anybody.Just some comments on my own experience. -feather(feather); 2001-2-27 (#28857@0)

I finished three assignments(projects) for different courses today, didn't eat anything.. you say 苦?, will not be regret in the future..it's late, i 'm going to sleep now, take care. -ely(Ely); 2001-2-27 (#28859@0)

@Florida