SQL 学业预警案例

学业预警触发器案例

计算机学院为了能及时提醒每一个学生的课程修学情况，研究制定了学生学业预警制度，对所修课程不及格的学生进行学业预警，并采取相应措施。预警级别如下：

黄色：m≥q/6；红色：n≥s/4；留级：n≥s/2；退学：n≥3s/4

其中q、m为上学期应修课程数与不及格课程数；s、n为上学年应修课程数与不及格课程数。

假设学生-课程数据库模式如下：

学生信息表(学号,姓名, 性别,年龄,专业)--student ( sno, sname, ssex,sage,sdept)

课程表(课号, 课程名称, 先修课号, 课程学分)--course(cno, cname, cpnoccredit)

选课成绩表(学号, 选修课号, 考试成绩)--sc(sno, cno, grade)

学生信息表(student):

------------------------------------------------------------------------------------------

学号姓名性别年龄专业

------------------------------------------------------------------------------------------

2007203803 张书才男21 计算机科学与技术

2007202629 宋艳霞女19 计算机科学与技术

2007202761 侯凤英女20 信息管理与信息系统

2007202714 赵德良男18 信息管理与信息系统

2007203632 马新成男22 软件工程

2007203625 刘永生男19 软件工程

2007203888 李晓琳女20 电子商务

课程表(course):

----------------------------------------------------------------------------------

课号课程名称先修课号课程学分

-----------------------------------------------------------------------------------

509308 数据库原理509309 4

409301 高等数学 6

509307 C语言程序设计409304 4

409304 计算机导论 4

509309 数据结构509307 4

509305 离散数学409301 5

选课成绩表(sc):

-----------------------------------------------------

学号课号考试成绩

-----------------------------------------------------

2007203803 509308 89

2007203803 409301 76

2007203803 509307 83

2007203803 409304 79

2007203803 509309 85

2007203803 509305 82

2007202629 509308 78 2007202629 409301 56.8 2007202629 509307 69.5 2007202629 409304 83 2007202629 509309 76 2007202629 509305 54

2007202761 509308 86.4 2007202761 409301 85 2007202761 509307 97 2007202761 409304 93 2007202761 509309 88 2007202761 509305 82

2007202714 509308 76 2007202714 409301 45 2007202714 509307 68 2007202714 409304 71 2007202714 509309 54 2007202714 509305 38

2007203632 509308 58.6 2007203632 409301 67 2007203632 509307 73 2007203632 409304 74 2007203632 509309 68.4 2007203632 509305 52.6

2007203625 509308 55.3 2007203625 409301 43.6 2007203625 509307 60 2007203625 409304 68 2007203625 509309 57 2007203625 509305 50.3

2007203888 509308 89.3 2007203888 409301 86 2007203888 509307 93 2007203888 409304 96 2007203888 509309 87 2007203888 509305 81.8

请按要求完成以下任务：

1、根据学生成绩单产生学业预警信息表warn(sno, sname, fail_num, w_level, w_date) ，其中fail_num记录成绩不及格课程数，w_level记录学业预警级别，w_date为产生预警的时间。

提示：(1)首先使用select语句自动产生表warn(sno,sname,fail_num)，然后向表warn中添加列w_level char(10)，w_datedatetime。

(2)同学们上机操作时表后应加上自己的后3位学号！

2、定义一个学年预警触发器(swarn_ins_update)，当学生成绩发生变化时自动修改学生的预警信息。

select *from student4072

select *from course4072

select *from sc4072

select *from sc_fail

select *from warn4072

--建表

create table student4072

( sno char(10) primary key,

sname char(10) not null,

ssex char(2) default '男',

sagesmallint,

sdept char(20),

check (sage>10)

);

create table course4072

( cno char(6) primary key,

cname char(15) not null,

cpno char(6),

ccreditsmallint,

foreign key (cpno) references course4072(cno)

);

create table sc4072

( sno char(10) not null,

cno char(6) not null,

grade numeric(5,1),

primary key (sno,cno),

foreign key (sno) references student4072(sno),

foreign key (cno) references course4072(cno),

check (grade>=0)

);

--数据输入