SQL 面试题

//没有学过赵老师课程的学生
SELECT * FROM student s 
         WHERE sid NOT in(
         SELECT sid 
    FROM  scgrade sc 
  JOIN  lesson l on sc.lid = l.lid
  WHERE l.lteacher = '赵老师')

列出2门以上（含2门）不及格学生名字及平均成绩
SELECT * from student  s JOIN (
SELECT sid,AVG(scgreade) as savg  from scgrade where sid in(
	SELECT sid from scgrade where scgreade <60
  GROUP BY sid HAVING COUNT(*)>=2
) GROUP BY sid
) r on s.sid = r.sid

列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
SELECT * from student where sid in (
SELECT sid from scgrade sc 
where sc.lid in(1,3) 
GROUP BY sid HAVING count(sid)=2
)

(1)统计有学生选修的课程门数   select COUNT(DISTINCT lid) from scgrade 

(2)求选修C4课程的女学生的平均年龄
SELECT avg(s.age) from student s where s.sex='女' and s.sid IN(
SELECT sid from scgrade sc WHERE lid ='2')

(3)求刘老师所授的课程的每门课程的平均成绩
SELECT  l.lname,AVG(sc.scgreade)
        from scgrade sc ,lesson l
        WHERE l.lteacher='刘老师' and sc.lid = l.lid
        GROUP BY l.lname 

(4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT lid as '课程号' ,count(sid) as '人数' 
from scgrade sc  
GROUP BY lid HAVING COUNT(*)>=1 
ORDER BY count(sid) DESC,lid ASC

其他

//查询二级别表 最多的
SELECT * from banners b JOIN (
SELECT Did,COUNT(Did) as s from article  
	GROUP BY Did  
	 ) as ar

on b.Did = ar.Did
ORDER BY ar.s DESC


SELECT b.Did,b.Dcontent,COUNT(ar.Did) as rsum from 
           banners  b 
         JOIN 
           article  ar
           on b.Did = ar.Did
GROUP BY ar.Did  
ORDER BY rsum desc