山东大学软件学院数据库实验六2024

实验六内容：

实验六	创建视图、删除视图
一、	实验内容
oracle管理平台，针对公共用户pub下的表，完成创建视图、查询验证视图、删除视图。视图名为test6_(题号，题号长度两位，前面补零),例如test6_01。
二、	实验题目
1．	找出年龄小于20岁且是“物理学院”的学生的学号、姓名、院系名称,按学号排序。
2．	查询统计2009级、软件学院所有学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）。
3．	查询所有课的最高成绩、最高成绩人数，test6_06有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次,需要考虑刷成绩情况，一个同学选了一个课程多次，两次都是最高分。如果结果错一行，可能就是没有考虑这种情况，这里是为了考核“去重复计数”知识点的）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零。
提示：参考讲义关于标量子查询（只返回包含单个属性的单个元组）。
4．	找出选修了“操作系统”并取得学分或者选修“数据结构”并且取得学分，但是没有选修“程序设计语言”或者没有取得这门课的学分的男学生的学号、姓名。
5．	查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)
Test6_05有四个列，并且列名必须是：sid、name、avg_score、sum_score。通过下面方式实现列名定义：
create or replace view test6_05 as select sid,name，（表达式） avg_score,（表达式） sum_score  from ……
6．	找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。
7．	找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。（题6的延伸和巩固）
8．	找出选修了所有课程并且得到所有课程的学分（即每门课程最少一次考试及格）的学生的学号、姓名。（题6的 延伸和巩固）。
9．	查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。（不统计没有选课的学生）。
10．	查询学生表中每一个姓氏及其人数（不考虑复姓，用到取子串函数substr(string,postion,length)）），test6_10有两个列：second_name、p_count

6.1

create or replace view test6_01 as select sid，name，dname
  from pub.student
  where age<20
  and dname='物理学院'-- 一定要是单引号
order by sid-- 是oreder不是oredered

6.2

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

6.3

create or replace view test6_03 as
select distinct c.cid,c.name,
  (select max(score)
  from pub.student_course sc
  where sc.cid=c.cid
  group by cid
  ) as max_score,-- 标量子查询，即返回为一行一列单个值的子查询
  (select count(distinct sid)-- 保证了同一个学号的学生有相同成绩只保留一条记录
  from pub.student_course sc
  where sc.cid=c.cid and score=(select max(score)
  from pub.student_course sc1
  where sc.cid=sc1.cid-- 关联子查询，和外部查询关联起来
  )
  group by cid
  ) as max_score_count
from pub.course c
group by c.cid,c.name

6.4

create or replace view test6_04 as
select distinct s.sid,s.name
from pub.student s,pub.student_course sc,pub.course c
where s.sid=sc.sid and sc.cid=c.cid
  and sex='男'
and s.sid in
  (select sid
  from pub.student_course sc ,pub.course c
  where sc.cid=c.cid
  and (c.name='数据结构'or c.name='操作系统')
and score>=60
  )
and c.name in('数据结构','操作系统') -- in的含义：只要c.name是in中包含的值中任意一个即可
and s.sid not in
(
  select sid
  from pub.student_course sc,pub.course c
  where sc.cid=c.cid
  and c.name='程序设计语言'
  and score>=60
)

6.5

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

6.6

create or replace view test6_06 as
  select s.sid,s.name
from pub.student s ,pub.student_course sc
where s.sid=sc.sid
and s.sid in
(select sid
  from pub.student_course sc
  where score<60
  group by sid,cid
  having count(*)>=2)

6.7

create or replace view test6_07 as
select sid,name
from pub.student s
where not exists
(select 1
  from pub.course c
  where not exists
  (select distinct 1
  from pub.student_course sc
  where score >=60
  and sc.sid=s.sid and sc.cid=c.cid
  ))
and sid not in
(select sid
  from pub.student_course sc
  where score<60)

6.8

create or replace view test6_08 as
select sid,name
from pub.student s
where not exists
(select 1
  from pub.course c
  where not exists
  (select 1
  from pub.student_course sc
  where s.sid=sc.sid and c.cid=sc.cid
  and score >=60))

6.9

create or replace view test6_09 as
select distinct s.sid,s.name,sum(credit) sum_credit
from pub.student s,pub.student_course sc,pub.course c
where s.sid=sc.sid and c.cid=sc.cid
and score >=60 --只有成绩大于等于六十才能获得该科的学分
and class='2010' and dname='化学与化工学院'
group by s.sid,s.name

6.10

create or replace view test6_10 as
select substr(name,0,1) second_name,count(*) p_count
from pub.student s
group by substr(name,0,1)