1、score:成绩Teacher(T#,Tname)教师表教师编号:Tname:教师名字问题:1、查询“001”课程比“002”课程成绩高的所有学生的学号select # from (select S#,score from SC where C#=001)a, (select s#,score from SC where c#=002)b Where and # = #;2、查询平均成绩大于60分的同学的学号和平均成绩select S#, avg(score) from sc group by S# having avg(score)603、查询所有同学的学号、姓名、选课数、总成绩select
2、 #, , count#), sum(score) from student left outer join SC on # = # group by #, Sname4、查询姓李的老师的个数:select count(distinct(Tname) from teacher where tname like 李%;5、查询没有学过“叶平”老师可的同学的学号、姓名:select #, from Student where S# not in (select distinct#) from SC,Course,Teacher where #=# AND #=# AND =叶平);6、查询学过“叶
3、平”老师所教的所有课的同学的学号、姓名:select S#,Sname from Student where S# in (select S# from SC ,Course ,Teacher where #=# and #=# and = group by S# having count#)=(select count(C#) from Course,Teacher where #=# and Tname=);7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名:from Student,SC where #=# and #=and exists( Select * from
4、 SC as SC_2 where #=# and #=8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名:Select S#,Sname from (select #,score ,(select score from SC SC_2 where #=# and #=) score2 from Student,SC where #=# and C#=) S_2 where score2 60);10、查询没有学全所有课的同学的学号、姓名:from student, scwhere #=# group by #, having count(c#)=60 THEN
5、1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM SC T,Course GROUP BY # ORDER BY 100 * SUM(CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004):21、查询不同老师所教不同课程平均分从高到低显示: SELECT max#) AS 教师ID, MAX AS 教师姓名, # AS 课程ID, AVG(Score) AS
6、平均成绩 FROM SC AS T,Course AS C ,Teacher AS Z where #=# and #=# GROUP BY # ORDER BY AVG(Score) DESC22、查询如下课程成绩第3名到第6名的学生成绩单:企业管理(001),马克思(002),UML(003),数据库(004):23、统计下列各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 小于60 :SELECT # as 课程ID, Cname as 课程名称,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 E
7、ND) 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 T2.平均成绩) as 名次, S# as 学生学号,平均成绩 FROM (SELECT S#,AVG(score) 平均成绩 GROUP BY S# ) AS T2 ORDER BY 平均成绩 desc;25、查询各科成绩前三名的记录(不考虑成绩并列情况):SELECT # as 学生ID,# as 课程ID,Score as 分数 FROM SC t1 WHERE score IN (SELECT TOP 3 score WHERE #= C# ORDER BY score DESC) 26、查询每门课程被选修的学生数:select c#, count(s#) from sc group by c#;27、查询出只选修一门课程的全部学生的学号和姓名:select #, , count(c#) as 选课数from sc,student where # =# having count(c#)=1;28、查
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1