/*Student(Sno,Sname,Sage,Ssex) 学生表Course(Cno,Cname,Tno) 课程表SC(Sno,Cno,score) 成绩表Teacher(Tno,Tname) 教师表 */1、查询“3”课程比“4”课程成绩高的所有学生的学号；select a.sno from (select sno,score from sc where cno='3' )a, (select sno,score from sc where cno='4')bwhere a.score>b.score and a.sno=b.sno;2、查询平均成绩大于60分的同学的学号和平均成绩；select sno,avg(score)from sc group by sno having avg(score)>60;/*select sno,avg(score)from sc where score>60 group by sno;这个为啥不行呢？*/3、查询所有同学的学号、姓名、选课数、总成绩；select a.sno,a.sname,count(b.cno),sum(c.score)from student a, course b, sc cwhere a.sno=c.sno and c.cno=b.cnogroup by a.sno;4、查询姓“李”的老师的个数；select count(distinct(tname)) from teacher where tname like '%李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select a.sno,a.snamefrom student awhere a.sno not in(select distinct(b.sno) from sc b,course c,teacher dwhere b.cno=c.cno and c.tno=d.tno and d.tname='叶平');6、查询学过“3”并且也学过编号“4”课程的同学的学号、姓名；select a.sno,a.snamefrom student a,sc bwhere a.sno=b.sno and b.cno='3' and exists(select * from sc as sc_01 where sc_01.sno=b.sno and sc_01.cno='4');7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select a.sno,a.snamefrom student awhere a.sno in(select distinct(b.sno) from sc b,course c,teacher dwhere b.cno=c.cno and c.tno=d.tno and d.tname='叶平' group by sno having count(b.cno)=(select count(cno) from course c,teacher d where d.tno=c.tno and d.tname='叶平'));8、查询所有课程成绩小于60分的同学的学号、姓名；select sno,snamefrom studentwhere sno not in(select a.sno from student a,sc b where a.sno=b.sno and b.score>'60');9、查询没有学全所有课的同学的学号、姓名；select a.sname,a.snofrom student a,sc bwhere a.sno=b.snogroup by a.sno,a.snamehaving count(cno)<(select count(*) from course);10、查询至少有一门课与学号为“5”的同学所学相同的同学的学号和姓名；select a.sno,a.snamefrom student a,sc bwhere a.sno=b.sno and b.cno in(select b.cno from sc b where b.sno='5');
11、删除学习“叶平”老师课的SC表记录；delete scfrom sc,course,teacherwhere course.cno=sc.cno and course.tno=teacher.tno and teacher.tname='叶平';
12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分select h.score as '最高分',l.score as '最低分',h.cno as '课程ID'from sc h,sc lwhere h.cno=l.cnoand h.score=(select max(IL.score) from sc IL,student IM where IL.cno=h.cno and IM.sno=IL.sno group by IL.cno)and l.score=(select min(IR.score) from sc IR where IR.cno=l.cno group by IR.cno);13、查询学生平均成绩及其名次;SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT sno,AVG(score) 平均成绩FROM SCGROUP BY Sno ) T1WHERE 平均成绩 > T2.平均成绩) 名次, Sno 学生学号,平均成绩FROM (SELECT sno,AVG(score) 平均成绩 FROM SC GROUP BY sno ) T2ORDER BY 平均成绩 desc;14、查询各科成绩前三名的记录:(不考虑成绩并列情况)SELECT t1.Sno as 学生ID,t1.Cno as 课程ID,t1.Score as 分数FROM SC t1WHERE score IN (SELECT TOP 3 scoreFROM SCWHERE t1.Cno= CnoORDER BY score DESC)ORDER BY t1.Cno; 15、查询每门功成绩最好的前两名SELECT t1.Sno as 学生ID,t1.Cno as 课程ID,Score as 分数FROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.Cno= CnoORDER BY score DESC )ORDER BY t1.Cno;
转载于:https://www.cnblogs.com/haibaowang/p/7149355.html
相关资源：SQL2012实训参考资料