学生管理系统MS数据库练习(1)

values(‘0701011107’,‘李莉’,‘女’,‘1992-10-2’,420,‘07010111’,0)

insert into student

values(‘0601011101’,‘张劲’,‘男’,‘1987-11-12’,486,‘06010111’,0)

insert into student

values(‘0601011102’,‘金伟’,‘男’,‘1988-4-14’,487,‘06010111’,0)

insert into student

values(‘0601011103’,‘李健’,‘男’,‘1987-6-29’,476,‘06010111’,0)

insert into student

values(‘0601011104’,‘王红青’,‘女’,‘1988-8-24’,478,‘06010111’,0)

insert into student

values(‘0601011105’,‘谭辉’,‘男’,‘1988-6-16’,465,‘06010111’,0)

insert into student

values(‘0601011106’,‘陆凤英’,‘女’,‘1988-1-7’,480,‘06010111’,0)

insert into student

values(‘0601011107’,‘胡涛’,‘男’,‘1987-7-15’,462,‘06010111’,0)

–创建选修课程表

use student

go

create table choice

(

sno char(10) not null,

cno char(7) not null,

grade real

)

go

alter table choice

add constraint fk_choice

foreign key(sno) references student(sno)

insert into choice

values(‘0701011101’,‘1001’,90)

insert into choice

values(‘0701011101’,‘1002’,50)

insert into choice

values(‘0701011101’,‘1003’,70)

insert into choice

values(‘0701011101’,‘1004’,70)

insert into choice

values(‘0701011102’,‘1001’,93)

insert into choice

values(‘0701011102’,‘1002’,53)

insert into choice

values(‘0701011103’,‘1001’,90)

insert into choice

values(‘0701011104’,‘1003’,72)

insert into choice

values(‘0701011105’,‘1003’,60)

insert into choice

values(‘0701011106’,‘1003’,78)

insert into choice

values(‘0701011107’,‘1003’,50)

insert into choice

values(‘0701011107’,‘1004’,70)

insert into choice

values(‘0601011101’,‘1002’,‘’)

–创建课程表

use student

create table course

(

cno char(7) constraint fk_co primary key,

cname char(30) not null,

credits real not null

)

insert into course

values(‘1001’,‘Illustrator平面设计’,90)

insert into course

values(‘1002’,‘Photoshop图像处理’,80)

insert into course

values(‘1003’,‘Dreamweaver网页制作’,70)

insert into course

values(‘1004’,‘数据结构’,60)

CREATE TABLE department

(

deptno char(2) constraint pk_dept primary key,

deptname char(2