sql学分管理系统
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
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