實驗6數(shù)據(jù)庫嵌套查詢和組合查詢

《實驗6數(shù)據(jù)庫嵌套查詢和組合查詢》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。

1、實驗六數(shù)據(jù)庫嵌套查詢和組合查詢實驗?zāi)康模赫莆砧迷冨那短仔釉兒徒M合查詢的操作方法，進(jìn)一步加深對select語句的理解。實驗內(nèi)容：(1)創(chuàng)建查詢窗口，設(shè)置査詢環(huán)境(2)分組查詢(3)統(tǒng)計查詢(4)嵌套查詢實驗步驟：新建查詢窗口，選擇studentcourse為當(dāng)前數(shù)據(jù)庫，輸入如bselect查詢語句并執(zhí)彳亍，對數(shù)據(jù)庫進(jìn)行嵌套杳詢和組合杏詢操作。(1)統(tǒng)計選修了【數(shù)據(jù)庫原理】課程的學(xué)生人數(shù)。代碼：SELECTCOUNT(SNO)AS人數(shù)FROMSCWHERECNOIN(SELECTCNOFROMCOURSESWHERECNAME=*數(shù)

2、據(jù)庫原理)SELECTCOUNT（SNO）AT入數(shù)FROMSCWHERECNOIN（SELECTCNOFROMCOURSESWHFRFCNAMF二'勒抿屋爲(wèi)禪‘）人數(shù)4~（2）查詢沒冇選修【數(shù)據(jù)庫原理】課程學(xué)生信息。代碼：SELECT*FROMSTUDENTSWHERESNONOTIN（SELECTSNOFROMSCWHERECNOIN（SELECTCNOFROMCOURSESWHERECNAME-數(shù)據(jù)庫原理））SELECT#FROMSTUDENTSWHERESNONOTIN（SELECTSNOFROMSCWHERECNOIN（S

3、ELECTCNOFROMCOURSESWHERECNAJffi=,數(shù)據(jù)庫原理’））<—11snosnamessexsbirthdaysdeptmemo0602005張三男1991-10-0100:00:00計算機NULL20602006張水花女1991-08-1500:00:00數(shù)學(xué)NULL(3)査詢其他系中比計算機系學(xué)生年齡都小的學(xué)生。加入一行數(shù)據(jù)0602007李四男1991-12-2數(shù)學(xué)代碼：SELECT*FROMSTUDENTSWHERESBIRTHDAY>ALL(SELECTSBIRTHDAYFROMSTUDENTSWHE

4、RESDEPT二'計算機')SELECT*FROMSTUDENTSWHERESBIRTHDAY>ALL(SELECTSBIRTHDAYFROMSTUDENTSWHERESDEPT=‘計算機')1snosnamessexsbirthdaysdeptmemo0602007李四男1991-12-0200:00:00數(shù)學(xué)NULL(4)查詢被0602001學(xué)牛?或0602002學(xué)牛所選修的課程的課程號(用union組合查詢與in條件查詢兩種方法實現(xiàn))代碼：SELECTCNOFROMSCWHERESNO=,0602()()11UNIONSEL

5、ECTCNOFROMSCWHERESNO=,0602002tSELECT50FROMSCWHERESNO=?060200TUI-IIONSELECTC恥FROMSCWHERES恥0602002?CNO1Cl2C23C34C4代碼：SELECTDISTINCT(CNO)FROMSCWHERESNOIN(*060200lr；0602002r)(1)査詢0602001學(xué)生和0602002學(xué)生同時選修的課程號(用intersect組合查詢與exists嵌套了查詢兩種方法實現(xiàn))代碼：SELECTCNOFROMSCWHERESNO=,0602

6、001'INTERSECTSELECTCNOFROMSCWHERESNO=,0602002,2000版木上運行：bbLEClCWUFKUMbCWHUKilbWU=UbUZUUlINTERSECTSELECTC恥FROMSCWHERES恥0602002?2008版木運行結(jié)果：0SELECTCNOFROMSC$WHERESNO=106020011INTERSECT-SELECTCNOFROMSC$WHERESNO=f06020021/XAAAAA/VWW-/SAAAAAFROMSCSCIWHEREEXISTS(SELECTCNOFRO

7、MSCSC2WHERESC1.CNO=SCI.CNOANDSC2.SNO=0602002,)ANDSNO=,0602001,SELECT50FROMSCSCIWHEREEXISTS(SELECTCNOFROMSCSC2WHERESCI.CNO二SCI.CNOANDSC2?SNO=56U20CI2')ANDSNO=?0602001⑹杳詢被0602001啓牛?選修但沒有被0602002同學(xué)選秀的課程的課程號（用excpt組合杳詢與notexists嵌套子查詢兩種方法實現(xiàn)）代碼：SELECTCNOFROMSCWHERESNO=,0602

8、00rEXCEPTSELECTCNOFROMSCWHERESNO=,0602002,2000版木：SELECTCNOFROMSCWHERESNO=?060200TEXCEPTSELECTCNOFROMSCWHERESNO二'0602002'服務(wù)器肖