SQL经典50题（部分）

1.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
select c.,a.score,b.score
from (select sid,score from sc where cid=01) a ,(select sid,score from sc where cid=02) b,student c
where a.sid=b.sid
and a.sid=c.sid
and a.score>b.score
2. 查询同时存在" 01 “课程和” 02 “课程的情况
select *
from (select sid,score from sc where cid=01) a ,(select sid,score from sc where cid=02) b
where a.sid=b.sid
3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
select *
from (select sid,score from sc where cid=01) a left join (select sid,score from sc where cid=02) b
on a.sid=b.sid
4.查询不存在” 01 “课程但存在” 02 "课程的情况
select *
from sc
where sid not in(select sid from sc where cid=01)
and cid=02
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select student.sid,sname,avg(score)
from student,sc
where student.sid=sc.sid
group by sid,sname
having avg(score)>=60
6.查询在 SC 表存在成绩的学生信息
select distinct student.
from student,sc
where student.sid=sc.sid
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.sid,sname,count,sumscore
from student a left join (select sum(score) sumscore,sid,count(1) count from sc group by sid) b
on a.sid=b.sid
8.查有成绩的学生信息
select *
from student
where sid in(select sid from sc)
9.查询「李」姓老师的数量
select count(1)
from teacher
where tname like ‘李%’
10.查询学过「张三」老师授课的同学的信息
select student.*
from student,sc,course,teacher
where student.sid=sc.sid
and sc.cid=course.cid
and course.tid=teacher.tid
and tname=‘张三’
11.查询没有学全所有课程的同学的信息
select *
from student
where sid not in (select sid from sc group by sid having count(1)=(select count(cid) from course))
12.查询至少有一门课与学号为” 01 "的同学所学相同的同学的信息
select distinct student.*
from student,sc
where student.sid=sc.sid
and cid in (select cid from sc where sid=01)
13.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select *
from student
where sid in(select sid from sc
where cid in(select cid from sc where sid=‘01’)
and sid<>‘01’
group by sid
having count(cid)=(select count(cid) from sc where sid=‘01’))
14.查询没学过"张三"老师讲授的任一门课程的学生姓名
select *
from student
where sid not in
(select sid from sc where cid in
(select cid from course where tid=
(select tid from teacher where tname=‘张三’)))
15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select sid ,sname ,(select avg(score) from sc c where a.sid = c.sid)
from student a
where (select count(1) from sc b where a.sid = b.sid and score<60) >=2
16. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.
from student,sc
where student.sid=sc.sid
and cid=01
and score<60
order by score desc
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select student.,course.*,score,(select avg(score) from sc where student.sid = sc.sid) a
from student ,sc ,course
where student.sid = sc.sid and sc.cid= course.cid
order by a desc
18.查询各科成绩最高分、最低分和平均分：
–以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
–要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select x. ,(select count(distinct sid) from sc c where x.cid=c.cid and score>=60)/counts,
(select count(distinct sid) from sc d where x.cid=d.cid and score>=70 and score<80)/counts,
(select count(distinct sid) from sc e where x.cid=e.cid and score>=80 and score<90)/counts,
(select count(distinct sid) from sc f where x.cid=f.cid and score>=90)/counts
from (select cname ,max(score),min(score),avg(score),b.cid,count(distinct sid)counts
from course a,sc b
where a.cid = b.cid
group by cname ,b.cid
order by count(distinct sid) desc ,b.cid) x
*19.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select a.cid, a.sid, a.score, count(b.score)+1
from sc as a
left join sc as b
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid,count(b.score)+1
20.按各科成绩进行排序，并显示排名， Score 重复时合并名次
select a.cid, a.sid, a.score, count(distinct b.score)+1
from sc as a
left join sc as b
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid,count(distinct b.score)+1
21.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
22. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
24.查询各科成绩前三名的记录
select *
from sc a
where (select count(1) from sc b where a.cid=b.cid and a.score<b.score)< 3
order by a.cid,a.score desc
25.查询每门课程被选修的学生数
select cid,count(1)
from sc
group by cid
26.查询出只选修两门课程的学生学号和姓名
select sid,sname
from student
where sid in (select sid from sc group by sid having count(cid) =2)
27.查询男生、女生人数
select ssex,count(1)
from student
group by ssex
28.查询名字中含有「风」字的学生信息
select *
from student
where sname like ‘%风%’
29.查询同名同性学生名单，并统计同名人数
select sname,ssex,count(1)
from student
group by sname,ssex
having count(1)>1
30.查询 1990 年出生的学生名单
select *
from student
where sage between ‘1990-1-1’ and ‘1990-12-31’
31.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,avg(score)
from sc
group by cid
order by avg(score) desc ,cid
32.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select sc.sid ,sname ,avg(score)
from sc,student
where sc.sid = student.sid
group by sc.sid ,sname
having avg(score)>85
33.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select sname ,score
from student ,sc ,course
where student.sid = sc.sid and sc.cid = course.cid
and score<60 and cname =‘数学’
*34.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select *
from student a left join sc
on a.sid = sc.sid
left join course
on sc.cid = course.cid
35.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select sname,cname,score
from student,sc,course
where student.sid=sc.sid
and sc.cid=course.cid
and score>70
36.查询不及格的课程
select *
from student a,sc,course
where a.sid= sc.sid and sc.score<60 and sc.cid = course.cid;
37.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select sid ,sname
from student
where sid in (select sid from sc where cid =‘01’ and score>80)
38.求每门课程的学生人数
select cid,count(1)
from sc
group by cid
*39.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
40.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.,score
from student a,sc b
where a.sid = b.sid
and (b.cid,b.score) in (
select course.cid ,max(score)
from course ,sc ,teacher
where course.cid = sc.cid and course.tid = teacher.tid and tname = ‘张三’
group by course.cid)
41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.sid,a.cid ,a.score
from sc a
where a.score in (select b.score from sc b where a.cid <>b.cid)
42.查询每门功成绩最好的前两名
select a.sid,a.cid
from sc a
where (select count(1) from sc b where a.cid= b.cid and a.score<b.score)<2
order by a.cid
43.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select cid,count(1)
from sc
group by cid
having count(1)>5
44.检索至少选修两门课程的学生学号
select sid,count(1)
from sc
group by sid
having count(1)>=2
45.查询选修了全部课程的学生信息
select *
from student
where sid in (
select sid
From sc
group by sid
having count(1)>=(select count(1) from course))