山东大学数据库实验

山东大学数据库实验

2-1

create or replace view test2_01 as 
(select sid,name  from pub.student where sid not in 
(select sid from pub.student_course)
)

2-2

create or replace view test2_02 as 
select pub.student.sid ,pub.student.name  from 
pub.student,pub.student_course 
where pub.student.sid=pub.student_course.sid  and
 pub.student_course.cid in (select cid from pub.student_course where sid=200900130417)
 and  pub.student.sid!=200900130417 
group by pub.student.sid,pub.student.name

2-3

create or replace view test2_03 as 
select pub.student.sid,pub.student.name 
from pub.student,pub.student_course 
where pub.student.sid=pub.student_course.sid  and 
pub.student_course.cid in(select cid from pub.course where fcid=300002)

2-4

create or replace view test2_04 as 
select sid,name from pub.student 
where sid in
(select sid from pub.student_course where cid=300002) and 
sid in(select sid from pub.student_course where cid=300005)```

2-5

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

2-6

create or replace view test2_06 as
select cid,name,max(score)max_score,count(score)max_score_count
from (select cid,sid,score from pub.student_course where (cid,score) in
(select cid,max(score)themax from pub.student_course group by cid) group by cid,sid,score) natural left outer join pub.course group by cid,name

2-7

create or replace view test2_07 as se