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

山东大学数据库实验5（2021年最新版）
本人分步写的，中间有空行的话，代表上一步结束，转向下一步，一个题 由多步完成

1
create table test5_01 (
 first_name varchar(4),
 frequency numeric(4)
);
insert into test5_01(first_name,frequency)
select  substr(name,2),count(*)
from pub.student
group by substr(name,2);

2
create table test5_02(
letter varchar(2),
frequency numeric(4));
错误做法：
insert into test5_02( letter, frequency)
select a ,count(a) b
from(
(select substr(name,2,1) a
from pub.student)
union all
(select substr(name,3) a
from pub.student)
)
group by a;
把a中的空值也select进去了，但是count（a）无法计算空值，默认为0

shan
正确做法：
insert into test5_02( letter, frequency)
select a ,count(*) b
from(
(select substr(name,2,1) a
from pub.student)
union all
(select substr(name,3) a
from pub.student)
)
where a is not null//把空值去掉
group by a;

3
create table test5_03(
Dname varchar(30) ,
class varchar(10) ,
P_count1 int,
P_count2 int,
P_count int);


insert into test5_03(dname,class,P_count)
select dname,class,count(sid)
from pub.student
where dname is not null
group by dname,class


create table a1 as
select *
from pub.student;

alter table a1 add sum_credit numeric(5,1) ;

update a1
set sum_credit=(
select sum(credit)
from ( 
select cid ,sid,max(score) score_
from pub.student_course   
group by sid,cid )
natural join pub.course
where a1.sid =sid and score_>=60
group by sid
);
update test5_03
set P_count1 =(
select(count(sid))
from a1
where test5_03.dname=a1.dname and test5_03.class=a2.class and
sum_credit>=10);

update test5_03
set P_count2 =(p_count-P_count1);//没选课的也算未达标，但是不能用sum_credit<10因为null无法比较

4
create table test5_04 (
Dname  varchar(30) ,
class  varchar(10) ,
P_count1 int,
 P_count2 int ,
P_count int);

insert into test5_04(dname,class,P_count)
select dname,class,count(sid)
from pub.student
where dname is not null
group by dname,class;

update test5_04
set P_count1 =(
select(count(sid))
from a1
where( test5_04.dname=a1.dname and test5_04.class=a1.class and to_number(test5_04.class)<=2008 and sum_credit>=8)
);

update test5_04
set P_count1 =(
select(count(sid))
from a1
where( test5_04.dname=a1.dname and test5_04.class=a1.class and sum_credit>=10)
)
where p_count1=0;//在上一步后，class大于2008的自动没有合适的select与之匹配，自动补为0（若为char类型的话，自动补为null）
update test5_04
set P_count2 =(p_count-P_count1);


5
create table test5_05(
dname varchar(30),
avg_ds_score int,
Avg_os_score int);

insert into test5_05(dname, Avg_ds_score)
select dname,round(avg(a),0)
from(
select max(score) a, dname,sid
from pub.student natural join pub.student_course
where dname is not null and cid=300002
group by sid,dname
)
group by dname;

update test5_05
set Avg_os_score=(
select round(avg (a),0)
from(
select max(score) a,sid ,dname
from pub.student natural join pub.student_course
where dname is not null and cid=300005
group by sid,dname)
where dname=test5_05.dname);

6
create table test5_06 as(
(select sid,name,dname
from pub.student natural join pub.student_course
where dname='计算机科学与技术学院' and cid=300002)
intersect
(select sid,name,dname
from pub.student natural join pub.student_course
where dname='计算机科学与技术学院' and cid=300005));
alter table test5_06 add ds_score int;
alter table test5_06 add os_score int;

update test5_06
set ds_score=(
select max(score)//有可能一个学生有多个成绩，所以取最大成绩
from pub.student_course
where cid=300002 and test5_06.sid=sid )

update test5_06
set os_score=(
select max(score)
from pub.student_course
where cid=300005 and test5_06.sid=sid )

7
create table test5_07 as(
(select sid,name,dname
from pub.student natural join pub.student_course
where dname='计算机科学与技术学院' and cid=300002)
union
(select sid,name,dname
from pub.student natural join pub.student_course
where dname='计算机科学与技术学院' and cid=300005));

alter table test5_07 add ds_score int;
alter table test5_07 add os_score int;

update test5_07
set ds_score=(
select max(score)
from pub.student_course
where cid=300002 and test5_07.sid=sid );

update test5_07
set os_score=(
select max(score)
from pub.student_course
where cid=300005 and test5_07.sid=sid );

8
create table test5_08 as(
select sid,name,dname
from pub.student 
where dname='计算机科学与技术学院');
alter table test5_08 add ds_score int;
alter table test5_08 add os_score int;
update test5_08
set ds_score=(
select max(score)
from pub.student_course
where cid=300002 and test5_08.sid=sid );

update test5_08
set os_score=(
select max(score)
from pub.student_course
where cid=300005 and test5_08.sid=sid );