山东大学数据库实验答案四、五、六

实验四

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

SQL：

create table test4_01 as(

select *

from pub.student_41)

 

alter table test4_01 add sum_score int

 

update test4_01

set sum_score=(

select sum(score)

from pub.student_course a

where a.sid=test4_01.sid)

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

主要SQL：

update test4_02

set avg_score=(

select avg(score)

from pub.student_course a

where a.sid=test4_02.sid)

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

update test4_03

set sum_credit=(

select sum(credit)

from (select sid ,cid ,max(score) score from pub.student_course group by sid,cid) a natural join pub.course b

where a.sid=test4_03.sid and a.score>=60)

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

主要SQL：

update test4_04

set dname=(select  did

from pub.department a

where test4_04.dname=a.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。

主要SQL：

update test4_05

set did=(select  did

from pub.department a

where test4_05.dname=a.dname

)

where dname in (

select dname from pub.department

)

 

update test4_05

set did=(select  did

from pub.department_41 a

where test4_05.dname=a.dname

)

where dname in (

select dname from pub.department_41

)

 

update test4_05

set did='00'

where dname not in (

select dname from pub.department

) or dname is null

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

主要SQL：

create table test4_06 as(

select *

from pub.student_42)

update test4_06

set name=translate(name,'/ ','/')

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

SQL：（区别为多了性字和空格）

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