mysql平均分 面试_经典mysql面试题 - 学生成绩

现有 student(sid.sname)学生表，course(cid,cname)课程表，sc(sid,cid,score)成绩表。CREATE TABLE `student` (

`sid` int(11) NOT NULL AUTO_INCREMENT,

`sname` varchar(255) DEFAULT NULL,

PRIMARY KEY (`sid`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course` (

`cid` int(11) NOT NULL AUTO_INCREMENT,

`cname` varchar(255) DEFAULT NULL,

PRIMARY KEY (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sc` (

`scid` int(11) NOT NULL AUTO_INCREMENT,

`sid` int(11) DEFAULT NULL,

`cid` int(11) DEFAULT NULL,

`score` float(5,2) DEFAULT NULL,

PRIMARY KEY (`scid`)

) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4;

1、查询各科平均戊绩，格式如下:cid,cname,avg_scoreSELECT AVG(sc.score) as avg_score,sc.cid,course.cname

FROM `sc`

LEFT JOIN course on sc.scid = course.cid

GROUP BY sc.cid

2、直询总分前3的学生数据，格式如下:sid,sname,total_scoreSELECT SUM(sc.score) as total_score ,sc.sid,student.sname

FROM sc

left JOIN student on sc.sid = student.sid

GROUP BY sc.sid

ORDER BY total_score desc limit 3

3.查询各科前三名学生select sc.score,sc.cid,sc.sid,course.cname,student.sname from sc

LEFT JOIN course on sc.cid = course.cid

LEFT JOIN student on sc.sid = student.sid

where (

select count(*)

from

sc b

where sc.cid = b.cid

and sc.score < b.score

) < 3

ORDER BY cid,score desc

4.查询各科都及格学生select distinct sc.sid,student.sname

from sc

LEFT join student on sc.sid = student.sid

where sc.sid not in (select distinct sid from sc where score<=60)

5，各科成绩超过平均分的同学，格式如下:sid,sname，score，cid，cname，avg_scoreSELECT

s.sid,

s.sname,

sc.score,

sc.cid,

c.cname,

avg_score

FROM

sc

LEFT JOIN ( SELECT cid, AVG( score ) avg_score FROM sc GROUP BY cid ) tmp ON sc.cid = tmp.cid

LEFT JOIN student s ON s.sid = sc.sid

LEFT join course c on c.cid = sc.cid

WHERE

sc.score > tmp.avg_score

6，各科成绩都超过平均分的同学，格式如下:sid,snameSELECT

s.sid,

s.sname

FROM

sc

LEFT JOIN ( SELECT cid, AVG( score ) avg_score FROM sc GROUP BY cid ) tmp ON sc.cid = tmp.cid

LEFT JOIN student s ON s.sid = sc.sid

WHERE

sc.score > tmp.avg_score

GROUP BY sc.sid

HAVING COUNT(s.sid) = (select COUNT(*) from course)

6、统计并列出各科成绩、各分数段入数;课程ID,课程名称,[100-90],[89-70],[69-60][ <60]select

sc.cid,

course.cname,

count(case when sc.score >= 90 then 1 end) as '[100-90]',

count(case when sc.score < 90 and sc.score >=70 then 1 end) as '[89-70]',

count(case when sc.score < 70 and sc.score >=60 then 1 end) as '[69-60]',

count(case when sc.score<60 then 1 end) as '[59-0]'

FROM sc

left JOIN course on sc.cid = course.cid

GROUP BY sc.cid

后续还有场景再补充。。。