实验四
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