山东大学数据库实验4（2021最新版）

山东大学数据库实验4（2021最新版）

本人分步写的，中间有空行的话，代表上一步结束，转向下一步，一个题 由多步完成

数据库实验四
1
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
where test4_01.sid=pub.student_course.sid
group by pub.student_course.sid);
2
create table test4_02 as
 select *
from pub.student_41;
alter table test4_02 add avg_score numeric( 4,1);
update test4_02
set avg_score=(
select avg(score)
from pub.student_course
where test4_02.sid=pub.student_course.sid
group by sid);

3
create table test4_03 as
select *
from pub.student_41;
alter table test4_03 add sum_credit numeric(5,1) ;

update test4_03
set sum_credit=(
select sum(credit)
from ( 
select cid ,sid,max(score) score_
from pub.student_course    //防止有人一门课考好几次，每次成绩都大于60，那就会计算好几次的学分，去最大的成绩的话，就说明，只要及格了就只会记录一次
group by sid,cid )
natural join pub.course
where test4_03.sid =sid and score_>=60
group by sid
);
//update test4_03
set sum_credit=(
select sum(credit)
from ( 
select cid ,sid,max(score) score_
from pub.student_course    //防止有人一门课考好几次，每次成绩都大于60，那就会计算好几次的学分，去最大的成绩的话，就说明，只要及格了就只会记录一次
group by sid,cid
Having max(score)>=60 )
natural join pub.course
where test4_03.sid =sid 
);



create table test4_04 as
select *
from pub.student_41;
update test4_04
set dname=(
select did
from pub.department
where test4_04.dname=dname)
//第一个where确保，只有test4_04.dname=dname时，才会更改did，而当没有符合test4_04.dname=dname时，select就会返回空值null，dname就会被置为null，这与题目不符合，题目要求，当没有test4_04.dname=dname时，不对did做改动
where test4_04.dname in(
select dname
from  pub.department);
//这个where保证，当test4_04.dname在department表中时，才对did改动，限制了set 的作用条件

5

create table test4_05 as
select *
from pub.student_41;

alter table test4_05 add sum_score int ;
alter table test4_05 add avg_score numeric( 4,1);
alter table test4_05 add sum_credit numeric(5,1) ;
alter table test4_05 add did varchar(2);

update test4_05
set sum_credit =(
select sum(credit)
from pub.course natural join
(
select sid,cid,max(score) mscore
from pub.student_course
group by sid,cid
having max(score)>=60
)
where test4_05.sid=sid);

update test4_05
set did=(
select did
from (
select did,dname
from pub.department
union
select did,dname
from pub.department_41) department
where test4_05.dname= department.dname);

update test4_05
set did='00'
where did is null;

6
update test4_06 
set name=replace(name,' ','');把空格替换成无空格

7
create table test4_07 as
select *
from pub.student_42;
update test4_07
set sex= replace( sex,' ','');//坑点：有的性别后面带空格
update test4_07
set sex= replace( sex,'性','');
8
create table test4_08 as
select *
from pub.student_42;
update test4_08
set class=replace(class,'级','');

9
create table test4_09 as
select *
from pub.student_42;

update test4_09
set age=(
select (2012-extract (year from birthday))
from pub.student_42
where test4_09.sid=sid
)
where test4_09.age is null;


10


create table test4_10 as
select *
from pub.student_42;

update test4_10
set name=replace(name,' ','');

update test4_10
set dname=replace(dname,' ','');

update test4_10
 set age=(
select (2012-extract (year from birthday))
from pub.student_42
where test4_10.sid=sid
)
where test4_10.age is null;