廣東工業(yè)大學(xué)SQL編程報(bào)告3

大作業(yè)名稱：數(shù)據(jù)庫(kù)的連接查詢、嵌套查詢和組合查詢實(shí)驗(yàn)一、目的與要求使用SQL語(yǔ)言實(shí)現(xiàn)數(shù)據(jù)庫(kù)的的連接查詢、嵌套查詢和組合查詢工作。必須給出完整的SQL源碼，并以截圖的形式表現(xiàn)出SQL語(yǔ)句的運(yùn)行是否成功。二、SQL源碼及運(yùn)行結(jié)果1、查詢目前被借出去的書籍的書名以及借出日期，并按借出日期排升序。.-查詢目前被借出去的書籍的書名以及借出日期，并按借出日期排升序.-連接查詢.SELECTtitle,borrow_dateFROMbook,borrowWHEREbook.bno=borrow.bnoANDborrow.return_dateISNULLORDERBYborrow_dateASC;目bookXSELECTtitle,borrowdate2FROMbook,borrowWHEREbook.brx）=borrow.bnoANDborrow.returndateISNULLORDERBYborrowdateASC4*臺(tái)的入以ES據(jù)P1OG）G）應(yīng)審圖個(gè)耗時(shí):42ms<1>共3條Q'，匕n，borrow.date▼varchar（40）1JAVAlanguage202207-102Starwars2022-08-103Thesecondworldwar2022-08-202、查詢“ZhangQi”老師借閱的（含己還和未還）書籍的書名。.--查詢“ZhangQi”老師借閱的（含已還和未還）書籍的書名（課內(nèi)完成）.--連接查詢.SELECTtitleFROMbook,borrow,cardWHEREbook.bno=borrow.bnoANDcard.eno=oANDpname='ZhangQi';S3bookXSELECTtitle2FROMbook,borrow,cardWHEREbook.bno=borrow.bno:ANOcard.eno=borrow.eno5ANOpnai?e='ZhangQi,于臺(tái)愴入出83幽R1'O???^[=1個(gè)J0懶:20ms<1>共4條C?title.varchar(40)▼1DatabasesystemsOperationsystemsAdvancedmathematicsDiscretemathematics3、查詢今年上半年未借過(guò)書的人員的姓名。.-查詢今年上半年未借過(guò)書的人員的姓名.--嵌套查詢.SELECTDISTINCTpnameFROMcardWHEREenoNOTIN(SELECTDISTINCTenoFROMborrowWHEREborrow_date>='2022-01-01'ANDborrow_date<'2022-07-01)；S3bookSELECTDISTINCTpname2FROMcardWHEREenoNOTIN(SELECTDISTINCTenoFROMborrowWHEREborrow_date>="2022-01-01*ANDborrow_date<'2022-07-01*)"+&IfiMgOK向⑼圖個(gè)曲:22ms<1〉共4條q-pname:=char18)UAiguWuMiWangLiZhaoGao4、查詢哪個(gè)類別的書出借最多。--查詢哪個(gè)類別的書出借最多SELECTcategoryFROMbook,borrowWHEREbook.bno=borrow.bnoGROUPBYcategoryHAVINGcount(*)>=ALL(SELECTcount(*)FROMbook,borrowWHEREbook.bno=borrow.bnoGROUPBYcategory);密bookXSELECTcategory/FROMbook,borrowUHEREbook.bno=borrow.bno4GROUPBYcategoryHAVINGcount(,)>=ALl(SELECTcount(?)FROMbook,borrowWHEREbook.bno-borrow.bnoGROUPBYcategory)十白徜入以搜索數(shù)據(jù)①①向用?個(gè)網(wǎng):16ms<1>共1條,C*閉2°".Jvarchar(10)▼1eng.5、查詢，his.'類或者M(jìn)IT出版社出版的圖書信息。.--查詢，his.，類或者M(jìn)IT出版社出版的圖書信息.--組合查詢.SELECT*4.FROMbook5.WHEREcategory="his.'6.UNION7.SELECT*|8.FROMbook19.UHEREpublisher='MIT';S3bookXSELECT?FROMbookWHEREcategory='his.，UNIONSELECT?FROMbook中wherepublisher="MIT'臺(tái)愴入以搜索效照atcc6向寥國(guó)個(gè)vl，015ms<1＞共5條Q?bno.char(8)▼?category.varchar(10)▼?title.varchar(40)▼publisher.varchar(3O)▼?pub.ycaryear-author.▼char(20)▼?pricedecimal60008his.ModernChinesehistoryTaylor2017Frank32.00b0009his.ThesecondworldwarTaylor2021Mark32.00b0003sd.AdvancedmathematicsMIT2021Catherine15.004b0004sd.DiscretemathematicsMIT2020Peter22.00b0010eng.JAVAlanguageMIT2022Salty38.00三、思考題1、試用多種SQL查詢方案實(shí)現(xiàn)上述五個(gè)查詢要求，并進(jìn)行比較。問(wèn)題1無(wú)法用嵌套查詢實(shí)現(xiàn)，因?yàn)橐樵冿@示的數(shù)據(jù)存在兩個(gè)表中。問(wèn)題2還可以用嵌套查詢實(shí)現(xiàn)：|1.--嵌套查詢2.SELECTtitleFROMbookWHEREbnoIN(SELECTbnoFROMborrowUHEREenoIN(SELECTenoFROMcardWHEREpname='ZhangQi'));SELECTtitleFROMbookWHEREbnoIN(SELECTbnoFROMborrowWHEREenoIN(SELECTenofromcardWHEREpnaae-'ZhangQi'))+&的入WSSMHH~'C◎①電⑼圖個(gè)。0耗時(shí):13ms<1>共4條一C?title-varchar(40)DatabasesystemsOperationsystemsAdvancedmathematicsDiscretemathematics問(wèn)題3無(wú)法用連接查詢實(shí)現(xiàn)問(wèn)題4另一種做法：.--或者.SELECTcategory,COUNT(*)ASbxFROMbook,borrowWHEREborrow.bno=book.bnoGROUPBYcategory|6.ORDERBYbxDESCLIMIT1；密bookXSELECTcategory,COUNT(")ASbx?FROMbook,borrowWHEREborrow.bno=book.bnoGROUPBYcategory5ORDERBYbxDESCLIMIT1小島輸入貂端④6向。國(guó)個(gè)4/0耗時(shí)：5ms<>Q藍(lán)鬻為，bx，1eng.10問(wèn)題5另一種做法:.--或者.SELECT*.FROMbook.WHEREcategory='his.'ORpublisher='MIT';H3bookXSELECT*FROMbookWHEREcategory="his.'ORpublisher='MIT'小白珀入以搜索的H①①位4圖個(gè)耗時(shí):2ms<1>共5條Q?bnochar(8).■category.▼varchar(IO)▼ithor.consoleAmessageformauthor,witberemovedafterlogo『化0)▼■pricedecimal1b0003SCi.AdvancedmathematicsMIT2021Cath