笔试面试那件小事（数据库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’;