數(shù)據(jù)庫sql查詢語句練習2-習題-結(jié)果

現(xiàn)在有一教學管理系統(tǒng)，具體的關(guān)系模式如下：Student(no,name,sex,birthday,class)Teacher(no,name,sex,birthday,prof,depart)Course(eno,cname,tno)Score(no,eno,degree)其中表中包含如下數(shù)據(jù)：Course表：Score表：Student表：Teacher表：根據(jù)上面描述完成下面問題：〔法意：注意保存腳本，尤其是加L和DML,以便進展數(shù)據(jù)復(fù)原)DDL.寫出上述表的建表語句。命 令 ：createtableStudent(noint,namevarchar(14),sexvarchar(2),birthdaydatezclassint);createtableTeacher(nointznamevarchar(14),sexvarchar(2),birthdaydaterprofvarchar(10),departvarchar(10));createtableCourse(cnovarchar(8),cnamevarchar(14),tnoint);createtableScore(noint,cnovarchar(8),degreeint);DML.給出相應(yīng)的INSERT語句來完成題中給出數(shù)據(jù)的插入。命令：insertintoStudentvalues(5001,1李勇男‘1987-7-22‘，95001);insertintoStudentvalues(5002,1劉晨?，'女I'1987-11-15',95002);insertintoStudentvalues(5003,1王敏女'，'1987-10-5‘，95001);insertintoStudentvalues(5004,1李好尚'J男I‘1987-9-25‘，95003);insertintoStudentvalues(5005,!李軍'男‘1987-7-17',95004);insertintoStudentvalues(5006,f范新位'女l4987-6-18195005);insertintoStudentvalues(5007,1張霞東'女I'1987-8-29195006);insertintoStudentvalues(5008.'趙薇'，「男'，'1987-6-15',95007);insertintoStudentvalues(5009,1人民幣民將'J女‘1987-6-23195008);insertintoStudentvalues(5010,1孫儷女'，'1987-9-24',95002);insertintoStudentvalues(108J趙里4987-6-15',95007);insertintoStudentvalues(109J丘處機男I'1987-6-23195008);insertintoStudentvalues(107J楊康'J男'J1987-9-24',95001);insertintoTeachervalues(1,*李衛(wèi)1,* ,11957-11-5*,*教授*,1電子工程系1);insertintoTeachervalues(2,'文U備I'男UgGI-lO— '副教授'Jmath*);insertintoTeachervalues(3,1關(guān)羽1,1 1,11977-9-20*,*講師1,1sc*);insertintoTeachervalues(4,'李修1,1,11957-6-251,1教授1,1elec1);insertintoTeachervalues(53諸葛亮I，男I，1977-6-151，教授1rT計算機系,);insertintoTeachervalues(6J殷素素‘3女I，1967-1-51，副教授1,1sc1);insertintoTeachervalues(7,。周芷假設(shè)'J女'J1947-2-23','教授*,*sc*);insertintoTeachervalues(8J趙云，J男I，1980-6-13I，副教授一計算機系1);insertintoTeachervalues(9J張敏I，女l，1985-5-51，助教I，sc，);insertintoTeachervalues(103黃蓉I，女I4967-3-22'J副教授*,1scT);insertintoTeachervalues(11J張三,,,,11967-3-221,1副教授*,*sc*);insertintoCoursevalues(13-101*,'數(shù)據(jù)庫\1)；insertintoCoursevalues(15-102I1數(shù)學1,2);insertintoCoursevalues('3-103I，信息系統(tǒng)I3);insertintoCoursevalues(13-1041,1操作系統(tǒng)1,4);insertintoCoursevalues(13-1051,1數(shù)據(jù)構(gòu)造1,5);insertintoCoursevalues(13-1061z1數(shù)據(jù)處理1,5);insertintoCoursevalues(14-107fr'pascal語言1,6);insertintoCoursevalues(14-1081,*C++1,7);insertintoCoursevalues(14-109,,,java\8);insertintoCoursevalues(13-2451z*數(shù)據(jù)挖掘1,10);insertintoCoursevalues(13-111*z*軟件工程,,11);,3—105169),5—102155)‘4-108185)f3-105\77),3-2451100)13-105153)‘4-109145),3—105198)‘4—109168),3—105188)13—105198)‘4-109168),3—105188)insertintoScorevalues(5001,insertintoScorevalues(5001,insertintoScorevalues(5003,insertintoScorevalues(5004,insertintoScorevalues(5005,insertintoScorevalues(5006,insertintoScorevalues(5003,insertintoScorevalues(5008,insertintoScorevalues,3—105169),5—102155)‘4-108185)f3-105\77),3-2451100)13-105153)‘4-109145),3—105198)‘4—109168),3—105188)13—105198)‘4-109168),3—105188),3—105198),4—109168)13—105188)‘4-109180)insertintoScorevalues(107,insertintoScorevalues(108,insertintoScorevalues(109,insertintoScorevalues(109,insertintoScorevalues(107,insertintoScore,3—105198),4—109168)13—105188)‘4-109180)單表查詢.以class降序輸出student的所有記錄fstudent表全部屬性)命令：select*fromStudentorderbyclassdesc;.列出教師所在的單位depart〔不重復(fù))。命令：selectdistinctdepartfromTeacher;.列出student表中所有記錄的name、sex和class列命令：selectname,sex,classfromStudent;.輸出student中不姓王的同學的姓名。命 令selectnamefromStudentexceptselectnamefromStudentwherenamelike*王％,;或selectnamefromStudentwherenamenotlikeT王％1;.輸出成績?yōu)?5或86或88或在60-80之間的記錄(no,eno,degree)命令:selectno,eno,DEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and80;.輸出班級為95001或性別為'女'的同學〔student表全部屬性)命令：select*fromStudentwhereclass=95001orsex='女，.以eno升序、degree降序輸出score的所有記錄。(score表全部屬性〕命令：select*fromScoreorderbycnoasc,degreedesc;.輸出男生人數(shù)及這些男生分布在多少個班級中命令：selectCOUNT(*),count(distinctclass)fromStudentwheresex='男'；11.列出存在有85分以上成績的課程編號。命令：selectdistinctcnofromScorewheredegree>85;.輸出95001班級的學生人數(shù)命令：selectCOUNT(*)fromStudentwhereclass=95001;.輸出‘3-105’號課程的平均分命令：selectavg(cast(degreeasfloat))fromScorewherecno=*3-105*;.輸出student中最大和最小的birthday日期值命令：selectMAX(birthday),MIN(birthday)fromStudent;.顯示95001和95004班全體學生的全部個人信息〔不包括選課)。［student表全部屬性)命令：select*fromStudentwhereclass=95001orclass=95004;聚合查詢.輸出至少有5個同學選修的并以3開頭的課程的課程號，課程平均分，課程最高分，課程最低分。命 令 ：selectcno,avg(cast(degreeasfloat)),MAX(degree),MIN(degree)fromScorewherecnolike*3%*groupbycnohavingCOUNT(eno)>5;或者：selecteno,AVG(cast(DEGREEasfloat))rMAX(degree)rMIN(DEGREE)fromScoregroupbycnohavingCOUNT(eno)>=5andenolike'3%1.輸出所選修課程中最低分大于70分且最高分小于90分的學生學號及學生姓名命 令 ：selectstudent.no,namefromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX(Score.degree)<90andMIN(Score.degree)>70;.顯示所教課程選修人數(shù)多于5人的教師姓名命 令 ：selectnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereCourse?cnoin(selectcnofromScoregroupbycnohavingCOUNT(Score.eno)>5);.輸出'95001,班級所選課程的課程號和平均分命令：selectcno,avg(cast(degreeasfloat))fromScorewherenoin(selectnofromStudentwhereclass=95001)groupbycno;或者：selectcno,AVG(cast(degreeasfloat))fromScorejoinStudentonScore.no=Student?nogroupbycno,classhavingclass=195001f.輸出至少有兩名男同學的班級編號。命 令 : selectclassfromStudentwheresex=1男,groupbyclasshavingCOUNT(class)>=2;或者： selecta.classfrom (select*fromStudentwheresex=1男1)agroupbya.classhavingCOUNT(a.class)>=2多表查詢.列出與108號同學同年出生的所有學生的學號、姓名和生日TOC\o"1-5"\h\z命 令 ：selectno,name,birthdayfromStudentwhereyear(birthday)=(selectyear(birthday)fromStudentwhereno=108);或 者 ：selectb.no,,b.birthdayfromStudentajoinStudentbondatediff(YEAR,a?birthday,b.birthday)=0anda.no=11081.列出存在有85分以上成績的課程名稱命 令 ：selectcnamefromCoursewherecnoin(selectdistinctcnofromScorewheredegree>85);或selectdistinctcnamefromCoursejoinScoreonCo=Sowheredegree>85;.列出“計算機系〃教師所教課程的成績表〔課程編號，課程名，學生名，成績)。命 令 ：selectCourse.eno,cname,S,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=Score.enojoinStudentonScore.no=Student.nowhereTeacher.depart=’計算機系,;.列出所有可能的“計算機系〃與“電子工程系〃不同職稱的教師配對信息，要求輸出每個教師的姓名(name)和(職稱)命 令 : ,f,,ffrom(selectname,prof,departfromTeacherwheredepart=,計算機系*ordepart=1電子工程系1)ajoin(selectname,prof,departfromTeacherwheredepart=1電子工程系1ordepart=1計算機系1)f=fandnota.depart=b.depart;.列出所有處于不同班級中，但具有一樣生日的學生，要求輸出每個學生的學號和姓名。(提示：使用datediff函數(shù)，具體用法可以參考： :〃hcmfys.javaeye /blog/588844)命 令 ：selects.no,,fromStudentajoinStudentbonnota.class=b.classanda.birthday=b.birthday;.顯示‘張三'教師任課的學生姓名，課程名，成績命 令 ：,cname,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=Score.enojoinStudentonScore.no=Student.nowhereT='張三,;.列出所講課已被選修的教師的姓名和系別命 令 ：selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course,tnojoinScoreonCo=Score.eno;.輸出所有學生的name、no和degree。(degree為空的不輸出和為空的輸出兩種情況)。命 令 ：selectname,Student.no,DEGREEfromStudentleftjoinScoreonStudent.no=Score,no;selectname,Student.no,DEGREEfromStudentjoinScoreonStudent.no=Score.no;.列出所有任課教師的name和加part?！矎恼n程選修和任課兩個角度考慮〕命 令 ：selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course?tno;selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course,tnojoinScoreonCo=Score.eno;.輸出男教師所上課程名稱。TOC\o"1-5"\h\z命 令 ：selectcnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereTeacher,sex='男，.出與“李軍〃同性別的所有同學的name。命 令 ：selectnamefromStudentwheresex=(selectsexfromStudentwherename=’李軍T)；.輸出選修“數(shù)據(jù)構(gòu)造〃課程的男同學的成績。命 令 ：selectDEGREEfromScorejoinStudentonScore.no=Student.nojoinCourseonSo=Cowheresex=1男,andcname=,數(shù)據(jù)構(gòu)造1;.列號選修編號為‘3-105’課程并且該門課程成績比課程’3-111,的最高分要高的eno,no和degree。命 令 ：selectCourse.eno,Score.noADEGREEfromCoursejoinScoreonCo=Score,enojoinStudentonScore.no=So=*3-1051andScore.degree〉(selectmax(degree)fromScorewhereSo=13-111f);子查詢.輸出score中成績最高的學號和課程號命 令 ：selectno,cnofromScorewheredegreein(selectMAX(degree)fromScore);.輸出選修3-105課程，其成績高于109號同學在此課程所得成績的所有同學的學號，姓名命 令 ：selectstudent.no,namefromStudentjoinScoreonStudent.no=Score.nowhereSo=13-1051anddegree>(selectdegreefromScorewhereno=!109fandcno=T3-105T);.列出成績比該課程平均成績低的同學的學號，成績和該門課的平均成績命 令 ：selectno,DEGREE,a.avg_degreefromScorejoin(selec