陇东学院 数据库 杨永峰 sql脚本

create database jwgl
create table Student(
Sno char(9) primary key,
Sname char(20),
Ssex char(2),
Sage smallint,
Sdept char(6)
);
create table Course(
Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno)
);
create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)


insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','男',19,'IS')

alter table Student alter column Sdept char(20)

select * from Student



insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',null,2)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',null,2)

insert into Course(Cno,Cname,Cpno,Ccredit)
values('7','Pascal语言','6',4)

select * from Course



insert into SC(Sno,Cno,Grade)
values('201215121','1',92)

insert into SC(Sno,Cno,Grade)
values('201215121','2',85)

insert into SC(Sno,Cno,Grade)
values('201215121','3',88)

insert into SC(Sno,Cno,Grade)
values('201215122','2',90)

insert into SC(Sno,Cno,Grade)
values('201215122','3',80)

select * from SC

alter table Student alter column Sage int;
alter table Course add unique (Cname);