數據庫實驗報告第一章實驗1.1（1）CREATETABLECUSTOMER(CIDCHAR(8)UNIQUE,CNAMECHAR(20),CITYCHAR(8),DISCNTINT,PRIMARYKEY(CID))CREATETABLEAGENTS(AIDCHAR(8)UNIQUE,ANAMECHAR(20),CITYCHAR(8),PERCENTSFLOAT,PRIMARYKEY(AID))CREATETABLEPRODUCTS(PIDCHAR(8)UNIQUE,PNAMECHAR(20),PRIMARYKEY(PID))（2）CREATETABLEORDERS(ORDNACHAR(8)UNIQUE,MONTHINT,CIDCHAR(8),AIDCHAR(8),PIDCHAR(8),QTYINT,DOLLARSFLOAT,PRIMARYKEY(ORDNA),FOREIGNKEY(CID)REFERENCESCUSTOMER,FOREIGNKEY(AID)REFERENCESAGENTS,FOREIGNKEY(PID)REFERENCESPRODUCTS)（3）ALTERTABLEPRODUCTSADDCITYCHAR(8)ALTERTABLEPRODUCTSADDQUANTITYINTALTERTABLEPRODUCTSADDPRICEFLOAT（4）CREATEINDEXXSNOONCUSTOMER(CID)CREATEINDEXXSNOONAGENTS(AID)CREATEINDEXXSNOONPRODUCTS(PID)CREATEINDEXXSNOONORDERS(ORDNA)（5）DROPINDEXCUSTOMER.XSNODROPINDEXAGENTS.XSNODROPINDEXPRODUCTS.XSNODROPINDEXORDERS.XSNO實驗1.2（1）SELECT*FROMCOURSES（2）SELECTSIDFROMCHOICES（3）SELECTCIDFROMCOURSESWHEREhour<88（4）SELECTSIDFROMCHOICESGROUPBYSIDHAVINGSUM(SCORE)>400（5）SELECTCOUNT(CID)FROMCOURSES（6）SELECTCID,COUNT(CID)FROMCHOICESGROUPBYcid（7）SELECTSIDFROMCHOICESWHEREscore>60GROUPBYsidHAVINGCOUNT(cid)>2（8）SELECTSID,COUNT(CID),AVG(SCORE)FROMCHOICESGROUPBYsid（9）SELECTSTUDENT.sid,snamefromstudent,choice,COURSESwherestudent.sid=choice.sidandchoice.cid=COURSES.cidandCOURSES.cname='java'（10）SELECTCHOICES.sid,CHOICES.scoreFROMCHOICES,STUDENTSWHEREsname='SSSHT'ANDCHOICES.sid=STUDENTS.sidSELECTCIDSCOREFROMCHOICESWHEREsidIN(SELECTSTUDENTS.sidFROMSTUDENTSWHEREsname='SSSHT')（11）SELECTC1.CNAMEFROMCOURSESASC1,COURSESASC2WHEREC1.hour>C2.hourANDC2.cname='C++'（12）SELECTSID,SNAMEFROMSTUDENTSWHEREsidIN(SELECTC1.SIDFROMCHOICESASC1,CHOICESASC2WHEREC1.score>C2.scoreANDC1.cid=C2.cidANDC2.sid=(SELECTsidFROMSTUDENTSWHEREsname='ZNKOO')ANDC1.cid=(SELECTcidFROMCOURSESWHEREcname='C++'))（13）SELECTSNAMEFROMSTUDENTSWHEREgradeIN(SELECTgradeFROMSTUDENTSWHEREsidIN('883794999','850955252'))（14）SELECTSNAMEFROMSTUDENTSWHEREsidNOTIN(SELECTsidFROMCHOICESWHEREcid=(SELECTcidFROMCOURSESWHEREcname='JAVA'))（15）SELECT*FROMCOURSESWHEREhour<=ALL(SELECThourFROMCOURSES)（16）SELECTCHOICES.tid,CIDFROMCHOICESWHERENOTEXISTS(SELECT*FROMTEACHERSWHERETEACHERS.salary>=(SELECTsalaryFROMTEACHERSWHERETEACHERS.tid=CHOICES.tid))（17）SELECTSIDFROMCHOICESWHEREscore=(SELECTMAX(score)FROMCHOICESWHEREcid=(SELECTcidFROMCOURSESWHEREcname='ERP'))（18）SELECTCNAMEFROMCOURSESWHEREcidNOTIN(SELECTcidFROMCHOICES)（19）SELECTCNAMEFROMCOURSESWHEREcid=SOME(SELECTcidFROMCHOICESWHEREtid=SOME(SELECTtidFROMCOURSES,CHOICESWHEREcname='UML'ANDCOURSES.cid=CHOICES.cid))（20）SELECTSNAMEFROMSTUDENTSWHERENOTEXISTS(SELECT*FROMCHOICESASC1WHERENOTEXISTS(SELECT*FROMCHOICESASC2WHEREC2.sid=STUDENTS.sidANDC2.cid=C1.cidANDC2.tid='200102901'))（21）SELECTSIDFROMCHOICES,COURSESWHERECOURSES.cid=CHOICES.cidANDCOURSES.cname='DATABASE'UNIONSELECTSIDFROMCHOICES,COURSESWHERECOURSES.cid=CHOICES.cidANDCOURSES.cname='UML'（22）SELECTX.sidFROMCHOICESASX,CHOICESASYWHERE(X.cid=(SELECTcidFROMCOURSESWHEREcname='DATABASE')ANDY.cid=(SELECTcidFROMCOURSESWHEREcname='UML'))ANDX.sid=Y.sid（23）SELECTX.SIDFROMCHOICESASX,CHOICESASYWHERE(X.cid=(SELECTcidFROMCOURSESWHEREcname='DATABASE'))ANDX.sid=Y.sidANDNOT(Y.cid=(SELECTcidFROMCOURSESWHEREcname='UML'))實驗1.3（1）INSERTINTOSTUDENTS(sid,sname)VALUES('8000022222','WANGLAN')（2）INSERTINTOTEACHERSVALUES('200001000','LXL','S4ZRCK@PEW.NET','3024')（3）UPDATETEACHERSSETsalary=4000WHEREtid='200010493'（4）UPDATETEACHERSSETsalary=2500WHEREsalary<2500（5）UPDATECHOICESSETtid=(SELECTtidFROMTEACHERSWHEREtname='RNUPX')WHEREtid='200016731'（6）UPDATESTUDENTSSETgrade=2001WHEREsid='800071780'（7）DELETEFROMCOURSESWHEREcidNOTIN(SELECTcidFROMCHOICESGROUPBYcid)（8）DELETEFROMSTUDENTSWHEREgrade<1998（9）DELETEFROMSTUDENTSWHEREsidNOTIN(SELECTsidFROMCHOICESGROUPBYsid)（10）DELETEFROMCHOICESWHEREscore<60實驗1.4（1）CREATEVIEWVIEWCASSELECTCHOICES.no,CHOICES.sid,CHOICES.tid,COURSES.cname,CHOICES.scoreFROMCHOICES,COURSESWHERECHOICES.cid=COURSES.cid（2）CREATEVIEWVIEWSASSELECTCHOICES.no,STUDENTS.sname,CHOICES.tid,CHOICES.cid,CHOICES.scoreFROMCHOICES,STUDENTSWHERECHOICES.sid=STUDENTS.sid（3）CREATEVIEWS2(SID,SNAME,GRADE)ASSELECTSTUDENTS.sid,STUDENTS.sname,STUDENTS.gradeFROMSTUDENTSWHEREgrade>1998（4）SELECT*FROMVIEWSWHERESNAME='UXJOF'（5）SELECTSID,SCOREFROMVIEWCWHERECNAME='UML'（6）INSERTINTOs1VALUES('60000001','LILY','2001')（7）CREATEVIEWS1(SID,SNAME,GREADE)ASSELECTSID,SNAME,GRADEFROMSTUDENTSWHEREgrade>1998WITHCHECKOPTION（8）UPDATEVIEWSSETSCORE=SCORE+5WHERESNAME='UXJOF'（9）DROPVIEWVIEWCDROPVIEWVIEWSDROPVIEWS1實驗1.6SELECTCID,HOUR*18FROMCOURSES（2）selectcount(*)fromchoices,courseswherechoices.cid=courses.cidandcname='C++'andscoreisnull（3）selectsid,scorefromchoices,courseswherechoices.cid=courses.cidandcname='C++'orderbyscore（4）SELECTDISTINCTSCOREFROMCHOICESWHEREcid=(SELECTcidFROMCOURSESWHEREcname='C++')ORDERBYscore（5）SE