数据库sql查询语句练习2-习题-结果(单世民).docx

现在有一教学管理系统，具体的关系模式如下：Student(no,name,sex,birthday,class)TeaCher(no,name,sex,birthday,prof,depart)Course(cno,cname,tno)Score(no,cno,degree)其中表中包含如下数据：COlIrSe表：cnocnametno3-101数据库15-102数学33-103信息系统43-104操作系统63-105数据结构43-106数据处理54-107PaSCaI语言54-108C+74-109java83-245数据挖掘103-111软件工程11Score表：nocnodegree5i!11l3-1056950015-1025550034-1088550043-1057750053-24510050063-1055350034-1094550083-1059850044-1096850103-1058850033-1059850054-1096850023-105881073-105981084-109681093-105881094-109801073-1118850033-11180Student表:nonamesexbirthdayclass李勇男1987-7-220:00:00950015002刘晨女1987-11-150:0.950025003王敏女1987-10-50:00:00950015004李好尚男1987-9-250:00:00950035005李军男1987-7-170:00:00950045006范新位女1987-6-180:00:00950055007张霞东女1987-8-290:00:00950065008赵薇男19876150:00:00950075009钱民将女1987-6-230:00:00950085010孙俪女1987-9-240:00:0095002108赵里男1987-6-150:00:0095007109丘处机男1987-6-230:00:0095008107杨康男1987-9-240:00:0095001Teacher表：nonamesexbirthdayprofdepartEJ李卫男1957-11-50:00:00教授电子工程系2刘备男1967-10-90:00:00副教授math3关羽男1977-9-200:00:00讲师SC4李修男1957-6250:00:00教授elec5诸葛亮男1977-6-150:00:00教授计篁机系6殷素素女1967-1-50:00:00副教授SC7周芷若女1947-2-230:00:00教授SC8赵云男19806130:00:00副教授计算机系9张敏女1985-5-50:00:00助教SC10黄蓉女1967-3-220:00:00副教授SC11张三男1967-3-220:00:00副教授SC根据上面描述完成下面问题：（法窝注意保存脚本，尤其是DDL和DML,以便进行数据复原）DDL1 .写出上述表的建表语句。2 .给出相应的INSERT语句来完成题中给出数据的插入。单表查询3 .以class降序输出student的所有记录（StIIdent表全部属性）命令：select*fromStudentorderbyclassdesc;nonamesexbirthdayclass1I5009i钱民将女1987-06-2300:00:00.000950082109丘处机男1987-06-2300:00:00,000950083108赵里男1987-06-1500:00:00.0009500745008赵薇男1987-06-1500:00:00.0009500755007张霞东女1987-08-2900:00:00.0009500665006范新位女1987-06-1800:00:00,0009500575005李军男1987-07-1700:00:00,0009500485004李好尚男1987-09-2500:00:00.0009500395002刘晨女1987-11-1500:00:00,00095002105010孙俪女1987-09-2400:00:00.0009500211107杨康男1987-09-2400:00:00.00095001125003王敏女1987-10-0500:00:00.00095001135001李勇男1987-07-2200:00:00,000950014 .列出教师所在的单位depart（不重复）。命令：selectdistinctdepartfromTeacher;5 .列出student表中所有记录的namcsex和class列命令：selectname,sexzclassfromStudent;nameSeXclass1李勇i男950012刘晨女950023王敏女950014李好尚男950035李军男950046范新位女950057张霞东女950068赵薇男950079钱民将女9500810孙丽女9500211赵里男9500712丘处机男9500813杨康男950016 .输出student中不姓王的同学的姓名。命令iselectnamefromStudentexceptselectnamefromStudentwherenamelike，王；或selectnamefromStudentwherenamenotlike'王'name1修勇i2刘晨3李好尚4李军5范新位6张霞东7赵薇8钱民将9孙俪10赵里11丘处机12杨康7 .输出成绩为85或86或88或在60-80之间的记录(no,cno,degree)命令：selectno,cnozDEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and808 .输出班级为95001或性别为'女'的同学（StlIdent表全部属性）命令：select*fromStudentwhereclass=95001orSeX='女'nonamesexbirthdayclass15001李勇男1987-07-2200:00:00,0009500125002刘晨女1987-11-1500:00:00.0009500235003王敏女1987-10-0500:00:00,0009500145006范新位女1987-06-1800:00:00.0009500555007张霞东女1987-08-2900:00:00.0009500665009钱民将女1987-06-2300:00:00.0009500875010孙俪女1987-09-2400:00:00,000950028107杨康男1987-09-2400:00:00.000950019 .以cno升序、degree降序输出score的所有记录。(score表全部属性)命令：select*fromScoreorderbycnoasc,degreedesc;10 .输出男生人数及这些男生分布在多少个班级中命令：selectCOUNT(*),count(distinctclass)fromStudentwhereSeX='男'；(Nocolumnname)(Nocolumnname)1Pr5H.列出存在有85分以上成绩的课程编号。命令：selectdistinctcnofromScorewheredegree>85;12.输出95001班级的学生人数命令：selectCOUNT(*)fromStudentwhereclass=95001.(Nocolumnname)1区二二二二13.输出43-105,号课程的平均分命令：selectavg(cast(degreeasfloat)fromScorewherecno='3-105,;(Nocolumnname)1ii;一i14.输出student中最大和最小的birthday日期值命令：selectMAX(birthday)zMIN(birthday)fromStudent;(Nocolumnname)(Nocolumnname)1119茄芾而;00；而诵；1987061500:00:00.00015.显示95001闹95004嵬全体学生的全部个人信息(不包括选课)。(StUdent表全部属性)命令：select*fromStudentwhereclass=95001orclass=95004nonamesexbirthdayclass1I5001i李勇男1987-07-2200:00:00,0009500125003王敏女1987-10-0500:00:00,0009500135005李军男1987-07-1700:00:00.000950044107杨康男1987-09-2400:00:00.00095001聚合查询16.输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分。命令：selectcno,avg(cast(degreeasfloat),MAX(degree),MIN(degree)fromScorewherecnolike,3%,groupbycnohavingCOUNT(cno)>5;或者：selectcnorAVG(cast(DEGREEasfloat)MAX(degree),MIN(DEGREE)fromScoregroupbycnohavingCOUNT(cno)>=5andcnolike,3%,.cno(Nocolumnname)(Nocolumnname)(Nocolumnname)1IIOS,84.111111985317.输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名命令：selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX(Score.degree)<90andMIN(Score.degree)>70;cno(Nocolumnname)1I3-10588,33333323-11184,00000034-10885.0000004410945.0000005510255.00000020 .输出至少有两名男同学的班级编号。命令:selectclassfromStudentwheresex=,男'groupbyclasshavingCOUNT(class)>=2;或者tselecta.classfrom(select*fromStudentwheresex=,H')agroupbya.classhavingCOUNT(a.class)>=2class1j95001;295007多表查询21 .列出与108号同学同年出生的所有学生的学号、姓名和生日命令：selectno,name,birthdayfromStudentwhereyear(birthday)=(selectyear(birthday)fromStudentwhereno=108);或者：selectb.nozb.namezb.birthdayfromStudentajoinStudentbondatediff(YEAR,a.birthdayzb.birthday)=0anda.no=,108,nonamebirthday15001:李勇1987-07-2200:00:00.00025002刘晨1987-11-1500:00:00.00035003王敏1987-10-0500:00:00.00045004李好尚1987-09-2500:00:00.00055005李军1987-07-1700:00:00.00085006范新位1987-06-1800:00:00,00075007张霞东1987-08-2900:00:00,00085008赵薇1987-06-1500:00:00.00095009钱民将1987-06-2300:00:00.000105010孙俪1987-09-2400:00:00.00011108赵里1987-06-1500:00:00.00012109丘处机1987-06-2300:00:00.00013107杨康1987-09-2400:00:00.00022 .列出存在有85分以上成绩的课程名称命令：selectcnamefromCoursewherecnoin(selectdistinctcnofromScorewheredegree>85);或selectdistinctcnamefromCoursejoinScoreonCourseo=Scoreowheredegree>85;cnamerSi23数据挖掘23 .列出“计算机系”教师所教课程的成绩表(课程编号，课程名，学生名，成绩)。命令：selectCourseozcname,Student.name,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCourseO=ScoreojoinStudentonScore.no=Student.nowhereTeacher.depart=,计算机系,;cnocnamenamedegree1I4-109Iava王敏4524-109java李好尚6834-109ava李军6844-109java赵里6854-109java丘处机8024 .列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息，要求输出每个老师的姓名（name）和（职称）命令：selecta.nsme,a.profzb.name,b.proffrom（selectname,profzdepartfromTeacherwheredepart=,计算机系，ordepart='电子工程系，）ajoin（selectnamezprofzdepartfromTeacherwheredepart=，电子工程系，ordepar七=，计算机系，）bonnota.prof=b.profandnota.depart=b.depart;nameprofnameprof1 军至：教授赵云副教授2 '三副教授李卫教授25 .列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学号和姓名。（提示：使用datediff函数，具体用法可以参考：）命令：selecta.no,a.name,b.no,b.namefromStudentajoinStudentbonnota.class=b.classanda.birthday=b.birthdayInonamenoname1IIO7J杨康5010孙俪25010孙俪107杨康26 .显示张三'教师任课的学生姓名，课程名，成绩命令：selectStudent.name,cname,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCourseo=ScoreojoinStudentonScore.no=Student.nowhereTeacher.name=,SfcH,;namecnamedegree1 蔽：软件工程802 '拓康软件工程8827 .列出所讲课已被选修的教师的姓名和系别join Course on命令：selectdistinctname,departfromTeacherTeacher.no=Course.tnojoinScoreonCourseo=Scoreo;namedepart1I关羽；SC2黄蓉SC3李修elec4张三SC5赵云计苴机系6周芷若SC28 .输出所有学生的name、no和degree。（degree为空的不输出和为空的输出两种情况）。命令:selectnamezStudent.no,DEGREEfromStudentleftjoinScoreonStudent.no=Score.no;selectname,Student.no,DEGREEfromStudentjoinScoreonStudent.no=Score.no;namenodegreenamenodegree1;杨康1107981李勇5001692杨康107882李勇5001553赵里108683王敏5003854丘处机109884李好尚5004775丘处机109805李军50051006李勇5001696范新位5006537李勇5001557王敏5003458刘晨5002888赵薇5008989王敏5003859李好尚50046810王敏50034510孙俪50108811王敏50039811王敏50039812王敏50038012李军50056813李好尚50047713刘晨50028814李好尚50046814杨康1079815李军500510015赵里1088816李军50056816丘处机1098817范新位50065317丘处机1098018张霞东5007NULL18杨康1078819赵薇50089819王敏50038020钱民将5009NULL21孙俪50108829 .列出所有任课教师的name和depart。（从课程选修和任课两个角度考虑）命令：selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tno;selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCourseo=Scoreo;namedepartnamedepart1一关羽Isc1:关羽|SC2黄蓉SC2黄蓉SC3李修elec3李卫电子工程系4张三SC4李修elec5赵云计算机系5殷素素SC6周芷若SC6张三SC7赵云计算机系8周芷若SC9诸葛克计算机系30 .输出男教师所上课程名称。命令：selectcnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereTeacher.sex='S'；cname1；数据库2数学3信息系统4数据结构5数据处理6pSCf357java8软件工程31 .出与“李军”同性别的所有同学的name。命令：selectnamefromStudentwheresex=(selectsexfromStudentwherename=李军，)；name:李勇：2李好尚Tj李军4赵薇5赵里6丘处机2_杨康32 .输出选修“数据结构”课程的男同学的成绩。命令：selectDEGREEfromScorejoinStudentonScore.no=Student.nojoinCourseonScoreO=Courseowheresex='男'andcname=,数据结构,;degree1 9I2 方3 984 885 9833 .列出选修编号为3405,课程并且该门课程成绩比课程'3111'的最高分要高的CIlO,no和degree。命令：selectCourseo,Score.no,DEGREEfromCoursejoinScoreonCourseo=ScoreojoinStudentonScore.no=Student.nowhereCourseo=*3-105,andScore.degree>(selectmax(degree)fromScorewhereScoreo=,3-111,);cnonodegree1"3-i5j5008982,3-105""50039833-10510798子查询34 .输出score中成绩最高的学号和课程号命令：selectno,cnofromScorewheredegreein(selectMAX(degree)fromScore);nocno15bd513-24535.输出选修31O5课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓名命令：selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nowhereScore0=,3-105,anddegree>(selectdegreefromScorewhereno=,109,andcno三,3-105,);noname15003在敏25008赵薇3107杨康36.列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩命令：selectno,DEGREEza.avg_degreefromScorejoin(selectcnozAVG(cast(degreeasfloat)fromScoregroupbycno)a(cnozavg_degree)onScoreo=a0whereScore.degree<a.avg_degree;nodegreeavg_degree150016984.111111250047784.111111350065384.111111450038084.000000550034565,80000037.列出没有实际授课的教师的姓名和系别命令：selectname,departfromTeacherexceptselectname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCourseo=Score0;namedepart1i李卫电子工程系2刘备math3诸葛亮计算机系4殷素素SC5张敏SC38.列出选修了编号为'3105'课程且其成绩高于'4109'课程最高成绩的同学的课程编号，学号和成绩命令:selectcno,no,DEGREEfromScorewherecno=*3-105,anddegree>(selectMAX(degree)fromScorewherecno=,4-109,)cnonodegree1三3-105I50089823-10550108833-10550039843-10550028853-1051079863-1051098839.*列出符合下述条件的所有可能的同学配对(sno1,snameI,sno2,sname2,difference)其中要求学号为snol的snamel同学的所学课程的平均分大于学号为sno2的sname2同学的所学课程平均分，两个同学的课程平均分的差值difference为(SlIOl同学平均分sno2同学平均分)命令：selecta.no,a.name,b.no,b.name,a.avg_degree-b.avg_degreeasdifferencefrom(selectStudent.no,name,avg(degree)fromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,name)a(no,namezavg_degree)join(selectStudent.no,name,avg(degree)fromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,name)b(no,name,avg_degree)ona.avg_degree>b.avg_degree;nonamenonamedifference1；5001:李勇5006范新位9.00000025002刘晨108赵里20.00000035002刘晨109丘处机4.00000045002刘晨5001李勇26.00000055002刘晨5003王敏11.00000065002刘晨5004李好尚15.50000075002刘晨5005李军4.00000085002刘晨5006范新位35.00000095003王敏108赵里9.000000105003王敏5001李勇15.000000115003王敏5004李好尚4.500000125003王敏5006范新位24.000000135004李好尚108赵里4.500000145004李好尚5001李勇10.500000155004李好尚5006范新位19.500000165005李军108赵里16.000000175005李军5001李勇22.000000185005李军5003王敏7.000000195005李军5004李好尚11.500000205005李军5006范新位31.000000215008赵薇107杨康5.000000225008赵薇108赵里30.000000235008赵薇109丘处机14.000000245008赵薇5001李勇36.000000255008赵薇5002刘晨10.000000265008赵薇5003王敏21.000000275008赵薇5004李好尚25.500000nonamenonamedifference285008赵薇5005李军14.000000295008赵薇5006范新位45.000000305008赵薇5010孙俪10.000000315010孙丽108赵里20,000000325010孙丽109丘处机4.000000335010孙俪5001李勇26.000000345010孙俪5003王敏11.000000355010孙俪5004李好尚15.500000365010孙俪5005李军4.000000375010孙丽5006范新位35.00000038108赵里5001李勇6.00000039108赵里5006范新位15.00000040109丘处机108赵里16.00000041109丘处机5001李勇22,00000042109丘处机5003王敏7.00000043109丘处机5004李好尚11.50000044109丘处机5006范新位31.00000045107杨康108赵里25.00000046107杨康109丘处机9.00000047107杨康5001李勇31.00000048107杨康5002刘晨5.00000049107杨康5003王敏16.00000050107杨康5004李好尚20.50000051107杨康5005李军9.00000052107杨康5006范新位40.00000053107杨康5010孙俪5.000000noname1Iidg丘处机25002,刘晨35010孙俪18.显示所教课程选修人数多于5人的教师姓名命令：selectnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereCourse0in(selectcnofromScoregroupbycnohavingCOUNT(Score0)>5);name1李修：19.输出厂950Oy班级所选课程的课程号和平均分命令：selectcnozavg(cast(degreeasfloat)fromScorewherenoin(selectnofromStudentwhereclass=95001)groupbycno;或者：selectcno,AVG(cast(degreeasfloat)fromScorejoinStudentonScore.no=Student.nogroupbycno,classhavingclass=*12395001,