查询平均分不及格的学生mysql 查询各科平均分sql

20>统计各科成绩，各分数段人数：课程ID，课程名称，[100-85],[85-70],[70-60],[<60]

SELECT SC.C# AS 课程ID，Cname AS课程名称，

       

SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85],
                 SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70],
                 SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70-60],
                 SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS [60-]
FROM SC,Course
WHERE SC.C#=Course.C#
BROUP BY SC.C#,Cname;
 
21>查询学生平均成绩及其名次
SELECT 1+(SELECT COUNT(distinct 平均成绩) FROM 
                         （SELECT S#，AVG(score)  AS 平均成绩    FROM  SC    BROUP BY S#）AS T1   WHERE 平均成绩>T2.平均成绩) AS  名次，
                  S# as 学生学号，平均成绩
FROM ( SELECT S#,AVG(score) 平均成绩  FROM SC GROUP BY S#) AS T2
ORDER BY 平均成绩 DESC；
 
22>查询各科成绩前三名的记录(不考虑成绩并列情况)
SELECT t1.S# AS 学生ID，t1.C# AS 课程ID，Score AS 分数
FROM SC t1
WHERE score IN（SELECT TOP 3 score  FROM  SC  WHERE t1.C#=C#   ORSER BY score DESC）
ORDER BY t1.C#;
 
22>查询每门课程被选修的学生数
SELECT C#,COUNT（S#） FROM SC
GROUP BY C#；
 
23>查询出只选修了一门课程的全部学生的学号和姓名
SELECT SC.S#, Student.Sname, COUNT(C#) AS 选课数
FROM SC ,Student
WHERE SC.S#=Student.S# 
GROUP BY SC.S#,Studengt.Sname HAVING COUNT（C#）=1;
 
24>查询男生、女生人数
SELECT COUNT(Ssex) AS 男生数  FROM Student 
GROUP BY Ssex HAVING Ssex='男';
 
SELECT COUNT(Ssex) AS 女生数 FROM Student
GROUP BY Ssex HAVING Ssex='女';
 
25>查询姓“张”的学生的名单
SELECT Sname FROM Student WHERE Sname like ‘张%’;
 
26>查询同姓名学生名单，并统计同名人数
SELECT Sname，COUNT(*) FROM Student 
GROUP BY Sname HAVING COUNT(*)>1;
 
27>1981年出生的学生名单（注：Student表中的Sage列类型为datetime）
SELECT Sname, CONVERT（CHAR（11）,DATEPART(year,Sage)） AS age
FROM Student
WHERE CONVERT(char(11),DATEPART(year,Sage))='19981';
 
28>查询每门课程的平均成绩，结果按照平均成绩升序排列，平均成绩相同，则按照课程号降序排列
SELECT C# ,AVG(score)  FROM SC  
GROUP BY C#
ORDER BY AVG(score),C# DESC;
 
29>查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT Sname,SC.S#,AVG(score)
FROM SC ,Student
WHERE SC.S#=Student.S#
GROUP BY SC.S#  HAVING AVG(score)>85;
 
30>查询课程名称为“数据库”，且分数低于60的学生姓名和分数
SELECT Sname,isnull(score,0)
FROM Student,SC,Course
WHERE SC.S#=Student.S# AND Course.C#=SC.C# AND Course.Cname='数据库' AND score<60;
 
31>查询所有学生的选课情况
SELECT SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
WHERE SC.S#=Student.S# AND SC.C#=Course.C#
 
32>查询任何一门课程成绩在70分以上的姓名、课程号和分数。
SELECT DISTINCT Student.S#,Student.Sname,SC.C#,SC.score
FROM Student,SC
WHERE SC.score>=70 AND SC.S#=Student.S#;
 
33>查询及格的课程，并按照课程号从大到小排列
SELECT C# FROM SC  WHERE score<60 ORDER BY C#;
 
34>查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名
SELECT SC.S#,Student.Sname  
FROM SC,Student 
WHERE SC.S#=Student.S# AND score>80 AND C#=‘003';
 
35>求选了课程的学生人数
SELECT COUNT(*) FROM SC;
 
36>查询选修“叶平”老师所教授课程的学生中，成绩最高的学生姓名及其成绩
SELECT Student.Sname,score
FROM Student,SC,Course C,Teacher
WHERE Student.S#=SC.S#  AND SC.C#=C.C# AND C.C#=Teacher.C# AND Teacher.Tname=“叶平”
AND SC.score=(SELECT MAX(score) FROM SC WHERE C.C#=C#);
 
37>查询各个课程及其相应的选修人数
SELECT C#，COUNT(*) FROM SC 
GROUP BY C#;
 
38>查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT DISTINCT A.S#,B.score FROM SC A,SC B
WHERE A.score=B.score AND A.C#<>B.C#;
 
39>查询每门课程成绩最好的前两名
SELECT t1.S# AS 学生ID，t1.C# AS 课程ID，score AS分数
FROM SC t1
WHERE score IN (SELECT  TOP 2 score FROM SC WHERE t1.C#=C#  ORDER BY score DESC)
ORDER BY t1.C#;
 
40>统计每门课程的学生选修人（超过10人才统计）。要求输出课程号和选修人数，按照选修人数进行降序排列，结果相同的，按照课程号升序排列
SELECT C# AS 课程号，COUNT(*) AS 人数
FROM SC
GROUP BY C# HAVING COUNT(*)>10
ORDER BY （COUNT* ）DESC，C#;
 
41>检索至少选修了两门课程的学生学号
SELECT S#  FROM SC  GROUP BY S# HAVING  COUNT(*)>=2;
 
42>查询全部学生都选修的课程的课程号和课程名
SELECT C#,Cname
FROM Course    WHERE C# IN(
    SELECT  C#  FROM SC 
    GROUP BY C#   HAVING COUNT(*)=(SELECT COUNT(*) FROM Student)
);
 
43>查询没学过“叶平”老师讲授的任何一门课程的学生姓名
SELECT Sname FROM Student
WHERE S# NOT IN(
      SELECT S# FROM Course,Teacher,SC WHERE Course.T#=Teacher.T# AND Tname='叶平' AND SC.C#=Course.C#
 
44>查询两门以上不及格课程的同学的学号及其平均成绩
SELECT S#,AVG(isnull(score,0))  FROM SC WHERE S# IN(
    SELECT S# FROM SC WHERE score<60  GROUP BY S# HAVING COUNT(*)>2
)
GROUP BY S#;
 
45>检索“004”课程分数小于60，按分数降序排列的同学号
SELECT S#  FROM  SC WHERE C#=‘004’ AND score <60 
ORDER BY S# DESC;

46>删除“002”同学的“001”课程的成绩
 DELETE FROM SC  WHERE S#=‘002’ AND C#=‘001’;