jiaoxuedb.sql

这是一个教学系统用SQL 2000编写的

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

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

use jiaoxuedb
create table Student
(  Sno char(6) not null primary key,
   Sname char(6),
   Sex smallint,
   Age smallint,
   Dept char(6))
create table Teacher
(  Tno char(6) not null primary key,
   Tname char(6),
   Sex smallint,
   Age smallint,
   Prof char(6),
   Sal int,
   Comm int,
   Dept char(6))
create table Course
(  Cno char(5) not null primary key,
   Cname char(10),
   Chour smallint,
   Ccredit smallint)
create table SC
(  Sno char(6) not null,
   Cno char(5) not null,
   Score smallint)
create table TC
(  Tno char(6) not null,
   Cno char(5) not null)

alter table Student
add Resume char(2000)
alter table Student
add Native char(20)

alter table SC
add constraint PK_Sno primary key(Sno,Cno)
alter table TC
add constraint PK_Tno primary key(Tno,Cno)

alter table Student
alter column Native char(40)

alter table Teacher
alter column Sex varchar(2)

insert into Teacher values('000006','许红霞','女','39','讲师','1100','1200','计算机')
insert into Teacher values('000007','许永军','男','57','教授','2000','3000','计算机')
insert into Teacher values('000008','李桂青','女','65','教授','2000','3000','计算机')
insert into Teacher values('000009','王一凡','女','43','讲师','1200','1200','计算机')
insert into Teacher values('000010','田峰','男','33','助教','500','800','信息')
insert into Teacher values('000001','李英','女','39','副教授','1500','2000','信息')
insert into Teacher values('000002','张雪','女','51','教授','1900','3000','信息')
insert into Teacher values('000003','张朋','男','30','讲师','1000','1200','计算机')
insert into Teacher values('000004','王平','女','28','讲师','850','1200','信息')
insert into Teacher values('000005','李力','男','47','教授','1800','3000','计算机')

insert into TC values('000001','02001')
insert into TC values('000008','02002')
insert into TC values('000003','02001')
insert into TC values('000011','02003')
insert into TC values('000001','01001')
insert into TC values('000002','01002')
insert into TC values('000002','01003')
insert into TC values('000004','02002')
insert into TC values('000005','01001')
insert into TC values('000006','01002')
insert into TC values('000003','01003')

insert into Student values(001101,'宋大方','男',19,'计算机')
insert into Student values(002102,'李王','男',20,'信息')
insert into Student values(991101,'张彬','男',18,'计算机')
insert into Student values(991102,'王蕾','女',19,'计算机')
insert into Student values(991103,'张建国','男',18,'计算机')
insert into Student values(991104,'李平方','男',18,'计算机')
insert into Student values(991201,'陈东辉','男',19,'计算机')
insert into Student values(991202,'葛鹏','男',21,'计算机')
insert into Student values(991203,'潘桃芝','女',19,'计算机')
insert into Student values(991204,'姚一峰','男',18,'计算机')
insert into Student values(001102,'许辉','女',22,'计算机')
insert into Student values(001201,'王一山','男',20,'计算机')
insert into Student values(001202,'牛莉','女',19,'计算机')
insert into Student values(002101,'李丽丽','女',19,'信息')

insert into Course values(01001,'计算机基础',60,3)
insert into Course values(01002,'程序设计',80,5)
insert into Course values(02003,'数据结构',60,6)
insert into Course values(02001,'数据库',80,6)
insert into Course values(02002,'计算机网络',60,6)
insert into Course values(01003,'微机原理',60,8)
insert into Course values(02004,'操作系统',60,6)
insert into Course values(03001,'软件工程',60,3)
insert into Course values(03002,'大型数据库',48,2)
insert into Course values(03003,'图像处理',48,2)

insert into SC values(991101,01001,88)
insert into SC values(991102,01001,93)
insert into SC values(991103,01001,90)
insert into SC values(991101,01002,90)
insert into SC values(991102,01002,98)
insert into SC values(991103,01002,74)
insert into SC values(991104,01002,85)
insert into SC values(001201,01002,64)
insert into SC values(991104,02001,33)
insert into SC values(991104,01001,35)
insert into SC values(991201,01001,76)


alter table Student
drop column Resume

alter table Student
drop column Native

alter table Student
alter column Sex varchar(2)