scST2SANDT2Ck2r
LEFTJOINscAST3r
ONscST3SANDT3Ck3r
LEFTJOINscAST4r
ONscST4SANDT4Ck4r
ORDERBYISNULLT1score0ISNULLT2score0ISNULLT3score0ISNULLT4score0DESCr
r
23、统计列印各科成绩各分数段人数课程ID课程名称100858570706060r
SELECTSCCas课程IDC
ameas课程名称r
SUMCASEWHENscoreBETWEEN85AND100THEN1ELSE0ENDAS10085r
SUMCASEWHENscoreBETWEEN70AND85THEN1ELSE0ENDAS8570r
SUMCASEWHENscoreBETWEEN60AND70THEN1ELSE0ENDAS7060r
SUMCASEWHENscore60THEN1ELSE0ENDAS60r
FROMSCCourser
whereSCCCourseCr
GROUPBYSCCC
amer
r
24、查询学生平均成绩及其名次r
SELECT1SELECTCOUNTdisti
ct平均成绩r
FROMSELECTSAVGscoreAS平均成绩r
FROMSCr
GROUPBYSr
AST1r
WHERE平均成绩T2平均成绩as名次r
Sas学生学号平均成绩r
FROMSELECTSAVGscore平均成绩r
FROMSCr
GROUPBYSr
AST2r
ORDERBY平均成绩descr
r
25、查询各科成绩前三名的记录不考虑成绩并列情况r
SELECTt1Sas学生IDt1Cas课程IDScoreas分数r
FROMSCt1r
WHEREscoreINSELECTTOP3scorer
FROMSCr
WHEREt1CCr
ORDERBYscoreDESCr
r
ORDERBYt1Cr
26、查询每门课程被选修的学生数r
selectccou
tSfromscgroupbyCr
27、查询出只选修了一门课程的全部学生的学号和姓名r
selectSCSStude
tS
amecou
tCAS选课数r
fromSCStude
tr
whereSCSStude
tSgroupbySCSStude
tS
amehavi
gcou
tC1r
28、查询男生、女生人数r
Selectcou
tSsexas男生人数fromStude
tgroupbySsexhavi
gSsex男r
Selectcou
tSsexas女生人数fromStude
tgroupbySsexhavi
gSsex女；r
29、查询姓“张”的学生名单r
SELECTS
ameFROMStude
tWHERES
amelike张r
30、查询同名同性学生名单，并统计同名人数r
selectS
amecou
tfromStude
tgroupbyS
amehavi
gcou
t1r
31、1981年出生的学生名单注：Stude
t表中Sage列的类型是datetimer
selectS
ameCONVERTchar11DATEPARTyearSageasager
fromstude
tr
whereCONVERTchar11DATEPARTyearSage1981r
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列r
SelectCAvgscorefromSCgroupbyCorderbyAvgscoreCDESCr
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩r
selectS
ar