創(chuàng)建數(shù)據(jù)庫(kù)TestTask，student表，teacher表，course表，score表

create database TestTask

go

use TestTask

go

create table student

(

Sno varchar(20) not null primary key ,--學(xué)號(hào)

Sname varchar(20) not null,--學(xué)生姓名

Ssex varchar(20) not null, --學(xué)生性別

Sbirthday datetime,--學(xué)生出生年月

Class varchar(20)--學(xué)生所在班級(jí)

)

insert into student values('108','曾華','男','1977-09-01','95033')

insert into student values('105','匡明','男','1975-10-02','95031')

insert into student values('107','王麗','女','1976-01-23','95033')

insert into student values('101','李軍','男','1976-02-20','95033')

insert into student values('109','王芳','女','1975-02-10','95031')

insert into student values('103','陸君','男','1974-06-03','95031')

select * from student

go

create table teacher--老師

(

Tno varchar(20) not null primary key ,--教工編號(hào)（主碼）

Tname varchar(20) not null,--教工姓名

Tsex varchar(20) not null, --教工性別

Tbirthday datetime,--教工出生年月

Prof varchar(20),--職稱

Depart varchar(20) not null--教工所在部門

)

insert into teacher values('804','李誠(chéng)','男','1958-12-02','副教授','計(jì)算機(jī)系')

insert into teacher values('856','張旭','男','1969-03-12','講師','電子工程系')

insert into teacher values('825','王萍','女','1972-05-05','助教','計(jì)算機(jī)系')

insert into teacher values('831','劉冰','女','1977-08-14','助教','電子工程系')

select * from teacher

go

create table Course--課程

(

Cno varchar(20) not null primary key ,--課程號(hào)

Cname varchar(20) not null,--課程名稱

Tno varchar(20) not null references teacher(Tno), --教工編號(hào)（外碼）

)

insert into course values('3-105','計(jì)算機(jī)導(dǎo)論','825')

insert into course values('3-245','操作系統(tǒng)','804')

insert into course values('6-166','數(shù)字電路','856')

insert into course values('9-888','高等數(shù)學(xué)','831')

select * from Course

go

create table Score--分?jǐn)?shù)

(

Sno varchar(20) not null references student(Sno), --學(xué)號(hào)（外碼）

Cno varchar(20) not null references Course(Cno), --課程號(hào)（外碼）

primary key(Sno,Cno),

Degree Decimal(4,1),--成績(jī)

)

insert into score values('103','3-245','86')

insert into score values('105','3-245','75')

insert into score values('109','3-245','68')

insert into score values('103','3-105','92')

insert into score values('105','3-105','88')

insert into score values('109','3-105','76')

insert into score values('101','3-105','64')

insert into score values('107','3-105','91')

insert into score values('108','3-105','78')

insert into score values('101','6-166','85')

insert into score values('107','6-166','79')

insert into score values('108','6-166','81')

select * from  Score

運(yùn)行結(jié)果：