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

inner join xxx_course on xxx_grade.课程代号=xxx_course.课程代号 group by xxx_student.姓名 go

select * from view1 order by 加权平均分 desc

3.11 select

min(平时*0.1+期中*0.2+期末*0.7) as 最低总评分, max(平时*0.1+期中*0.2+期末*0.7) as 最高总评分,

课程代号,count(课程代号) as 课程选修人数 from xxx_grade group by 课程代号

order by count (课程代号) desc

3.12

select 姓名,课程名,max (xxx_grade.平时*0.1+xxx_grade.期中*0.2+xxx_grade.期末*0.7) as 最高总评分

from xxx_grade inner join xxx_course on xxx_grade.课程代号=xxx_course.课程代号 inner join xxx_student on xxx_grade.学号=xxx_student.学号 group by 课程名,姓名 order by 课程名

3.13

create procedure 显示学生基本信息 @Name varchar(100) as

if (select count(*) from xxx_student where 姓名=@Name)>0 select *

from xxx_student where 姓名=@Name else

print '不存在此学生'

3.14

Where name=’my_srig’ and type=’tr’ Drop trigger my_srig Go

Create trigger my_strg

On xxx_student For delete As

Delete from xxx_grade

Where 学号in (select 学号 from deleted) Go