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

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

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

2
create or replace view test2_02 as
(select sid,name
from pub.student_course natural join pub.student
where sid!='200900130417'and 
cid in(
select cid
from pub.student_course
where sid='200900130417'));

4
create or replace view test2_04 as(
(select sid,name
from pub.student natural join pub.student_course
where cid =
(
select cid
from pub.course
where pub.course.name='操作系统'
))
intersect
(select sid,name
from pub.student natural join pub.student_course
where cid =
(
select cid
from pub.course
where pub.course.name='数据结构'
))
minus
select sid,name
from pub.student natural join pub.student_course
where cid =(
select cid
from pub.course
where pub.course.name='程序设计语言'));


5

create or replace view test2_05 as(
select sid,name,round(avg(score),0) avg_score,sum(score) sum_score
from pub.student natural join pub.student_course
where  age='20'
group by sid,name);
8

create or replace view test2_08 as(
select substr(name,1,1) second_name,count(sid ) p_count
from pub.student
group by substr(name,1,1));

9

create or replace view test2_09 as(
select sid,name,score
from pub.student natural join pub.student_course
where cid ='300003');



	create or replace view test2_10 as(
select sid,name,count(score) count_score
from pub.student natural join pub.student_course
where score<60
group by sid,cid
having count_score>1
);

10

create table a as(
select sid,name,count(score) count_score
from pub.student natural join pub.student_course
where score<60 
group by sid,cid,name
having count(score) >1
);
create or replace view test2_10 as(
select sid,name
from a);


create  or replace view test2_06  as(
select cid,name,max(score) max_score,count(sid) max_score_count,score
From（ pub.student_course right join pub.course），c
Where(cid=s.cid and score=c.s
group by cid,name
);



创建c表
create table c as(
select cid,name,max(score) s
from pub.student_course natural join pub.course
group by cid,name
);