数据库实验六

1.例如：找出年龄小于20岁的所有学生的学号、姓名、年龄
正确执行:create view test6_00 as select sid，name，age from pub.student where age>20
Oracle扩展后方便写法：
create or replace view test6_00 as select sid，name，age from pub.student where age>20
直行select count(*) from test6_00 检查是否能够5分钟内查询出全部结果，如果超时说明可能有错误，这种情况下严禁执行"update dbtest set test=6"进行交卷。
找出年龄小于20岁且是"物理学院"的学生的学号、姓名、院系名称,按学号排序。

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

  2．查询统计2009级、软件学院每个学生的学号、姓名、总成绩（列名sum_score）（如果有学生没有选一门课，则总成绩为空值）。

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

 3． 查询所有课的最高成绩、最高成绩人数，test6_03有四个列：课程号cid、课程名称name、最高成绩max_score、最高成绩人数max_score_count（一个学生同一门课成绩都是第一，只计一次,需要考虑刷成绩情况，一个同学选了一个课程多次，两次都是最高分。如果结果错一行，可能就是没有考虑这种情况，这里是为了考核"去重复计数"知识点的）。如果没有学生选课，则最高成绩为空值,最高成绩人数为零。
提示：参考讲义关于标量子查询（只返回包含单个属性的单个元组）

CREATE OR REPLACE VIEW test6_03 AS
SELECT
  c.cid,
  (select name from pub.course where pub.course.cid=c.cid) name,
  max_score,
  COALESCE(max_score_count, 0) AS max_score_count
FROM
  pub.course c
LEFT JOIN
  (
  SELECT
    sc.cid,
    sc.score AS max_score,
    COUNT(DISTINCT sc.sid) AS max_score_count
  FROM 
    pub.student_course sc
  WHERE
    sc.score = (
      SELECT MAX(temp.score) 
      FROM pub.student_course temp 
      WHERE temp.cid = sc.cid
    )
  GROUP BY 
    sc.cid, sc.score
  ) max_scores
ON c.cid = max_scores.cid;

4. 找出选修了"操作系统"并取得学分或者选修"数据结构"并且取得学分，但是没有选修"程序设计语言"或者没有取得这门课的学分的男学生的学号、姓名。

create or replace view test6_04 as
select sid , name
from   
pub.student
natural join
(select sid
from pub.student_course sc join pub.course c
on sc.cid = c.cid
where  score>=60 and (name='操作系统'  or  name='数据结构')
) 
where sex='男' and sid not in
(select sid
from   pub.student_course sc join pub.course c
on sc.cid = c.cid
where  name='程序设计语言' and  score>=60 )

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 ……

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

6．找出同一个同学同一门课程有两次或以上不及格的所有学生的学号、姓名（即一门课程需要补考两次或以上的学生的学号、姓名）。

HAVING 子句的作用类似于 WHERE 子句，但它用于筛选分组后的结果，而不是单个行。它允许您在聚合函数（例如 COUNT、SUM、AVG 等）的结果上应用条件，以过滤满足特定条件的分组。

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

 7．找出选修了所有课程并且每门课程每次考试成绩均及格的学生的学号、姓名。

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

8．找出选修了所有课程并且得到所有课程的学分（即每门课程最少一次考试及格）的学生的学号、姓名。

create view test6_08 as
	select distinct sid, name
	from pub.student s
	where not exists
		((select cid
		from pub.course)
	minus
		(select cid
		from pub.student_course sc
		where s.sid = sc.sid))
	and sid not in
		(select sid
     from(select sid,cid, max(score) max_score
          from pub.student_course
          group by sid,cid)
     where max_score < 60
    )

9． 查询统计2010级、化学与化工学院的学生总学分表，内容有学号、姓名、总学分sum_credit。 

create or replace view test6_09 as
	select t0.sid, t0.name, t1.sum_credit
	from 
		(select distinct sid, name
		from pub.student
		where class = '2010'
		and dname = '化学与化工学院') t0,
		
		(select distinct sid, sum(credit) sum_credit
		from (select distinct sid, cid, max(score) max_score
		from pub.student_course
		group by sid, cid) natural join pub.course
		where max_score >= 60
		group by sid)t1
	where t0.sid = t1.sid

10．查询学生表中每一个姓氏及其人数（不考虑复姓，用到取子串函数substr(string,postion,length)），test6_10有两个列：second_name、p_count

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