武汉轻工大学2013-2014学年第 2学期《大型数据库应用》期末考核报告专业：信息管理与信息系统班级：信管（2）班学号：*********姓名：***2013－2014年度第二学期《大型数据库应用》期末考核任务1.任务内容1.1以自己的学号为用户名建立用户；1.2 完成以下数据库文件的创建工作：（1）创建一个永久性的表空间TEST，表空间采用自动分区管理方式，其对应的数据文件名称为USERTEST.DBF，大小为50M，并将该表空间设置为数据库默认的表空间。

CREATE TABLESPACE TEST DATAFILE'F:\app\wanglu\oradata\orcl\USERTEST.DBF' SIZE 50MEXTENT MANAGEMENT LOCAL AUTOALLOCATE;ALTER DATABASE DEFAULT TABLESPACE TEST;（2）为数据库添加一个重做日志文件组，组内包含三个成员文件，成员文件的大小为3M。

ALTER DATABASE ADD LOGFILE GROUP 5('F:\app\wanglu\oradata\orcl\redo05a.log','F:\app\wanglu\oradata\orcl\redo05b.log','F:\app\wanglu\oradata\orcl\redo05c.log')SIZE 5M;1.3使用SQL语言创建下列关系表：（1）CHOICE表（选课表）字段名字段说明字段类型是否允许为空S_NO 学号字符串否COURSE_NO 该学号学生选课课号字符串否SCORE 该课程该学生的成绩整数是主键：(S_No, Course_no)CONNECT S110502221/S110502221;CREATE TABLE CHOICE(S_NO V ARCHAR(10) NOT NULL ,COURSE_NO V ARCHAR(10) NOT NULL,SCORE NUMBER(2) ,PRIMARY KEY(S_NO,COURSE_NO));（2）CLASS表（班级表）字段名字段说明字段类型是否允许为空CLASS_NO 班级编号字符串否CLASS_NAME 班级名称字符串否DEPT_NO 班级所在系部编号字符串否CREATE TABLE CLASS(CLASS_NO V ARCHAR(10) PRIMARY KEY ,CLASS_NAME V ARCHAR(20) NOT NULL,DEPT_NO V ARCHAR(10) NOT NULL);（3）COURSE表（课程表）字段名字段说明字段类型是否允许为空COURSE_NO 课程编号字符串否COURSE_NAME 课程名称字符串否主键：COURSE_NOCREATE TABLE COURSE(COURSE_NO V ARCHAR(10) PRIMARY KEY ,COURSE_NAME V ARCHAR(20) NOT NULL);（4）DEPARTMENT表（系部表）字段名字段说明字段类型是否允许为空DEPT_NO 系部编号字符串否DEPT_NAME 系部名称字符串否主键：DEPT_NOCREATE TABLE DEPARTMENT(DEPT_NO V ARCHAR(10) PRIMARY KEY ,DEPT_NAME V ARCHAR(20) NOT NULL);（5）STUDENT表（学生表）字段说明：字段名字段说明字段类型是否允许为空S_NO 学号字符串否S_NAME 学生姓名字符串否S_SEX 性别字符串否S_BIRTHDAY 出生日期日期是S_SCORE 入学总分整数是S_ADDF 入学附加分整数是CLASS_NO 所在班级编号字符串否主键：S_NOCREATE TABLE STUDENT(S_NO V ARCHAR(10) PRIMARY KEY ,S_NAME V ARCHAR(20) NOT NULL ,S_SEX V ARCHAR (2) NOT NULL,S_BIRTHDAY DA TE,S_SCORE NUMBER(3),S_ADDF NUMBER(2),CLASS_NO V ARCHAR(10) NOT NULL);（6）TEACHER表（教师表）字段说明：字段名字段说明字段类型是否允许为空T_NO 教师编号字符串否T_NAME 教师姓名字符串否T_SEX 性别字符串否S_BIRTHDAY 出生日期日期是TECH_TITLE 职称字符串否AGE 年龄整数是SALARY 薪水小数是主键：T_NOCREATE TABLE TEACHER(T_NO V ARCHAR(10) PRIMARY KEY ,T_NAME V ARCHAR(20) NOT NULL ,T_SEX V ARCHAR (2) NOT NULL,T_BIRTHDAY DA TE,TECH_TITLE VARCHAR(5) NOT NULL ,AGE NUMBER(2),SALARY NUMBER);（7）TEACHING表（教师授课表）字段说明：字段名字段说明字段类型是否允许为空T_NO 教师编号字符串否COURSE_NO 所带课程编号字符串否主键：（T_NO，COURSE_NO）CREATE TABLE TEACHING(T_NO V ARCHAR(10) NOT NULL ,COURSE_NO V ARCHAR(10) NOT NULL ,PRIMARY KEY(T_NO, COURESE_NO));1.3建立约束：各关系表的约束要求如下外键约束：（1）CHOICE——COURSE关系ADD CONSTRAINT CS1 FOREIGN KEY(COURSE_NO)REFERENCES COURSE(COURSE_NO);（2ADD CONSTRAINT CS2 FOREIGN KEY(S_NO) REFERENCES STUDENT (S_NO);（3）CLASS——DEPARTMENT关系ADD CONSTRAINT CS3 FOREIGN KEY(DEPT_NO)REFERENCES DEPARTMENT (DEPT_NO);（4）STUDENT——CLASS关系ADD CONSTRAINT CS4 FOREIGN KEY(CLASS_NO)REFERENCES CLASS(CLASS_NO);（5）TEACHING——TEACHER关系ADD CONSTRAINT CS5 FOREIGN KEY(T_NO) REFERENCES TEACHER (T_NO);（6ADD CONSTRAINT CS6 FOREIGN KEY(COURSE_NO)REFERENCES COURSE (COURSE_NO);检查性约束：STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为’男’或’女’。

ALTER TABLE STUDENT ADD CHECK (S_SEX IN(‘男’,’女’));ALTER TABLE TEACHER ADD CHECK (T_SEX IN(’男’,’女’));STUDENT表的S_SEX字段和TEACHER表的T_SEX字段取值均为’男’或’女’。

1.4使用SQL语言插入下列所示的数据TEARCHING表CHOICE表CLASS表COURSE表DEPARTMENT表STUDENT表deTEARCHER表表d epartmentINSERT INTO DEPARTMENT V ALUES('02','计算机'); INSERT INTO DEPARTMENT V ALUES('03','数学系'); INSERT INTO DEPARTMENT V ALUES('04','电气系');表classINSERT INTO CLASS V ALUES('0202','计算机2班','02'); INSERT INTO CLASS V ALUES('0302','数学3班','03'); INSERT INTO CLASS V ALUES('0303','数学3班','03'); INSERT INTO CLASS V ALUES('0401','电气1班','04');课程表INSERT INTO COURSE V ALUES('01','数学分析'); INSERT INTO COURSE V ALUES('02','模拟电路'); INSERT INTO COURSE V ALUES('03','数字电路'); INSERT INTO COURSE V ALUES('04','计算机文化'); INSERT INTO COURSE V ALUES('05','离散数学'); INSERT INTO COURSE V ALUES('13','线性代数'); INSERT INTO COURSE V ALUES('06','高等数学'); INSERT INTO COURSE V ALUES('07','邓小平理论'); INSERT INTO COURSE V ALUES('08','马克思哲学'); INSERT INTO COURSE V ALUES('09','大学物理'); INSERT INTO COURSE V ALUES('10','大学语文'); INSERT INTO COURSE V ALUES('11','数据结构'); INSERT INTO COURSE V ALUES('12','C语言');学生表INSERT INTO STUDENT V ALUES('020201','张三','男','',560,0,'0202'); INSERT INTO STUDENT V ALUES('020202','李四','男','',578,0,'0202'); INSERT INTO STUDENT V ALUES('030201','王五','女','',545,10,'0302'); INSERT INTO STUDENT V ALUES('030202','丁一','女','',589,0,'0302'); INSERT INTO STUDENT V ALUES('030301','周一','女','',570,0,'0303'); INSERT INTO STUDENT V ALUES('030302','牛二','男','',609,0,'0303'); INSERT INTO STUDENT V ALUES('030303','阮七','女','',603,0,'0303'); INSERT INTO STUDENT V ALUES('040101','苏山','女','',548,0,'0401');老师表INSERT INTO TEACHER V ALUES('000001','教师一','男','','教授',56,4800); INSERT INTO TEACHER V ALUES('000002','教师二','女','','教授',55,4333); INSERT INTO TEACHER V ALUES('000003','教师三','男','','副教授',43,3342); INSERT INTO TEACHER V ALUES('000005','教师四','女','','讲师',29,2234); INSERT INTO TEACHER V ALUES('000006','教师五','男','','讲师',30,2312); INSERT INTO TEACHER V ALUES('000007','教师六','女','','讲师',45,2313);授课表INSERT INTO TEACHING V ALUES('000001','01'); INSERT INTO TEACHING V ALUES('000001','06'); INSERT INTO TEACHING V ALUES('000001','13'); INSERT INTO TEACHING V ALUES('000002','02'); INSERT INTO TEACHING V ALUES('000002','03'); INSERT INTO TEACHING V ALUES('000003','07'); INSERT INTO TEACHING V ALUES('000003','08'); INSERT INTO TEACHING V ALUES('000005','05'); INSERT INTO TEACHING V ALUES('000006','04'); INSERT INTO TEACHING V ALUES('000006','12'); INSERT INTO TEACHING V ALUES('000007','11');选课表INSERT INTO CHOICE V ALUES('020201','07',83); INSERT INTO CHOICE V ALUES('020201','08',79); INSERT INTO CHOICE V ALUES('020201','09',78); INSERT INTO CHOICE V ALUES('020201','11',92); INSERT INTO CHOICE V ALUES('020201','12',95); INSERT INTO CHOICE V ALUES('020201','04',86);INSERT INTO CHOICE V ALUES('020202','13',67);INSERT INTO CHOICE V ALUES('020202','07',89);INSERT INTO CHOICE V ALUES('020202','08',77);INSERT INTO CHOICE V ALUES('020202','09',80);INSERT INTO CHOICE V ALUES('020202','11',90);INSERT INTO CHOICE V ALUES('020202','12',93);INSERT INTO CHOICE V ALUES('020202','04',88);INSERT INTO CHOICE V ALUES('030201','01',79);INSERT INTO CHOICE V ALUES('030201','04',77);INSERT INTO CHOICE V ALUES('030201','07',88);INSERT INTO CHOICE V ALUES('030201','08',86);INSERT INTO CHOICE V ALUES('030201','10',77);INSERT INTO CHOICE V ALUES('030202','01',55);INSERT INTO CHOICE V ALUES('030202','04',54);INSERT INTO CHOICE V ALUES('030202','07',67);INSERT INTO CHOICE V ALUES('030202','08',65);INSERT INTO CHOICE V ALUES('030202','10',70);INSERT INTO CHOICE V ALUES('040101','02',78);INSERT INTO CHOICE V ALUES('040101','03',82);INSERT INTO CHOICE V ALUES('040101','04',78);INSERT INTO CHOICE V ALUES('040101','07',67);INSERT INTO CHOICE V ALUES('040101','08',76);INSERT INTO CHOICE V ALUES('040101','09',86);INSERT INTO CHOICE V ALUES('030202','13',78);INSERT INTO CHOICE V ALUES('030202','05',80);INSERT INTO CHOICE V ALUES('030201','13',86);INSERT INTO CHOICE V ALUES('030201','05',79);1.5 完成以下查询要求，给出实现该要求的SQL语句（1）查询入学总分高于平均总分的学生的学号、姓名、总分、班级编号、班级名信息，并将显示结果按班级编号、学号排序。