SQL经典五十道题

SQL经典五十道题漏船载酒20171101213110浏览10647评论1数据存储与数据库SQLBYGROUPVARCHAR摘要1学生表STUDENTS,SNAME,SAGE,SSEXS学生编号,SNAME学生姓名,SAGE出生年月,SSEX学生性别2课程表COURSEC,CNAME,TC课程编号,CNAME课程名称,T教师编号31学生表STUDENTS,SNAME,SAGE,SSEXS学生编号,SNAME学生姓名,SAGE出生年月,SSEX学生性别2课程表COURSEC,CNAME,TC课程编号,CNAME课程名称,T教师编号3教师表TEACHERT,TNAMET教师编号,TNAME教师姓名4成绩表SCS,C,SCORES学生编号,C课程编号,SCORE分数/创建测试数据CREATETABLESTUDENTSVARCHAR10,SNAMEVARCHAR10,SAGEDATETIME,SSEXNVARCHAR10INSERTINTOSTUDENTVALUES01,赵雷,19900101,男INSERTINTOSTUDENTVALUES02,钱电,19901221,男INSERTINTOSTUDENTVALUES03,孙风,19900520,男INSERTINTOSTUDENTVALUES04,李云,19900806,男INSERTINTOSTUDENTVALUES05,周梅,19911201,女INSERTINTOSTUDENTVALUES06,吴兰,19920301,女INSERTINTOSTUDENTVALUES07,郑竹,19890701,女INSERTINTOSTUDENTVALUES08,王菊,19900120,女CREATETABLECOURSECVARCHAR10,CNAME,VARCHAR10,TVARCHAR10INSERTINTOCOURSEVALUES01,语文,02INSERTINTOCOURSEVALUES02,数学,01INSERTINTOCOURSEVALUES03,英语,03CREATETABLETEACHERTVARCHAR10,TNAME,VARCHAR10INSERTINTOTEACHERVALUES01,张三INSERTINTOTEACHERVALUES02,李四INSERTINTOTEACHERVALUES03,王五CREATETABLESCSVARCHAR10,CVARCHAR10,SCOREDECIMAL18,1INSERTINTOSCVALUES01,01,80INSERTINTOSCVALUES01,02,90INSERTINTOSCVALUES01,03,99INSERTINTOSCVALUES02,01,70INSERTINTOSCVALUES02,02,60INSERTINTOSCVALUES02,03,80INSERTINTOSCVALUES03,01,80INSERTINTOSCVALUES03,02,80INSERTINTOSCVALUES03,03,80INSERTINTOSCVALUES04,01,50INSERTINTOSCVALUES04,02,30INSERTINTOSCVALUES04,03,20INSERTINTOSCVALUES05,01,76INSERTINTOSCVALUES05,02,87INSERTINTOSCVALUES06,01,31INSERTINTOSCVALUES06,03,34INSERTINTOSCVALUES07,02,89INSERTINTOSCVALUES07,03,981、查询“01“课程比“02“课程成绩高的学生的信息及课程分数SELECTA,CFROMSELECTAFROMSELECTFROMSCWHERESCCIN01ALEFTJOINSELECTFROMSCWHERESCCIN02BONASBSWHEREASCOREBSCOREA,STUDENTCWHEREASCS2、查询“01“课程比“02“课程成绩低的学生的信息及课程分数SELECTA,CFROMSELECTAFROMSELECTFROMSCWHERESCCIN01ALEFTJOINSELECTFROMSCWHERESCCIN02BONASBSWHEREASCORE60ANDCSBS4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩SELECTBS,CSNAME,BAVGSCOREFROMSELECTASS,AVGASCOREAVGSCOREFROMSCAGROUPBYASB,STUDENTCWHEREBAVGSCORE0114、查询没学过“张三“老师讲授的任一门课程的学生姓名SELECTFROMSTUDENTAWHEREASNOTINSELECTBSFROMSELECTBC,BCNAMEFROMTEACHERA,COURSEBWHEREATBTANDATNAME张三A,SCBWHEREACBC15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩SELECTBS,BSNAME,AAVGSCOREFROMSELECTAVGSCOREAVGSCORE,SSFROMSCWHERESCSCORE2A,STUDENTBWHEREASBS16、检索“01“课程分数小于60，按分数降序排列的学生信息SELECTBFROMSELECTASSFROMSCAWHEREAC01ANDASCORE60，中等为7080，优良为8090，优秀为90SELECTAC,BCNAME,MAXASCORE,MINASCORE,AVGASCORE,SELECTCOUNT1FROMSCWHERESCCBCANDSCORE60/SELECTCOUNT1FROMSCWHERESCCBCJIGELVFROMSCA,COURSEBWHEREACBCGROUPBYACSELECTAC,ACNAME,MAXBSCORE,MINBSCORE,CASTAVGBSCOREASDECIMAL18,2PINGQUNFEN,CASTSELECTCOUNT1FROMSCWHERESCCACANDSCSCORE60/SELECTCOUNT1FROMSCWHERESCCACASDECIMAL18,2JIGELV,CASTSELECTCOUNT1FROMSCWHERESCCACANDSCSCORE70ANDSCSCOREASCORE1MCFROMSCA,COURSEB,STUDENTCWHEREAC01ANDACBCANDASCSORDERBYMCSELECTCS,CSNAME,BCNAME,ASCORE,SELECTCOUNT1FROMSCWHERESCCACANDSCSCOREASCORE1MCFROMSCA,COURSEB,STUDENTCWHEREAC02ANDACBCANDASCSORDERBYMCSELECTCS,CSNAME,BCNAME,ASCORE,SELECTCOUNT1FROMSCWHERESCCACANDSCSCOREASCORE1MCFROMSCA,COURSEB,STUDENTCWHEREAC03ANDACBCANDASCSORDERBYMC20、查询学生的总成绩并进行排名SELECTBS,BSUMSCORE,ROWNUMROWNUM1ASROWNUMFROMSELECTASS,SUMASCORESUMSCOREFROMSCAGROUPBYASORDERBYSUMSCOREDESCASB,SELECTROWNUM0R21、查询不同老师所教不同课程平均分从高到低显示SELECTCTNAME,BCNAME,AVGASCOREFROMSCA,COURSEB,TEACHERCWHEREACBCANDBTCTGROUPBYACORDERBYAVGASCOREDESC22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩SELECTA,BFROMSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC01ANDASCOREBSCORE1MCFROMSCBWHEREBC01HAVINGMCBETWEEN2AND3UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC02ANDASCOREBSCORE1MCFROMSCBWHEREBC02HAVINGMCBETWEEN2AND3UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC03ANDASCOREBSCORE1MCFROMSCBWHEREBC03HAVINGMCBETWEEN2AND3A,STUDENTBWHEREASBS23、统计各科成绩各分数段人数课程编号,课程名称,10085,8570,7060,060及所占百分比SELECTAFROMSELECTCCNAME,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC0185以上,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC0170_85,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC0160_70,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC70_85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC60_70以上比率,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC0285以上,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC0270_85,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC0260_70,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC70_85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC60_70以上比率,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC0385以上,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC0370_85,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC0360_70,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC03/SELECTCOUNTFROMSCWHEREC03ANDCBCANDCCC85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC03/SELECTCOUNTFROMSCWHEREC03ANDCBCANDCCC70_85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC03/SELECTCOUNTFROMSCWHEREC03ANDCBCANDCCC60_70以上比率,SELECTCOUNTFROMSCAWHEREASCOREBSCORE1MCFROMSCBWHEREBC01HAVINGMCBETWEEN1AND3UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC02ANDASCOREBSCORE1MCFROMSCBWHEREBC02HAVINGMCBETWEEN1AND3UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC03ANDASCOREBSCORE1MCFROMSCBWHEREBC03HAVINGMCBETWEEN1AND3A,STUDENTBWHEREASBS26、查询每门课程被选修的学生数SELECTACNAME,COUNTBCFROMSCB,COURSEAWHEREACBCGROUPBYBC27、查询出只有两门课程的全部学生的学号和姓名SELECTBS,BSNAME,ACOUNTCFROMSELECTS,COUNTCCOUNTCFROMSCGROUPBYSHAVINGCOUNTC2A,STUDENTBWHEREASBS28、查询男生、女生人数SELECTSSSEX,COUNTFROMSTUDENTSWHERESSSEX男UNIONSELECTSSSEX,COUNTFROMSTUDENTSWHERESSSEX女29、查询名字中含有“风“字的学生信息SELECT,COUNTFROMSTUDENTWHERESNAMERLIKE风30、查询同名同性学生名单，并统计同名人数SELECTSSNAME,SSSEX,COUNTCOUNTNUMFROMSTUDENTSGROUPBYSSNAME,SSSEXHAVINGCOUNTNUM231、查询1990年出生的学生名单注STUDENT表中SAGE列的类型是DATETIMESELECTFROMSTUDENTWHERESAGELIKE199032、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号SELECTACNAME,AVGSCSCOREAVGSCOREFROMSC,COURSEAWHERESCCACGROUPBYSCCORDERBYAVGSCOREDESC33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩SELECTFROMSELECTASNAME,AS,AVGSCSCOREAVGSCOREFROMSC,STUDENTAWHERESCSASGROUPBYSCSORDERBYAVGSCOREDESCAWHEREAAVGSCORE8534、查询课程名称为“数学“，且分数低于60的学生姓名和分数SELECTFROMSCA,COURSEBWHEREACBCANDBCNAME数学ANDASCORE6035、查询所有学生的课程及分数情况；SELECTFROMSTUDENTA,COURSEB,SCCWHEREASCSANDBCCC36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；SELECTBSNAME,CCNAME,ASCOREFROMSCA,STUDENTB,COURSECWHEREASCORE70ANDACCCANDASBS37、查询不及格的课程SELECTBSNAME,CCNAME,ASCOREFROMSCA,STUDENTB,COURSECWHEREASCORE80ANDCSBSANDAC0139、求每门课程的学生人数SELECTBCNAME,COUNTACFROMSCA,COURSEBWHEREBCACGROUPBYAC40、查询选修“张三“老师所授课程的学生中，成绩最高的学生信息及其成绩SELECTB,MAXCSCOREFROMTEACHERA,STUDENTB,SCC,COURSEDWHEREATDTANDDCCCANDCSBSANDATNAME张三41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩SELECTCFROMSCCGROUPBYCC,CSCOREHAVINGCOUNT142、查询每门功成绩最好的前两名SELECTA,BFROMSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC01ANDASCOREBSCORE1MCFROMSCBWHEREBC01HAVINGMCBETWEEN1AND2UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC02ANDASCOREBSCORE1MCFROMSCBWHEREBC02HAVINGMCBETWEEN1AND2UNIONALLSELECTBS,BC,BSCORE,SELECTCOUNTFROMSCAWHEREAC03ANDASCOREBSCORE1MCFROMSCBWHEREBC03HAVINGMCBETWEEN1AND2A,STUDENTBWHEREASBS43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECTBC,BCNAME,COUNTACCOUNTCFROMSCA,COURSEBWHEREACBCGROUPBYACHAVINGCOUNTC5ORDERBYCOUNTCDESC44、检索至少选修两门课程的学生学号SELECTSCS,COUNTSCCCOUNTCFROMSCGROUPBYSCSHAVINGCOUNTC245、查询选修了全部课程的学生信息SELECTA,COUNTSCCCOUNTCFROMSC,STUDENTAWHERESCSASGROUPBYSCSHAVINGCOUNTC346、查询各学生的年龄SELECTSS,SSNAME,EXTRACTYEARFROMNOWEXTRACTYEARFROMSSAGE年龄FROMSTUDENTS47、查询本周过生日的学生SELECTSS,SSNAME,SSAGEFROMSTUDENTSWHEREYEARWEEKDATE_FORMATSSAGE,YMDYEARWEEKNOW48、查询下周过生日的学生SELECTSS,SSNAME,SSAGEFROMSTUDENTSWHEREYEARWEEKDATE_FORMATSSAGE,YMDYEARWEEKNOW149、查询本月过生日的学生SELECTSS,SSNAME,SSAGEFROMSTUDENTSWHEREMONTHDATE_FORMATSSAGE,YMDMONTHNOW50、查询下月过生日的学生SELECTSS,SSNAME,SSAGEFROMSTUDENTSWHEREMONTHDATE_FORMATSSAGE,YMDMONTHNOW1摘要1学生表STUDENTS,SNAME,SAGE,SSEXS学生编号,SNAME学生姓名,SAGE出生年月,SSEX学生性别2课程表COURSEC,CNAME,TC课程编号,CNAME课程名称,T教师编号3STUDENTS,SNAME,SAGE,SSEXS学生编号,SNAME学生姓名,SAGE出生年月,SSEX学生性别CREATETABLESTUDENTSVARCHAR10,SNAMEVARCHAR10,SAGEDATETIME,SSEXNVARCHAR10AS0118、查询各科成绩最高分、最低分和平均分以如下形式显示课程ID，课程NAME，最高分，最低分，平均分，及格率，中等率，优良率，优秀率,SELECTCOUNT1FROMSCWHERESCCBCANDSCORE60/SELECTCOUNT1FROMSCWHERESCCBCJIGELV,CASTSELECTCOUNT1FROMSCWHERESCCACANDSCSCORE70ANDSCSCORE85ANDACBCANDCCACANDAC0185以上,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC0170_85,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC0160_70,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC70_85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC01/SELECTCOUNTFROMSCWHEREC01ANDCBCANDCCC60_70以上比率,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC0285以上,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC0270_85,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC0260_70,SELECTCOUNTFROMSCAWHEREASCORE85ANDACBCANDCCACANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC70_85以上比率,SELECTCOUNTFROMSCAWHEREASCORE70ANDACBCANDCCACANDASCORE60ANDAC02/SELECTCOUNTFROMSCWHEREC02ANDCBCANDCCC60_70以上比率,SELECTCOUNTFROMSCAWHEREASCORE85ANDA