ORACLE统计题

考题1：
两个表，班级1表（t_one_class），班级2表（t_two_class），两表的表结构一样。
字段及类型如下：
字段        类型
SID            NUMBER
SNAME        VARCHAR2(20)
SUBJECT        VARCHAR2(20)
SCORE        NUMBER
班级1表数据：
SID    SNAME    SUBJECT    SCORE
1    A1        语文    90
2    A1        数学    90
3    A1        英文    80
4    A2        语文    80
5    A2        数学    90
6    A2        英文    60

班级2表数据：
SID    SNAME    SUBJECT    SCORE
1    A3        语文    90
2    A3        数学    80
3    A3        英文    70
4    A4        语文    80
5    A4        数学    90
6    A4        英文    60

问题A：查出每个班的单科平均成绩，排序规则按班级、学科排序，如下
CLASS    SUBJECT        SCORE
ONE        语文        85
ONE        数学        90
ONE        英文        70
TWO        语文        85
TWO        数学        85
TWO        英文        65

问题B：查出2个班的集合中单科成绩最好的同学，排序规则按学科、班级排序，如下
CLASS    SNAME    SUBJECT        SCORE
ONE        A1        语文        90
TWO        A3        语文        90
ONE        A1        数学        90
ONE        A2        数学        90
TWO        A4        数学        90
ONE        A1        英文        80

问题C：查询出1班每个学科最好的学生及2班总成绩最好的学生及成绩，如下
SNAME    SUBJECT        SCORE
A1        语文        90
A2        数学        90
A1        英文        80
A3        总成绩        240

sql提交：
建表语句、建数据语句、查询语句
 

考题2
沿用上一题的表及数据写一段PLSQL代码
打印出每个学生的学习评分
例如A1学生需打印出：
1班的学生A1的语文成绩90分，数学成绩90分，英文成绩80分。单科成绩最低分80分，总分数：260分，最后评分：优秀。
评判标准：
总分>=230分并且单科最低分>=70分的则为优秀，反之则为一般

提交内容：执行的PLSQL代码

 

--1.创建表代码
  create table t_one_class
  (
     sid number,
     sname varchar2(20),
     subject varchar2(20),
     score number
  )
--2.插入数据
 insert into t_one_class values (1,'A1','语文',90);
 insert into t_one_class values (2,'A1','数学',90);
 insert into t_one_class values (3,'A1','英语',80);
 insert into t_one_class values (4,'A2','语文',80);
 insert into t_one_class values (5,'A2','数学',90);
 insert into t_one_class values (6,'A2','英语',60);
 
 

 select * from t_one_class;
 --3.创建two表 
  create table t_two_class 
  as
  select * from t_one_class 
  where 1 = 0;

 insert into t_two_class values (1,'A3','语文',90);
 insert into t_two_class values (2,'A3','数学',80);
 insert into t_two_class values (3,'A3','英语',70);
 insert into t_two_class values (4,'A4','语文',80);
 insert into t_two_class values (5,'A4','数学',90);
 insert into t_two_class values (6,'A4','英语',60);
 
 select * from t_two_class;
 
 --问题A
 select 'one' class,a.subject,avg(a.score) score from t_one_class a 
 group by a.subject 
 union all
 select 'two' class,b.subject,avg(b.score) score from t_two_class b
 group by b.subject
 
 --问题B
 create or replace view a_b as
 select 'one' class,a.* from t_one_class a
 union
 select 'two' class,b.* from t_two_class b

select * from a_b;

select a.class,a.sname,s.subject,s.kk from a_b a ,(select SUBJECT,max(SCORE) kk from a_b  group by SUBJECT) s  
where  a.SUBJECT = s.subject and a.SCORE = s.kk;
  
 --问题C
  select a.sname,a.subject,a.score from t_one_class a, 
  (select subject,max(score) d from t_one_class  group by subject) b 
  where a.subject = b.subject and a.score = b.d
  union
  select sname, '总成绩',sum(score)  ss from t_two_class  group by sname 
  having  sum(score) >=all(select  sum(score)  from t_two_class group by sname)
 --考题二 
  create or replace view a_b as
  select 'one' class,a.* from t_one_class a
  union
  select 'two' class,b.* from t_two_class b
  select * from a_b;
  
  
  declare
      cursor a is 
         select  a.sname,sum(a.SCORE) sumgrade,min(a.SCORE) mingrade from a_b a 
         group by a.SNAME;
      cursor b(nam varchar2) is
         select score from a_b where sname = nam and subject = '语文';
     cursor c(nam varchar2) is
         select score from a_b where sname = nam and subject = '数学';
     cursor d(nam varchar2) is
         select decode(class,'one','1班','two','2班','其它') bj,score from a_b where sname = nam and subject = '英语';
      aa a%rowtype;
      bb a_b.SCORE%type;
      cc a_b.SCORE%type;
      dd d%rowtype;
  begin
      open a;
         loop
           fetch a into aa;
           exit when a%notfound;
              open b(aa.sname);
                  loop
                    fetch b into bb;
                    exit when b%notfound;
                       
                        open c(aa.sname);
                        loop
                        fetch c into cc;
                       exit when c%notfound; 
                          
                        open d(aa.sname);
                        loop
                        fetch d into dd;
                        exit when d%notfound;
                     
                       
                       end loop;
                       close d; 
                       
                       end loop;
                       close c; 
                    
                     
                  end loop;
              close b;
           
                        if  aa.sumgrade>=230 and aa.mingrade >=70
                       then
                       dbms_output.put_line(dd.bj||'的学生'||aa.sname||'  语文成绩'||bb||'分,'||' 数学成绩'||cc||'分,'||'  英语成绩'||
                       dd.score||'分。'||'  单科最低分'||aa.mingrade||'分,'||'  总分:'||aa.sumgrade||'分,'||'  最后评分:'||'优秀'||'。');
                       else
                       dbms_output.put_line(dd.bj||'的学生'||aa.sname||'  语文成绩'||bb||'分,'||' 数学成绩'||cc||'分,'||'  英语成绩'||
                       dd.score||'分。'||'  单科最低分'||aa.mingrade||'分,'||'  总分:'||aa.sumgrade||'分,'||'  最后评分:'||'一般'||'。');  
                       end if;
         end loop;
      close a;
  end;