1 -- 1、数据表准备:
2 -- --学生表---
3 CREATE TABLE STUDENT
4 (SNO VARCHAR(3) NOT NULL PRIMARY KEY,
5 SNAME VARCHAR(4) NOT NULL,
6 SSEX VARCHAR(2) NOT NULL,
7 SBIRTHDAY DATETIME,
8 CLASS VARCHAR(5));
9 -- ---课程表---
10 CREATE TABLE COURSE (CNO VARCHAR(5) NOT NULL,CNAME VARCHAR(10) NOT NULL,TNO VARCHAR(10) NOT NULL);
11 -- ---成绩表---
12 CREATE TABLE SCORE (SNO VARCHAR(3) NOT NULL, CNO VARCHAR(5) NOT NULL, DEGREE NUMERIC(10, 1) NOT NULL);
13 -- ---教师表---
14 CREATE TABLE TEACHER (TNO VARCHAR(3) NOT NULL,
15 TNAME VARCHAR(4) NOT NULL,
16 TSEX VARCHAR(2) NOT NULL,
17 TBIRTHDAY DATETIME NOT NULL,
18 PROF VARCHAR(6), DEPART VARCHAR(10) NOT NULL);
19 -- 2、插入数据:
20 -- --学生表---
21 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,'曾华','男','1977-09-01 00:00:00',95033);
22 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,'匡名','女','1975-10-02 00:00:00',95031);
23 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,'王力','男','1976-01-23 00:00:00',95033);
24 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,'李君','男','1976-02-20 00:00:00',95033);
25 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,'王方','女','1975-02-10 00:00:00',95031);
26 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,'陆钧','男','1974-06-03 00:00:00',95031);
27 INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('','陆仪','男','1984-04-03 00:00:00',95031);
28 insert into Student values('01' , '赵雷' ,'男' ,'1990-01-01' , '95033');
29 insert into Student values('02' , '钱电' ,'男', '1990-12-21' , '95032');
30 insert into Student values('03' , '孙风' ,'男', '1990-05-20' , '95032');
31 insert into Student values('04' , '李云' , '男','1990-08-06' , '95033');
32 insert into Student values('05' , '周梅' , '女','1991-12-01' , '95033');
33 insert into Student values('06' , '吴兰' , '男','1992-03-01' , '95034');
34 insert into Student values('07' , '郑竹' , '女','1989-07-01' , '95035');
35 insert into Student values('08' , '王菊' , '男','1990-01-20' , '95036');
36 -- --课程表---
37 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105','计算机导论',825);
38 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245','操作系统',804);
39 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166','数据电路',856);
40 INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888','高等数学',100);
41 insert into Course values('01' , '语文' , '02');
42 insert into Course values('02' , '数学' , '01');
43 insert into Course values('03' , '英语' , '03');
44 -- --成绩表---
45 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
46 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
47 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
48 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
49 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
50 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
51 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
52 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
53 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
54 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
55 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
56 INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
57 insert into Score values('01' , '01' , 80);
58 insert into Score values('01' , '02' , 90);
59 insert into Score values('01' , '03' , 99);
60 insert into Score values('02' , '01' , 70);
61 insert into Score values('02' , '02' , 60);
62 insert into Score values('02' , '03' , 80);
63 insert into Score values('03' , '01' , 80);
64 insert into Score values('03' , '02' , 80);
65 insert into Score values('03' , '03' , 80);
66 insert into Score values('04' , '01' , 50);
67 insert into Score values('04' , '02' , 30);
68 insert into Score values('04' , '03' , 20);
69 insert into Score values('05' , '01' , 76);
70 insert into Score values('05' , '02' , 87);
71 insert into Score values('06' , '01' , 31);
72 insert into Score values('06' , '03' , 34);
73 insert into Score values('07' , '02' , 89);
74 insert into Score values('07' , '03' , 98);
75 -- --教师表---
76 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
77 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
78 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
79 INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
80 insert into Teacher values('01', '张三','男','1978-02-10','副教授','数学');
81 insert into Teacher values('02', '李四','女','1968-11-02','副教授','语文');
82 insert into Teacher values('03' , '王五','男','1970-12-18','副教授','英语');