山东大学数据库实验6-2021年最新版-一一网

【摘要】山东大学数据库实验6-2021年最新版
6-1

create view test6_01 as
select sid,name,dname
from pub.student
where dname=’物理学院’and age<20
order by sid;

6-2

create view test6_02 as
select sid,name,sum(scor…

山东大学数据库实验6-2021年最新版

6-1

create view test6_01 as
select sid,name,dname
from pub.student
where dname='物理学院'and age<20
order by sid;

6-2

create view test6_02 as
select sid,name,sum(score) sum_score
from pub.student left outer join pub.student_course using (sid)
where class=2009 and dname='软件学院'
group by sid,name;

6-3

create view test6_03 as
select sid,name,score 
from pub.student natural join pub.student_course 
where class=2010 and dname='计算机科学与技术学院'  and cid =300005
;

6-4

create view test6_04 as
select sid,name
from pub.student natural join pub.student_course 
where cid=300003 and score >90
;

6-5

create view test6_05 as
select sid,cid, name ,score
from pub.course natural join (
select sid,cid,score
from pub.student natural join pub.student_course
where name='李龙' ）
;

6-6

create view test6_06 as
(select sid,name 
from pub.student s
where not exists (
( select cid
  from pub.course ) 
minus
( select cid
  from pub.student_course t
where s.sid=t.sid )
));

注意：Oracle中使用MINUS代替except

6-7

create view test6_07 as
select  sid,name,score
from test6_06 natural join pub.student_course
group by sid,name
having min(score)>=60;//保证了所有成绩都及格（如果只是score>=60，则代表只要有成绩大于等于60即可，但是不能保证所有成绩都大于等于60

6-8

create or replace view test6_08 as
select cid,name
from pub.course a
where exists ( select cid from pub.course where cid=a.fcid and credit=2 );
6-9

create or replace view test6_09 as
select sid,name,sum(sum_credit) sum_credit
from pub.student  natural join(
select sid,sum(credit) sum_credit
from pub.student_course natural join pub.course
group by sid,cid
having max(score) >=60) //from子查询得到一个学生的及格的课程得到的学分
where class=2010 and dname='化学与化工学院'
group by sid;//所以要求学生的总学分应该把所有及格课程得到的学分加在一起，所以最外层应该再次做一个sum求和

6-10

create or replace view test6_10 as Select cid,name From pub.course a where exists( Select cid From pub.course b Where cid=a.fcid and exists( Select cid From pub.course Where cid=b.fcid) ); 
  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62
  63
  64
  65
  66
  67
  68
  69
  70
  71
  72
  73
  74
  75
  76
  77
  78
  79
  80
  81
  82
  83
  84
  85
  86
  87
  88
  89
  90
  91
  92
  93
  94
  95
  96
  97
  98
  99
  100
  101
  102
  103
  104
  105