MYSQL 常见学生成绩面试题

1、成绩表 sc 

 

 2、老师表 teacher

 

 3、学生表 student

 

 4、课程表 course

 

 1、查询'01'课程比'02'课程成绩高的所有学生的学号；

　　SELECT DISTINCT a.sid

　　　　FROM (
　　　　 　　　　SELECT sid
　　　　　　　　　　　　　,score
        　　　　FROM sc
       　　　　 WHERE cid = '01'
      　　　　) AS a
　　　　join (
       　　　　SELECT sid
              　　　　,score
       　　　　FROM sc
       　　　　WHERE cid = '02'
　　　　　　　) AS b
　　ON a.sid = b.sid


　　WHERE a.score > b.score;
 
2、查询平均成绩大于60分的同学的学号和平均成绩；

　　SELECT sid
　　　　　　,avg(score)
　　FROM sc
　　GROUP BY sid
　　HAVING avg(score) > 60
;
3、查询所有同学的学号、姓名、选课数、总成绩；
  1）
　　　　

　　SELECT stu.sid
　　　　　　,stu.sname
　　　　　　,count(s.cid) AS 选课数
　　　　　　,sum(s.score) AS 总成绩
　　FROM sc AS s
　　JOIN student AS stu
　　ON s.sid = stu.sid
　　GROUP BY stu.sid
;
2）
　　

　　SELECT t1.sid
　　　　　　,t1.sname
　　　　　　,( SELECT count(cid) FROM sc s1 WHERE s.sid=t1.sid ) 数量
　　　　　　,(SELECT sum(score) FROM sc s2 WHERE s2.sid=t1.sid) 总成绩
　　FROM student t1
;
4、查询姓“李”的老师的个数
    SELECT COUNT(tid) FROM teacher where tname LIKE 'Li%'
 
5、查询没学过“叶平”老师课的同学的学号、姓名
 

　　SELECT sid
　　　　,sname
　　FROM student
   WHERE sid NOT IN (SELECT DISTINCT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = 'Ye Ping')))
 
6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名
　　

1）
　　　　SELECT DISTINCT stu.sid
　　　　　　　　,stu.sname
　　　　FROM sc AS s
　　　　JOIN student AS stu
　　　　ON s.sid = stu.sid
　　　　WHERE 01 IN (SELECT cid FROM sc WHERE sid = stu.sid)
　　　　AND 02 IN (SELECT cid FROM sc WHERE sid = stu.sid)
;
 
2）
　　

　　SELECT sid
　　　　　　,sname
　　FROM student AS stu
　　where (SELECT count( * ) FROM sc AS s1 WHERE s1.sid = stu.sid AND cid = '01') > 0
　　and   (SELECT count( * ) FROM sc AS s2 WHERE s2.sid = stu.sid AND cid = '02') > 0
;
 
7、查询学过“Li Pengfei”老师所教的所有课的同学的学号、姓名
  

　　SELECT name
       　　,sid
　　FROM student
　　WHERE sid IN ( SELECT DISTINCT sid FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid IN (SELECT tid FROM teacher WHERE tname = 'Li Pengfei')) )
 
8、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名

方法一：
　　　　SELECT sid
　　　　　　　　,sname
　　　　FROM student
　　　　WHERE sid IN (SELECT s1.sid FROM sc s1 ,sc s2 WHERE s1.sid = s2.sid AND s2.cid = '02' AND s1.cid = '01' AND s1.score > s2.score)
;
方法二：
　　　

　　SELECT DISTINCT a.sid
　　　　　　,sname
　　FROM (
　　　　　　　　SELECT sid
　　　　　　　　　　　　,score
　　　　　　　　FROM `sc`
　　　　　　　　WHERE cid = '01'
　　　　　　) AS a
　　JOIN (
　　　　　　SELECT sid
　　　　　　　　　　,score
　　　　　　FROM `sc`
　　　　　　WHERE cid = '02'
　　　　　) AS b
　　ON a.sid = b.sid
　　JOIN student AS stu
　　ON stu.sid = a.sid
　　WHERE b.score < a.score
 
9、查询所有课程成绩小于80分的同学的学号、姓名
 
　　　　

　　SELECT sid
　　　　　　,sname
　　FROM student
　　WHERE sid NOT IN (SELECT sid FROM sc WHERE score < 80)
;
10、查询没有学全所有课的同学的学号、姓名

　　SELECT sid
　　　　　　,sname
　　FROM student
　　WHERE sid IN ( SELECT sid FROM sc GROUP BY sid HAVING count(cid) < (SELECT count(cid) FROM course) )
;
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名
　　

　　SELECT sid
　　　　　　,sname
　　FROM student
　　WHERE sid IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM sc WHERE sid = '01') AND sid NOT IN (1) )
;
 
12、查询学过学号为“07”同学所有门课的其他同学学号和姓名



　　　　

　　SELECT sid
　　　　　　,sname
　　FROM student
　　WHERE sid IN (SELECT sid FROM sc WHERE cid IN (SELECT cid FROM sc WHERE sid = '07') GROUP BY cid HAVING count(sid) >= (SELECT count(cid) FROM sc WHERE sid = '07'))
 
13、查询各科成绩最高和最低的分
  

　　SELECT cid AS coures_id
　　　　　　,max(score) AS 'max'
　　　　　　,min(score) AS 'min'
　　FROM sc
　　GROUP BY cid
;
14、按各科平均成绩从低到高和及格率的百分数从高到低顺序


       

　　　　SELECT cid
　　　　　　　　,AVG(score) AS '平均成绩'
　　　　　　　　,sum(CASE WHEN score>60 THEN 1 ELSE 0 END)/count(*)*100 AS '及格率'
　　　　FROM sc
　　　　GROUP BY cid
　　　　ORDER BY AVG(score) asc
　　　　　　　　　,sum(CASE WHEN score>=60 THEN 1 ELSE 0 END)/count(*) DESC
;