實(shí)驗(yàn)6數(shù)據(jù)庫(kù)嵌套查詢(xún)和組合查詢(xún)

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

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

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

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

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

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

6、001'INTERSECTSELECTCNOFROMSCWHERESNO=,0602002,2000版木上運(yùn)行：bbLEClCWUFKUMbCWHUKilbWU=UbUZUUlINTERSECTSELECTC恥FROMSCWHERES恥0602002?2008版木運(yùn)行結(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⑹杳詢(xún)被0602001啓牛?選修但沒(méi)有被0602002同學(xué)選秀的課程的課程號(hào)（用excpt組合杳詢(xún)與notexists嵌套子查詢(xún)兩種方法實(shí)現(xiàn)）代碼：SELECTCNOFROMSCWHERESNO=,0602

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