数据库应用 答案

第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'数据库';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总课程数fromcourses;4-23selectMAX(period)as最长时长,MIN(credit)as最低时长fromcourses;4-24selectSUM(score)as总成绩,AVG(score)as平均成绩fromscwherestuId='210101003';4-25selectclass,COUNT(*)AS人数fromstudentsgroupbyclass;4-26selectstuId,count(corId)as选课人数fromscgroupbycorId;4-27selectclass,count(stuId)as学生人数fromstudentsgroupbyclass;havingcount(stuId)<35;4-28selectcorId,count(stuId)as选课人数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学生人数from(studentsinnerjoinsconstudents.stuId=sc.stuId)innerjoincoursesoncourses.corId=sc.corIdwherecorName='数据库'groupbyclass；4-38selectcourses.corId,corNamefromcoursesLEFTJOINsconcourses.corId=sc.corIdwheresc.corIdisnull;4-39selectstuId,score,strDatefromscwherecorIdin(selectcorId fromcourses wherecorName='操作系统');4-40select*fromcourseswherecredit>(selectcredit fromcourses wherecorName='操作系统');4-41SELECT*FROMstudentsWHEREbirth>All(SELECTbirth FROMstudents WHEREclass='计算机应用')；4-42SELECT*FROMstudentsWHEREbirth>ANY(SELECTbirth FROMstudents WHEREclass='计算机应用')；4-43select*fromscwherenotexists(select*fromcourses wheresc.corId=courses.couId);4-44select*fromcourseswhereperiod>‘54’unionselect*fromcourseswherecredit<’3’;4-45selectcorIdfromcourseswhereperiod>54unionselectdistinctcorIdfromsc;第5章答案5-1INSERTintostudentsVALUES('220102035','张小燕','电子商务','女','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='操作系统';5-5updatescsetscore=0wherestuIdin(selectstuIdfromstudents whereclass='计算机应用')；5-6deletefromcourseswherecorName='操作系统'5-7deletefromscwherecorIdin(selectstuNamefromsc wherecorName='计算机应用')第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='数据库';6-7select*fromfemaie001wherescore>='60'；6-8inereintocourses1values('065','大数据可视化技术','54','3');6-9updatecourselsetperiod=54wherecorName='数据库';6-10deletefromcourse1wherecorName='数据库';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('数据库')6-14createprocedureStuproc7(invar_corIdchar(3),outvar_countint)beginselectcount(*)intovar_countfromscwherecorName=var_corId;End6-16dropprocedureStuproc5;6-17createtabledelcourses(corIdchar(9)primarykey,corNamevarchar(30));createtriggerdel_cor