最新 SQL综合实验一（下附完整脚本）(6)

order by 学号 desc

3.05

select 学号,姓名 from xxx_student

where 学号 like '1998%'and 姓名 like'李%' go

3.06

select 学号,课程代号,平时*0.1+期中*0.2+期末*0.7 as 总评 from xxx_grade

3.07

declare @w int

exec 各学分课程数 '2',@w output print @w

3.08

select 课程数=count(*),学时总数=36*sum(cast(周学时 as float)), 学分总数=sum(cast(学分 as float)) from xxx_grade,xxx_course

where xxx_grade.课程代号=xxx_course.课程代号

3.09

select 学号 from xxx_student

where 学号 in (select 学号 from xxx_course group by 学号 having count(课程代号)>=3) go

3.10

create view view1 as

select xxx_student.姓名 as 姓名,

count(xxx_grade.课程代号) as 选修课程门数,sum(xxx_course. cast(周学时 as float)) as 总周学时,

sum(xxx_course.cast(学分 as float))as总学分 , sum((xxx_grade.cast(平时as float)*0.1+xxx_grade.

期中*0.2+xxx_grade.期末*0.6)*xxx_course.学分)/sum(xxx_course.学分) as 加权平均分 from xxx_student

inner join xxx_grade on xxx_student.学号=xxx_grade.学号