题目：根据《SQL语言练习1》中的数据表结构，以存储过程的形式改成SQL查询语句。
--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
(select * from score sc where sc.sno in (select s.snofrom 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)));
----------------------------------------------------------------------------------------------------------------------------------------------------
declarecursor 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;