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

...wd......wd......wd...現(xiàn)在有一教學(xué)管理系統(tǒng)，具體的關(guān)系模式如下：Student(no,name,sex,birthday,class)Teacher(no,name,sex,birthday,prof,depart)Course(cno,cname,tno)Score(no,cno,degree)其中表中包含如下數(shù)據(jù)：Course表：Score表：Student表：Teacher表：根據(jù)上面描述完成下面問題：〔注意：注意保存腳本，尤其是DDL和DML，以便進展數(shù)據(jù)復(fù)原〕DDL寫出上述表的建表語句。命令：createtableStudent(noint,namevarchar(14),sexvarchar(2),birthdaydate,classint);createtableTeacher(noint,namevarchar(14),sexvarchar(2),birthdaydate,profvarchar(10),departvarchar(10));createtableCourse(cnovarchar(8),cnamevarchar(14),tnoint);createtableScore(noint,cnovarchar(8),degreeint);DML給出相應(yīng)的INSERT語句來完成題中給出數(shù)據(jù)的插入。命令：insertintoStudentvalues(5001,'李勇','男','1987-7-22',95001);insertintoStudentvalues(5002,'劉晨','女','1987-11-15',95002);insertintoStudentvalues(5003,'王敏','女','1987-10-5',95001);insertintoStudentvalues(5004,'李好尚','男','1987-9-25',95003);insertintoStudentvalues(5005,'李軍','男','1987-7-17',95004);insertintoStudentvalues(5006,'范新位','女','1987-6-18',95005);insertintoStudentvalues(5007,'張霞東','女','1987-8-29',95006);insertintoStudentvalues(5008,'趙薇','男','1987-6-15',95007);insertintoStudentvalues(5009,'人民幣民將','女','1987-6-23',95008);insertintoStudentvalues(5010,'孫儷','女','1987-9-24',95002);insertintoStudentvalues(108,'趙里','男','1987-6-15',95007);insertintoStudentvalues(109,'丘處機','男','1987-6-23',95008);insertintoStudentvalues(107,'楊康','男','1987-9-24',95001);insertintoTeachervalues(1,'李衛(wèi)','男','1957-11-5','教授','電子工程系');insertintoTeachervalues(2,'劉備','男','1967-10-9','副教授','math');insertintoTeachervalues(3,'關(guān)羽','男','1977-9-20','講師','sc');insertintoTeachervalues(4,'李修','男','1957-6-25','教授','elec');insertintoTeachervalues(5,'諸葛亮','男','1977-6-15','教授','計算機系');insertintoTeachervalues(6,'殷素素','女','1967-1-5','副教授','sc');insertintoTeachervalues(7,'周芷假設(shè)','女','1947-2-23','教授','sc');insertintoTeachervalues(8,'趙云','男','1980-6-13','副教授','計算機系');insertintoTeachervalues(9,'張敏','女','1985-5-5','助教','sc');insertintoTeachervalues(10,'黃蓉','女','1967-3-22','副教授','sc');insertintoTeachervalues(11,'張三','男','1967-3-22','副教授','sc');insertintoCoursevalues('3-101','數(shù)據(jù)庫',1);insertintoCoursevalues('5-102','數(shù)學(xué)',2);insertintoCoursevalues('3-103','信息系統(tǒng)',3);insertintoCoursevalues('3-104','操作系統(tǒng)',4);insertintoCoursevalues('3-105','數(shù)據(jù)構(gòu)造',5);insertintoCoursevalues('3-106','數(shù)據(jù)處理',5);insertintoCoursevalues('4-107','pascal語言',6);insertintoCoursevalues('4-108','C++',7);insertintoCoursevalues('4-109','java',8);insertintoCoursevalues('3-245','數(shù)據(jù)挖掘',10);insertintoCoursevalues('3-111','軟件工程',11);insertintoScorevalues(5001,'3-105',69);insertintoScorevalues(5001,'5-102',55);insertintoScorevalues(5003,'4-108',85);insertintoScorevalues(5004,'3-105',77);insertintoScorevalues(5005,'3-245',100);insertintoScorevalues(5006,'3-105',53);insertintoScorevalues(5003,'4-109',45);insertintoScorevalues(5008,'3-105',98);insertintoScorevalues(5004,'4-109',68);insertintoScorevalues(5010,'3-105',88);insertintoScorevalues(5003,'3-105',98);insertintoScorevalues(5005,'4-109',68);insertintoScorevalues(5002,'3-105',88);insertintoScorevalues(107,'3-105',98);insertintoScorevalues(108,'4-109',68);insertintoScorevalues(109,'3-105',88);insertintoScorevalues(109,'4-109',80);insertintoScorevalues(107,'3-111',88);insertintoScorevalues(5003,'3-111',80);單表查詢以class降序輸出student的所有記錄〔student表全部屬性〕命令：select*fromStudentorderbyclassdesc;列出教師所在的單位depart〔不重復(fù)〕。命令：selectdistinctdepartfromTeacher;列出student表中所有記錄的name、sex和class列命令：selectname,sex,classfromStudent;輸出student中不姓王的同學(xué)的姓名。命令：selectnamefromStudentexceptselectnamefromStudentwherenamelike'王%';或selectnamefromStudentwherenamenotlike'王%';輸出成績?yōu)?5或86或88或在60-80之間的記錄〔no,cno,degree〕命令：selectno,cno,DEGREEfromScorewheredegree=85ordegree=86ordegree=88ordegreebetween60and80;輸出班級為95001或性別為‘女’的同學(xué)〔student表全部屬性〕命令：select*fromStudentwhereclass=95001orsex='女';以cno升序、degree降序輸出score的所有記錄?！瞫core表全部屬性〕命令：select*fromScoreorderbycnoasc,degreedesc;輸出男生人數(shù)及這些男生分布在多少個班級中命令：selectCOUNT(*),count(distinctclass)fromStudentwheresex='男';列出存在有85分以上成績的課程編號。命令：selectdistinctcnofromScorewheredegree>85;輸出95001班級的學(xué)生人數(shù)命令：selectCOUNT(*)fromStudentwhereclass=95001;輸出‘3-105’號課程的平均分命令：selectavg(cast(degreeasfloat))fromScorewherecno='3-105';輸出student中最大和最小的birthday日期值命令：selectMAX(birthday),MIN(birthday)fromStudent;顯示95001和95004班全體學(xué)生的全部個人信息〔不包括選課〕?！瞫tudent表全部屬性〕命令：select*fromStudentwhereclass=95001orclass=95004;聚合查詢輸出至少有5個同學(xué)選修的并以3開頭的課程的課程號，課程平均分，課程最高分，課程最低分。命令：selectcno,avg(cast(degreeasfloat)),MAX(degree),MIN(degree)fromScorewherecnolike'3%'groupbycnohavingCOUNT(cno)>5;或者：selectcno,AVG(cast(DEGREEasfloat)),MAX(degree),MIN(DEGREE)fromScoregroupbycnohavingCOUNT(cno)>=5andcnolike'3%'輸出所選修課程中最低分大于70分且最高分小于90分的學(xué)生學(xué)號及學(xué)生姓名命令：selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nogroupbyStudent.no,namehavingMAX(Score.degree)<90andMIN(Score.degree)>70;顯示所教課程選修人數(shù)多于5人的教師姓名命令：selectnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereCoin(selectcnofromScoregroupbycnohavingCOUNT(So)>5);輸出’95001’班級所選課程的課程號和平均分命令：selectcno,avg(cast(degreeasfloat))fromScorewherenoin(selectnofromStudentwhereclass=95001)groupbycno;或者：selectcno,AVG(cast(degreeasfloat))fromScorejoinStudentonScore.no=Student.nogroupbycno,classhavingclass='95001'輸出至少有兩名男同學(xué)的班級編號。命令：selectclassfromStudentwheresex='男'groupbyclasshavingCOUNT(class)>=2;或者：selecta.classfrom(select*fromStudentwheresex='男')agroupbya.classhavingCOUNT(a.class)>=2多表查詢列出與108號同學(xué)同年出生的所有學(xué)生的學(xué)號、姓名和生日命令：selectno,name,birthdayfromStudentwhereyear(birthday)=(selectyear(birthday)fromStudentwhereno=108);或者：selectb.no,,b.birthdayfromStudentajoinStudentbondatediff(YEAR,a.birthday,b.birthday)=0anda.no='108'列出存在有85分以上成績的課程名稱命令：selectcnamefromCoursewherecnoin(selectdistinctcnofromScorewheredegree>85);或selectdistinctcnamefromCoursejoinScoreonCo=Sowheredegree>85;列出“計算機系〞教師所教課程的成績表〔課程編號，課程名，學(xué)生名，成績〕。命令：selectCo,cname,S,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=SojoinStudentonScore.no=Student.nowhereTeacher.depart='計算機系';列出所有可能的“計算機系〞與“電子工程系〞不同職稱的教師配對信息，要求輸出每個教師的姓名〔name〕和〔職稱〕命令：,f,,ffrom(selectname,prof,departfromTeacherwheredepart='計算機系'ordepart='電子工程系')ajoin(selectname,prof,departfromTeacherwheredepart='電子工程系'ordepart='計算機系')f=fandnota.depart=b.depart;列出所有處于不同班級中，但具有一樣生日的學(xué)生，要求輸出每個學(xué)生的學(xué)號和姓名?！蔡崾荆菏褂胐atediff函數(shù)，具體用法可以參考：://hcmfys.javaeye/blog/588844〕命令：selecta.no,,b.no,fromStudentajoinStudentbonnota.class=b.classanda.birthday=b.birthday;顯示‘張三’教師任課的學(xué)生姓名，課程名，成績命令：selectS,cname,DEGREEfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=SojoinStudentonScore.no=Student.nowhereT='張三';列出所講課已被選修的教師的姓名和系別命令：selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=So;輸出所有學(xué)生的name、no和degree?！瞕egree為空的不輸出和為空的輸出兩種情況〕。命令：selectname,Student.no,DEGREEfromStudentleftjoinScoreonStudent.no=Score.no;selectname,Student.no,DEGREEfromStudentjoinScoreonStudent.no=Score.no;列出所有任課教師的name和depart?！矎恼n程選修和任課兩個角度考慮〕命令：selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tno;selectdistinctname,departfromTeacherjoinCourseonTeacher.no=Course.tnojoinScoreonCo=So;輸出男教師所上課程名稱。命令：selectcnamefromTeacherjoinCourseonTeacher.no=Course.tnowhereTeacher.sex='男';出與“李軍〞同性別的所有同學(xué)的name。命令：selectnamefromStudentwheresex=(selectsexfromStudentwherename='李軍');輸出選修“數(shù)據(jù)構(gòu)造〞課程的男同學(xué)的成績。命令：selectDEGREEfromScorejoinStudentonScore.no=Student.nojoinCourseonSo=Cowheresex='男'andcname='數(shù)據(jù)構(gòu)造';列出選修編號為‘3-105’課程并且該門課程成績比課程‘3-111’的最高分要高的cno,no和degree。命令：selectCo,Score.no,DEGREEfromCoursejoinScoreonCo=SojoinStudentonScore.no=Student.nowhereCo='3-105'andScore.degree>(selectmax(degree)fromScorewhereSo='3-111');子查詢輸出score中成績最高的學(xué)號和課程號命令：selectno,cnofromScorewheredegreein(selectMAX(degree)fromScore);輸出選修3-105課程，其成績高于109號同學(xué)在此課程所得成績的所有同學(xué)的學(xué)號，姓名命令：selectStudent.no,namefromStudentjoinScoreonStudent.no=Score.nowhereSo='3-105'anddegree>(selectdegreefromScorewhereno='109'andcno='3-105');列出成績比該課程平均成績低的同學(xué)的學(xué)號，成績和該門課的平均成績命令：selectno,DEGREE,a.avg_degreefromScorejoin(sele