SQL语言练习3

题目：根据《SQL语言练习1》中的数据表结构，以存储过程的形式改成SQL查询语句。

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

(select * from score sc where sc.sno in (select s.sno
  from score s
 group by s.sno having count(s.cno)>=2))
 minus
((select * from score sc where sc.degree in(select max(s.degree) from score s group by s.cno)));

----------------------------------------------------------------------------------------------------------------------------------------------------

declare
   cursor maxdegree is
      select max(s.degree) from score s group by s.cno;
   maxdeg score.degree%type;
   
   cursor screcord is
      select * from score;
   screc score%rowtype;
   
   cursor scsno is
      select s.sno from score s group by s.sno having count(s.cno)>=2;
   scs score.sno%type;
   
   j integer:=0;
begin
   open screcord;
   loop
       fetch screcord into screc;
       exit when screcord%notfound;
       open scsno;
       loop
          fetch scsno into scs;
          exit when scsno%notfound;
               if screc.sno=scs then
                  open maxdegree;
                  loop
                     fetch maxdegree into maxdeg;
                     exit when maxdegree%notfound;
                     if maxdeg=screc.degree
                     
                     
                      then
                        j:=1;
                        exit;
                     else
                        fetch maxdegree into maxdeg;   
                     end if;  
                  end loop;
                  if j=0 then
                       dbms_output.put_line(screc.sno);
                       dbms_output.put_line(screc.cno);
                       dbms_output.put_line(screc.degree);
                  end if; 
                  close maxdegree;
              end if;
 
         end loop;
         close scsno;
   end loop;
end;

--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score s where s.degree>(select s1.degree from score s1 where s1.sno=‘109‘ and s1.cno=‘3-105‘)
----------------------------------------------------------------------------------------------------------------------------------------------------
declare 
    cursor mydegree is
       select s1.degree from score s1 where s1.sno=‘109‘ and s1.cno=‘3-105‘;
    sadegree score.degree%type;
    cursor scord is
       select * from score;
    sascord scord%rowtype;
begin
     open mydegree;
     fetch mydegree into sadegree;
     open scord;
     fetch scord into sascord;
     loop
         if scord%notfound then
           dbms_output.put_line(‘输完‘);
           exit;
         else
             if sascord.degree>sadegree then
                dbms_output.put_line(sascord.sno);
                dbms_output.put_line(sascord.cno);
                dbms_output.put_line(sascord.degree);
                fetch scord into sascord;
              else 
                fetch scord into sascord;  
             end if;
        end if;
    end loop;
end;

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname, sbirthday
  from student
where to_char(sbirthday,‘yyyy‘)=(select to_char(st.sbirthday,‘yyyy‘) from student st where st.sno=‘107‘ )
----------------------------------------------------------------------------------------------------------------------------------------------------
declare 
   birthday student.sbirthday%type;
   
   cursor myrecord is
       select * from student;
   strecord student%rowtype;
begin
   select st.sbirthday into birthday from student st where st.sno=‘107‘;
   open myrecord;
   fetch myrecord into strecord;
   loop
       if myrecord%notfound then
           dbms_output.put_line(‘输完‘);
           exit;
       else
           if to_char(strecord.sbirthday,‘yyyy‘)=to_char(birthday,‘yyyy‘) then
              dbms_output.put_line(strecord.sno);
              dbms_output.put_line(strecord.sname);
              dbms_output.put_line(strecord.sbirthday);
              fetch myrecord into strecord;
           else
              fetch myrecord into strecord; 
           end if;
       end if;
   end loop; 
end;