搞定SQL面试(2)

• 16、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
  按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

select t.sid as '学生ID',
(select score from sc where sid=t.sid and cid='002') as '语文',
(select score from sc where sid=t.sid and cid='003') as '数学',
(select score from sc where sid=t.sid and cid='004') as '英语',
count(t.cid) as '有效课程数',
avg(t.score) as '有效平均分'
from sc t
group by t.sid
order by avg(t.score);

• 17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select cid as '课程ID',max(score) as '最高分',min(score) as '最低分'
from sc group by cid order by cid;

• 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select sc.cid,c.cname,round(IFNULL(avg(sc.score),0),2) as '平均成绩',
round(100*sum(case when ifnull(sc.score,0)>=60 then 1 else 0 end)/count(*),2) as '及格率'
from sc sc,course c
where sc.cid=c.cid
group by 1,2
order by '及格率' desc;

• 19、查询如下课程平均成绩和及格率的百分数(备注：需要在1行内显示):
  企业管理（002），OO&UML （003），数据库（004）

select
sum(case when cid='002' then score else 0 end)/sum(case when cid='002' then 1 else 0 end) as '企业管理平均分',
sum(case when cid='002' and score>=60 then 1 else 0 end)/sum(case when cid='002' then 1 else 0 end) as '企业管理及格率',
sum(case when cid='003' then score else 0 end)/sum(case when cid='003' then 1 else 0 end) as 'OO&UML平均分',
sum(case when cid='003' and score>=60 then 1 else 0 end)/sum(case when cid='003' then 1 else 0 end) as 'OO&UML及格率',
sum(case when cid='004' then score else 0 end)/sum(case when cid='004' then 1 else 0 end) as '数据库平均分',
sum(case when cid='004' and score>=60 then 1 else 0 end)/sum(case when cid='004' then 1 else 0 end) as '数据库及格率'
from sc;

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

select c.cid,max(c.cname) as 'cname',max(t.tid) as 'tid',max(t.tname) as 'tname',
avg(score) as 'avgscore'
from sc sc,course c,teacher t
where sc.cid=c.cid and c.tid=t.tid
group by c.cid
order by avgscore desc;

• 21、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select sc.cid,max(c.cname) as '课程名称',
sum(case when sc.score between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when sc.score between 70 and 85 then 1 else 0 end) as '[85-70]',
sum(case when sc.score between 60 and 70 then 1 else 0 end) as '[70-60]',
sum(case when sc.score between 0 and 100 then 1 else 0 end) as '[ <60]'
from sc sc,course c
where sc.cid=c.cid
group by sc.cid

• 22、查询学生平均成绩及其名次；

select s.Sid,s.Sname,T2.AvgScore,
    (select COUNT(AvgScore) from 
    (select Sid,AVG(Score) as 'AvgScore' from SC group by Sid) as T1 
    where T2.AvgScore<T1.AvgScore)+1 as 'Rank'
from 
    (select Sid,AVG(Score) as 'AvgScore' from SC
    group by Sid) as T2,
    Student s
where s.Sid=T2.Sid
order by AvgScore desc

• 23、查询各科成绩前三名的记录:(不考虑成绩并列情况)

select sc.cid,c.cname,sc.sid,s.sname,sc.score
from student s,sc sc,course c
where sc.cid=c.cid and sc.sid=s.sid and sc.score in
(
    select score from sc sc2
    where sc.cid=sc2.cid
    order by score desc
)
order by sc.cid,sc.Score desc

• 24、查询每门课程被选修的学生数；

select sc.cid,max(c.cname) as 'CNAME',count(distinct sc.sid) as 'studentCount'
from sc sc,course c
where sc.cid=c.cid
group by sc.cid;

• 25、查询出只选修了一门课程的全部学生的学号和姓名；

select s.sid,s.sname
from student s
where s.sid in
(select sc.sid from sc sc
    group by sc.sid
    having count(distinct sc.cid)=3)

• 26、查询男生、女生的人数

select COUNT(sid) as 'BoysCount' from Student s where s.Ssex='男';
select COUNT(sid) as 'GirlsCount' from Student s where s.Ssex='女'

• 27、查询姓“张”的学生名单；

select s.sid,s.Sname 
from Student s 
where s.Sname like '张%'

• 28、查询同名同姓学生名单，并统计同名人数；

select s.Sname,COUNT(Sname) as 'SameCount'
from Student s
group by s.Sname
having COUNT(Sname)>1

• 29、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select sc.cid,AVG(sc.Score) as 'AvgScore' 
from SC sc
group by sc.cid
order by AvgScore asc,cid desc

• 30、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select sc.sid,s.Sname,AVG(sc.Score) as 'AvgScore' 
from Student s,SC sc
where s.sid=sc.sid
group by sc.sid,s.Sname
having AVG(sc.Score)>85

• 31、查询课程名称为“数学”，且分数低于60的学生姓名和分数

select s.Sname,sc.Score from Student s,SC sc,Course c
where s.Sid=sc.Sid and sc.Cid=c.Cid and c.Cname='数学' and sc.Score<60

• 32、查询所有学生的选课情况；

select s.Sid,s.Sname,c.Cid,c.Cname from Student s,SC sc,Course c
where s.Sid=sc.Sid and c.Cid=sc.Cid
order by s.Sid

• 33、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

select distinct s.sid,s.sname,c.cname,sc.score
from student s,sc sc,course c
where s.sid=sc.sid and sc.cid=c.cid and sc.score>=70;

• 34、查询不及格的课程，并按课程号从大到小排列；

select distinct sc.cid,c.cname from sc sc,course c
where sc.cid=c.cid and sc.score<60
order by sc.cid desc;

• 35、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名

select sc.sid,s.sname,sc.score
from student s,sc sc
where s.sid=sc.sid and sc.cid='003' and sc.score>=80;

• 36、求选了课程的学生人数（超简单的一题）

select count(distinct sid) as 'studentCount' from sc;

• 37、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select s.sid,s.sname,sc.score
from student s,sc sc,course c,teacher t
where s.sid=sc.sid and sc.cid=c.cid and c.tid=t.tid and t.tname='杨艳'
and sc.score=
(
    select max(sc2.score) from sc sc2 where sc.cid=sc2.cid
)

文章转载自https://www.cnblogs.com/edisonchou/p/3878135.html
欢迎加入数据分析交流群(加群备注博客园)

转载于:https://www.cnblogs.com/linxiaochi/p/9650356.html