数据库系统基础教程第6章课后习题答案

SolutionsChapter66.1.1Attributesmustbeseparatedbycommas.ThushereBisanaliasofA.6.1.2a)SELECTaddressASStudio_AddressFROMStudioWHERENAME='MGM';b)SELECTbirthdateASStar_BirthdateFROMMovieStarWHEREname='SandraBullock';c)SELECTstarNameFROMStarsInWHEREmovieYear=1980ORmovieTitleLIKE'%Love%';However,abovequerywillalsoreturnwordsthathavethesubstringLovee.g.Lover.BelowquerywillonlyreturnmoviesthathavetitlecontainingthewordLove.SELECTstarNameFROMStarsInWHEREmovieYear=1980ORmovieTitleLIKE'Love%'ORmovieTitleLIKE'%Love%'ORmovieTitleLIKE'%Love'ORmovieTitle='Love';d)SELECTnameASExec_NameFROMMovieExecWHEREnetWorth=10000000;e)SELECTnameASStar_NameFROMmovieStarWHEREgender='M'ORaddressLIKE'%Malibu%';6.1.3a)SELECTmodel,speed,hdFROMPCWHEREprice1000;MODELSPEEDHD---------------------10022.1025010031.428010042.8025010053.2025010072.2020010082.2025010092.0025010102.8030010111.8616010122.8016010133.068011record(s)selected.b)SELECTmodel,speedASgigahertz,hdASgigabytesFROMPCWHEREprice1000;MODELGIGAHERTZGIGABYTES------------------------10022.1025010031.428010042.8025010053.2025010072.2020010082.2025010092.0025010102.8030010111.8616010122.8016010133.068011record(s)selected.c)SELECTmakerFROMProductWHERETYPE='printer';MAKER-----DDEEEHH7record(s)selected.d)SELECTmodel,ram,screenFROMLaptopWHEREprice1500;MODELRAMSCREEN------------------2001204820.12005102417.02006204815.42010204815.44record(s)selected.e)SELECT*FROMPrinterWHEREcolor;MODELCASETYPEPRICE------------------------3001TRUEink-jet993003TRUElaser9993004TRUEink-jet1203006TRUEink-jet1003007TRUElaser2005record(s)selected.Note:ImplementationofBooleantypeisoptionalinSQLstandard(featureIDT031).PostgreSQLhasimplementationsimilartoaboveexample.OtherDBMSprovideequivalentsupport.E.g.InDB2thecolumntypecanbedeclareasSMALLINTwithCONSTRAINTthatthevaluecanbe0or1.TheresultcanbereturnedasBooleantypeCHARusingCASE.CREATETABLEPrinter(modelCHAR(4)UNIQUENOTNULL,colorSMALLINT,typeVARCHAR(8),priceSMALLINT,CONSTRAINTPrinter_ISCOLORCHECK(colorIN(0,1)));SELECTmodel,CASEcolorWHEN1THEN'TRUE'WHEN0THEN'FALSE'ELSE'ERROR'ENDCASE,type,priceFROMPrinterWHEREcolor=1;f)SELECTmodel,hdFROMPCWHEREspeed=3.2ANDprice2000;MODELHD-----------100525010063202record(s)selected.6.1.4a)SELECTclass,countryFROMClassesWHEREnumGuns=10;CLASSCOUNTRY------------------------------TennesseeUSA1record(s)selected.b)SELECTnameASshipNameFROMShipsWHERElaunched1918;SHIPNAME------------------HarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyalOakRoyalSovereign11record(s)selected.c)SELECTshipASshipName,battleFROMOutcomesWHEREresult='sunk';SHIPNAMEBATTLE------------------------------------ArizonaPearlHarborBismarkDenmarkStraitFusoSurigaoStraitHoodDenmarkStraitKirishimaGuadalcanalScharnhorstNorthCapeYamashiroSurigaoStrait7record(s)selected.d)SELECTnameASshipNameFROMShipsWHEREname=class;SHIPNAME------------------IowaKongoNorthCarolinaRenownRevengeYamato6record(s)selected.e)SELECTnameASshipNameFROMShipsWHEREnameLIKE'R%';SHIPNAME------------------RamilliesRenownRepulseResolutionRevengeRoyalOakRoyalSovereign7record(s)selected.Note:Asmentionedinexercise2.4.3,therearesomedanglingpointersandtoretrieveallshipsaUNIONofShipsandOutcomesisrequired.Belowqueryreturns8rowsincludingshipnamedRodney.SELECTnameASshipNameFROMShipsWHEREnameLIKE'R%'UNIONSELECTshipASshipNameFROMOutcomesWHEREshipLIKE'R%';f)Onlyusingafilterlike'%%%'willincorrectlymatchnamesuchas'ab'since%canmatchanysequenceof0ormorecharacters.SELECTnameASshipNameFROMShipsWHEREnameLIKE'_%_%_%';SHIPNAME------------------0record(s)selected.Note:Asin(e),UNIONwithresultsfromOutcomes.SELECTnameASshipNameFROMShipsWHEREnameLIKE'_%_%_%'UNIONSELECTshipASshipNameFROMOutcomesWHEREshipLIKE'_%_%_%';SHIPNAME------------------DukeofYorkKingGeorgeVPrinceofWales3record(s)selected.6.1.5a)Theresultingexpressionisfalsewhenneitherof(a=10)or(b=20)isTRUE.a=10b=20a=10ORb=20NULLTRUETRUETRUENULLTRUEFALSETRUETRUETRUEFALSETRUETRUETRUETRUEb)TheresultingexpressionisonlyTRUEwhenboth(a=10)and(b=20)areTRUE.a=10b=20a=10ANDb=20TRUETRUETRUEc)TheexpressionisalwaysTRUEunlessaisNULL.a10a=10a=10ANDb=20TRUEFALSETRUEFALSETRUETRUEd)TheexpressionisTRUEwhena=bexceptwhenthevaluesareNULL.aba=bNOTNULLNOTNULLTRUEwhena=b;elseFALSEe)Likein(d),theexpressionisTRUEwhena=bexceptwhenthevaluesareNULL.aba=bNOTNULLNOTNULLTRUEwhena=b;elseFALSE6.1.6SELECT*FROMMoviesWHERELENGTHISNOTNULL;6.2.1a)SELECTM.nameASstarNameFROMMovieStarM,StarsInSWHEREM.name=S.starNameANDS.movieTitle='Titanic'ANDM.gender='M';b)SELECTS.starNameFROMMoviesM,StarsInS,StudiosTWHERET.name='MGM'ANDM.year=1995ANDM.title=S.movieTitleANDM.studioName=T.name;c)SELECTX.nameASpresidentNameFROMMovieExecX,StudioTWHEREX.cert#=T.presC#ANDT.name='MGM';d)SELECTM1.titleFROMMoviesM1,MoviesM2WHEREM1.lengthM2.lengthANDM2.title='GoneWiththeWind';e)SELECTX1.nameASexecNameFROMMovieExecX1,MovieExecX2WHEREX1.netWorthX2.netWorthANDX2.name='MervGriffin';6.2.2a)SELECTR.makerASmanufacturer,L.speedASgigahertzFROMProductR,LaptopLWHEREL.hd=30ANDR.model=L.model;MANUFACTURERGIGAHERTZ----------------------A2.00