50道经典sql题

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT t1.SId, t1.score,t2.score from sc as t1 ,(SELECT SId, score from sc where CId='02' ) as t2 WHERE t1.CId='01' and t1.score>t2.score and t1.SId=t2.SId

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

SELECT t1.SId FROM SC as t1, (SELECT  SId from sc WHERE CId='02' ) AS t2 WHERE t1.SId=t2.SId and t1.CId='01'

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT t1.SId,t1.score,t2.score FROM SC as t1 LEFT JOIN (SELECT Sid,score from sc WHERE CId='02') as t2 on t1.SId=t2.SID WHERE t1.CId='01'

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT * from sc WHERE CId='02' and SId not in (SELECT SId from sc WHERE cid='01')

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT  t1.SID, t1.avg_value from (SELECT SID,SUM(score)/3 AS avg_value FROM sc  GROUP BY SID) as t1 ,(SELECT SId, sname FROM student  ) as t2 WHERE t1.avg_value>60  and t1.SId=t2.SId 

3. 查询在 SC 表存在成绩的学生信息

SELECT sid from sc  GROUP BY SId HAVING COUNT(score)>1

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT t1.sid,t1.Sname,t2.totle,t2.su from student as t1 LEFT JOIN (SELECT sid, COUNT(*) as totle,SUM(score) as su from sc GROUP BY SId ) AS t2 on t1.sid=t2.sid  

4.1 查有成绩的学生信息

SELECT t1.* from student AS t1  ,
(SELECT DISTINCT(Sid) as s from sc) as t2 WHERE t1.SId=t2.s

5. 查询「李」姓老师的数量

SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%'

6. 查询学过「张三」老师授课的同学的信息

SELECT * FROM student WHERE SId IN (SELECT sid from sc , 
(SELECT CId from course, (select Tid from teacher WHERE Tname="张三") t2 WHERE t2.Tid = course.TId) 
t1 WHERE t1.CId=sc.CId)

SELECT student.* from student,sc,course,teacher where student.SId = sc.SId and sc.CId =course.CId and course.TId = teacher.TId and teacher.Tname="张三"

7. 查询没有学全所有课程的同学的信息

SELECT * FROM student WHERE SId not IN (SELECT SID  as c FROM sc GROUP BY SId HAVING COUNT(CId) =( SELECT COUNT(*)FROM course))

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT * FROM student WHERE SId not IN (SELECT SID  as c FROM sc GROUP BY SId HAVING COUNT(CId) =( SELECT COUNT(*)FROM course))

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
   这道题有难度，一般是通过比较字符串的方式

select B.* from student B 
INNER JOIN 
(select SId,GROUP_CONCAT(cid ORDER BY cid) gc from sc GROUP BY sid ) A on A.sid=B.sid 
where A.gc=
(select GROUP_CONCAT(cid ORDER BY cid) 
from sc where sid='01') and b.SId !="01"

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

select student.* from student WHERE student.SId not in(SELECT sc.SId from  sc, teacher,course WHERE teacher.Tname='张三' and teacher.TId = course.TId and sc.CId=course.CId )  

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT t1.sid ,t1.Sname ,t2.avg_score from student as t1 ,(SELECT AVG(score) as avg_score,SId from sc WHERE score<60 GROUP BY SId  HAVING COUNT(score)>=2  ) as t2 WHERE t1.SId = t2.SId

12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT student.* ,sc.score from student , SC WHERE student.sid =  sc.SId and sc.CId="01" ORDER BY sc.score DESC

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT student.* ,max(case c.Cname when '语文' then sc.score else null end) 语文 ,

max(case c.Cname when '数学' then sc.score else null end) 数学 ,

max(case c.Cname when '英语' then sc.score else null end) 英语 ,

avg(sc.score) AS avg_score FROM student LEFT JOIN sc on sc.SId=student.SId LEFT JOIN course c on c.CId= sc.CId GROUP BY student.SId ORDER BY avg_score DESC

14. 查询各科成绩最高分、最低分和平均分：

    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT course.* ,
(SELECT MAX(score) from sc WHERE course.CId = sc.CId) as 最高分, 
(SELECT MIN(score) from sc WHERE course.CId = sc.CId) as 最底分 ,
(SELECT AVG(score) from sc WHERE course.CId = sc.CId) as 平均分 ,
(SELECT COUNT(*) FROM SC WHERE course.CId = sc.CId) as 人数,
(SELECT (SELECT COUNT(1) from sc WHERE score>60 and sc.CId = course.CId)/COUNT(1) FROM SC WHERE course.CId = sc.CId ) as 及格率,
(SELECT (SELECT COUNT(1) from sc WHERE score>70 and score<80 and sc.CId = course.CId)/COUNT(1) FROM SC WHERE course.CId = sc.CId ) as 中等率,
(SELECT (SELECT COUNT(1) from sc WHERE score>80 and score<90 and sc.CId = course.CId)/COUNT(1) FROM SC WHERE course.CId = sc.CId ) as 优良率,
(SELECT (SELECT COUNT(1) from sc WHERE score>90  and sc.CId = course.CId)/COUNT(1) FROM SC WHERE course.CId = sc.CId ) as 优秀率 
from course ORDER BY 人数 DESC ,CId 

15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT t1.*,COUNT(t2.score) +1
from sc t1  LEFT JOIN sc t2 on   t1.CId = t2.CId and t2.score>t1.score 
GROUP BY t1.SId,t1.CId 
ORDER BY t1.CId ,t1.score DESC

SELECT * ,(SELECT COUNT(*) FROM SC WHERE sc.CId=A.CId AND sc.score> a.score)+1 AS RNGK FROM SC A ORDER BY a.cid,a.score DESC

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

SELECT t1.*,COUNT(DISTINCT(t2.score)) +1
from sc t1  LEFT JOIN sc t2 on   t1.CId = t2.CId and t2.score>t1.score 
GROUP BY t1.SId,t1.CId 
ORDER BY t1.CId ,t1.score DESC

SELECT * ,(SELECT COUNT(DISTINCT(score)) FROM SC WHERE sc.CId=A.CId AND sc.score> a.score)+1 AS RNGK FROM SC A ORDER BY a.cid,a.score DESC

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
    这两题难度较大

SELECT t1.* ,
(SELECT COUNT(1) from (SELECT SUM(score) 总成绩 from sc GROUP BY sid) t2 WHERE t2.总成绩>t1.总成绩) +1 as rank 
from (SELECT SId,SUM(score) 总成绩 from sc  GROUP BY sid) t1 ORDER BY t1.总成绩 DESC

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT t1.* ,
(SELECT COUNT(DISTINCT(总成绩)) from (SELECT SUM(score) 总成绩 from sc GROUP BY sid) t2 WHERE t2.总成绩>t1.总成绩) +1 as rank 
from (SELECT SId,SUM(score) 总成绩 from sc  GROUP BY sid) t1 ORDER BY t1.总成绩 DESC

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT CId,
(SELECT COUNT(1) from sc WHERE cid=t1.cid and score<100 and score>85 ) AS 100至85 ,
(SELECT COUNT(1)/t1.总人数 from sc WHERE cid=t1.cid and score<100 and score>85 ) AS 100至85百分比 ,
(SELECT COUNT(1) from sc WHERE cid=t1.cid and score<85 and score>75 ) AS 85至75 ,
(SELECT COUNT(1)/t1.总人数 from sc WHERE cid=t1.cid and score<85 and score>75 ) AS 85至75百分比 ,
(SELECT COUNT(1) from sc WHERE cid=t1.cid and score<75 and score>60 ) AS 75至60 ,
(SELECT COUNT(1)/t1.总人数 from sc WHERE cid=t1.cid and score<75 and score>60 ) AS 75至60百分比 ,
(SELECT COUNT(1) from sc WHERE cid=t1.cid and score<60 and score>0 ) AS 60至0 ,
(SELECT COUNT(1)/t1.总人数 from sc WHERE cid=t1.cid and score<60 and score>0 ) AS 60至0百分比 
 from (SELECT cid ,COUNT(1) 总人数 from sc GROUP BY cid) t1

18. 查询各科成绩前三名的记录
    难度大

SELECT a.SId,a.CId ,a.score from sc a  WHERE (SELECT COUNT(*) from sc  WHERE cid = a.cid and  score>a.score )<3 ORDER BY CId,score DESC

19. 查询每门课程被选修的学生数

SELECT cid,COUNT(1) from sc group by cid

20. 查询出只选修两门课程的学生学号和姓名

SELECT * from student WHERE sid in (SELECT SId FROM SC GROUP BY SID HAVING COUNT(CID) =2)

21. 查询男生、女生人数

SELECT Ssex,COUNT(1) from student GROUP BY Ssex 

22. 查询名字中含有「风」字的学生信息

SELECT * from student WHERE Sname like "%风%"

23. 查询同名同性学生名单，并统计同名人数

SELECT sname, COUNT(1) FROM student GROUP BY Sname HAVING COUNT(SNAME)>1

24. 查询 1990 年出生的学生名单

SELECT * from student WHERE YEAR(Sage) = "1990"

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

SELECT cid,AVG(score) as 平均成绩 from sc GROUP BY cid ORDER BY 平均成绩 DESC,cid

26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT SC.sid ,
AVG(SC.score) as av ,
student.Sname 
from sc 
LEFT JOIN student on student.SId = sc.SId 
GROUP BY sid HAVING av>85

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT student.Sname,sc.score from  sc ,student ,course
WHERE sc.score <60 and course.Cname = "数学"
and sc.CId = course.CId and student.SId = sc.SId

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT student.Sname,sc.score,SC.CId FROM student 
LEFT JOIN SC ON sc.SId=student.SId
 

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

SELECT student.Sname,SC.score,course.Cname from student,sc ,course
WHERE sc.score>70 and student.SId=sc.SId
and sc.CId = course.CId

30. 查询不及格的课程
    题目不知所云

SELECT sc.CId, sc.score from sc,course
WHERE sc.score<60 AND course.CId = sc.CId

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT student.SId,student.Sname,SC.score from student,sc
WHERE sc.score>=80 AND sc.CId="01" and sc.SId =student.SId

32. 求每门课程的学生人数

SELECT cid,COUNT(sid) from sc GROUP BY cid

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT student.*,t.score from student,
(SELECT sc.SId ,sc.score from sc ,course,teacher
WHERE sc.CId = course.CId and teacher.Tname = "张三" and teacher.TId = course.TId)
as t
WHERE t.SId = student.SId ORDER BY t.score DESC LIMIT 1

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT *,a.score from sc a  WHERE (SELECT COUNT(*) from sc WHERE cid=a.CId and sc.score>a.score) = 0

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT A.*,B.* from  sc a,sc  b WHERE a.CId!=b.CId AND a.score=b.score

36. 查询每门功成绩最好的前两名

SELECT * from sc a WHERE (SELECT COUNT(1) from sc WHERE sc.CId=a.CId  and sc.score>a.score)<2
ORDER BY cid ,score DESC

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT cid, COUNT(sid) as num from sc GROUP BY CId HAVING num >5

38. 检索至少选修两门课程的学生学号

SELECT SID,COUNT(CID) AS NUM from sc GROUP BY sid HAVING NUM>=2

39. 查询选修了全部课程的学生信息

SELECT * FROM student WHERE SID IN (SELECT SID from sc GROUP BY sid HAVING COUNT(CID)=3)

40. 查询各学生的年龄，只按年份来算

SELECT *,year(NOW()) - year(sage) from student 

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT *,
year(NOW())-year(sage)
- 
(case when 
date_format(now(),'%m%d')>
date_format(sage,'%m%d') then 0 else 1  end) 
from student 

42. 查询本周过生日的学生

    select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(Sage)

43. 查询下周过生日的学生

    select * from student where 2=WEEK(Sage)

在where 语句中计算是很差劲的sql写法，这里直接写成2

44. 查询本月过生日的学生

    select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=month(Sage)

45. 查询下月过生日的学生