数据库---学生选课查询案例---经典查询题

语句见下方

–1、查询"c10"课程比"c20"课程成绩高的学生的信息及课程分数
select s.,sc1.score as c10成绩,sc2.score as c20成绩 --sql 92 写法
from student s,sc sc1,sc sc2
where sc1.s# = sc2.s#
and s.s# = sc1.s#
and (sc1.c# = ‘c10’ and sc2.c# = ‘c20’)
and sc1.score > sc2.score;
–sql 99 写法
select s.,sc1.score as c10成绩,sc2.score as c20成绩 from student s join sc sc1
on s.s# = sc1.s#
join sc sc2 on sc1.s# = sc2.s#
where sc1.c# = ‘c10’ and sc2.c# = ‘c20’ and sc1.score > sc2.score;

–2、查询"c10"课程比"c20"课程成绩低的学生的信息及课程分数
select s.*,sc1.score as c10成绩,sc2.score as c20成绩 from student s join sc sc1
on s.s# = sc1.s#
join sc sc2 on sc1.s# = sc2.s#
where sc1.c# = ‘c10’ and sc2.c# = ‘c20’ and sc1.score < sc2.score;

–3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,sname,avg_sc from student s join
(select s#,avg(score) avg_sc from sc
group by s# having avg(score) >= 60) t
on s.s# = t.s#
order by s.s#;

–4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select s.s#,sname,avg_sc from student s join
(select s#,avg(score) avg_sc from sc group by s# having avg(score) < 60) t
on s.s# = t.s#;

–5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.s#,sname,选课总数,总成绩 from student s join
(select s#,count(c#) as 选课总数,sum(score) as 总成绩 from sc group by s#) t
on s.s# = t.s#
order by s.s#;

–6、查询"李"姓老师的数量
select count(tname) as 李姓老师 from teacher group by tname having tname like ‘李%’;

–7、查询学过"张三"老师授课的同学的信息
select s.* from student s join sc on s.s# = sc.s#
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where tname = ‘张三’;

–8、查询没学过"张三"老师授课的同学的信息
select s.* from student s where s.s# not in
(select s.s# as tid from student s join sc on s.s# = sc.s# --学过张三老师的课程的同学
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where tname = ‘张三’);

–9、查询学过编号为"c40"并且也学过编号为"c50"的课程的同学的信息
select s.* from student s join sc sc1 on s.s# = sc1.s#
join sc sc2 on sc1.s# = sc2.s#
where sc1.c# = ‘c40’ and sc2.c# = ‘c50’;

–10、查询学过编号为"c40"但是没有学过编号为"c50"的课程的同学的信息
select s.* from student s
where s# in (select s# from sc sc1 where c# = ‘c40’) --学过c40
and s# not in (select sc.s# from sc where c# = ‘c50’); --没学过c50

–11、查询没有学全所有课程的同学的信息
select distinct s.* from student s join sc
on s.s# = sc.s#
where s.s# in(select s# from sc group by s# having count(c#) < 5);

–12、查询至少有一门课与学号为"s101"的同学所学相同的同学的信息
select distinct s.* from student s join sc sc1 on s.s# = sc1.s#
where sc1.c# in (select c# from sc where s# = ‘s101’)
and s.s# <> ‘s101’;

–13、查询和"s101"号的同学学习的课程完全相同的其他同学的信息 --分条解决条件 有点复杂，应该有简单一些的写法

select s.* from student s where s.s# in
(select distinct s# from sc where s# <> ‘s101’ --编号不为s101
and c# in (select c# from sc where s# = ‘s101’) --选修课程在s101的选修课程里
and s# not in (select s# from sc where c# not in (select c# from sc where s# = ‘s101’))
and s# in (select s# from sc group by s# --选修课程都再s101的选修课程里的
having count(c#) = (select count(c#) from sc where s# = ‘s101’)));
–选修课程数与s101相同的学生编号

–14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s.* from student s where s.s# not in
(select s.s# as tid from student s join sc on s.s# = sc.s# --学过张三老师的课程的同学
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where tname = ‘张三’);

–15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.s#,sname,t.avg_score from student s
join (select s#,avg(score) as avg_score from sc group by s#) t
on s.s# = t.s#
where t.s# in (select s# from sc where score < 60 group by s# having count(s#) >= 2);

–16、检索"c10"课程分数小于60，按分数降序排列的学生信息
select s.*,sc.score as c1_score from student s join sc on s.s# = sc.s#
where sc.c# = ‘c10’ and score < 60 order by score desc;

–17、查询各科成绩前三名的记录 分科目查 c10 到 c50 以c10为例
select t.s#,s.sname,t.c#,t.score from student s join
(select * from sc where c# = ‘c10’ order by score desc) t
on s.s# = t.s#
where rownum <= 3;

–18、查询每门课程被选修的学生数
select t.c#,cname,t.count_s# from course c
join (select c#,count(s#) as count_s# from sc group by c#) t
on c.c# = t.c#;

–19、查询出只有两门课程的全部学生的学号和姓名 --改为4门课程
select distinct s.s#,sname from student s join sc on s.s# = sc.s#
where s.s# in (select s# from sc group by s# having count(c#) = 4);

–20、查询男生、女生人数
select ssex,count(s#) from student group by ssex;

–21、查询名字中含有"风"字的学生信息 改为月
select * from student where sname like ‘%月%’;

–22、查询同名同性学生名单，并统计同名人数

select sname ,count(sname) as 人数 from --同名同性人数
(select sname,ssex as sse from student s where sname in
(select sname from student group by sname having count(sname) >1 /同名/)) --同名同性
group by sname ;

–23、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student where trunc(to_date(sage),‘YEAR’) = ‘1-1月-1990’;

–24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c#,avg(score) from sc group by c# order by avg(score) desc,c#;

–25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.s#, sname ,avg_score from student s join
(select s#, avg(score) as avg_score from sc group by s#) t on s.s# = t.s#
where avg_score >= 85;

–26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select sname, score from student s join sc on s.s# = sc.s#
join course c on sc.c# = c.c#
where c.cname = ‘数学’ and score < 60;

–27、查询所有学生的课程及分数情况；
select sname, cname, score from student s join sc on s.s# = sc.s#
join course c on sc.c# = c.c# ;

–28、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select sname, cname, score from student s join sc on s.s# = sc.s#
join course c on sc.c# = c.c#
where sc.score > 70;

–29、查询不及格的课程
select sname, cname, score from student s join sc on s.s# = sc.s#
join course c on sc.c# = c.c#
where sc.score < 60;

–30、查询课程编号为c10且课程成绩在80分以上的学生的学号和姓名；
select s.s# ,sname from student s join sc on s.s# = sc.s#
where sc.c# = ‘c10’ and sc.score > 80;

–31、求每门课程的学生人数
select c.c# ,cname, count_s# from course c
join (select c# ,count(s#) as count_s# from sc group by c#) t
on c.c# = t.c#;

–32、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
–普通做法
select sname ,sc.score from student s join sc on s.s# = sc.s#
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where t.tname = ‘张三’ --找到选修了张三老师的同学
and sc.score = --与张三老师的学生分数最高的那个进行匹配
(select max(score) from student s join sc on s.s# = sc.s#
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where t.tname = ‘张三’) ;
–使用rownum
select sname,sc_score from (select sname,sc.score as sc_score from student s
join sc on s.s# = sc.s#
join course c on c.c# = sc.c#
join teacher t on t.t# = c.t#
where t.tname = ‘张三’
order by score desc)
where rownum = 1

–33、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.s#,s.sname, sc1.c# ,sc1.score from student s join sc sc1
on s.s# = sc1.s#
join sc sc2 on sc1.s# = sc2.s#
where sc1.c# <> sc2.c# --不同课程
and sc1.score = sc2.score; --分数相同

–34、查询每门成绩最好的前两名 还是以c10为例
select t.s#,s.sname,t.c#,t.score from student s join
(select * from sc where c# = ‘c10’ order by score desc) t
on s.s# = t.s#
where rownum <= 2;

–35、检索至少选修两门课程的学生学号
select s.s# ,sname, count_c# from student s
join (select s#,count(c#) as count_c# from sc group by s# having count(c#) >= 2) t
on t.s# = s.s#
order by t.count_c# desc,s.s#;

–36、查询选修了全部课程的学生信息
select s.s#, sname, count_c# from student s
join (select s#,count(c#) as count_c# from sc group by s# ) t
on t.s# = s.s#
where count_c# = (select count(c#) from course); --选修所有课程

–37、查询各学生的年龄

select sname, round(months_between(sysdate, sage)/12)
as age from student;