数据库SQL基本功练习

爱是王道

21、查询不同老师所教不同课程平均分从高到低显示　　SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩
　　FROM SC AS T,Course AS C ,Teacher AS Z
　　where T.C#=C.C# and C.T#=Z.T#
　　GROUP BY C.C#
　　ORDER BY AVG(Score) DESC 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
　　[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
　　SELECT  DISTINCT top 3
　　SC.S# As 学生学号,
　　Student.Sname AS 学生姓名 ,
　　T1.score AS 企业管理,
　　T2.score AS 马克思,
　　T3.score AS UML,
　　T4.score AS 数据库,
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
　　FROM Student,SC  LEFT JOIN SC AS T1
　　ON SC.S# = T1.S# AND T1.C# = '001'
　　LEFT JOIN SC AS T2
　　ON SC.S# = T2.S# AND T2.C# = '002'
　　LEFT JOIN SC AS T3
　　ON SC.S# = T3.S# AND T3.C# = '003'
　　LEFT JOIN SC AS T4
　　ON SC.S# = T4.S# AND T4.C# = '004'
　　WHERE student.S#=SC.S# and
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
　　NOT IN
　　(SELECT
　　DISTINCT
　　TOP 15 WITH TIES
　　ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
　　FROM sc
　　LEFT JOIN sc AS T1
　　ON sc.S# = T1.S# AND T1.C# = 'k1'
　　LEFT JOIN sc AS T2
　　ON sc.S# = T2.S# AND T2.C# = 'k2'
　　LEFT JOIN sc AS T3
　　ON sc.S# = T3.S# AND T3.C# = 'k3'
　　LEFT JOIN sc AS T4
　　ON sc.S# = T4.S# AND T4.C# = 'k4'
　　ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
　　23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[  T2.平均成绩) as 名次,
　　S# as 学生学号,平均成绩
　　FROM (SELECT S#,AVG(score) 平均成绩
　　FROM SC
　　GROUP BY S#
　　) AS T2
　　ORDER BY 平均成绩 desc;
　　25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
　　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#
　　ORDER BY score DESC
　　)
　　ORDER BY t1.C#;
　　26、查询每门课程被选修的学生数
　　select c#,count(S#) from sc group by C#;
　　27、查询出只选修了一门课程的全部学生的学号和姓名
　　select SC.S#,Student.Sname,count(C#) AS 选课数
　　from SC ,Student
　　where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
　　28、查询男生、女生人数
　　Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='男';
　　Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex='女'；
　　29、查询姓“张”的学生名单
　　SELECT Sname FROM Student WHERE Sname like '张%';
　　30、查询同名同性学生名单，并统计同名人数
　　select Sname,count(*) from Student group by Sname having  count(*)>1;;
　　31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
　　select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age
　　from student
　　where  CONVERT(char(11),DATEPART(year,Sage))='1981';
　　32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
　　Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
　　33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
　　select Sname,SC.S# ,avg(score)
　　from Student,SC
　　where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;
　　34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
　　Select Sname,isnull(score,0)
　　from Student,SC,Course
　　where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score =70 AND SC.S#=student.S#;
　　37、查询不及格的课程，并按课程号从大到小排列
　　select c# from sc where scor e 80 and C#='003';
　　39、求选了课程的学生人数
　　select count(*) from sc;
　　40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
　　select Student.Sname,score
　　from Student,SC,Course C,Teacher
　　where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
　　41、查询各个课程及相应的选修人数
　　select count(*) from sc group by C#;
　　42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
　　select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# B.C# ;
　　43、查询每门功成绩最好的前两名
　　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#;
　　44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
　　select  C# as 课程号,count(*) as 人数
　　from  sc
　　group  by  C#
　　order  by  count(*) desc,c#
　　45、检索至少选修两门课程的学生学号
　　select  S#
　　from  sc
　　group  by  s#
　　having  count(*)  >  =  2 46、查询全部学生都选修的课程的课程号和课程名
　　select  C#,Cname
　　from  Course
　　where  C#  in  (select  c#  from  sc group  by  c#)
　　47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
　　select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
　　48、查询两门以上不及格课程的同学的学号及其平均成绩
　　select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score 2)group by S#;
　　49、检索“004”课程分数小于60，按分数降序排列的同学学号
　　select S# from SC where C#='004'and score