SQL基础40题

1、查询“01”课程比“02”课程成绩高的所有学生的学号
select
distinct a.sid
from sc a 
inner join sc b
on a.sid=b.sid
and a.cid = '01'
and b.cid = '02'
where a.score_number>b.score_number

2、查询平均成绩大于60分的同学的

select
sid,
avg(score_number) as avg_score
from sc
group by
sid
HAVING avg(score_number) >60

3、查询所有同学的学号、姓名、选课数、总成绩
select
    student.sid as sid
    ,sname
    ,count(distinct cid) course_cnt
    ,sum(score_number) as total_score
from student
left join sc 
on student.sid=sc.sid
group by student.sid,sname;

4、查询姓“李”的老师的个数；

select
count(tid) as count_tid
from teacher
where tname like '李%'

5、查询没学过“张三”老师课的同学的学号、姓名；
select sid,sname from student where sid not in (
select s.sid from sc s where cid in (
select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname='张三'
));

select
distinct sid,
sname
from 
student
where sid in
(select
distinct a.sid
from sc a
inner join course b
on a.cid = b.cid
where b.tid = '01')

6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
select
sid,
sname from
student
where sid in
(select
distinct
a.sid
from sc a
inner join
sc b
on a.sid=b.sid
and a.cid='01'
and b.cid ='02')

7、查询学过“张三”老师所教的课的同学的学号、姓名
select
distinct sid,
sname
from 
student
where sid in
(select
distinct a.sid
from sc a
inner join course b
on a.cid = b.cid
where b.tid = '01')

8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select
sid,sname
from student
where sid in
(select
distinct
a.sid
from sc a
inner join
sc b
on a.sid=b.sid
and a.cid='01'
and b.cid ='02'
where a.score_number < b.score_number)

9、查询所有课程成绩小于60分的同学的学号、姓名；
select
sid,
sname from student where sid in
(
select
distinct sid
from sc
group by
sid
having max(score_number) < 60
)

10、查询没有学全所有课的同学的学号、姓名；
select
a.sid,
a.sname
from
student a
left join
(select
sid
from sc
group by sid
having count(distinct cid) = (select count(distinct cid) from course)) b
on a.sid =b.sid 
where b.sid is null

11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
select
sid,
sname
from student
where sid in
(select
distinct
b.sid
from sc a
left join
sc b
on a.cid=b.cid
and a.sid = '01'
and b.sid <> '01'
where b.cid is not null)

12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
select
sid,sname
from student
where sid in
(select
b.sid
from sc a
left join
sc b
on a.cid=b.cid
and a.sid = '01'
and b.sid <> '01'
where b.cid is not null
group by
b.sid
having count(distinct b.cid) = (select count(distinct cid) from sc where sid ='01'))

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
select
distinct b.cid,c.cname,
b.max_score,
b.min_score,
b.avg_score,
b.rate
from
(select
a.cid,
sum(case when a.rn1 = 1 then a.score end) as max_score,
sum(case when a.rn2 = 1 then a.score end) as min_score,
sum(a.avg_score) as avg_score,
round(count(case when a.score>=60 then a.cid else null end)/count(a.cid),2) as rate
from
(select
cid,
score,
rank() over(partition by cid order by score DESC) as rn1,
rank() over(partition by cid order by score ASC) as rn2,
avg(score) over(partition by cid) as avg_score
from sc) a
group by a.cid) b
inner join
course c
on b.cid=c.cid

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select
cid,
avg(score) as avg_score,
count(if(score>=60,1,null))/count(1) as pass_rate
from sc 
GROUP BY cid
order by
avg_score,
pass_rate desc

20、查询学生的总成绩并进行排名
select
a.sid,
a.all_score,
rank() over(order by a.all_score desc) as rn 
from
(select
sid,
sum(score) all_score
from sc
group by sid) a

21、查询不同老师所教不同课程平均分从高到低显示

select
c.tname,
b.cname,
avg(a.score) as avg_score
from sc a
inner join 
course b
on a.cid = b.cid
inner join
teacher c
on b.tid =c.tid
group by
c.tname,
b.cname
order by avg_score desc

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select
distinct b.sid,a.cid,
b.sname,
b.sage,
b.ssex,
a.score,
a.rn
from
(select
sid,
cid,
score,
dense_rank() over(partition by cid order by score desc) as rn
from sc) a
inner join 
student b
on a.sid = b.sid
and a.rn in (2,3)
order by cid

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select
b.cid,
b.cname,
count(if(a.score >=0 and a.score < 60,1,null)) as '[0-60]',
count(if(a.score >=0 and a.score < 60,1,null))/count(a.cid) as '[0-60]_rate',
count(if(a.score >=60 and a.score < 70,1,null)) as '[60-70]',
count(if(a.score >=60 and a.score < 70,1,null))/count(a.cid) as '[60-70]_rate',
count(if(a.score >=70 and a.score < 85,1,null)) as '[70-85]',
count(if(a.score >=70 and a.score < 85,1,null))/count(a.cid) as '[70-85]_rate',
count(if(a.score >=85 and a.score < 100,1,null)) as '[85-100]',
count(if(a.score >=85 and a.score < 100,1,null))/count(a.cid) as '[85-100]_rate'
from sc a
inner join
course b
on a.cid = b.cid
group by
b.cid,
b.cname

24、查询学生平均成绩及其名次
select
a.sid,
a.avg_score,
dense_rank() over(order by a.avg_score desc) as rn
from
(select
sid,
avg(score) as avg_score
from sc
group by sid) a

25、查询各科成绩前三名的记录
select
a.sid,
a.cid,
a.score
from
(select
sid,
cid,
score,
dense_rank() over(partition by cid order by score desc) as rn
from sc) a
where a.rn<=3
order by cid,score desc

26、查询每门课程被选修的学生数
select
cid,
count(distinct sid) as number
from sc
group by
cid

27、查询出只选修了两门课程的全部学生的学号和姓名

select
a.sid,
a.sname
from student a
inner join sc b
on a.sid=b.sid
group by
a.sid,sname
having count(distinct b.cid) =2

28、查询男生、女生人数
select
ssex,
count(distinct sid) as number
from student
group by ssex

29、查询名字中含有"风"字的学生信息
select * from student
where sname rlike '风'

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

select sname ,count(sname) cnt 
from student 
group by sname 
having count(sname)>=2