武汉轻工大学2013-2014学年第 2学期《大型数据库应用》期末考核报告专业：数学与计算机班级：大类2班学号：1205110205姓名：钱婷设计书具体内容一、设计题目在Oracle数据库中创建关系表并完成SQL操作。

二、设计目的数据库系统课程设计是计算机类专业的集中实践性环节之一，是学习完《数据库系统概论》课程后进行的一次全面的综合练习。

其目的在于加深对大型数据库基础理论和基本知识的理解，掌握使用数据库设计与使用的基本方法，提高运用数据库解决实际问题的能力。

三、设计任务（1）查询入学总分高于平均总分的学生的学号、姓名、总分、班级编号、班级名信息，并将显示结果按班级编号、学号排序。

（2）创建一个名为STUDENT_NEW的新表，将STUDENT表中的记录复制到该新表中。

（3）列出《计算机文化》课程的成绩高于“王五”的学生名单。

（4）分组统计选修各门课程的学生人数。

五、设计内容用Scott用户身份登录Oracle数据库，并完成任务书中所给的一系列表（注：所有任务全部用SQL或PL/SQL语句完成）。

代码如下：create table department //创建系部表(dept_no varchar2(50),dept_name varchar2(50) not null,constraint pk_dept_no primary key (dept_no));插入数据：INSERT INTO DEPARTMENT (DEPT_NO, DEPT_NAME) V ALUES ('02', '计算机'); INSERT INTO DEPARTMENT (DEPT_NO, DEPT_NAME) V ALUES ('03', '数学系'); INSERT INTO DEPARTMENT (DEPT_NO, DEPT_NAME) V ALUES ('04', '电气系'); create table teacher //创建教师表(t_no varchar2(50),t_name varchar2(50) not null,t_sex varchar2(20) not null,t_birthday date,tech_title varchar2(50) not null,age number,salary number,constraint pk_t_no primary key (t_no),constraint ck_sex check (t_sex in ('男', '女')));INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000001', '教师一', '男', '教授', '56', '4800');INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000002', '教师二', '女', '教授', '55', '4333');INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000003', '教师三', '男', '副教授', '43', '3342');INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000004', '教师四', '女', '讲师', '29', '2234');INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000005', '教师五', '男', '讲师', '30', '2312');INSERT INTO TEACHER (T_NO, T_NAME, T_SEX, TECH_TITLE, AGE, SALARY) V ALUES ('000006', '教师六', '女', '讲师', '45', '2313');create table course //创建课表(course_no varchar2(50),course_name varchar2(50) not null,constraint pk_course_no primary key (course_no));INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('01', '数学分析'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('02', '模拟电路'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('03', '数字电路'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('04', '计算机文化'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('05', '离散数学'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('06', '高等数学'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('07', '邓小平理论'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('08', '马克思哲学'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('09', '大学物理'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('10', '大学语文'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('11', '数据结构'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('12', 'C语言'); INSERT INTO COURSE (COURSE_NO, COURSE_NAME) V ALUES ('13', '线性代数'); create table class //创建班级表(class_no varchar2(50) ,class_name varchar2(50) not null,dept_no varchar2(50) not null,constraint pk_class_no primary key (class_no),constraint fk_dept_no foreign key (dept_no) references department(dept_no) on delete set null);INSERT INTO CLASS (CLASS_NO, CLASS_NAME, DEPT_NO) V ALUES ('0202', '计算机2班', '02');INSERT INTO CLASS (CLASS_NO, CLASS_NAME, DEPT_NO) V ALUES ('0302', '数学2班', '03');INSERT INTO CLASS (CLASS_NO, CLASS_NAME, DEPT_NO) V ALUES ('0303', '数学3班', '03');INSERT INTO CLASS (CLASS_NO, CLASS_NAME, DEPT_NO) V ALUES ('0401', '电气1班', '04');create table student //创建学生表(s_no varchar2(50),s_name varchar2(50) not null,s_sex varchar2(20) not null,s_birthday date,s_score number,s_addf number,class_no varchar2(50) not null,constraint pk_n_no primary key(s_no),constraint fk_clss_no foreign key(class_no) references class(class_no) on delete set null, constraint ck_s_sex check (s_sex in('男', '女')));INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('020201', '张三', '男', '560', '0', '0202');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('020202', '李四', '男', '578', '0', '0202');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('030201', '王五', '女', '545', '10', '0302');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('030202', '丁一', '女', '589', '0', '0302');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('030301', '周一', '女', '570', '0', '0303');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('030302', '牛二', '男', '609', '0', '0303');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('030303', '阮七', '女', '603', '0', '0303');INSERT INTO STUDENT (S_NO, S_NAME, S_SEX, S_SCORE, S_ADDF, CLASS_NO) V ALUES ('040101', '苏山', '女', '548', '50', '0401');create table choice //创建选课表(s_no varchar2(50),course_no varchar2(50),score number,constraint pk_s_c_no primary key (s_no, course_no),constraint fk_s_no foreign key(s_no) references student(s_no) on delete set null,constraint fk_course_no foreign key (course_no) references course(course_no) on delete set null);INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '07', '83');INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '08', '79'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '09', '78'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '11', '92'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '12', '95'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020201', '04', '86'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '13', '67'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '07', '89'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '08', '77'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '09', '80'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '11', '90'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '12', '93'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('020202', '04', '88'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '01', '79'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '04', '77'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '07', '88'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '08', '86'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '10', '77'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '01', '55'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '04', '54'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '07', '67'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '08', '65'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '10', '70'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('040101', '02', '78'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('040101', '03', '82'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('040101', '04', '78'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '13', '78'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030202', '05', '80'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '13', '86'); INSERT INTO CHOICE (S_NO, COURSE_NO, SCORE) V ALUES ('030201', '05', '79'); create table teaching //创建教师授课表(t_no varchar2(50),course_no varchar2(50),constraint pk_t_c_no primary key (t_no, course_no),constraint fk_t_no foreign key (t_no) references teacher(t_no) on delete set null,constraint fk_c_no foreign key (course_no) references course(course_no) on delete set null);INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000001', '01');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000001', '06');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000001', '13');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000002', '02');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000002', '03');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000003', '07');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000003', '08');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000005', '05');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000006', '04');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000006', '12');INSERT INTO TEACHING (T_NO, COURSE_NO) V ALUES ('000001', '11');六、实验习题代码如下由于运行结果上机时已展示，在此只显示代码段（1）select s_no, s_name, s_score, s.class_no, class_name, dept_nofrom student s ,class cwhere s.class_no = c.class_no and s.s_score >(select avg(s_score)from student)order by s.s_no, c.class_no;（2）create table new_class asselect *from new_class;（3）select s_namefrom student,choice,coursewhere student.s_no=choice.s_noand course.course_no=choice.course_noand course_name='计算机文化'and score>(select scorefrom student,course,choicewhere student.s_no=choice.s_noand course.course_no=choice.course_noand s_name='王五' and course_name = '计算机文化');（4）select course_name , count(s_no)from choice,coursewhere course.course_no=choice.course_nogroup by course_name;七、实验总结通过本次实验，我对表的创建和操作有了更深刻的理解,尤其是表与表之间的联系,对主码的操作需注意些什么,我将写好的SQL语句放入到SQL server 2005上运行发现虽然Oracle和SQL server 2005 虽然都用的是标准的SQL,但还是有差别，让我学到了一种新的学习工具，对数据库有了更深刻的认识，但仍要继续努力。