山东大学软件学院数据库系统实验三

一、实验时间

2021年4月20日星期二，第8周

二、实验题目

1．将pub用户下的Student_31及数据复制到主用户的表test3_01，删除表中的学号不全是数字的那些错误数据，学号应该是数字组成，不能够包含字母空格等非数字字符。
方法之一：用substr函数，例如Substr(sid,1,1)返回学号的第一位，判断是否是数字。

create table test3_01 as select * from pub.student_31 where regexp_like(sid, '^+[0-9]*$')

2．将pub用户下的Student_31及数据复制到主用户的表test3_02，删除表中的出生日期和年龄(截止到2012年的年龄，即年龄=2012-出生年份)不一致的那些错误数据。
函数extract(year from birthday)返回birthday的年份

create table test3_02 as select * from pub.student_31 where age = 2012-extract(year from birthday)

3． 将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的那些错误数据（性别只能够是"男"、"女"或者空值）。

create table test3_03 as select * from pub.student_31 where sex = '男' or sex = '女' or sex is null

4． 将pub用户下的Student_31及数据复制到主用户的表test3_04，删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于3个字的那些错误数据。

create table test3_04 as select * from pub.student_31 where dname is not null and dname not like '% %' and length(dname) >=  3

5． 将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的那些错误数据，不规范是指和大多数不一致。
这个题知识点是学会用sql找出不规范的数据，而不是用人工办法找不规范。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。

create table test3_05 as select * from pub.student_31 where class not like '%级%' and class not like '% %'

6．将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是那些错误数据。
（1）学号不全是数字；
（2）出生日期和年龄不一致的(年龄=2012-出生年份)；
（3）姓名有空格的或者长度小于2个字的；函数length()返回字符串长度。
（4）性别有错误的（只能够是"男"、“女”、空值）；
（5）院系名称有空格的、院系名称为空值的；
（6）院系名称小于3个字的；
（7）班级数据有错误的（需要先找到班级里面的错误）。
保留最后全部正确的数据。

create table test3_06 as select * from pub.student_31 where regexp_like(sid, '^+[0-9]*$') and age = 2012-extract(year from birthday) and (sex = '男' or sex = '女' or sex is null) and (dname is not null and dname not like '% %' and length(dname) >=  3) and (class not like '%级%' and class not like '% %') and (name not like '% %' and length(name) >= 2)

7． 将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：
学号在学生信息pub.student中不存在的；

create table test3_07 as select * from pub.student_course_32 where sid in (select sid from pub.student)

8． 将pub用户下的Student_course_32及数据复制到主用户的表test3_08，删除其中的错误数据，错误指如下情况：
课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程

create table test3_08 as select * from pub.student_course_32 where (cid,tid) in (select cid,tid from pub.teacher_course)

9． 将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：
成绩数据有错误（需要先找到成绩里面的错误）。
这个题知识点是学会用sql找出错误数据，而不是用人工办法找错误数据。
提示：寻找不规范有很多解决思路，可以去对比大纲最后的提示。

create table test3_09 as select * from pub.student_course_32 where score >= 0 and score <= 100

10．将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据，错误指如下情况：
（1） 学号在学生信息pub.student中不存在的；
（2） 课程号在课程信息pub.course中不存在的；
（3） 教师编号在教师信息pub.teacher中不存在的；
（4） 课程号和教师编号在教师授课表pub.teacher_course中不存在的；
（5） 成绩数据有错误（需要先找到成绩里面的错误）。
保留最后正确的数据。

create table test3_10 as select * from pub.student_course_32 where sid in (select sid from pub.student) and cid in (select cid from pub.course) and tid in (select tid from pub.teacher) and (cid,tid) in (select cid,tid from pub.teacher_course) and score >= 0 and score <= 100