山东大学数据库实验7

实验七


1
create table test7_01(
First_name varchar(4),
frequency numeric(4)
)


insert int test7_01
select substr(name,2) , count(*)
from pub.STUDENT
group by substr(name,2)


update dbtest set test=7
select * from dbscore

2
create table test7_02
as select letter,count(*) frequency
from(select substr(name,2,1) letter
from pub.STUDENT
union all
select substr(name,3,1) letter
from pub.STUDENT
where length(name)=3)
group by letter

3

create table test7_03 as
with a as (
select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid),
b as (select * from pub.student natural left join a),
c as (select dname,class,count(sid) as p_count from b group by (dname,class)),
d as (select dname,class,count(sid) as p_count1 from b where tot>=10 group by (dname,class)),
e as (select dname,class,count(sid) as p_count2 from b where tot<10 or tot is null group by (dname,class))
select * from c natural left join d natural join e


4
create table test7_04 as
with a as (
select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid),
b as (select * from pub.student natural left join a),
c as (select dname,class,count(sid) as p_count from b group by (dname,class)),
d as (select dname,class,count(sid) as p_count1 from b
where (class <=2008 and tot>=8) or (class >2008 and tot>=10)
group by (dname,class)),
e as (select dname,class,count(sid) as p_count2 from b
where (class <=2008 and tot<8) or (class >2008 and tot<10) or tot is null
group by (dname,class))
select * from c natural left join d natural join e


(如果select * from d natural join e natural join c 顺序就更对了)