求助！SQLServer查询实现由四张表得到新表？最新问题求助！！

有四张表：Course、CourseType、Grade、student

 表Course：
 courseID    coursename    coursetypeID
 100001        智育1               001
 100002        智育2               002
 200001        德育1               001
 200002        德育2               002
 300001        体育1               001
 300002        体育2               002

 表：CourseType:
 coursetypeID       typename
 001                         必修课
 002                         选修课

 表Grade：
 studentID    courseID    grade
  S1                 100001      80
    S1                 100002      80
    S1                 200001      80
    S1                 200002      80
    S1                 300001      80
    S1                 300002      80
    S2                 100001      70
    S2                 100002      70
    S2                 200001      70
    S2                 200002      70
    S2                 300001      70
    S2                 300002      70

表student:
studentID   studentName
S1                姓名1
S2                姓名2

我如何得到表：

studentID     studentName   CountZhiYu    CouuntDeYu     CountTiYu    Count
S1                  姓名1                 70%*160        20%*160            10%*160     160
S2                  姓名2                 70%*140        20%*140             10%*140     140


 其中：1.     160=80+80,140=70+70.即两个智育成绩相加。德育、体育成绩同理。
           2.      70%*160要总后计算结果。20%*160  、10%*160同理
                     3.      Count字段是前面三个数字字段的累加。
                              例：160=70%*160+20%*160+10%*160
                     4.       S1、S2的Count总分按照从大到小排序。

恳请各位行家高手不吝惜指教！万分感谢！！

同样的道理，可以join，然后group by
解决了之前的问题后再写给你


SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count
from(
select studentID,studentName 
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1000%')*0.7 CountZhiYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '2000%')*0.2 CouuntDeYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '3000%')*0.1 CountTiYu
from student
) a

可以用inner join , outer join union 等等

select * from Course a union CourseType b union Grade c union Student d

谢谢大家的慷慨帮助！
我这样写有红色错误提示：

 SELECT A.*,(CountZhiYu+CouuntDeYu+CountTiYu) count
from(
select studentID,studentName 
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '10000%')*0.7 CountZhiYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '20000%')*0.2 CouuntDeYu
,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '30000%')*0.1 CountTiYu
from student
) a

 错误提示：
 消息 4104，级别 16，状态 1，第 5 行
无法绑定由多个部分组成的标识符 "A.studentID"。
消息 4104，级别 16，状态 1，第 6 行
无法绑定由多个部分组成的标识符 "A.studentID"。
消息 4104，级别 16，状态 1，第 7 行
无法绑定由多个部分组成的标识符 "A.studentID"。

我该怎么改啊？

还有一点需要优化：问什么有的count字段的数据是NULL？？但是前面三个不都是NULL啊？例如：第三个：学生何洁，CountZhiYu是56.0，那么他的count应该是56.0才对啊！？？
如下图所示：
图片说明

首先感谢大家的慷慨帮助！再者，我还有一个问题需要完善。

 我这样得出的结果是表student里所有同学的成绩，所以没录入成绩的学生也显示成绩为0。但是我想只得到表Grade里所有已录入成绩的学生的成绩。 
 代码如下：
 SELECT A.*,(isnull(CountZhiYu,0)+isnull(CountDeYu,0)+isnull(CountTiYu,0)) Count 
from( select studentID,studentName ,(select sum(grade) from Grade where studentID=A.studentID and courseID LIKE '1%')*0.7 CountZhiYu ,(select sum(grade) 
from Grade where studentID=A.studentID and courseID LIKE '2%')*0.2 CountDeYu ,(select sum(grade) 
from Grade where studentID=A.studentID and courseID LIKE '3%')*0.1 CountTiYu from student a ) a 
order by count DESC

我该如何对代码作改动呢？？恳请再次相助！！

可以join，然后group by