1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
select s.*, a.score, b.score from student s join sc a on s.sid = a.sid and a.cid = '01' join sc b on s.sid = b.sid and b.cid = '02' where a.score > b.score;
select student.*,a.score as 01_score,b.score as 02_score from student join sc a on a.sid=student.sid join sc b on b.sid=student.sid where a.cid='01' and b.cid='02' and a.score > b.score;
select s.*, chinese, math from student s join ( select sid, max(case when cid = '01' then score end) as chinese, max(case when cid = '02' then score end) as math from sc group by sid ) a on s.sid = a.sid where chinese > math;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
select s.*, a.score, b.score from student s join sc a on s.sid = a.sid and a.cid = '01' join sc b on s.sid = b.sid and b.cid = '02' where a.score < b.score;
select s.*, a.score, b.score from student s join sc a on s.sid = a.sid join sc b on s.sid = b.sid where a.cid = '01' and b.cid = '02' and a.score < b.score;
select s.*, chinese, math from student s join ( select sid, max(case when cid = '01' then score end) as chinese, max(case when cid = '02' then score end) as math from sc group by sid ) a on s.sid = a.sid where a.chinese < a.math;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
select s.sid, s.sname, round(avg_score, 2) avg_score from student s join ( select sid, avg(score) as avg_score from sc group by sid having avg(score) >= 60 ) t on s.sid = t.sid;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
(包括有成绩的和无成绩的)
select s.sid, s.sname, nvl(avg_score, 0) as avg_score from student s left join ( select sid, round(avg(score), 2) avg_score from sc group by sid ) t on s.sid = t.sid where t.sid is null or t.avg_score < 60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
select s.sid, s.sname, nvl(t.num, 0) as num, nvl(t.total_score, 0) as total_score from student s left join ( select sid, count(1) num, sum(score) total_score from sc group by sid ) t on s.sid = t.sid;
6、查询"李"姓老师的数量:
select count(1) from teacher where tname like '李%';
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)