查询练习.docx
- 文档编号:9421948
- 上传时间:2023-05-19
- 格式:DOCX
- 页数:13
- 大小:18.42KB
查询练习.docx
《查询练习.docx》由会员分享,可在线阅读,更多相关《查询练习.docx(13页珍藏版)》请在冰点文库上搜索。
查询练习
表结构
Student(SNO,Sname,Sage,Ssex)学生表
Course(CNO,Cname,TNO)课程表
SC(SNO,CNO,score)成绩表
Teacher(TNO,Tname)教师表
问题:
1、查询“”课程比“”课程成绩高的所有学生的学号;
SELECTa.SNOFROM(SELECTSno,scoreFROMSCWHERECNO='001')a,
(SELECTSno,scoreFROMSCWHERECNO='002')b
WHEREa.score>b.scoreANDa.Sno=b.Sno;
2、查询平均成绩大于分的同学的学号和平均成绩;
SELECTSNO,avg(score)
FROMsc
GROUPBYSNOhavingavg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
SELECTStudent.SNO,Student.Sname,count(SC.CNO),sum(score)
FROMStudentleftOuterJOINSConStudent.SNO=SC.SNO
GROUPBYStudent.SNO,Sname
4、查询姓“李”的老师的个数;
SELECTcount(distinct(Tname))
FROMTeacher
WHERETnamelike'李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
SELECTStudent.SNO,Student.SnameFROMStudent
WHERESNOnotin(SELECTdistinct(SC.SNO)FROMSC,Course,TeacherWHERESC.CNO=Course.CNOANDTeacher.TNO=Course.TNOANDTeacher.Tname='叶平');
6、查询学过“”并且也学过编号“”课程的同学的学号、姓名;
SELECTStudent.SNO,Student.SnameFROMStudent,SCWHEREStudent.SNO=SC.SNOANDSC.CNO='001'andexists(SELECT*FROMSCasSC_2WHERESC_2.SNO=SC.SNOANDSC_2.CNO='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECTSNO,Sname
FROMStudent
WHERESNOin(SELECTSNOFROMSC,Course,TeacherWHERESC.CNO=Course.CNOANDTeacher.TNO=Course.TNOANDTeacher.Tname='叶平'GROUPBYSNOhavingcount(SC.CNO)=(SELECTcount(CNO)FROMCourse,TeacherWHERETeacher.TNO=Course.TNOANDTname='叶平'));
8、查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名;
SELECTSNO,SnameFROM(SELECTStudent.SNO,Student.Sname,score,(SELECTscoreFROMSCSC_2WHERESC_2.SNO=Student.SNOANDSC_2.CNO='002')score2
FROMStudent,SCWHEREStudent.SNO=SC.SNOANDCNO='001')S_2WHEREscore2 9、查询所有课程成绩小于分的同学的学号、姓名; SELECTSNO,Sname FROMStudent WHERESNOnotin(SELECTStudent.SNOFROMStudent,SCWHERES.SNO=SC.SNOANDscore>60); 10、查询没有学全所有课的同学的学号、姓名; SELECTStudent.SNO,Student.Sname FROMStudent,SC WHEREStudent.SNO=SC.SNOGROUPBYStudent.SNO,Student.Snamehavingcount(CNO)<(SELECTcount(CNO)FROMCourse); 11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名; SELECTSNO,SnameFROMStudent,SCWHEREStudent.SNO=SC.SNOANDCNOinSELECTCNOFROMSCWHERESNO='1001'; 12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名; SELECTdistinctSC.SNO,Sname FROMStudent,SC WHEREStudent.SNO=SC.SNOANDCNOin(SELECTCNOFROMSCWHERESNO='001'); 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; updateSCsetscore=(SELECTavg(SC_2.score) FROMSCSC_2 WHERESC_2.CNO=SC.CNO)FROMCourse,TeacherWHERECourse.CNO=SC.CNOANDCourse.TNO=Teacher.TNOANDTeacher.Tname='叶平'); 14、查询和“”号的同学学习的课程完全相同的其他同学学号和姓名; SELECTSNOFROMSCWHERECNOin(SELECTCNOFROMSCWHERESNO='1002') GROUPBYSNOhavingcount(*)=(SELECTcount(*)FROMSCWHERESNO='1002'); 15、删除学习“叶平”老师课的SC表记录; DelectSC FROMcourse,Teacher WHERECourse.CNO=SC.CNOANDCourse.TNO=Teacher.TNOANDTname='叶平'; 16、向SC表中插入一些记录,这些记录要求符合以下条件: 没有上过编号“”课程的同学学号号课的平均成绩; InsertSCSELECTSNO,'002',(SELECTavg(score) FROMSCWHERECNO='002')FROMStudentWHERESNOnotin(SELECTSNOFROMSCWHERECNO='002'); 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 SELECTSNOas学生ID (SELECTscoreFROMSCWHERESC.SNO=t.SNOANDCNO='004')AS数据库 (SELECTscoreFROMSCWHERESC.SNO=t.SNOANDCNO='001')AS企业管理 (SELECTscoreFROMSCWHERESC.SNO=t.SNOANDCNO='006')AS英语 COUNT(*)AS有效课程数,AVG(t.score)AS平均成绩 FROMSCASt GROUPBYSNO ORDERBYavg(t.score) 18、查询各科成绩最高和最低的分: 以如下形式显示: 课程ID,最高分,最低分 SELECTL.CNOAs课程ID,L.scoreAS最高分,R.scoreAS最低分 FROMSCL,SCASR WHEREL.CNO=R.CNOAND L.score=(SELECTMAX(IL.score) FROMSCASIL,StudentASIM WHEREL.CNO=IL.CNOANDIM.SNO=IL.SNO GROUPBYIL.CNO) AND R.Score=(SELECTMIN(IR.score) FROMSCASIR WHERER.CNO=IR.CNO GROUPBYIR.CNO ); 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序 SELECTt.CNOAS课程号,max(course.Cname)AS课程名,isnull(AVG(score),0)AS平均成绩,100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)AS及格百分数 FROMSCT,Course WHEREt.CNO=course.CNO GROUPBYt.CNO ORDERBY100*SUM(CASEWHENisnull(score,0)>=60THEN1ELSE0END)/COUNT(*)DESC 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(),马克思(),OO&UML(),数据库() SELECTSUM(CASEWHENCNO='001'THENscoreELSE0END)/SUM(CASECNOWHEN'001'THEN1ELSE0END)AS企业管理平均分 100*SUM(CASEWHENCNO='001'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENCNO='001'THEN1ELSE0END)AS企业管理及格百分数 SUM(CASEWHENCNO='002'THENscoreELSE0END)/SUM(CASECNOWHEN'002'THEN1ELSE0END)AS马克思平均分 100*SUM(CASEWHENCNO='002'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENCNO='002'THEN1ELSE0END)AS马克思及格百分数 SUM(CASEWHENCNO='003'THENscoreELSE0END)/SUM(CASECNOWHEN'003'THEN1ELSE0END)ASUML平均分 100*SUM(CASEWHENCNO='003'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENCNO='003'THEN1ELSE0END)ASUML及格百分数 SUM(CASEWHENCNO='004'THENscoreELSE0END)/SUM(CASECNOWHEN'004'THEN1ELSE0END)AS数据库平均分 100*SUM(CASEWHENCNO='004'ANDscore>=60THEN1ELSE0END)/SUM(CASEWHENCNO='004'THEN1ELSE0END)AS数据库及格百分数 FROMSC 21、查询不同老师所教不同课程平均分从高到低显示 SELECTmax(Z.TNO)AS教师ID,MAX(Z.Tname)AS教师姓名,C.CNOAS课程ID,MAX(C.Cname)AS课程名称,AVG(Score)AS平均成绩 FROMSCAST,CourseASC,TeacherASZ WHERET.CNO=C.CNOANDC.TNO=Z.TNO GROUPBYC.CNO ORDERBYAVG(Score)DESC 22、查询如下课程成绩第3名到第6名的学生成绩单: 企业管理(),马克思(),UML(),数据库() [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 SELECTDISTINCTtop3 SC.SNOAs学生学号, Student.SnameAS学生姓名, T1.scoreAS企业管理, T2.scoreAS马克思, T3.scoreASUML, T4.scoreAS数据库, ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)as总分 FROMStudent,SCLEFTJOINSCAST1 ONSC.SNO=T1.SNOANDT1.CNO='001' LEFTJOINSCAST2 ONSC.SNO=T2.SNOANDT2.CNO='002' LEFTJOINSCAST3 ONSC.SNO=T3.SNOANDT3.CNO='003' LEFTJOINSCAST4 ONSC.SNO=T4.SNOANDT4.CNO='004' WHEREstudent.SNO=SC.SNOAND ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0) NOTIN (SELECT DISTINCT TOP15WITHTIES ISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0) FROMsc LEFTJOINscAST1 ONsc.SNO=T1.SNOANDT1.CNO='k1' LEFTJOINscAST2 ONsc.SNO=T2.SNOANDT2.CNO='k2' LEFTJOINscAST3 ONsc.SNO=T3.SNOANDT3.CNO='k3' LEFTJOINscAST4 ONsc.SNO=T4.SNOANDT4.CNO='k4' ORDERBYISNULL(T1.score,0)+ISNULL(T2.score,0)+ISNULL(T3.score,0)+ISNULL(T4.score,0)DESC); 23、统计列印各科成绩,各分数段人数: 课程ID,课程名称,[100-85],[85-70],[70-60],[<60] SELECTSC.CNOas课程ID,Cnameas课程名称 SUM(CASEWHENscoreBETWEEN85AND100THEN1ELSE0END)AS[100-85] SUM(CASEWHENscoreBETWEEN70AND85THEN1ELSE0END)AS[85-70] SUM(CASEWHENscoreBETWEEN60AND70THEN1ELSE0END)AS[70-60] SUM(CASEWHENscore<60THEN1ELSE0END)AS[60-] FROMSC,Course WHERESC.CNO=Course.CNO GROUPBYSC.CNO,Cname; 24、查询学生平均成绩及其名次 SELECT1+(SELECTCOUNT(distinct平均成绩) FROM(SELECTSNO,AVG(score)AS平均成绩 FROMSC GROUPBYSNO )AST1 WHERE平均成绩>T2.平均成绩)as名次, SNOas学生学号,平均成绩 FROM(SELECTSNO,AVG(score)平均成绩 FROMSC GROUPBYSNO )AST2 ORDERBY平均成绩desc; 25、查询各科成绩前三名的记录: (不考虑成绩并列情况) SELECTt1.SNOas学生ID,t1.CNOas课程ID,Scoreas分数 FROMSCt1 WHEREscoreIN(SELECTTOP3score FROMSC WHEREt1.CNO=CNO ORDERBYscoreDESC ) ORDERBYt1.CNO; 26、查询每门课程被选修的学生数 SELECTCno,count(SNO)FROMscGROUPBYCNO; 27、查询出只选修了一门课程的全部学生的学号和姓名 SELECTSC.SNO,Student.Sname,count(CNO)AS选课数 FROMSC,Student WHERESC.SNO=Student.SNOGROUPBYSC.SNO,Student.Snamehavingcount(CNO)=1; 28、查询男生、女生人数 SELECTcount(Ssex)as男生人数FROMStudentGROUPBYSsexhavingSsex='男'; SELECTcount(Ssex)as女生人数FROMStudentGROUPBYSsexhavingSsex='女'; 29、查询姓“张”的学生名单 SELECTSnameFROMStudentWHERESnamelike'张%'; 30、查询同名同性学生名单,并统计同名人数 SELECTSname,count(*)FROMStudentGROUPBYSnamehavingcount(*)>1;; 31、年出生的学生名单(注: Student表中Sage列的类型是datetime) SELECTSname,CONVERT(char(11),DATEPART(year,Sage))asage FROMstudent WHERECONVERT(char(11),DATEPART(year,Sage))='1981'; 32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 SELECTCNO,Avg(score)FROMSCGROUPBYCNOORDERBYAvg(score),CNODESC; 33、查询平均成绩大于的所有学生的学号、姓名和平均成绩 SELECTSname,SC.SNO,avg(score) FROMStudent,SC WHEREStudent.SNO=SC.SNOGROUPBYSC.SNO,Snamehavingavg(score)>85; 34、查询课程名称为“数据库”,且分数低于的学生姓名和分数 SELECTSname,isnull(score,0) FROMStudent,SC,Course WHERESC.SNO=Student.SNOANDSC.CNO=Course.CNOANDCourse.Cname='数据库'andscore<60; 35、查询所有学生的选课情况; SELECTSC.SNO,SC.CNO,Sname,Cname FROMSC,Student,Course WHERESC.SNO=Student.SNOANDSC.CNO=Course.CNO; 36、查询任何一门课程成绩在分以上的姓名、课程名称和分数; SELECTdistinctstudent.SNO,student.Sname,SC.CNO,SC.score FROMstudent,Sc WHERESC.score>=70ANDSC.SNO=student.SNO; 37、查询不及格的课程,并按课程号从大到小排列 SELECTCnoFROMscWHEREscore<60ORDERBYCNO; 38、查询课程编号为且课程成绩在分以上的学生的学号和姓名; SELECTSC.SNO,Student.SnameFROMSC,StudentWHERESC.SNO=Student.SNOANDScore>80ANDCNO='003'; 39、求选了课程的学生人数 SELECTcount(*)FROMsc; 40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩 SELECTStudent.Sname,score FROMStudent,SC,CourseC,Teacher WHEREStudent.SNO=SC.SNOANDSC.CNO=C.CNOANDC.TNO=Teacher.TNOANDTeacher.Tname='叶平'ANDSC.score=(SELECTmax(score)FROMSCWHERECNO=C.CNO); 41、查询各个课程及相应的选修人数 SELECTcount(*)FROMscGROUPBYCNO; 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 SELECTdistinctA.SNO,B.scoreFROMSCA,SCBWHEREA.Score=B.ScoreANDA.CNO<>B.CNO; 43、查询每门功成绩最好的前两名 SELECTt1.SNOas学生ID,t1.CNOas课程ID,Scoreas分数 FROMSCt1 WHEREscoreIN(SELECTTOP2score FROMSC WHEREt1.CNO=CNO ORDER
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 查询 练习