山东大学《数据库系统》实验四

实验题目：

复制表、修改表结构、修改数据

实验目的：

利用 oracle 管理平台完成对表的结构、数据进行修改，每一个问题可以通过多个 SQL 语句完成。

实验原理和方法：

• 复制表结构及其数据：
  create table table_name_new as select * from table_name_old
• 添加一列：
  alter table table_name add (column1列名 [column2 列名…]）
• 更新语句：
  update table_name set 列名=表达式\子查询 [where 条件表达式]

实验步骤：

1． 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_01 中，使用 alter table 语句为表增加列：“总成绩:sum_score”。
使用 update 语句,利用 pub.student_course，统计 “总成绩”；

复制：create table test4_01 as select * from pub.student_41
增加：alter table test4_01 add sum_score int
更新：update test4_01 b
	 set sum_score=(select sum(score)
      	   				from pub.student_course a
                		where a.sid=b.sid)	

2． 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_02 中，使用 alter table 语句为表增加列“平均成绩:avg_score”（小数点后保留 1 位）。
利用 pub.student_course，统计“平均成绩”，四舍五入到小数点后 1 位

复制：create table test4_02 as select * from pub.student_41
增加：alter table test4_02 add avg_score numeric(3,1)
更新：update test4_02 b
 	 set avg_score=(select avg(score) 
						from pub.student_course a
				 		where a.sid=b.sid)

3． 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_03 中，使用 alter table 语句为表增加列：“总学分:sum_credit”。
使用 update 语句,利用 pub.student_course、pub.course，统计 “总学分”；
这是需要注意：成绩及格才能够计算所得学分,一门课多个成绩都及格只计一次学分。

复制：create table test4_03 as select * from pub.student_41
增加：alter table test4_03 add sum_credit int
更新：update test4_03 b set sum_credit=
	 (select sum(credit) 
		from(select cid,sid,max(score) max_score 
			from pub.student_coursegroup by cid,sid) a
		natural join pub.course 
		where a.sid=b.sid and max_score>=60)

4． 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_04 中。
根据列院系名称 dname 到 pub.department 找到对应院系编号 did，将对应的院系编号回填到院系名称列dname 中，如果表中没有对应的院系名称，则列 dname 中内容不变仍然是原来的内容。

复制：create table test4_04 as select * from pub.student_41
更新：update test4_04 b      
	 set dname=(select did from pub.department a
	 				where a.dname=b.dname)
	 where dname in (select dname from pub.department)

5． 将 pub 用户下表 student_41 及数据复制到主用户的表 test4_05 中，使用 alter table 语句为表增加4列：“总成绩:sum_score”、“平均成绩:avg_score”、“总学分:sum_credit”、“院系编号:did varchar(2) ”。
（1） 利用 pub.student_course、pub.course，统计 “总成绩”；
（2） 利用 pub.student_course、pub.course，统计“平均成绩”，四舍五入到小数点后 1 位；
（3） 利用 pub.student_course、pub.course，统计 “总学分”；
（4） 根据院系名称到 pub.department 或者 pub.department_41 中，找到对应编号，填写到院系编号中，如果都没有对应的院系，则填写为 00。
说明：执行 update 后，在查询表中数据，可能出现顺序变化，这是正常，因为数据在表中是无序。需要顺序的时候可以通过 orderby 实现。

复制：create table test4_05 as select * from pub.student_41
增加：alter table test4_05 add(sum_score int,avg_score numeric(3,1),sum_credit int,did varchar(2))
更新总成绩、平均成绩：
update test4_05 b 
set (sum_score,avg_score)=(
select sum(score),round(avg(score),1) 
	from pub.student_course a 
	where a.sid=b.sid)
更新总学分：
update test4_05 b 
set sum_credit=(select sum(credit) 
	from (select cid,sid,max(score) max_score 
			from pub.student_coursegroup by cid,sid) a
	natural join pub.course 
	where a.sid=b.sid 
	and max_score>=60)
更新院系编号：
create table test4_04 as select * from pub.student
update test4_04 b
set dname=(select did  from pub.department a 
				where a.dname=b.dname)
where dname in(select dname from pub.department)
create table test4_04 as select * from pub.student_41
update test4_04 b
set dname=(select did from pub.department a 
				where a.dname=b.dname)
where dname in(select dname from pub.department_41)
update test4_05 set did='00' where did is null

6． 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_06 中,对表中的数据进行整理，修复不规范的数据：剔除姓名列中的所有空格；

复制：create table test4_06 as select * from pub.student_42
更新：
update test4_06
set name=translate(name,'/ ','/')
where name like'% %'

7． 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_07 中,对表中的数据进行整理，修复不规范的数据：对性别列进行规范；
（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）

复制：create table test4_07 as select * from pub.student_42
更新：update test4_07 set sex=translate(sex,'/性 ','/') 

8． 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_08 中,对表中的数据进行整理，修复不规范的数据：对班级列进行规范（需要先确定哪些班级不规范）。

复制：create  table test4_08 as select * from pub.student_42
更新：update test4_08 set class=translate(class,'/级','/')

9． 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_09 中,对表中的数据进行整理，修复不规范的数据：
年龄为空值的根据出生日期设置学生年龄（截止到 2012 年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

复制：create  table test4_09 as select * from pub.student_42
更新：
update test4_09 
set age=2012-extract(year from birthday) 
where age is null

10． 将 pub 用户下的 Student_42 及数据复制到主用户的表 test4_10 中,对表中的数据进行整理，修复不规范的数据：
（1） 剔除姓名列中的所有空格；
（2） 剔除院系名称列中的所有空格；
（3） 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；
（4） 对班级列进行规范（需要先确定哪些班级不规范）。
（5） 年龄为空值的根据出生日期设置学生年龄（截止到 2012 年的年龄，即年龄=2012-出生年份），年龄不为空值的不要改变。

复制：create  table test4_10 as select * from pub.student_42
更新：
update test4_10
set name=translate(name,'\ ','\'),
dname=translate(dname,'\ ','\'),
sex=translate(sex,'\性 ','\'),
class=translate(class,'\级','\')

update test4_10set age=2012-extract(year from birthday)where age is null

结论分析与体会：

要认真读懂题意再进行操作，例如第五题的第（4）小问，原题为根据院系名称到 pub.department 或者 pub.department_41 中，找到对应编号，填写到院系编号中，起初默认为pub.department和pub.department_41二表任选其一即可，实际上是要在两个表中分别进行查找对test4_05中对did进行更新，导致走了不少弯路。

就实验过程中遇到和出现的问题，你是如何解决和处理的，自拟1－3道问答题：

Q1:查询3:提交测试返回3行数据错误，如何解决？
A1:考虑同课程及格重修情况,用Maxscore>=60确保该名同学该课程学分获得且保证计算一次。

Q2：查询5:如果都没有对应的院系，则填写为00，表中数据却只有0，为什么？
A2：因为此处的00是字符串，而不是数字0，加引号‘00’即可。