SQL server 第十一章-----连接查询和分组查询上机实践

SQL server 第十一章-----连接查询和分组查询上机实践

–查询每个年级的总学时数
create table WorkTime(
WorkID int not null,
WorkName nvarchar(50) not null,
WorkTime int not null
)
go
insert WorkTime
(WorkID,WorkName,WorkTime)
select ‘1’,‘java’,‘50’ union
select ‘1’,‘html’,‘40’ union
select ‘1’,‘c#’,‘60’ union
select ‘2’,‘VS’,‘50’ union
select ‘2’,‘CSS’,‘30’ union
select ‘2’,‘PS’,‘40’ union
select ‘3’,‘ph’,‘60’ union
select ‘3’,‘js’,‘50’
go
select WorkID,sum(WorkTime) as 课时
from WorkTime
group by WorkID
order by sum(WorkTime)
go

–查询每个参加考试的学员的平均分。
select StudentNo,avg(StudentResult) as 平均成绩
from Result
group by StudentNo
go

–查询每门课程的平均分，并按照降序排列。
select SubjectId,avg(StudentResult) as 平均成绩
from Result
group by SubjectId
order by avg(Stude