SQL案例-高校信息管理系统实现要求

(1) 建表

stuInfo(学生信息表)

字段名称	数据类型	说明
stuName	字符	学生姓名，该列必填，要考虑姓氏可能是两个字的，如欧阳俊雄
stuNo	字符	学号，该列必填，学号不能重复，且必须是S253XX格式
stuSex	字符	学生性别，该列必填，且只能是“男”或“女”。因为男生较多，默认为“男”
stuAge	数字	学生年龄，该列必填，必须在15～50岁之间
stuSeat	数字	学生的座位号，不用人工输入，采用自动编号方式，且必须是1～30号
stuAddress	文本	学生地址，该列可不填，如没有填写，默认为“地址不详”

stuMarks(学生成绩表)

字段名称	数据类型	说明
examNo	字符	考号，该列必填，且不能重复，必须是E200507XXXX格式
stuNo	字符	学号 ，该列必填，且必须在学生基本信息表中有相关记录
writtenExam	数字	学生笔试成绩，数据必须是0～100分之间，不填默认为0分
labExam	数字	学生机试成绩，数据必须是0～100之间，不填默认为0分

(2) 加约束

(3) 建关系

(4) 插入数据

插入数据时注意:

1. 其中有一名学员的地址不详，因此请将其中一名学生的地址设为空
2. 有一名学员机试缺考，因此他的机试成绩请设为空
3. 有一名学员因病，所有的考试都没有参加.

数据样板

建表

create table stuinfo(stuName varchar2(40) not null,stuNo varchar2(10) primary key constraint CK_stuNo check(stuNo like 'S253__'),stuSex varchar2(2) default '男'constraint CK_stuSex check(stuSex in('男','女')),stuAge intconstraint CK_stuAge check(stuAge between 15 and 30),stuSeat intconstraint CK_stuSeat check(stuSeat between 1 and 30),   --要求座位号只能在1-30之间，超过30从1开始重新自增stuAddress varchar2(100) default '地址不详'
);create table stuMarks(examNo varchar2(20) not nullconstraint CK_examNo check(examNo like 'E200507____'),stuNo varchar2(10) not nullconstraint FK_sNo references stuInfo(stuNo),writtenExam Numeric(5,2) default 0constraint CK_we check(writtenExam between 0 and 100),labExam Numeric(5,2) default 0constraint CK_le check(labExam between 0 and 100)
);-- 序列定义
create sequence seq_stuinfo_stuSeat start with 1 increment by 1 maxvalue 30 cycle cache 10;-- 触发器  获取插入的最新序列值
create or replace trigger tri_stuinfo
before insert on stuinfo
for each row
beginselect seq_stuinfo_stuSeat.nextval into :new.stuSeat from dual;
end;

数据插入

insert into stuInfo values('张秋丽','S25301','男',18,1,'湖南衡阳');
insert into stuInfo values('李斯文','S25303','女',22,1,'河南洛阳');
insert into stuInfo values('李文才','S25302','男',20,1,default);
insert into stuInfo values('欧阳俊雄','S25304','男',28,1,'新疆威武哈');
insert into stuInfo values('张丽','S25306','男',18,1,'北京海淀');insert into stuMarks(ExamNo,stuNo,writtenExam,LabExam) values('E2005070001','S25301',80,58);
insert into stuMarks(ExamNo,stuNo,writtenExam) values('E2005070002','S25302',50);
insert into stuMarks(ExamNo,stuNo,writtenExam,LabExam) values('E2005070003','S25303',97,82);select * from stuInfo;

每人的笔试成绩都提5分

update stuMarks set writtenexam = writtenexam+5 where writtenexam <= 95;

100分封顶（加分后超过100分的，按100分计算）

case when then 实现

update stuMarks set wittenExam = (case when wittenExam + 5 >100 then 100 else wittenExam + 5  end )

cursor 实现

declarecursor cur_smks is select * from stuMarks for update
beginfor v_smks in cur_smks loopif v_smks.wittenExam + 5 > 100 thenupdate stuMarks set writtenexam = 100 where current of cur_smks;elseupdate stuMarks set writtenexam = writtenexam+5 where current of cur_smks;end if;end loop;
end;