-.z.數(shù)據(jù)庫(kù)實(shí)習(xí)答案1、E-R圖向關(guān)系模式的轉(zhuǎn)化。最終答案：職工(職工號(hào)，，性別，工資，職稱，簡(jiǎn)歷,部門(mén)號(hào))；部門(mén)(部門(mén)號(hào)，名稱，地址,領(lǐng)導(dǎo)人職工號(hào))；工程(工程號(hào)，工程名，參加人數(shù)，預(yù)算,部門(mén)號(hào),負(fù)責(zé)人職工號(hào))；辦公室(編號(hào)，地點(diǎn)，,部門(mén)號(hào))；參加(職工號(hào)，工程號(hào)，日期，具體職務(wù))；(紅色局部為主鍵,綠色局部為外鍵)。2、SQL查詢語(yǔ)句練習(xí)。/*******************************************************實(shí)驗(yàn)3數(shù)據(jù)庫(kù)的簡(jiǎn)單查詢實(shí)驗(yàn)答案******************************************************/【例3.1】查詢?nèi)w學(xué)生的**與姓名SELECTSno,SnameFROMStudents【例3.2】查詢?nèi)w學(xué)生的**、、性別、年齡、所在系。SELECTSno,Sname,SnameSse*,Sage,SdeptFROMStudents【例3.3】查詢?nèi)w學(xué)生的選課情況，即**、課程號(hào)、成績(jī)，成績(jī)值都加5SELECTSno,Cno,Grade+5FROMEnrollment【例3.4】查詢?nèi)w學(xué)生的**、，并為原來(lái)的英文列名設(shè)置中文列別名。以下三種方法等價(jià)。SELECTSno'**',Sname''FROMStudentsSELECTSnoAS'**',SnameAS''FROMStudentsSELECT'**'=Sno,''=SnameFROMStudents【例3.5】查詢?nèi)w學(xué)生的選課情況，其成績(jī)列值都加5，并為各列設(shè)置中文的別名SELECTSno'**',Cno'課程號(hào)',Grade+5'成績(jī)'FROMEnrollment【例3.6】顯示所有選課學(xué)生的**。SELECTSno'**'FROMEnrollment等價(jià)于：SELECTALLSno'**'FROMEnrollment【例3.7】顯示所有選課學(xué)生的**，并去掉重復(fù)行。SELECTDISTINCTSno'**'FROMEnrollment【例3.8】查詢學(xué)生選課成績(jī)大于80分的學(xué)生**、課程號(hào)、成績(jī)。SELECT*FROMEnrollmentWHEREGrade>80【例3.9】查詢數(shù)學(xué)系全體學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESdept='Math'【例3.10】查詢學(xué)生選課成績(jī)?cè)?0～90分之間的學(xué)生**、課程號(hào)、成績(jī)。SELECT*FROMEnrollmentWHEREGradeBETWEEN80AND90等價(jià)于：SELECT*FROMEnrollmentWHEREGrade>=80ANDGrade<=90【例3.11】查詢學(xué)生年齡不在20～30之間的學(xué)生**、、所在系。SELECTSno,Sname,SdeptFROMStudentsWHERESageNOTBETWEEN20AND30等價(jià)于：SELECTSno,Sname,SdeptFROMStudentsWHERESage<20ORSage>30【例3.12】查詢數(shù)學(xué)系、計(jì)算機(jī)系、藝術(shù)系學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESdeptIN('Math','Computer','Art')等價(jià)于：SELECTSno,SnameFROMStudents WHERESdept='Math'ORSdept='Computer'ORSdept='Art'【例3.13】查詢既不是數(shù)學(xué)系、計(jì)算機(jī)系，也不是藝術(shù)系學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESdeptNOTIN('Math','Computer','Art')等價(jià)于：SELECTSno,SnameFROMStudents WHERESdept!='Math'ANDSdept!='Computer'ANDSdept!='Art'【例3.14】查找姓名的第二個(gè)字符是u并且只有三個(gè)字符的學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESnameLIKE'_u_'【例3.15】查找姓名以S開(kāi)頭的所有學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESnameLIKE'S%'【例3.16】查找姓名以S、D或J開(kāi)頭的所有學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESnameLIKE'[SDJ]%'【例3.17】查找姓名不是以S、D或J開(kāi)頭的所有學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESnameLIKE'[^SDJ]%'等價(jià)于：SELECTSno,SnameFROMStudentsWHERESnameNOTLIKE'[SDJ]%'【例3.18】查詢無(wú)考試成績(jī)的學(xué)生的**和相應(yīng)的課程號(hào)。SELECTSno,CnoFROMEnrollmentWHEREGradeISNULL不等價(jià)于：SELECTSno,CnoFROMEnrollmentWHEREGrade=0【例3.19】查詢有考試成績(jī)(即成績(jī)不為空值)的學(xué)生的**、課程號(hào)。SELECTSno,CnoFROMEnrollmentWHEREGradeISNOTNULL【例】查詢計(jì)算機(jī)系年齡在18歲以上的學(xué)生**、。SELECTSno,SnameFROMStudentsWHERESdept='Computer'ANDSage>18【例3.21】求選修了C1課程或C2課程的學(xué)生**、成績(jī)。SELECTSno,GradeFROMEnrollmentWHERECno='C1'ORCno='C2'為了使查詢語(yǔ)句的可讀性更好，可以將WHERE后面的整個(gè)條件用括號(hào)括起來(lái)。等價(jià)于:SELECTSno,GradeFROMEnrollmentWHERE(Cno='C1'ORCno='C2')【例3.22】求學(xué)生的總?cè)藬?shù)SELECTCOUNT(*)FROMStudents【例3.23】求選修了課程的學(xué)生人數(shù)SELECTCOUNT(DISTINCTSno)FROMEnrollment【例3.24】求選修了C1課程的學(xué)生的平均成績(jī)。SELECTAVG(Grade)FROMEnrollmentWHERECno='C1'【例3.25】求20010102號(hào)學(xué)生的考試總成績(jī)之和。SELECTSUM(Grade)FROMEnrollmentWHERESno='20010102'【例3.26】選修了C1課程的學(xué)生的最高分和最低分SELECTMA*(Grade),MIN(Grade)FROMEnrollmentWHERECno='C1'【例】求選修每門(mén)課程的學(xué)生人數(shù)。SELECTCnoAS'課程號(hào)',COUNT(Sno)AS'選修人數(shù)'FROMEnrollmentGROUPBYCno【例3.28】求每個(gè)學(xué)生的**和各門(mén)課程的總成績(jī)。SELECTSno'**',Sum(grade)'總成績(jī)'FROMEnrollmentGROUPBYSno【例3.29】求選修課程超過(guò)2門(mén)課的學(xué)生的**、平均成績(jī)，選修的門(mén)數(shù)。SELECTSno,AVG(Grade)AS'平均成績(jī)',COUNT(*)AS'選修門(mén)數(shù)'FROMEnrollmentGROUPBYSnoHAVINGCOUNT(*)>=2【例3.30】查詢所有學(xué)生的行，并按學(xué)生的年齡值從小到大排序。SELECT*FROMStudentsORDERBYSage【例3.31】查詢選修了C1課程的學(xué)生的**和成績(jī)，查詢結(jié)果按成績(jī)降序排列。SELECTSno,GradeFROMEnrollmentWHERECno='C1'ORDERBYGradeDESC【例3.32】查詢?nèi)w學(xué)生信息，查詢結(jié)果按所在系的系名升序排列，同一系的學(xué)生按年齡降序排列。SELECT*FROMStudentsORDERBYSdept,SageDESC【例3.33】求選修課程超過(guò)2門(mén)課的學(xué)生的**、平均成績(jī)和選課門(mén)數(shù)，并按平均成績(jī)降序排列。SELECTSnoAS'**',AVG(Grade)AS'平均成績(jī)',COUNT(*)AS'修課門(mén)數(shù)' FROMEnrollmentGROUPBYSnoHAVINGCOUNT(*)>=2ORDERBYAVG(Grade)DESC/*******************************************************實(shí)驗(yàn)4數(shù)據(jù)庫(kù)的多表連接查詢實(shí)驗(yàn)答案******************************************************/【例4.1】查詢每個(gè)學(xué)生的根本信息以及他/她選課的情況。SELECTStudents.*,Enrollment.*FROMStudents,EnrollmentWHEREStudents.Sno=Enrollment.Sno上述是等值連接,改為自然連接，表示如下：SELECTStudents.Sno,Sname,Sse*,Sage,Sdept,Cno,GradeFROMStudents,Enrollment【例4.2】查詢每個(gè)學(xué)生的**、、選修的課程名、成績(jī)。SELECTStudents.Sno,Sname,Cname,GradeFROMStudents,Courses,Enrollment【例4.3】查詢選修了C2且成績(jī)大于90分的學(xué)生的**、、成績(jī)。SELECTStudents.Sno,Sname,GradeFROMStudents,EnrollmentWHEREStudents.Sno=Enrollment.SnoANDCno='C2'ANDGrade>90【例4.4】求計(jì)算機(jī)系選修課程超過(guò)2門(mén)課的學(xué)生的**、、平均成績(jī),并按平均成績(jī)從高到低排序。SELECTStudents.Sno,Sname,AVG(Grade)'Average'FROMStudents,EnrollmentWHEREStudents.Sno=Enrollment.SnoANDSdept='Computer'GROUPBYStudents.Sno,SnameHAVINGCOUNT(*)>=2ORDERBYSUM(Grade)DESC【例4.5】查詢與Sue在同一個(gè)系學(xué)習(xí)的所有學(xué)生的**和姓名。FROMStudentsS1,StudentsS2whereS1.Sdept=S2.SdeptANDS1.Sname='Sue'【例4.6】查詢所有學(xué)生的選修情況，要求包括選修了課程的學(xué)生和沒(méi)有修課的學(xué)生，顯示他們的**、、課程號(hào)、成績(jī)。SELECTStudents.Sno,Sname,Cno,GradeFROMStudents,Enrollment上述左外連接可可以用右外連接等價(jià)表示：SELECTStudents.Sno,Sname,Cno,GradeFROMStudents,Enrollment在SQLSERVER2000中，以上左外連接還可等價(jià)表示為：SELECTStudents.Sno,Sname,Cno,Grade-或者表示為：SELECTStudents.Sno,Sname,Cno,Grade/*******************************************************實(shí)驗(yàn)5數(shù)據(jù)庫(kù)的子查詢、組合查詢實(shí)驗(yàn)答案******************************************************/【例5.1】查詢與Sue在同一個(gè)系學(xué)習(xí)的學(xué)生**、。 SELECTSno,SnameFROMStudents WHERESdept=(SELECTSdeptFROMStudentsWHERESname='Sue')【例5.2】查詢其他系中比計(jì)算機(jī)系任一學(xué)生年齡都小的學(xué)生根本情況。SELECT*FROMStudentsWHERESdept!='Computer'ANDSage<ALL(SELECTSageFROMStudentsWHERESdept='Computer')【例5.3】查詢其他系中比計(jì)算機(jī)系*一學(xué)生年齡小的學(xué)生的根本情況。SELECT*FROMStudentsWHERESdept!='Computer'ANDSage<ANY(SELECTSageFROMStudentsWHERESdept='Computer')【例5.4】查詢成績(jī)大于80分的學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERESno=ANY(SELECTSnoFROMEnrollmentWHEREGrade>80)【例5.5】查詢選修了課程名為English的課程并且成績(jī)大于80分的學(xué)生**、。SELECTSno,SnameFROMStudentsWHERESnoIN(SELECTSnoFROMEnrollmentWHEREGrade>80ANDCno=(SELECTCnoFROMCoursesWHERECname='English'))【例5.6】查詢選修了C2課程的學(xué)生的**和姓名。SELECTSno,SnameFROMStudentsWHEREE*ISTS(SELECT*FROMEnrollmentWHERESno=Students.SnoANDCno='C2')【例5.7】查詢沒(méi)有選修C2課程的學(xué)生的**、。SELECTSno,SnameFROMStudentsWHERENOTE*ISTS(SELECT*FROMEnrollmentWHERESno=Students.SnoANDCno='C2')【例5.8】查詢選修了C1課程或選修了C2課程的學(xué)生**。SELECTSnoFROMEnrollmentWHERECno='C1'UNIONSELECTSnoFROMEnrollmentWHERECno='C2'【例5.9】查詢選修了C1課程并且也選修了C2課程的學(xué)生**。SELECTSnoFROMEnrollmentWHERECno='C1'SQLSERVER2000中不能運(yùn)行INTERSECTSELECTSnoFROMEnrollmentWHERECno='C2'上述查詢?cè)赟QLSERVER2000中應(yīng)表示為：SELECTSnoFROMEnrollmentE1WHERECno='C1'ANDE*ISTS(SELECTSnoFROMEnrollmentE2WHEREE1.Sno=E2.SnoANDE2.Cno='C2')【例5.10】查詢選修了C1課程但沒(méi)有選修了C2課程的學(xué)生**。SELECTSnoFROMEnrollmentWHERECno='C1'SQLSERVER2000中不能運(yùn)行MINUSSELECTSnoFROMEnrollmentWHERECno='C2'上述查詢?cè)赟QLSERVER2000中應(yīng)表示為：SELECTSnoFROMEnrollmentE1WHERECno='C1'ANDNOTE*ISTS(SELECTSnoFROMEnrollmentE2WHEREE1.Sno=E2.SnoANDE2.Cno='C2')/*******************************************************實(shí)驗(yàn)6數(shù)據(jù)庫(kù)的更新實(shí)驗(yàn)答案******************************************************/【例6.1】向Students表中添加一個(gè)學(xué)生記錄，學(xué)生**為20010105,姓名為Stefen,性別為男，年齡25歲，所在系為藝術(shù)系A(chǔ)rt。INSERTINTOStudentsVALUES('20010105','Stefen','F',25,'Art')【例6.2】向Enrollment表中添加一個(gè)學(xué)生的選課記錄，學(xué)生**為20010105,所選的課程號(hào)為C2。INSERTINTOEnrollment(Sno,Cno)VALUES('20010105','C2')【例6.3】假定當(dāng)前數(shù)據(jù)庫(kù)中有一個(gè)臨時(shí)表Temp，見(jiàn)表。把它的所有行一次性地加到Students表中。要求先定義Temp表，輸入假設(shè)干行數(shù)據(jù)。具體內(nèi)容表3-7INSERTINTOStudentsSELECT*FROMTemp【例6.4】將所有學(xué)生選課的成績(jī)加5。UPDATEEnrollmentSETGrade=Grade+5【例6.5將姓名為Sue的學(xué)生所在系改為計(jì)算機(jī)系。UPDATEStudentsSETSdept='Computer'WHERESname='Sue'【例6.6】將選課了課程名為Database課程的學(xué)生成績(jī)加10。UPDATEEnrollmentSETGrade=Grade+10WHERECno=(SELECTCnoFROMCoursesWHERECname='Database')【例6.7】刪除所有成績(jī)?yōu)榭罩档倪x修記錄。DELETEFROMEnrollmentWHEREGradeISNULL【例6.8】刪除學(xué)生姓名為Deepa的學(xué)生記錄。DELETEFROMStudentsWHERESname='Deepa'【例6.9】刪除計(jì)算機(jī)系選修成績(jī)不及格的學(xué)生選修記錄。DELETEFROMEnrollmentWHEREGrade<60ANDSnoIN(SELECTSnoFROMStudentsWHERESdept='Computer')GO/*******************************************************實(shí)驗(yàn)7數(shù)據(jù)庫(kù)的視圖定義及使用實(shí)驗(yàn)答案******