数据库sql查询语句练习2-习题-结果.docx

《数据库sql查询语句练习2-习题-结果.docx》由会员分享，可在线阅读，更多相关《数据库sql查询语句练习2-习题-结果.docx（6页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、现在有一教学管理系统，具体的关系模式如下：Student (no, name, sex, birthday, class)Teacher (no, name, sex, birthday, prof, depart)Course (eno, cname, tno)Score (no, eno, degree)其中表中包含如下数据：Course 表：Score 表：Student 表：Teacher 表：根据上面描述完成下面问题：法意：注意保存脚本，尤其是加L和DML,以便进展数据复原)DDL1 .写出上述表的建表语句。命令：createtableStudent(noint,namevarcha

2、r(14),sexvarchar(2),birthdayda tezclassint);createtableTeacher(nointznamevarchar(14),sexvarchar(2),birthdayda terprofvarchar (10),departvarchar (10);createtableCourse(cnovarchar(8),cnamevarchar(14),tnoint);createtableScore(noint,cnovarchar(8),degreeint);DML2 .给出相应的INSERT语句来完成题中给出数据的插入。命令：inser tinto

3、Studentvalues (5001, 1 李勇男1987-7-22，95001);ins er tintoStudent values (5002, 1刘晨，女I 1987 -11 -15, 95002);ins er tintoStudentvalues (5003, 1王敏女，1987-10-5，95001);insert in toStudent values (5004, 1李好尚J男 I 1987 - 9-25，95003);ins er tintoStudentvalues (5005, !李军男1987 - 7 -17,95004);ins er tintoStudentva

4、lues (5006, f范新位女l 4987-6-18195005);ins er tintoStudentvalues (5007, 1张霞东女I 1987 - 8 -291 95006);ins er tintoStudentvalues (5008.赵薇，男，1987-6-15, 95007);inser tintoStudentvalues (5009, 1人民币民将J 女1987-6-23195008);ins er tintoStudentvalues (5010, 1孙俪女，1987-9-24, 95002);ins er tintoStudentvalues (108 J 赵

5、里4987 - 6-15,95007);ins er tintoStudentvalues (10 9 J 丘处机男 I 1987-6-23195008);inser tintoStudentvalues (107 J 杨康J 男J1987 - 9-24,95001);insert in toTeache rvalues (1, * 李卫 1 , *, 11957-11-5 * , * 教授 * , 1电子工程系 1);insertintoTeachervalues (2,文U备I 男UgGI-lO 副教授J math * );insertintoTeachervalues (3, 1 关羽

6、1, 11, 11977-9-20 * , * 讲师 1, 1 sc * );insertintoTeachervalues (4 ,李修 1, 1, 11957-6-25 1, 1教授 1, 1 elec 1);insert in toTeache rvalues (5 3 诸葛亮 I，男 I，1977-6-151，教授 1rT计算机系 , );insert in toTeache rvalues (6 J 殷素素 3 女I，1967-1 - 51，副教授 1, 1 sc 1);inser tintoTeachervalues (7,。周芷假设J 女J1947 -2 -23,教授 *, *

7、sc * );insertintoTeachervalues (8 J赵云，J 男 I，1980-6-13 I，副教授一计算机系 1);insert intoTeachervalues (9J 张敏 I，女 l，1985-5-51，助教 I，sc，);insert in toTeache rvalues (103 黄蓉 I，女 I 4967 - 3 -22J 副教授 * , 1 sc T);insertintoTeachervalues (11J 张三 , , , 11967-3-22 1, 1副教授 * , * sc * );insertintoCoursevalues ( 1 3-101

8、* ,数据库 1)；ins er t in toCourse values ( 1 5-102 I 1数学 1,2);insertintoCoursevalues ( 3-103 I，信息系统 I 3);insert in toCourseva lues ( 1 3-104 1, 1 操作系统 1,4);insertintoCoursevalues ( 1 3-105 1 , 1数据构造 1,5);insertintoCoursevalues ( 1 3-106 1z1数据处理 1, 5);insertintoCoursevalues ( 1 4-107 frpascal语言 1,6);ins

9、ertintoCoursevalues(1 4-1081, * C+ 1,7);insertintoCoursevalues(14-109 , , , java 8);insertintoCoursevalues ( 1 3-245 1z* 数据挖掘 1 , 10);insertintoCoursevalues ( 1 3-111 * z* 软件工程 , 11);,3 1051 69),5 102155)4 - 108185)f3-10577),3-2451 100)13-105153)4-109145),3 1051 98)4 1091 68),3 1051 88)13 1051 98)4-

10、1091 68),3 1051 88)insertintoScorevalues(5001, insertintoScorevalues(5001, insertintoScorevalues(5003, insertintoScorevalues(5004, insertintoScorevalues(5005, insertintoScorevalues(5006, insertintoScorevalues(5003, insertintoScorevalues(5008, insertintoScorevalues(5004, insertintoScorevalues(5010, i

11、nsertintoScorevalues(5003, insertintoScorevalues(5005, insertintoScorevalues(5002 f,3 1051 98),4 1091 68)13 105188)4-1091 80)insertintoScorevalues(107, insertintoScorevalues(108, insertintoScorevalues(109, insertintoScorevalues(109, insertintoScorevalues(107, insertintoScorevalues(5003, 13-1111,80);

12、单表查询3 .以class降序输出student的所有记录f student表全部属性) 命令：select* fromStudentorderbyclassdesc;4 .列出教师所在的单位depart不重复)。命令：selectdistinctdepartf romTeacher;5 .列出student表中所有记录的name、sex和class列命令： selectname, sex, classfromStudent;6 .输出student中不姓王的同学的姓名。命令selectnamefromStudentexceptselectnamefromStudentwherenamelik

13、e * 王 , ; 或selectnamefromStudentwherenamenotlike T王 1;7 .输出成绩为85或86或88或在60-80之间的记录(no,eno,degree)命 令 : selectno,eno,DEGREEfromScorewheredegree=85 ordegree=8 6 ordegree=88 ordegreebetween 60 and 80;8 .输出班级为95001或性别为女的同学student表全部属性)命令： select* fromStudentwhereclass=950 01 orsex=女，9 .以eno升序、degree降序输出

14、score的所有记录。(score表全部属性命令： select*fromScoreorderbycnoasc, degreedesc;10 .输出男生人数及这些男生分布在多少个班级中命令： selectCOUNT (*) , count (distinctclass) fromStudentwheresex=男； 11.列出存在有85分以上成绩的课程编号。命令：selectdistinctcnof romScorewheredegree85;12 .输出95001班级的学生人数命令：selectCOUNT (*) fromStudentwhereclass=95001;13 .输出3-105

15、号课程的平均分命令： selectavg (cast (degreeasfloat) ) fromScorewherecno= * 3-105 * ;14 .输出student中最大和最小的birthday日期值命令： selectMAX (birthday) , MIN (birthday) f romStudent;15 .显示95001和95004班全体学生的全部个人信息不包括选课)。student表全部属性) 命令： select* fromStudentwhereclass=950 01 orclass=95004;聚合查询16 .输出至少有5个同学选修的并以3开头的课程的课程号，课

16、程平均分，课程最高分，课 程最低分。命令：selectcno,avg (cast(degreeasfloat),MAX(degree),MIN(degree)fromScorewherecnolike * 3% * groupbycnohavingCOUNT(eno)5; 或者：selecteno,AVG(cast(DEGREEasfloat)rMAX(degree)rMIN(DEGREE)fromSco regroupbycnohavingCOUNT(eno)=5 andenolike3%117 .输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名命令：selectstu

17、dent.no,namefromStudentj oinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX(Score.degree)70;18 .显示所教课程选修人数多于5人的教师姓名命令：selectnamefromTeacherj oinCourseonTeacher.no=Course.tnowhereCourse cnoin(selectcnofromScoregroupbycnohavingCOUNT(Score.eno)5);19 .输出95001,班级所选课程的课程号和平均分命 令 ： selectcno,avg(

18、cast(degreeasfloat)fromScorewherenoin(selectnofrom Studentwhereclass=95001)groupbycno;或者：selectcno,AVG(cast(degreeasfloat)fromScorej oinStudentonScore.no=Studentnogroupbycno,classhavingclass=195001f20 .输出至少有两名男同学的班级编号。命令:selectclassfromStudentwheresex=1男,groupbyclasshavingCOUNT(class)=2;或 者 ：selecta

19、.classfrom(select*fromStudentwheresex=1男1)agroupbya.classhavingCOUNT(a.class)=2 多表查询21 .列出与108号同学同年出生的所有学生的学号、姓名和生日命令：selectno,name,birthdayfromStudentwhereyear(birthday)=(selectyear( birthday)fromStudentwhereno=108);或者：selectb.no,b.name,b.birthdayfromStudentaj oinStudentbondatediff(YE AR,a birthday

20、,b.birthday)=0 anda.no=1108122 .列出存在有85分以上成绩的课程名称命令：selectcnamefromCoursewherecnoin(selectdistinctcnofromScorewherede gree85);或 selectdistinctcnamefromCoursej oinScoreonCo=Sowhere degree85;23 .列出“计算机系教师所教课程的成绩表课程编号，课程名，学生名，成绩)。命令：selectCourse.eno,cname,Student.name,DEGREEfromTeacherj oinCourseon Tea

21、cher.no=Course.tnoj oinScoreonCo=Score.enoj oinStudenton Score . no=Student. nowhereTeacher . depart=计算机系 , ;24 .列出所有可能的“计算机系与“电子工程系不同职称的教师配对信息，要求输出每 个教师的姓名(name)和(职称)命令:selecta.name,a.prof,b.name,b.proffrom(selectname, prof, depart fromTeacherwheredepart= , 计算机系 * ordepart= 1 电子工程系 1 ) aj oin (sele

22、ctname, prof, departfromTeacherwheredepart = 1电子 工 程 系 1ordepart=1计 算 机 系 1)bonnota.prof=b.profandnota.depart=b.depart;25 .列出所有处于不同班级中，但具有一样生日的学生，要求输出每个学生的学 号和姓名。(提示：使用datediff函数，具体用法可以参 考：:hcmfys.javaeye/blog/588844)命令：selects.no,a.name,b.nozb.namefromStudentaj oinStudentbonnota.class =b . classand

23、a.birthday=b.birthday;26 .显示张三教师任课的学生姓名，课程名，成绩命令：selectstudent.name,cname,DEGREEfromTeacherj oinCourseonTeacher.no= Course.tnoj oinScoreonCourse . cno=Score.enoj oinStudentonScore.no=St udent . nowhereTeacher . name=张三 , ;27 .列出所讲课已被选修的教师的姓名和系别命令：selectdistinctname,departfromTeacherj oinCourseonTeac

24、her.no=Course , tnoj oinScoreonCo=Score.eno;28 .输出所有学生的name、no和degree。(degree为空的不输出和为空的输出两种 情况)。命令：selectname,Student.no,DEGREEfromStudentleftj oinScoreonStudent.no= Score , no;selectname,Student.no,DEGREEfromStudentj oinScoreonStudent.no=Scor e. no;29 .列出所有任课教师的name和加part。从课程选修和任课两个角度考虑 命令：selectdi

25、stinctname,departfromTeacherj oinCourseonTeacher.no=Course tno;selectdistinctname,departfromTeacherj oinCourseonTeacher.no=Course , tnoj oinScoreonCo=Score.eno;30 .输出男教师所上课程名称。命令：selectcnamefromTeacherj oinCourseonTeacher.no=Course.tnowhereTeach er , sex=男，31 .出与“李军同性别的所有同学的name。命令：selectnamefromStu

26、dentwheresex=(selectsexfromStudentwherename=李军 T)；32 .输出选修“数据构造课程的男同学的成绩。命令：selectDEGREEfromScorej oinStudentonScore.no=Student.noj oinCourseon Score . cno=Course . cnowheresex= 1男 , andcname= , 数据构造1;33 .列号选修编号为3-105课程并且该门课程成绩比课程3-111,的最高分 要高的 eno,no 和 degree。命令：selectCourse.eno,Score.noADEGREEfrom

27、CoursejoinScoreonCo=S core , enoj oinStudentonScore.no=So= * 3-1051an dScore.degree(selectmax(degree)fromScorewhereSo=13-111f); 子查询34 .输出score中成绩最高的学号和课程号命令：selectno,cnofromScorewheredegreein(selectMAX(degree)fromScore);35 .输出选修3-105课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓名命令：selectstudent.no,namefromStuden

28、tj oinScoreonStudent.no=Score.nowhe reSo=13-1051anddegree(selectdegreefromScorewhereno=!109 fa ndcno=T 3-105 T);36 .列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩命令：selectno,DEGREE,a.avg_degreefromScorej oin(selectcno,AVG(cast(degr eeasfloat)fromScoregroupbycno)a(eno,avg_degree)onSo=a.eno whereScore.degree(selectM

29、AX( degree)fromScorewherecno= f 4-1091);39 . *列出符合下述条件的所有可能的同学配对(sno 1 ,sname 1 ,sno2,sname2,difference)。其 中要求学号为snol的snamel同学的所学课程的平均分大于学号为sno2的sname2同学 的所学课程平均分，两个同学的课程平均分的差值difference为snol同学平均分-sno2 同学平均分)命令：selecta.no,a.name,b no,b name,a.avg_degree-b avg_degreeasdifferen cefrom(selectstudent.no,name,avg(degree)fromStudentj oinScoreonStudent.n o=Score.nogroupbyStudent.no,name)a(no,name,avg_degree)j oin(select Student.no,name,avg(degree)fromStudentj oinScoreonStudent.no=Score nogroupbyStudent.no,name)b(no,name,avg_degree)ona.avg_degreeb.a vg_degree;