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

现在有一教学管理系统，具体的关系模式如下Student no,name,sex,birthday,classTeacher no,name,sex,birthday,prof,departCourse eno,cname,tnoScore no,eno,degree其中表中包含如下数据表Course表Score表Student表Teacher根据上面描述完成下面问题〔法意注意保存脚本，尤其是加和以便进展数据复原L DML,DDL写出上述表的建表语句

1.命令createtableStudentnoint,namevarchar14,sexvarchar2,birthdaydate classint;zcreatetableTeachernoint namevarchar14,sexvarchar2,birthdaydazterprofvarchar10,departvarchar10;createtableCoursecnovarchar8,cnamevarchar14,tnoint;createtableScorenoint,cnovarchar8,degreeint;DML给出相应的语句来完成题中给出数据的插入

2.INSERT命令1李勇男，inser tintoStudentvalues5001,‘1987-7-22‘95001;1刘晨•，女Iins ertintoStudent values5002,1987-11-15,95002;1王敏女，，ins er tintoStudentvalues5003,1987-10-5‘95001;1李好尚男J I，insert intoStudent values5004,‘1987-9-25‘95003;!李军男ins ertintoStudentvalues5005,‘1987-7-17,95004;f范新位女lins ertintoStudentvalues5006,4987-6-18195005;1张霞东女Iins ertintoStudentvalues5007,1987-8-29195006;赵薇，「男，ins ertintoStudentvalues

5008.1987-6-15,95007;1人民币民将女Jinser tintoStudentvalues5009,‘1987-6-23195008;1孙俪女，ins ertintoStudentvalues5010,1987-9-24,95002;赵里ins ertintoStudentvalues108J4987-6-15,95007;丘处机男J Iins ertintoStudentvalues1091987-6-23195008;杨康男J JinsertintoStudentvalues107J1987-9-24,95001;*李卫*教授*,1电子工1,*1insert intoTeache rvalues1,,1957-11-5*,程系1;文备男副教授I JinsertintoTeachervalues2,U UgGI-lO—math*;关羽1,1*讲师1,1111insertintoTeachervalues3,,1977-9-20*,sc*;李修1,1,1教授1,1111insertintoTeachervalues4,1957-6-25,elec;诸葛亮男教授1T计算机系,;，，，insert intoTeache rvalues53I I1977-6-151r殷素素女副教授1,，，11insert intoTeache rvalues6J‘3I1967-1-51sc;周芷假设女教授*,*inser tintoTeachervalues7,J J1947-2-23,sc*;赵云，男副教授一计算机系1;，，insertintoTeachervalues8J J I1980-6-13I张敏女助教，，，，，insert intoTeachervalues9JIl1985-5-51I sc;黄蓉女副教授*,，1Tinsert intoTeache rvalues103I I4967-3-22J sc;张三,,,,1副教授*,*11insertintoTeachervalues11J1967-3-22,sc*;数据库\；insertintoCoursevalues13-101*,11数学1,insert intoCourse values15-102I2;，信息系统insertintoCoursevalues3-103I I3;操作系统1,1insert intoCourseva lues13-104,14;1数据构造1,insertintoCoursevalues13-1051,5;1数据处理11insertintoCoursevalues3-1061,5;z语言1,1finsertintoCoursevalues4-107pascal6;r11insertintoCoursevalues14-108,*C++,7;1insertintoCoursevalues4-109,,,java\8;*数据挖掘1insertintoCoursevalues13-2451,10;z*软件工程,,insertintoCoursevalues13-111*11;zinsertintoScorevalues5001,,3—105169insertintoScorevalues5001,,5—102155insertintoScorevalues5003,‘4-108185insertintoScorevalues5004,f3-105\77insertintoScorevalues5005,,3-2451100insertintoScorevalues5006,13-105153insertintoScorevalues5003,‘4-109145insertintoScorevalues5008,,3—105198insertintoScorevalues5004,‘4—109168insertintoScorevalues5010,,3—105188insertintoScorevalues5003,13—105198insertintoScorevalues5005,‘4-109168insertintoScorevalues5002f,3—105188insertintoScorevalues107,,3—105198insertintoScorevalues108,,4—109168insertintoScorevalues109,insertintoScorevalues109,13—105188insertintoScorevalues107,‘4-109180insertintoScorevalues5003,113-111,80;单表查询以降序输出的所有记录表全部属性命令

3.class studentstudentf select*fromStudentorderbyclassdesc;列出教师所在的单位〔不重复

4.depart命令selectdistinctdepartf romTeacher;列出表中所有记录的、和列

5.student namesex class命令selectname,sex,classfromStudent;输出中不姓王的同学的姓名

6.student命令王％,;或selectnamefromStudentexceptselectnamefromStudentwherenamelike*王％1;TselectnamefromStudentwherenamenotlike输出成绩为或或或在之间的记录

7.85868860-80no,eno,degree命令:selectno,eno,DEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and80;输出班级为或性别为女的同学〔表全部属性

8.95001student命令女，select*fromStudentwhereclass=95001orsex=以升序、降序输出的所有记录表全部属性〕

9.eno degreescore score命令select*fromScoreorderbycnoasc,degreedesc;输出男生人数及这些男生分布在多少个班级中

10.命令男；

11.selectCOUNT*,count distinctclassfromStudentwheresex=列出存在有分以上成绩的课程编号85命令selectdistinctcnof romScorewheredegree85;输出班级的学生人数

12.95001命令selectCOUNT*fromStudentwhereclass=95001;输出号课程的平均分

13.‘3-105’命令selectavg castdegreeasfloatfromScorewherecno=*3-105*;输出中最大和最小的日期值

14.student birthday命令selectMAX birthday,MIN birthdayf romStudent;显示和班全体学生的全部个人信息〔不包括选课［表全部属性命

15.9500195004student令select*fromStudentwhereclass=95001orclass=95004;聚合查询输出至少有个同学选修的并以开头的课程的课程号，课程平均分，课程最高分，课程

16.53最低分命令selectcno,avg castdegreeasfloat,MAXdegree,MINdegreefromSco或者rewherecnolike*3%*groupbycnohavingCOUNTeno5;selecteno,AVGcastDEGREEasfloat MAXdegreeMINDEGREEfromScor r1regroupbycnohavingCOUNTeno=5andenolike3%输出所选修课程中最低分大于分且最高分小于分的学生学号及学生姓名

17.7090命令selectstudent.no,namefromStudentj oinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAXScore.degree90andMINScore.degree70;显示所教课程选修人数多于人的教师姓名

18.5命令・selectnamefromTeacherj oinCourseonTeacher.no=Course.tnowhereCoursecnoinselectcnofromScoregroupbycnohavingCOUNTScore.eno5;

19.输出95001,班级所选课程的课程号和平均分命令selectcno,avgcastdegreeasfloatfromScorewherenoinselectnofromStudentwhereclass=95001groupbycno;或者selectcno,AVGcastdegreeasfloatfromScorej oinStudentonScore.no=1fStudent•nogroupbycno,classhavingclass=95001输出至少有两名男同学的班级编号

20.命令:男1selectclassfromStudentwheresex=,groupbyclasshavingCOUNTclass=2;或者男1selecta.classfrom select*fromStudentwheresex=多表查询1agroupbya.classhavingCOUNTa.class=2列出与号同学同年出生的所有学生的学号、姓名和生日

21.108命令selectno,name,birthdayfromStudentwhereyearbirthday=selectyear birthdayfromStudentwhereno=108;或者・selectb.no,b.name,b.birthdayfromStudentaj oinStudentbondatediffYEAR,a11birthday,b.birthday=0anda.no=108列出存在有分以上成绩的课程名称

22.85命令selectcnamefromCoursewherecnoinselectdistinctcnofromScorewherede;或gree85selectdistinctcnamefromCoursejoinScoreonCourse.cno=Score.cnowhere degree85;列出“计算机系〃教师所教课程的成绩表〔课程编号，课程名，学生名，成绩

23.命令selectCourse.eno,cname,Student.name,DEGREEfromTeacherj oinCourseonTeacher.no=Course.tnoj oinScoreonCourse.cno=Score.enoj oinStudenton’计算机系,;Score.no=Student.nowhereTeacher.depart=列出所有可能的“计算机系〃与“电子工程系〃不同职称的教师配对信息，要求输出每个

24.教师的姓名和职称name命令:selecta.name,a.prof,b.name,b.proffrom计算机系*1selectname,prof,depart fromTeacherwheredepart=,ordepart=电子工程系11aj oinselectname,prof,departfromTeacherwheredepart=电子工程系计算机系11ordepart=1bonnota.prof=b.profandnota.depart=b.depart;列出所有处于不同班级中，但具有一样生日的学生，要求输出每个学生的学号和姓名提

25.示使用函数，具体用法可以参考:〃hcmfys.javaeye/blog/588844datediff命令selects.no,a.name,b.no b.namefromStudentaj oinStudentbonnota.class=b.zclassanda.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 oinCourseonTeacher.no=Course,tnojoinScoreonCourse.cno=Score.eno;输出所有学生的、和为空的不输出和为空的输出两种情况

28.name nodegree degree命令selectname,Student.no,DEGREEfromStudentleftj oinScoreonStudent.no=Score,no;selectname,Student.no,DEGREEfromStudentj oinScoreonStudent.no=Scor e.no;列出所有任课教师的和加〔从课程选修和任课两个角度考虑〕命令

29.name part・selectdistinctname,departfromTeacherj oinCourseonTeacher.no=Course tno;selectdistinctname,departfromTeacherj oinCourseonTeacher.no=Course,tnojoinScoreonCourse.cno=Score.eno;输出男教师所上课程名称

30.命令selectcnamefromTeacherj oinCourseonTeacher.no=Course.tnowhereTeach er,男，sex=出与“李军〃同性别的所有同学的

31.name命令李军T；selectnamefromStudentwheresex=selectsexfromStudentwherename=’.输出选修“数据构造〃课程的男同学的成绩32命令selectDEGREEfromScorej oinStudentonScore.no=Student.noj oinCourseon男,数据构造1;1Score.cno=Course.cnowheresex=andcname=,列号选修编号为课程并且该门课程成绩比课程的最高分要高的和

33.‘3-105’’3-111,eno,nodegree命令selectCourse.eno,Score.no DEGREEfromCoursejoinScoreonCourse.cno=S core,A〉1enoj oinStudentonScore.no=Student.nowherecourse.cno=*3-105an dScore.degree子查询1fselectmaxdegreefromScorewhereScore.cno=3-111;输出中成绩最高的学号和课程号

34.score命令selectno,cnofromScorewheredegreeinselectMAXdegreefromScore;输出选修课程，其成绩高于号同学在此课程所得成绩的所有同学的学号，姓

35.3-105109名命令selectstudent.no,namefromStudentj oinScoreonStudent.no=Score.nowhe11!f TreScore.cno=3-105anddegreeselectdegreefromScorewhereno=109a ndcno=T3-105;列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩

36.命令selectno,DEGREE,a.avg_degreefromScorej oinselectcno,AVGcastdegreeasfloatfromScoregroupbycnoaeno,avg_degreeonScore.cno=a.enowhereScore.degreea.avg_degree;列出没有实际授课的教师的姓名和系别

37.命令selectname,departfromTeacherexceptselectname,departfromTeacherj oinCourseonTeacher.no=Course.tnoj oinScoreonCourse.cno=Score.eno;列出选修了编号为课程且其成绩高于课程最高成绩的同学的课程编号，学

38.‘3・105’‘4-109’号和成绩命令11selectcno,no,DEGREEfromScorewherecno=3-105anddegreeselectMAX degreef1fromScorewherecno=4-109;**列出符合下述条件的所有可能的同学配对其中

39.sno1,sname1,sno2,sname2,difference要求学号为的同学的所学课程的平均分大于学号为的同学的所学snol snamelsno2sname2课程平均分，两个同学的课程平均分的差值为〔同学平均分同学平均difference snol-sno2分命令・・・selecta.no,a.name,b no,b name,a.avg_degree-b avg_degreeasdifferen cefromselectstudent.no,name,avgdegreefromStudentj oinScoreonStudent.no=Score.nogroupbyStudent.no,nameano,name,avg_degreej oinselect・Student.no,name,avgdegreefromStudentj oinScoreonStudent.no=ScorenogroupbyStudent.no,namebno,name,avg_degreeona.avg_degreeb.avg_degree;。