超经典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稍有差异*50道练习题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

1 / 19
下载文档,编辑使用

©2015-2020 m.777doc.com 三七文档.

备案号:鲁ICP备2024069028号-1 客服联系 QQ:2149211541

×
保存成功