超经典SQL练习题20181119

测试表格--1.学生表Student(SNO,Sname,Sage,Ssex)--SNO学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别--2.课程表Course(CNO,Cname,TNO)--CNO--课程编号,Cname课程名称,TNO教师编号--3.教师表Teacher(TNO,Tname)--TNO教师编号,Tname教师姓名--4.成绩表SC(SNO,CNO,score)--SNO学生编号,CNO课程编号,score分数创建测试数据学生表StudentcreatetableStudent(SNOvarchar(10),Snamenvarchar(10),Sagedatetime,Ssexnvarchar(10))insertintoStudentvalues('01',N'赵雷','1990-01-01',N'男')insertintoStudentvalues('02',N'钱电','1990-12-21',N'男')insertintoStudentvalues('03',N'孙风','1990-05-20',N'男')insertintoStudentvalues('04',N'李云','1990-08-06',N'男')insertintoStudentvalues('05',N'周梅','1991-12-01',N'女')insertintoStudentvalues('06',N'吴兰','1992-03-01',N'女')insertintoStudentvalues('07',N'郑竹','1989-07-01',N'女')insertintoStudentvalues('08',N'王菊','1990-01-20',N'女')科目表CoursecreatetableCourse(CNOvarchar(10),Cnamenvarchar(10),TNOvarchar(10))insertintoCoursevalues('01',N'语文','02')insertintoCoursevalues('02',N'数学','01')insertintoCoursevalues('03',N'英语','03')教师表TeachercreatetableTeacher(TNOvarchar(10),Tnamenvarchar(10))insertintoTeachervalues('01',N'张三')insertintoTeachervalues('02',N'李四')insertintoTeachervalues('03',N'王五')成绩表SCcreatetableSC(SNOvarchar(10),CNOvarchar(10),scoredecimal(18,1))insertintoSCvalues('01','01',80)insertintoSCvalues('01','02',90)insertintoSCvalues('01','03',99)insertintoSCvalues('02','01',70)insertintoSCvalues('02','02',60)insertintoSCvalues('02','03',80)insertintoSCvalues('03','01',80)insertintoSCvalues('03','02',80)insertintoSCvalues('03','03',80)insertintoSCvalues('04','01',50)insertintoSCvalues('04','02',30)insertintoSCvalues('04','03',20)insertintoSCvalues('05','01',76)insertintoSCvalues('05','02',87)insertintoSCvalues('06','01',31)insertintoSCvalues('06','03',34)insertintoSCvalues('07','02',89)insertintoSCvalues('07','03',98)*以下代码为mysql中运行结果，与SQLServer稍有差异*５０道练习题1.查询01课程比02课程成绩高的学生的信息及课程分数selectA.*,B.CNO,B.scorefrom(select*fromscwhereCNO='01')Aleftjoin(select*fromscwhereCNO='02')BonA.SNO=B.SNOwhereA.scoreB.score1.1查询同时存在01课程和02课程的情况SELECT*from(SELECT*fromscwhereCNO='01')ALEFTJOIN(SELECT*fromscwhereCNO='01')BonA.SNO=B.SNOwhereB.scoreisnotnull1.2查询存在01课程但可能不存在02课程的情况(不存在时显示为null)SELECT*FROM(SELECT*fromscwhereCNO='01')ALEFTJOIN(SELECT*fromscwhereCNO='02')BonA.SNO=B.SNO#可能不存在1.3查询不存在01课程但存在02课程的情况SELECT*FROM(SELECT*fromscwhereCNO='01')ArightJOIN(SELECT*fromscwhereCNO='02')BonA.SNO=B.SNOwhereA.scoreisnull2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩SELECTB.Sname姓名,B.SNO编号,A.PJ平均成绩fROM(SELECTSNO,AVG(score)PJfromscGROUPBYSNO)ALEFTJOINstudentBonA.SNO=B.SNOwherePJ=603.查询在SC表存在成绩的学生信息SELECT*fromstudentwhereSNOin(SELECTSNOfromsc)4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)SELECTA.SNO学生编号,A.Sname学生姓名,B.kc选课总数,B.zcj总成绩fromstudentALEFTJOIN(SELECTSNO,COUNT(*)kc,SUM(score)zcjfromscGROUPBYSNO)BonA.SNO=B.SNO4.1查有成绩的学生信息SELECTA.SNO学生编号,A.Sname学生姓名,B.kc选课总数,B.zcj总成绩fromstudentArightJOIN(SELECTSNO,COUNT(*)kc,SUM(score)zcjfromscGROUPBYSNO)BonA.SNO=B.SNO5.查询「李」姓老师的数量selectCOUNT(*)李姓老师数量fromTeacherwhereTnamelike'李%'6.查询学过「张三」老师授课的同学的信息SELECTA.*fromstudentALEFTJOINscBonA.SNO=B.SNOLEFTJOINcourseConB.CNO=C.CNOLEFTjointeacherDonC.TNO=D.TNOwhereD.Tname='张三'7.查询没有学全所有课程的同学的信息SELECTA.*fromstudentALEFTJOIN(SELECTSNO,COUNT(*)kcsFROMscGROUPBYSNO)BonA.SNO=B.SNOwhereB.kcs38.查询至少有一门课与学号为01的同学所学相同的同学的信息SELECT*fromstudentwhereSNOin(selectDISTINCTSNOfromscwhereCNOin(selectCNOfromscwhereSNO='01'))9.查询和01号的同学学习的课程完全相同的其他同学的信息SELECTA.*FROMstudentArightJOIN(SELECTSNO,COUNT(*)KCSfromscGROUPBYSNOHAVINGKCS=3)BonA.SNO=B.SNOwhereA.SNO0110.查询没学过张三老师讲授的任一门课程的学生姓名SELECTSnameFROMstudentWHERESnamenotin(SELECTD.SnamefromcourseALEFTJOINteacherBonA.TNO=B.TNOLEFTJOINscConA.CNO=C.CNOLEFTJOINstudentDonC.SNO=D.SNOwhereB.Tname='张三')11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩SELECTA.SNO学号,A.Sname姓名,B.PJ平均成绩fromstudentArightJOIN(SELECTSNO,COUNT(*),AVG(score)pjfromscwherescore60GROUPBYSNOHAVINGCOUNT(*)1)BonA.SNO=B.SNO12.检索01课程分数小于60，按分数降序排列的学生信息SELECTB.*,A.scorefromscALEFTJOINstudentBonA.SNO=B.SNOwhereA.CNO=01andA.score60ORDERBYa.scoreDESC13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩selectSNO,max(caseCNOwhen'01'thenscoreelse0end)'01',max(caseCNOwhen'02'thenscoreelse0end)'02',max(caseCNOwhen'03'thenscoreelse0end)'03',AVG(score)平均分fromSCgroupbySNOorderby平均分desc14.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为=60，中等为：70-80，优良为：80-90，优秀为：=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列selectA.CNO,A.Cname,B.DA最高分,B.XIAO最低分,B.JUN平均分,C.jige及格率,D.zd中等率,E.yl优良率,F.yx优秀率,G.rs选修人数fromcourseALEFTJOIN(SELECTCNO,max(score)DA,min(score)XIAO,avg(score)JUNfromscGROUPBYCNO)BonA.CNO=B.CNOLEFTJOIN(SELECTCNO,sum(casewhenscore=60then1else0end)/count(*)jigeFROMscGROUPBYCNO)ConA.CNO=C.CNOLEFTJOIN(SELECTCNO,sum(casewhenscore=70andscore80then1else0end)/count(*)zdfromscGROUPBYCNO)DonA.CNO=D.CNOLEFTJOIN(SELECTCNO,sum(casewhenscore=80andscore90then1else0end)/count(*)ylfromscGROUPBYCNO)EonA.CNO=E.CNOLEFTJOIN(SELECTCNO,sum(casewhenscore=90then1else0end)/count(*)yxfromscGROUPBYCNO)FonA.CNO=F.CNOLEFTJOIN(SELECTCNO,count(*)rsfromscGROUPBYCNO)GonA.CNO=G.CNOORDERBYG.rsDESC,A.CNO15.按各科成绩进行排序，并显示排名，Score重复时保留名次空缺SELE