Cc (C#,CN ) C#,CN 分别代表课程编号、课程名称
SCsc ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
1 列出既学过“1”号课程,又学过“2”号课程的学生且“1”号课成绩比“2”号成绩高的学生学号
Select SC1.Sid,SC1.g as "1号课成绩",SC2.g as "2号课成绩" FROM SCsc SC1,Cc C1,SCsc SC2,Cc C2 Where SC1.Cid=C1.Cid AND C1.cn='1' AND SC2.Cid=C2.Cid AND C2.cn='2' AND SC1.g>SC2.g;
2 利用pl/sql编程解决
declare
----------列出既学过“1”号课程,又学过“2”号课程的所有学生编号----------------
cursor c_cursor is select sid from scsc where cid in ('1','2') group by sid having count(cid) =2;
v_c1G scsc.g%type;
v_c2G scsc.g%type;
v_sql varchar2(100);
begin
for v_sid in c_cursor loop--open c_cursor
v_sql:='select g from scsc where sid='||v_sid.sid||' and cid=1';
execute immediate v_sql into v_c1G;--execute dynamic sql
v_sql:='select g from scsc where sid='||v_sid.sid||' and cid=2';
execute immediate v_sql into v_c2G;--execute dynamic sql
if(v_c1G > v_c2G) then
--列出既学过“1”号课程,又学过“2”号课程的学生
--且“1”号课成绩比“2”号成绩高的学生学号
dbms_output.put_line('sid='||v_sid.sid);
end if;
end loop;
end;