sql学分管理系统

SQL数据库学分管理系统
（简单）
create table student
(
classno char(10),
sno char(10),
sname char(10),
ssex char(2) default '男',
dept char(10))
insert into student
values('091113','09111301','杜红梅','女','软件')
insert into student
values('091113','09111302','甘西','男','软件')
insert into student
values('091113','09111303','高小佩','女','软件')
insert into student
values('091113','09111304','李四','女','软件')
create table course
(
cno char(10),
course_type char(10),
cname char(10)
)
insert into course
values(01,'基础课','数学')
insert into course
values(02,'专业课','语文')
insert into course
values(03,'选修课','英语')
insert into course
values(04,'人文课','地理')
insert into course
values(05,'实验课','政治')
create table chouse_course
(
sno char(10)primary key,
basiccourseno char(10),
basiccoursecredit char(10),
majorcourseno char(10),
majorcoursecredit char(10),
xuanxiucourseno char(10),
xuanxiucoursecredit char(10),
renwencourseno char(10),
renwencoursecredit char(10),
shiyancourseno char(10),
shiyancoursecredit char(10)
)
insert into chouse_course
values('09111301',01,69,02,60,03,41,04,51,05,20)
insert into chouse_course
values('09111302',01,71,02,55,03,42,04,52,05,21)
insert into chouse_course
values('09111303',01,70,02,52,03,43,04,53,05,22)
create view 班级学分完成表
as select student.classno,student.sno,student.sname,chouse_course.basiccoursecredit,chouse_course.majorc oursecredit,chouse_course.xuanxiucoursecredit,chouse_course.renwencoursecredit,chouse_course. shiyancoursecredit
from chouse_course,student
where chouse_course.sno=student.sno
select*
from 班级学分完成表
where classno='091113'
select classno,sno,sname,ssex,dept
from student
where sno='09111301'
select sno,sname,ssex,dept
from student
where classno='091113'
select sno,renwencoursecredit
from chouse_course
order by renwencoursecredit desc
insert into student(classno,sno,sname,ssex,dept) values('091113','09111305','李三','男','软件')
select* from student
create procedure change
@sno char(8)
as
update chouse_course
set renwencoursecredit=renwencoursecredit+10 where sno=@sno
exec change @sno='09111302'
select* from chouse_course
create trigger changedisplay
on chouse_course
for delete
as
delete from student
where student.sno in
(select sno from deleted)
delete from chouse_course where sno='09111303' select* from student
select* from chouse_course
create procedure panduan
@sno char(8)
as
if (50<=(select basiccoursecredit from chouse_course where sno=@sno and chouse_course.basiccourseno=01)
and 50<=(select majorcoursecredit from chouse_course where sno=@sno and chouse_course.majorcourseno=02)
and 24<=(select xuanxiucoursecredit from chouse_course where sno=@sno and chouse_course.xuanxiucourseno=03)
and 8<= (select renwencoursecredit from chouse_course where sno=@sno and chouse_course.renwencourseno=04)
and 20<= (select shiyancoursecredit from chouse_course where sno=@sno and chouse_course.shiyancourseno=05)
)
print '可以毕业'
else
print '不能毕业'
exec panduan '09111303'。