测试sql

学生表
CREATE TABLE student (
sno VARCHAR ( 20 ) PRIMARY KEY,
sname VARCHAR ( 20 ) NOT NULL,
ssex VARCHAR ( 10 ) NOT NULL,
sbirthday datetime,
class VARCHAR ( 20 ) 
);

教师表
CREATE TABLE teacher (
tno VARCHAR ( 20 ) PRIMARY KEY,
tname VARCHAR ( 20 ) NOT NULL,
tsex VARCHAR ( 20 ) NOT NULL,
tbirthday datetime,
prof VARCHAR ( 20 ),
depart VARCHAR ( 20 ) NOT NULL 
);

课程表
CREATE TABLE course (
cno VARCHAR ( 20 ) PRIMARY KEY,
cname VARCHAR ( 20 ) NOT NULL,
tno VARCHAR ( 20 ) NOT NULL,
FOREIGN KEY ( tno ) REFERENCES teacher ( tno ) 
);

成绩表
CREATE TABLE score ( 
sno VARCHAR ( 20 ) not null, 
cno VARCHAR ( 20 ) NOT NULL, 
degree DECIMAL,
primary key(sno,cno));

插入数据
学生表数据
INSERT INTO student VALUES( '101', '增华', '男', '1997-09-01', '95033' );
INSERT INTO student VALUES( '102', '狂名', '男', '1997-05-01', '95033' );
INSERT INTO student VALUES( '103', '王丽', '女', '1997-04-01', '95033' );
INSERT INTO student VALUES( '104', '李军', '男', '1997-09-01', '95033' );
INSERT INTO student VALUES( '105', '王芳', '女', '1997-02-06', '95033' );
INSERT INTO student VALUES( '106', '陆军', '男', '1997-09-01', '95033' );
INSERT INTO student VALUES( '107', '王尼玛', '男', '1997-09-01', '95033' );
INSERT INTO student VALUES( '108', '张全蛋', '女', '1997-09-07', '95033' );
INSERT INTO student VALUES( '109', '赵铁柱', '男', '1997-09-08', '95033' );

教师表数据
insert into teacher values('808','李成','男','1956-02-12','副教授','计算机系');
insert into teacher values('856','张旭','男','1954-02-12','副教授','计算机系');
insert into teacher values('825','王萍','女','1953-02-12','副教授','计算机系');
insert into teacher values('831','刘冰','女','1952-02-12','副教授','计算机系');

课程表数据
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','808');
insert into course values('3-166','数字电路','856');
insert into course values('3-888','高等数据','831');

成绩表
insert into score values('103','3-245','86');
insert into score values('105','3-245','82');
insert into score values('109','3-245','34');
insert into score values('103','3-105','54');
insert into score values('105','3-105','54');
insert into score values('109','3-105','66');
insert into score values('103','3-166','76');
insert into score values('105','3-166','79');
insert into score values('109','3-166','81');