【IT专家】sql语句,如何查询一个班的学生成绩都及格的学生的姓名

本文由我司收集整编，推荐下载，如有疑问，请与我司联系sql语句,如何查询一个班的学生成绩都及格的学生的姓名2011/03/09 10 where成绩60and........关键是如何判断一个班的都及格了？？？？？？？？？？？？？？？？？？select班级，学生姓名from成绩表where成绩60and班级notin(selectdistinct班级from成绩表where成绩60) select*from成绩表twherenotexists(select1from成绩表wheret.班级=班级andt.成绩60) select班级,sum(casewhen成绩=60then1else0end)及格人数,count(*)总人数from 成绩表groupby班级withcteas(select班级,sum(casewhen成绩=60then1else0end)及格人数,count(*)总人数from成绩表groupby班级)select班级,(casewhen及格人数总人数then’不完全及格’else’全部及格’end)及格标识fromcte if(selectcount(0)from成绩表)=(selectcount(0)from成绩表where成绩=60)print’都及格了’elseprint’有人不及格’ SQL语句练习题参考答案1、selectSname,Ssex,ClassfromStudent;2、selectdistinctdepartfromteacher;3、selectSnoas’学号’,Snameas’姓名’,Ssexas’性

别’,Sbirthdayas’出生日期’,Classas’班号’fromstudent;或selectSnoas学号,Snameas姓名,Ssexas性别,Sbirthdayas出生日期,Classas班号fromstudent;4、

select*fromscorewheredegreebetween60and80;或select*fromscorewheredegree

=60anddegree 5、select*fromscorewheredegreein(85,86,88);6、select*fromstudentwhere orSsex=‘女’;7、select*fromstudentorderbyclassdesc;8、

select*fromscoreorderbycnoasc,degreedesc;或

select*fromscoreorderbycno,degreedesc;9、selectcount(*)asCNTfromstudentwhere ;10、selectSnoas’学号’,cnoas’课程号’,degreeas’最高

分’fromscorewheredegree=(selectmax(degree)fromscore)11、selectavg(degree)as课程平均分fromscorewherecno=‘3-105’;12、

selectcno,avg(degree)fromscorewherecnolike’3%’groupbycnohavingcount(*) 13、selectSnofromscoregroupbySnohavingmin(degree) 70andmax(degree) 14、selectstudent.Sname,o,score.degreefromstudent,scorewherestudent.Sno=score.Sno ;15、selectx.Sno,ame,x.degreefromscorex,o=o;16、