1.查询“生物”课程比“物理”课程成绩高的所有学生的学号
selectA.student_id,sw,wlfrom(SELECTscore.student_id,numberasswfromscoreLEFTJOINcourseonscore.course_id=course.cidwherecourse.cname='生物')asALEFTJOIN(SELECTscore.student_id,numberaswlfromscoreLEFTJOINcourseonscore.course_id=course.cidwherecourse.cname='物理')asBonA.student_id=B.student_idwheresw>wl
2.查询平均成绩大于60分的同学的学号和平均成绩
SELECTstudent_id,avg(number)fromscoreGROUPBYscore.student_idHAVINGavg(number)>60
3.查询所有同学的学号、姓名、选课数、总成绩
SELECTscore.student_id,student.sname,count(score.course_id),sum(number)fromscoreLEFTJOINstudentonscore.student_id=student.sidGROUPBYscore.student_id
4.查询姓“李”的老师的个数
SELECTcount(1)fromteacherwhereteacher.tnamelike'李%'
5.查询没学过“李平老师”课的同学的学号、姓名
SELECTsid,snamefromstudentwherestudent.sidnotin(SELECTDISTINCTscore.student_idfromscorewherescore.course_idin(SELECTcourse.cidfromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='李平老师'))
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
SELECTsid,snamefromstudentwherestudent.sidin(SELECTscore.student_idfromscorewherescore.course_id=1orscore.course_id=2GROUPBYscore.student_idHAVINGcount(1)>1);
7.查询学过“李平老师”老师所教的所有课的同学的学号、姓名
SELECTsid,snamefromstudentwherestudent.sidin(SELECTscore.student_idfromscorewherescore.course_idin(SELECTcidfromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='李平老师')GROUPBYscore.student_idHAVINGcount(1)=(SELECTcount(1)fromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='李平老师'))
8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
SELECTsid,snamefromstudentwherestudent.sidin(selectA.student_idfrom(selectstudent_id,numberasnum_2fromscorewherescore.course_id=2)asALEFTJOIN(selectstudent_id,numberasnum_1fromscorewherescore.course_id=1)asBonA.student_id=B.student_idwhereA.num_2 9.查询有课程成绩小于60分的同学的学号、姓名 SELECTDISTINCTstudent.sid,student.snamefromscoreLEFTJOINstudentonscore.student_id=student.sidwherescore.number<60; 10.查询没有学全所有课的同学的学号、姓名 SELECTsid,snamefromstudentwheresidin(SELECTscore.student_idfromscoreGROUPBYstudent_idHAVINGcount(1)!=(SELECTcount(1)fromcourse)) 11.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名 SELECTsid,snamefromstudentwheresidin(SELECTDISTINCTstudent_idfromscorewherescore.course_idin(SELECTcourse_idfromscorewherestudent_id=1))andsid!=1; 12.查询至少学过学号为“1”同学所有课的其他同学学号和姓名***** SELECTsid,snamefromstudentwheresidin(SELECTstudent_idfromscorewherescore.course_idin(SELECTcourse_idfromscoreWHEREscore.student_id=1)GROUPBYstudent_idHAVINGcount(1)=(SELECTcount(1)fromscoreWHEREscore.student_id=1)) 13.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名 SELECTstudent.sid,snamefromscoreLEFTJOINstudentonscore.student_id=student.sidwherescore.student_idin(SELECTscore.student_idfromscorewherescore.student_id!=1GROUPBYstudent_idHAVINGcount(1)=(SELECTcount(1)fromscorewherescore.student_id=1)andcourse_idin(SELECTcourse_idfromscorewherescore.course_idin(SELECTcourse_idfromscorewherescore.student_id=1)GROUPBYstudent_idHAVINGcount(1)=(SELECTcount(1)fromscorewherescore.student_id=1))) 14.删除学习“叶平”老师课的score表记录 DELETEfromscorewherecourse_idin(SELECTcourse.cidfromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='李平老师'); 15.向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“001”课程的同学学号；②插入“001”号课程的平均成绩 INSERTintoscore(student_id,course_id,number)SELECTsid,1,(SELECTavg(number)fromscorewherescore.course_id=1)fromstudentwherestudent.sidnotin(selectstudent_idfromscorewherescore.course_id=1) 16.按平均成绩从低到高显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示：学生ID,生物、物理、体育,有效课程数,有效平均分 SELECTsc.student_id,(SELECTavg(number)fromscoreLEFTJOINcourseonscore.course_id=course.cidwherescore.student_id=sc.student_idandcourse.cname='生物')assw,(SELECTavg(number)fromscoreLEFTJOINcourseonscore.course_id=course.cidwherescore.student_id=sc.student_idandcourse.cname='物理')aswl,(SELECTavg(number)fromscoreLEFTJOINcourseonscore.course_id=course.cidwherescore.student_id=sc.student_idandcourse.cname='体育')asty,count(sc.course_id),avg(sc.number)asavg_nfromscorescGROUPBYsc.student_idORDERBYavg_nasc; 17.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 selectcourse_id,max(number),min(number)fromscoreGROUPBYcourse_id 18.按各科平均成绩从低到高和及格率的百分数从高到低顺序 SELECTscore.course_id,avg(number)asavg_n,(sum(casewhenscore.number>60then1else0end)/count(1)*100)aspercentfromscoreGROUPBYscore.course_idORDERBYavg_nasc,percentdesc; 19.课程平均分从高到低显示（现实任课老师） SELECTscore.course_id,avg(score.number)asavg_n,teacher.tnamefromscoreLEFTJOINcourseonscore.course_id=course.cidLEFTJOINteacheroncourse.teacher_id=teacher.tidGROUPBYscore.course_idORDERBYavg_ndesc; 20.查询各科成绩前三名的记录:(不考虑成绩并列情况) SELECTsc.course_id,(SELECTnumberfromscorewherescore.course_id=sc.course_idORDERBYnumberdesclimit0,1)asfirst_num,(SELECTnumberfromscorewherescore.course_id=sc.course_idGROUPBYnumberORDERBYnumberdesclimit1,1)assecond_num,(SELECTnumberfromscorewherescore.course_id=sc.course_idGROUPBYnumberORDERBYnumberdesclimit2,1)asthird_numfromscorescGROUPBYsc.course_id 21.查询每门课程被选修的学生数 SELECTcourse_id,count(student_id)fromscoreGROUPBYcourse_id 22.查询出只选修了一门课程的全部学生的学号和姓名 SELECTstudent.sid,student.snamefromscoreLEFTJOINstudentonscore.student_id=student.sidGROUPBYstudent_idHAVINGcount(score.course_id)=1; 23.查询男生、女生的人数 SELECT*from(SELECTcount(1)asboyfromstudentwherestudent.gender='男')asA,(SELECTcount(1)asgirlfromstudentwherestudent.gender='女')asB 24.查询姓“张”的学生名单 SELECT*fromstudentwherestudent.snamelike'张%' 25.查询同名同姓学生名单，并统计同名人数 SELECTsname,count(sname)fromstudentGROUPBYsnameHAVINGcount(1)>1; 26.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 SELECTcourse_id,avg(number)asavg_nfromscoreGROUPBYcourse_idORDERBYavg_nasc,course_iddesc; 27.查询平均成绩大于80的所有学生的学号、姓名和平均成绩 SELECTscore.student_id,student.sname,avg(number)fromscoreLEFTJOINstudentonscore.student_id=student.sidGROUPBYstudent_idHAVINGavg(number)>80; 28.查询课程名称为“生物”，且分数低于60的学生姓名和分数 SELECT*fromscoreLEFTJOINstudentonscore.student_id=student.sidwherescore.number<60andscore.course_idin(SELECTcidfromcoursewherecourse.cname='生物') 29.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名 SELECTstudent.sid,student.snamefromscoreLEFTJOINstudentonscore.student_id=student.sidwherescore.course_id=3andscore.number>80; 30.求选了课程的学生人数 SELECTcount(DISTINCTstudent_id)fromscorewherescore.course_idisnotnull; 31.查询选修刘海燕老师所授课程的学生中，成绩最高的学生姓名及其成绩 SELECTstudent.sname,max(score.number)fromscoreLEFTJOINstudentonscore.student_id=student.sidwherescore.course_idin(SELECTcidfromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='刘海燕老师') 32.查询各个课程及相应的选修人数 SELECTcourse_id,count(1)fromscoreGROUPBYcourse_id 33.查询不同课程但成绩相同的学生的学号、课程号、学生成绩 SELECTDISTINCTs1.course_id,s2.course_id,s1.number,s2.numberfromscoreass1,scoreass2wheres1.number=s2.numberands1.course_id!=s2.course_idorderbys1.course_id 34.查询每门课程成绩最好的前两名 SELECTsc.course_id,(SELECTnumberasf_nfromscorewherecourse_id=sc.course_idORDERBYnumberdesclimit0,1)asfirst_num,(SELECTnumberasf_nfromscorewherecourse_id=sc.course_idGROUPBYnumberORDERBYnumberdesclimit1,1)assecond_numfromscorescGROUPBYsc.course_id 35.检索至少选修两门课程的学生学号 SELECTstudent_id,count(1)fromscoreGROUPBYstudent_idHAVINGcount(1)>1; 36.查询全部学生都选修的课程的课程号和课程名 SELECTscore.course_id,course.cnamefromscoreLEFTJOINcourseonscore.course_id=course.cidGROUPBYcourse_idHAVINGcount(1)=(SELECTcount(1)fromstudent) 37.查询没学过李平老师讲授的任一门课程的学生姓名 SELECTsid,snamefromstudentwheresidnotin(SELECTDISTINCTstudent_idfromscorewherescore.course_idin(SELECTcidfromcourseLEFTJOINteacheroncourse.teacher_id=teacher.tidwhereteacher.tname='李平老师')) 38.查询两门以上不及格课程的同学的学号及其平均成绩 SELECTsc.student_id,(SELECTavg(number)fromscorewherescore.student_id=sc.student_id)asavg_nfromscorescwheresc.number<60GROUPBYstudent_idHAVINGcount(1)>1; 39.检索“004”课程分数小于60，按分数降序排列的同学学号 SELECTstudent_idfromscorewherescore.course_id=4andscore.number<60ORDERBYscore.numberdesc; 40.删除“002”同学的“001”课程的成绩 DELETEfromscorewherescore.student_id=2andscore.course_id=1;