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

预警级别如下：黄色：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 4409301 高等数学 6509307 C语言程序设计409304 4409304 计算机导论 4509309 数据结构509307 4509305 离散数学409301 5选课成绩表(sc):-----------------------------------------------------学号课号考试成绩-----------------------------------------------------2007203803 509308 892007203803 409301 762007203803 509307 832007203803 409304 792007203803 509309 852007203803 509305 822007202629 509308 78 2007202629 409301 56.8 2007202629 509307 69.5 2007202629 409304 83 2007202629 509309 76 2007202629 509305 542007202761 509308 86.4 2007202761 409301 85 2007202761 509307 97 2007202761 409304 93 2007202761 509309 88 2007202761 509305 822007202714 509308 76 2007202714 409301 45 2007202714 509307 68 2007202714 409304 71 2007202714 509309 54 2007202714 509305 382007203632 509308 58.6 2007203632 409301 67 2007203632 509307 73 2007203632 409304 74 2007203632 509309 68.4 2007203632 509305 52.62007203625 509308 55.3 2007203625 409301 43.6 2007203625 509307 60 2007203625 409304 68 2007203625 509309 57 2007203625 509305 50.32007203888 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 student4072select *from course4072select *from sc4072select *from sc_failselect *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));--数据输入insert into student4072 values('2007203803','张书才','男',21,'计算机科学与技术'); insert into student4072 values('2007202629','宋艳霞','女',19,'计算机科学与技术') insert into student4072 values('2007202761','侯凤英','女',20,'信息管理与信息系统'); insert into student4072 values('2007202714','赵德良','男',18,'信息管理与信息系统'); insert into student4072 values('2007203632','马新成','男',22,'软件工程');insert into student4072 values('2007203625','刘永生','男',19,'软件工程');insert into student4072 values('2007203888','李晓琳','女',20,'电子商务');insert into course4072(cno,cname,ccredit) values('509308','数据库原理',4);insert into course4072(cno,cname,ccredit) values('409301','高等数学',6);insert into course4072(cno,cname,ccredit) values('509307','C语言程序设计',4); insert into course4072(cno,cname,ccredit) values('409304','计算机导论',4);insert into course4072(cno,cname,ccredit) values('509309','数据结构',4);insert into course4072(cno,cname,ccredit) values('509305','离散数学',5);update course4072 set cpno='509309' where cno='509308';update course4072 set cpno='409304' where cno='509307';update course4072 set cpno='509307' where cno='509309';update course4072 set cpno='409301' where cno='509305';insert into sc4072 values('2007202629','409301',56.8);insert into sc4072 values('2007202629','409304',83.0);insert into sc4072 values('2007202629','509305',54.0);insert into sc4072 values('2007202629','509307',69.5);insert into sc4072 values('2007202629','509308',78.0);insert into sc4072 values('2007202629','509309',76.0);insert into sc4072 values('2007202714','409301',45.0);insert into sc4072 values('2007202714','409304',71.0);insert into sc4072 values('2007202714','509305',38.0);insert into sc4072 values('2007202714','509307',68.0);insert into sc4072 values('2007202714','509308',76.0);insert into sc4072 values('2007202714','509309',54.0);insert into sc4072 values('2007202761','409301',85.0);insert into sc4072 values('2007202761','409304',93.0);insert into sc4072 values('2007202761','509305',82.0);insert into sc4072 values('2007202761','509307',97.0);insert into sc4072 values('2007202761','509308',86.4);insert into sc4072 values('2007202761','509309',88.0);insert into sc4072 values('2007203625','409301',43.6);insert into sc4072 values('2007203625','409304',68.0);insert into sc4072 values('2007203625','509305',50.3);insert into sc4072 values('2007203625','509307',60.0);insert into sc4072 values('2007203625','509308',55.3);insert into sc4072 values('2007203625','509309',57.0);insert into sc4072 values('2007203632','509308',58.6);insert into sc4072 values('2007203632','409301',67);insert into sc4072 values('2007203632','509307',73);insert into sc4072 values('2007203632','409304',74);insert into sc4072 values('2007203632','509309',68.4);insert into sc4072 values('2007203632','509305',52.6);insert into sc4072 values('2007203803','409301',76.0);insert into sc4072 values('2007203803','409304',79.0);insert into sc4072 values('2007203803','509305',82.0);insert into sc4072 values('2007203803','509307',83.0);insert into sc4072 values('2007203803','509308',89.0);insert into sc4072 values('2007203803','509309',85.0);insert into sc4072 values('2007203888','409301',86.0);insert into sc4072 values('2007203888','409304',96.0);insert into sc4072 values('2007203888','509305',81.8);insert into sc4072 values('2007203888','509307',93.0);insert into sc4072 values('2007203888','509308',89.3);insert into sc4072 values('2007203888','509309',87.0);select *from sc4072select *from sc_failselect *from warn4072/* 1.对所修课程不及格的学生进行学业预警，预警级别分为黄色(m≥q/6)、红色(n≥s/4)、留级(n≥s/2)和退学(n≥3s/4),其中q、m为学期应修课程数与不及格课程数,s、n为学年应修课程数与不及格课程数。