數(shù)據(jù)庫上機作業(yè)

數(shù)據(jù)庫上機實驗報告Ships表Classes表Outcomes表Battles表*/a)找出至少有10門炮的軍艦類別名和制造國家SELECTclass,countryFROMClassesWHEREnumGuns>=10b)找出所有在1918年以前下水的艦船的名字，并且把結果列名改為ShipName.SELECTnameASShipNameFROMShipsWHEREYEAR(launched)<1918c)找出所有在戰(zhàn)斗中被擊沉的船只和那次戰(zhàn)斗的名字。SELECTshipASShipName,battleFROMOutcomesWHEREresult='sunk'd)找出所有和它的類別名同名的船只。SELECTnameASShipNameFROMShipsWHEREname=classe)找出所有以“R”字符打頭的船只的名字。SELECTnameASShipNameFROMShipsWHEREnameLIKE'R%'*/a)找出重量超過35000噸的船只。SELECTSFROMShips,ClassesWHEREShips.class=Classes.classANDClasses.displacement>35000b)找出參加Guadalcanal戰(zhàn)斗的船只的名字、排水量和火炮數(shù)量。SELECTS.name,C.displacement,C.numGunsFROMShipsS,OutcomesO,ClassesCWHERE S.name=O.shipANDS.class=C.classANDO.battle='Guadalcanal'c)列出數(shù)據(jù)庫在抗洪提到的所有船只。SELECTnameShipNameFROMShipsUNIONSELECTshipShipNameFROMOutcomesd)找出同時擁有戰(zhàn)列艦和巡洋艦的國家。SELECTC1.countryFROMClassesC1,ClassesC2WHEREC1.country=C2.countryANDC1.type='bb'ANDC2.type='bc';/*6.3.2*/a)找出火炮數(shù)量最多的船只所屬的國家。<1>SELECTC.countryFROMClassesCWHEREnumGunsIN(SELECTMAX(numGuns)FROMClasses)<2>SELECTC.countryFROMClassesCWHEREnumGuns>=ALL(SELECTnumGunsFROMClasses)b)找出至少有一艘船在戰(zhàn)斗中被擊沉的船只種類。<1>SELECTDISTINCTC.classFROMClassesC,ShipsSWHEREC.class=S.classANDEXISTS(SELECTshipFROMOutcomesOWHEREO.ship=S.nameANDO.result='sunk')<2>SELECTDISTINCTC.classFROMClassesC,ShipsSWHEREC.class=S.classANDS.nameIN(SELECTshipFROMOutcomesOWHEREO.result='sunk')c)找出具有16英寸口徑火炮的船只的名字。<1>SELECTS.nameFROMShipsSWHERES.classIN(SELECTclassFROMClassesCWHEREbore=16)<2>SELECTS.nameFROMShipsSWHEREEXISTS(SELECTclassFROMClassesCWHEREbore=16ANDC.class=S.class)d)找出有Kongo類型的船只參加的戰(zhàn)斗。<1>SELECTO.battleFROMOutcomesOWHEREO.shipIN(SELECTnameFROMShipsSWHERES.Class='Kongo')<2>SELECTO.battleFROMOutcomesOWHEREEXISTS(SELECTnameFROMShipsSWHERES.Class='Kongo'ANDS.name=O.ship)/*6.4.7*/查詢軍艦類型的數(shù)目。SELECTCOUNT(type)ASClasses_numFROMClassesWHEREtype='bb'b)查詢軍艦的所有類型的平均火炮數(shù)量。SELECTAVG(numGuns)ASAvg_GunsFROMClassesWHEREtype='bb'c)查詢軍艦的平均火炮數(shù)量。SELECTAVG(C.numGuns)ASAvg_GunsFROMClassesCINNERJOINShipsSON(C.class=S.class)WHEREC.type='bb'd)查詢每個類型(class)的第一艦船下水的年份。SELECTC.class,MIN(YEAR(S.launched))ASFirst_LaunchedFROMClassesC,ShipsSWHEREC.class=S.classGROUPBYC.classe)查詢每個類型在戰(zhàn)斗中被擊沉的船的數(shù)目。SELECTC.class,COUNT(O.