第4章答案4-1selectcorId,corNamefromcourses;4-2select*fromsc;4-3selectcorName,period*1.1fromcourses;4-4selectcorId,corName,round(period,0)fromcourses;4-5selectstuId,stuName,year(curdate())-year(birth)asagefromstudents;4-6selectdistinctcreditfromcourses;4-7select*fromstudentswheresex='男';4-8select*fromcourseswhereperiod<='40';4-9select*fromstudentswherebirth>='2003-1-1'andbirth<='2003-12-31';4-10select*fromstudentswherestuNamein('劉晨','王小敏','趙麗');4-11select*fromcourseswherecorNamelike'數(shù)據(jù)庫';4-12select*fromcourseswherecorNamelike'____';4-13select*fromstudentswheretelNonotlike'22%';4-14select*fromstudentswherecommentisnull;4-15select*fromscwherecorId='001'andscore<='60';4-16select*fromstudentswherestuNamelike'張%'orstuNamelike'王%';4-17select*fromcoursesorderbycorld,corName,credit;4-18select*fromscwherestuId='210101001'orderbycorId,scoredesc;4-19select*fromcourseslimit3;4-20select*fromcoursesLIMIT5,4;4-21select*fromscwherestuId='210101001'LIMIT5;4-22selectCOUNT(corName)as總課程數(shù)fromcourses;4-23selectMAX(period)as最長時(shí)長,MIN(credit)as最低時(shí)長fromcourses;4-24selectSUM(score)as總成績(jī),AVG(score)as平均成績(jī)fromscwherestuId='210101003';4-25selectclass,COUNT(*)AS人數(shù)fromstudentsgroupbyclass;4-26selectstuId,count(corId)as選課人數(shù)fromscgroupbycorId;4-27selectclass,count(stuId)as學(xué)生人數(shù)fromstudentsgroupbyclass;havingcount(stuId)<35;4-28selectcorId,count(stuId)as選課人數(shù)fromscgroupbycorIdhavingcount(stuId)<5;4-32selectcourses.*,sc.*fromcoursesinnerjoinsconcourses.corId=sc.corId;4-33selectstudents.stuId,stuName,score,strDatefromstudentsinnerjoinsconstudents.stuId=sc.stuIdwherestudents.stuId='210101001';4-34selectstudents.stuId,stuName,courses.corId,corName,scorefrom(studentsinnerjoinsconstudents.stuId=sc.stuId)innerjoincoursesoncourses.corId=sc.corIdwherescore<60；4-35selectstuName,corId,scorefromstudentsinnerjoinsconstudents.stuId=sc.stuIdwherestuName='張永立'andscore>=80；4-36selectstudents.stuId,stuName,avg(score)fromstudentsinnerjoinsconstudents.stuId=sc.stuIdgroupbystudents.stuId;4-37selectclass,corName,count(sc.stuId)as學(xué)生人數(shù)from(studentsinnerjoinsconstudents.stuId=sc.stuId)innerjoincoursesoncourses.corId=sc.corIdwherecorName='數(shù)據(jù)庫'groupbyclass；4-38selectcourses.corId,corNamefromcoursesLEFTJOINsconcourses.corId=sc.corIdwheresc.corIdisnull;4-39selectstuId,score,strDatefromscwherecorIdin(selectcorId fromcourses wherecorName='操作系統(tǒng)');4-40select*fromcourseswherecredit>(selectcredit fromcourses wherecorName='操作系統(tǒng)');4-41SELECT*FROMstudentsWHEREbirth>All(SELECTbirth FROMstudents WHEREclass='計(jì)算機(jī)應(yīng)用')；4-42SELECT*FROMstudentsWHEREbirth>ANY(SELECTbirth FROMstudents WHEREclass='計(jì)算機(jī)應(yīng)用')；4-43select*fromscwherenotexists(select*fromcourses wheresc.corId=courses.couId);4-44select*fromcourseswhereperiod>‘54’unionselect*fromcourseswherecredit<’3’;4-45selectcorIdfromcourseswhereperiod>54unionselectdistinctcorIdfromsc;第5章答案5-1INSERTintostudentsVALUES('220102035','張小燕','電子商務(wù)','女','2003-12-25','22319874','zhangxiaoyan@163.com','null')；5-2INSERTINTOsc(stuId,corId,score,strdate)VALUE('220102035','85','2022-9-2'),('220102035','002','70','2022-9-2'),('220102035','003','93','2022-9-1')；5-3CREATETABLEscoreavg(stuidchar(3),avgscoreINT)INSERTINTOscoreavgSELECTstuId,avg(score)fromsccgupbystuId；5-4updatecoursessetperiod=36wherecorName='操作系統(tǒng)';5-5updatescsetscore=0wherestuIdin(selectstuIdfromstudents whereclass='計(jì)算機(jī)應(yīng)用')；5-6deletefromcourseswherecorName='操作系統(tǒng)'5-7deletefromscwherecorIdin(selectstuNamefromsc wherecorName='計(jì)算機(jī)應(yīng)用')第6章答案6-1CREATEVIEWcourses1ASSELECT*FROMcoursesWHEREperiod>'54';6-2CREATEVIEWcourses2ASSELECT*FROMcoursesWHEREperiod>'54'WITHCHECKOPTIONS;6-3createviewfemale001(stuId,stUName,corId,score)asselectstudents.stuId,stuName,corId,scorefromstudentsinnerjoinsconstudnets.stuId=sc.stuIdwherecorId=‘001'andsex='女'；6-4createviewfemale00180asselectstuId,stuName,corId,scorefromfemale001wherescore>'80';6-5creatviewstuavgscore(corId,avg)asselectcorId,round(avg(score),1)fromscgroupbycorId；6-6selectperiod,creditfromcourselwherecorName='數(shù)據(jù)庫';6-7select*fromfemaie001wherescore>='60'；6-8inereintocourses1values('065','大數(shù)據(jù)可視化技術(shù)','54','3');6-9updatecourselsetperiod=54wherecorName='數(shù)據(jù)庫';6-10deletefromcourse1wherecorName='數(shù)據(jù)庫';6-11dropviewcourse1;6-12createprocedureStuproc5(var_corNamevarchar(30))beginselectcorName,period,creditfromcourseswherecorName=var_corName;End6-13createprocedureStuproc6(var_corNamevarchar(30))beginselectstuId,scorefromcoursesinnerjoinsconcourses.corId=sc.corIdwherecorName=var_corName;EndcallStuproc6('數(shù)據(jù)庫')6-14createprocedureStuproc7(invar_corIdchar(3),outvar_countint)beginselectcount(*)intovar_countfromscwherecorName=var_corId;End6-16dropprocedureStuproc5;6-17createtabledelcourses(corIdchar(9)primarykey,corNamevarchar(30));createtriggerdel_cor