學(xué)習(xí)sql語句練習(xí)及答案

Student(no,name,sex,birthday,class)Teacher(no,name,sex,birthday,prof,depart)Course(cno,cname,tno)Score(no,cno,degree)Student(no,name,sex,birthday,class)Teacher(no,name,sex,birthday,prof,depart)Course(cno,cname,tno)Score(no,cno,degree)ScoreStudent根據(jù)上面描述完成下面問題給出相應(yīng)的INSERT。根據(jù)上面描述完成下面問題給出相應(yīng)的INSERT。classstudent（student表全部屬性studentname、sexclassstudent85868860-80（95001（student表全部屬性命令：selectCOUNT(*),COUNT(distinctclassfromStudentwheresex='男列出存在有85。命令：selectdistinctcnofromScore列出存在有85。命令：selectdistinctcnofromScorewhere95001命令：selectCOUNT(*fromStudentwhere)studentbirthday命令：selectMAX(birthday),MIN(birthdayfrom命令：select*fromStudentwhereclass='95001orselectcno,AVG(degree),MAX(degree),MIN(degree)fromScorewherecnolikegroupbying命令：groupbying命令：selectstudent.no,fromScoreinnerjoinStudentongroupbying(MIN(degree)>70and命令：selectnamefromScoreinnerjoincourseononTeacher.no=course.tnogroupbyteacher.no,nameoinnering命令：selectcno,AVG(degreefromStudentinnerjoinScoreonstudent.no=score.nowherestudent.class='95001'groupbycno輸出至少有兩名男同學(xué)的班 命令：selectclassfromStudentinnerjoinScoreonstudent.no=score.nowheresex='男g(shù)roupbyingCOUNT(distinct20.108命令：selectno,name,birthdayfromStudentwhereyear(birthday)=(selectYEAR(birthday)fromStudentwhereno=108)命令：selectcnamefromcourseinnerjoinScoreoningMAX(degree)>85ogroupby命令：selectcnamefromcourseinnerjoinScoreoningMAX(degree)>85ogroupbyfromStudentinnerjoinScorestudent.no=score.noinnerjoincourseoinnereacher）命令：select,f,,fTeacherex1,Teacherex2ex1.depart='計(jì)算機(jī)系andex2.depart='電子工程系and號和（提示：使用datediff函數(shù)，具體用法可以參考： selectex1.no,,ex2.no,fromStudentex1innerselectex1.no,,ex2.no,fromStudentex1innerjoinStudentex1.birthday=ex2.birthdaywhereselectex1.no,,ex2.no,fromStudentex1,Studentex2whereanddatediff(day,ex1.birthday,ex2.birthday命令：selectstudent.no=score.noinnerjoincourseStudentinnerjoinoinner 命令： oinnereacheron命令：select,student.no,DEGREEfromStudentinnerjoinScoreon,student.no,DEGREEfromStudentleftjoinScoreonselectdistinctselectdistinctjoincourseoeacheron（任課,departfromteacherinnerjoincourseonselectcnamefromTeacherinnerjoincourseonTeacherselectcnamefromTeacherinnerjoincourseonTeacher.no=course.tnowhereteacher.sex='男命令：selectnamefromStudentwheresex=(selectsexfromStudentwherename='李軍命令：selectDEGREEfromStudentinnerjoinScoreonstudent.no=score.nowheresex='男命令：selectcno,student.no,DEGREEfromStudentinnerjoinScoreonstudent.no=score.nowherecno='3-105'anddegree>(selectMAX(degree)fromScorewherecno='3-111')33.scoreofromScorewheredegree=(selectMAX(degree)from命令：selectstudent.no,namefromStudent命令：selectstudent.no,namefromStudentinnerjoinScoreonstudent.no=score.nocno='3-105'anddegree>(selectdegreefromStudentinnerjoinScoreonwherestudent.no=109andcno='3-selectno,DEGREE,temp.avgdegreefromScoreinnerjoin(selectcno,AVG(degree)avgdegreefromScoregroupbycno)asowhereselectdistinctfromleftjoincourseonleftjoinScorewherescore.noiso為‘3-105’課程且其成績高于‘4-109命令：selectcno,student.no,DEGREEfromStudentinnerjoinScoreonwherecno='3-105'andwherecno='3-105'anddegree>(selectMAX(degree)fromScorewherecno='4-（sno1,ae1,so2,snae2,dfrnce中要求學(xué)號為sno1的sname1同學(xué)的所學(xué)課程的平均分大于學(xué)號為sno2的sname2同學(xué)selectex1.no,,ex2.no,,AVG(ex3.degree)-AVG(ex4.degree)asdiffenencefrom(Studentex1innerjoinScoreex3onex1.no=ex3.no),(Studentex2innerjoinScoreex4onex2.no=ex4.no)groupbyex1.no,,ex2.no,ingselectstuavg1.no,,stuavg2.no,,stuavg1.avgscore-stuavg2.avgscoreasdiffenenceselectstudent.no,name,avg(degree)asavgscorefromscoreleftjoinst