山东大学数据库实验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(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)
        );





  • 4
    点赞
  • 3
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值