面试的两道SQL题

1没有做出来，下午回来后，慢慢弄出来了。跟大家分享一下，看有无更好的解决方法
表一test
name result type

 张三 95 语文
 张三 85 数学
 张三 87 英语
 李四 67 数学
 李四 85 英语
 王五 86 语文
 王五 78 数学

所有成绩都在80分以上的

SELECT *
FROM (SELECT name, COUNT(*) AS a
        FROM test
        WHERE (result > 80)
        GROUP BY name)
WHERE (a = 3)

表2 test2
id goodsid scount type

 1 1 10 进
 2 2 20 进
 3 3 20 出
 4 1 45 出
 5 2 30 进
 6 3 40 出

要得出
goodsid 进货 出库 库存
1 10 45 -35
2 50 0 50
3 0 60 -60
这样的结果
select isnull(a.goodsid,b.goodsid),isnull(a.sumjin,0),isnull(b.sumchu,0),isnull(a.sumjin,0)-

isnull(b.sumchu,0) as kucun from
(SELECT GoodsId, SUM(SCount) AS sumjin
FROM test2
WHERE (Type = '进')
GROUP BY GoodsId) as a full join
(SELECT GoodsId, SUM(SCount) AS sumchu
FROM test2
WHERE (Type = '出')
GROUP BY GoodsId) as b on a.goodsid=b.goodsid