student.sql

这是一个学生系统的查询语句

create database STUDENT
on
(
	name='STU_data',
	filename='G:\pw\STU.mdf',
	size=1mb,
	maxsize=5mb,
	filegrowth=10%
)

log on
(	name='STU_log',
	filename='G:\pw\STU.ldf',
	size=1mb,
	maxsize=5mb,
	filegrowth=10%
)

use STUDENT
create table S
(  sno char(6) not null primary key,
   sn char(20),
   sa smallint)
create table C
(  cno char(4) not null primary key,
   cn char(30),
   pcno char(4))
create table SC
(  sno char(6) not null,
   cno char(4) not null,
   g smallint)
alter table S
add sd char(2)
alter table SC
add constraint PK_sno primary key(sno,cno)
create index S_sno
on S(sno)
create index C_cno
on C(cno)
create index SC_no
on SC(sno,cno)
alter table S
add constraint CK_sa check(sa between 12 and 50)
alter table SC
add constraint CK_g check (g=1 or g=2 or g=3 or g=4 or g=5)
alter table SC
add constraint FK_sno
foreign key(sno) references S(sno)
alter table SC
add constraint FK_cno
foreign key(cno) references C(cno)
insert into S values('990104','SHANWANG','20','CS')