Oracle的04作业

查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select s2.sid,s2.score
from (select sid,score from sc where cid = 01)s1,
(select sid,score from sc where cid = 02)s2
where s1.sid = s2.sid and s1.score < s2.score;

查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a1.sid,sname,"平均成绩"
from (select sid,trunc (avg(score),2) "平均成绩"  from sc group by sid having avg(score)> 60) a1,student s 
where a1.sid = s.sid;

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.sid,sname,"选课总数","总成绩"
from student s
left join (select sid,count(cid) "选课总数",sum(score) "总成绩" from sc group by sid) s1
on s.sid = s1.sid

查询"李"姓老师的数量
select count(tid) 
from teacher 
where tname like'李%';

查询学过"张三"老师授课的同学的信息
select * 
from student
where sid in (select sid from sc,course c,teacher t where tname = '张三' and sc.cid = c.cid and c.tid = t.tid); 

查询没学过"张三"老师授课的同学的信息
select * 
from student
where sid not in (select sid from sc,course c,teacher t where tname = '张三' and sc.cid = c.cid and c.tid = t.tid);

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息;
select *
from (select s1.sid 
from (select sid from sc where cid = 01) s1,
(select sid from sc where cid = 02) s2
where s1.sid = s2.sid) a,student s
where a.sid = s.sid

感觉学完mysql之后学习Oracle的知识容易理解懂,这作业也是能完成