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

《数据库系统基础教程第6章课后习题答案.docx》由会员分享，可在线阅读，更多相关《数据库系统基础教程第6章课后习题答案.docx（54页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Solutions Chapter 66.1.1Attributes must be separated by commas. Thus here B is an alias of A.6.1.2a)SELECTaddress AS Studio_Address FROMStudioWHERENAME = MGM;b)SELECTbirthdate AS Star_Birthdate FROMMovieStarWHEREname = Sandra Bullock;c)SELECTstarName FROMStarsInWHEREmovieYear = 1980OR movieTitle LIK

2、E %Love%;However, above query will also return words that have the substring Love e.g. Lover. Below query will only return movies that have title containing the word Love.SELECTstarName FROMStarsInWHEREmovieYear = 1980OR movieTitle LIKE Love % OR movieTitle LIKE % Love % OR movieTitle LIKE % Love OR

3、 movieTitle = Love;d)SELECTname AS Exec_Name FROMMovieExecWHEREnetWorth = 10000000;e)SELECTname AS Star_Name FROMmovieStarWHEREgender = MOR address LIKE % Malibu %;6.1.3a)SELECTmodel,speed, hdFROMPCWHEREprice 1000 ;MODELSPEEDHD10022.1025010031.428010042.8025010053.2025010072.2020010082.2025010092.00

4、25010102.8030010111.8616010122.8016010133.068011 record(s) selected.b)SELECTmodel,FROMspeed hdPCAS gigahertz, AS gigabytesWHEREprice 1500 ; MODEL RAMSCREEN4 record(s) selected.e)SELECT*FROMPrinterWHEREcolor ;MODEL CASETYPEPRICE3001TRUEink-jet993003TRUElaser9993004TRUEink-jet1203006TRUEink-jet1003007

5、TRUElaser2005 record(s) selected.Note: Implementation of Boolean type is optional in SQL standard (feature ID T031). PostgreSQL has implementation similar to above example. Other DBMS provide equivalent support. E.g. In DB2the column type can be declare as SMALLINT with CONSTRAINT that the value can

6、 be 0 or 1. The result can be returned as Boolean type CHAR using CASE.CREATE TABLE Printer();SELECTmodel,model CHAR(4) UNIQUE NOT NULL,color SMALLINT,typeVARCHAR(8),price SMALLINT,CONSTRAINT Printer_ISCOLOR CHECK(color IN(0,1)CASE colorWHEN 1THEN TRUE WHEN 0THEN FALSE ELSE ERROREND CASE,type, price

7、FROMPrinter WHEREcolor = 1;f)SELECTmodel,hdFROMPCWHEREspeed = 3.2 AND price =10 ;CLASSCOUNTRYTennesseeUSA1 record(s) selected.b)SELECTname AS shipName FROMShipsWHERElaunched 1918 ; SHIPNAMEHaruna Hiei Kirishima Kongo Ramillies Renown Repulse Resolution Revenge Royal OakRoyal Sovereign11 record(s) se

8、lected.c)SELECTship AS shipName,battleFROMOutcomesWHEREresult = sunk ; SHIPNAMEBATTLEArizonaPearl HarborBismarkDenmark StraitFusoSurigao StraitHoodDenmark StraitKirishimaGuadalcanalScharnhorstNorth CapeYamashiroSurigao Strait7 record(s) selected.d)SELECTname AS shipName FROMShipsWHEREname = class ;

9、SHIPNAMEIowa KongoNorth Carolina Renown Revenge Yamato6 record(s) selected.e)SELECTname AS shipName FROMShipsWHEREname LIKE R%; SHIPNAMERamillies Renown Repulse Resolution Revenge Royal OakRoyal Sovereign7 record(s) selected.Note: As mentioned in exercise 2.4.3, there are some dangling pointers and

10、to retrieve all ships a UNION of Ships and Outcomes is required.Below query returns 8 rows including ship named Rodney.SELECTname AS shipName FROMShipsWHEREname LIKE R% UNIONSELECTship AS shipName FROMOutcomesWHEREship LIKE R%;f) Only since %using a filter like % % % can match any sequence of 0will

11、incorrectly match name such as a b or more characters.SELECTname AS shipNameFROMShipsWHEREname LIKE _% _% _% ;SHIPNAME0 record(s) selected.Note: As in (e), UNION with results from Outcomes.SELECT FROM WHEREUNIONname AS shipName Shipsname LIKE _% _%_%SELECT FROMWHEREship AS shipName Outcomesship LIKE

12、 _% _%_% ;SHIPNAMEDuke of York King George V Prince of Wales3 record(s) selected.6.1.5a)The resulting expression is false when neither of (a=10) or (b=20) is TRUE. a = 10b = 20a = 10 OR b = 20NULLTRUETRUETRUENULLTRUEFALSETRUETRUETRUEFALSETRUETRUETRUETRUEb)The resulting expression is only TRUE when b

13、oth (a=10) and (b=20) are TRUE. a = 10b = 20a = 10 AND b = 20TRUETRUETRUEc)The expression is always TRUE unless a is NULL. a = 10a = 10 AND b = 20TRUEFALSETRUEFALSETRUETRUEd)The expression is TRUE when a=b except when the values are NULL. aba = bNOT NULLNOT NULLTRUE when a=b; else FALSEe)Like in (d)

14、, the expression is TRUE when a=b except when the values are NULL.aba = bNOT NULLNOT NULLTRUE whena M2.lengthAND M2.title=Gone With the Wind ;e)SELECTX1.name AS execName FROMMovieExec X1,MovieExec X2WHEREXWorth XWorthAND X2.name= Merv Griffin ;6.2.2a)SELECTR.maker AS manufacturer, L.speed AS gigaher

15、tzFROMProduct R, Laptop LWHEREL.hd= 30A2.00A2.16A2.00B1.83E2.00E1.73E1.80F1.60F1.60G2.00AND R.model = L.model ; MANUFACTURER GIGAHERTZ10 record(s) selected.b)SELECTR.model,FROMP.priceProductR,WHEREPC PR.maker= BANDR.model= P.modelUNIONSELECTFROMR.model, L.price ProductR,Laptop L WHERER.maker = BAND

16、R.model = L.model UNIONSELECTR.model,T.priceFROMProduct R, Printer TWHERER.maker = B100464910056301006104920071429AND R.model = T.model ; MODEL PRICE4 record(s) selected.c)SELECTR.maker FROMProduct R,Laptop LWHERER.model = L.model EXCEPTSELECTR.maker FROMProduct R,PC PWHERER.model = P.model ; MAKERF

17、 G2 record(s) selected.d)SELECT DISTINCT P1.hd FROMPC P1,PC P2WHEREP1.hd=P2.hdAND P1.model P2.model ; Alternate Answer:SELECT DISTINCT P.hd FROMPC PGROUP BY P.hdHAVING COUNT(P.model) = 2 ;e)SELECTP1.model,P2.modelFROMPC P1, PC P2WHEREP1.speed = P2.speed AND P1.ram= P2.ram AND P1.model = 3.0AND P.mod

18、el=R.model UNIONSELECTmaker,R.modelFROMLaptop L, Product RWHEREspeed = 3.0 AND L.model=R.model) MGROUP BY M.makerHAVING COUNT(M.model) = 2 ; MAKERB1 record(s) selected.6.2.3a) SELECTFROMS.name Ships S,Classes CWHERES.class=C.classANDC.displacement35000;NAMEIowa Missouri Musashi New JerseyNorth Carol

19、ina Washington Wisconsin Yamato8 record(s) selected.b)SELECTS.name,C.displacement, C.numGunsFROM Ships S , Outcomes O, Classes CWHERES.name= O.ship AND S.class= C.classAND O.battle = Guadalcanal ;NAMEDISPLACEMENT NUMGUNSKirishima320008Washington3700092 record(s) selected.Note:South Dakota was also e

20、ngaged in battle of Guadalcanal but not chosen since it is not in Ships table(Hence, no information regarding its Class is available).c)SELECTname shipName FROMShipsUNIONSELECTship shipName FROMOutcomes ;SHIPNAMEArizona Bismark California Duke of York FusoHaruna Hiei Hood IowaKing George V Kirishima

21、 Kongo Missouri MusashiNew Jersey North Carolina Prince of Wales Ramillies RenownRepulse Resolution Revenge Rodney Royal OakRoyal Sovereign Scharnhorst South Dakota Tennesee Tennessee Washington West Virginia Wisconsin Yamashiro Yamato34 record(s) selected.d)SELECTC1.country FROMClasses C1,Classes C

22、2WHEREC1.country = C2.country AND C1.type= bbAND C2.type= bc ; COUNTRYGt. Britain Japan2 record(s) selected.e)SELECTO1.ship FROMOutcomes O1,Battles B1WHEREO1.battle = B1.name AND O1.result = damaged AND EXISTS(SELECT B2.dateFROMOutcomes O2, Battles B2WHEREO2.battle=B2.name AND O1.ship= O2.ship AND B

23、1.date 3;SELECTO.battle FROMShips S,Classes C, Outcomes OWHEREC.Class = S.class AND O.ship= S.nameGROUP BY C.country,O.battleHAVING COUNT(O.ship) = 3;6.2.4Since tuple variables are not guaranteed to be unique, every relation Ri should be renamed using an alias. Every tuple variable should be qualifi

24、ed with the alias. Tuple variables for repeating relations will also be distinctly identified this way.Thus the query will be likeSELECT A1.COLL1,A1.COLL2,A2.COLL1, FROM R1A1,R2A2,RnAnWHERE A1.COLL1=A2.COLC2,6.2.5Again, create a tuple variable for every Ri, i=1,2,.,n That is, the FROM clause isFROM

25、R1A1, R2A2,.,RnAn.Now, build the WHERE clause from C by replacing every reference to some attribute COL1 of Ri by Ai.COL1. In addition apply Natural Join i.e. add condition to check equality of common attribute names between Ri and Ri+1 for all i from 0 to n-1. Also, build the SELECT clause from lis

26、t of attributes L by replacing every attribute COLj of Ri by Ai.COLj.6.3.1a)SELECT DISTINCT makerFROMProduct WHEREmodel IN(SELECT model FROMPCWHEREspeed = 3.0);SELECT DISTINCT R.makerFROMProduct RWHEREEXISTS(SELECT P.model FROMPC PWHEREP.speed = 3.0AND P.model=R.model);b) SELECT FROMWHEREP1.model Pr

27、interP1.priceP1= ALLSELECT(SELECT FROM) ;P1.modelP2.price Printer P2FROMPrinterP1WHEREP1.price (SELECT FROM) ;INMAX(P2.price) Printer P2c) SELECTL.modelFROMLaptop LWHERESELECTL.speed (SELECT FROM) ;L.model= L.speedd)SELECTmodel FROM(SELECT model,priceFROMPC UNIONSELECTmodel,priceFROMLaptop UNIONSELE

28、CTmodel,priceFROMPrinter) M1WHEREM1.price = ALL(SELECT price FROMPCUNIONSELECTprice FROMLaptopUNIONSELECTprice FROMPrinter) ;(d) contd -SELECTmodel FROM(SELECT model,priceFROMPC UNIONSELECTmodel,priceFROMLaptop UNIONSELECTmodel,priceFROMPrinter) M1WHEREM1.price IN(SELECT MAX(price) FROM(SELECT price

29、 FROMPCUNIONSELECTprice FROMLaptopUNIONSELECTprice FROMPrinter) M2) ;e)SELECTR.maker FROMProduct R,Printer TWHERER.model=T.model AND T.price = ALL (SELECT P1.speed FROMProduct R1,PC P1WHERER1.model=P1.model AND P1.ram IN(SELECT MIN(ram) FROMPC);SELECTR1.maker FROMProduct R1,PC P1WHERER1.model=P1.mod

30、el AND P1.ram=(SELECT MIN(ram) FROMPC)AND P1.speed IN(SELECT MAX(P1.speed)FROMProduct R1, PC P1WHERER1.model=P1.model AND P1.ram IN(SELECT MIN(ram) FROMPC);6.3.2a)SELECTC.country FROMClasses C WHEREnumGuns IN(SELECT MAX(numGuns) FROMClasses);SELECTC.country FROMClasses C WHEREnumGuns = ALL(SELECT nu

31、mGuns FROMClasses);b)SELECTDISTINCTC.classFROMClassesC,Ships SWHEREC.class= S.classAND EXISTS(SELECT shipFROMOutcomes O WHEREO.result=sunkAND O.ship= S.name) ;SELECT DISTINCT C.classFROMClasses C, Ships SWHEREC.class = S.class AND S.name IN(SELECT shipFROMOutcomes O WHEREO.result=sunk) ;c)SELECTS.na

32、me FROMShips S WHERES.class IN(SELECT class FROMClasses CWHEREbore=16) ;SELECTS.name FROMShips SWHEREEXISTS(SELECT class FROMClasses CWHEREbore=16AND C.class = S.class);d)SELECTO.battle FROMOutcomes O WHEREO.ship IN(SELECT name FROMShips SWHERES.Class =Kongo);SELECTO.battle FROMOutcomes OWHEREEXISTS

33、(SELECT name FROMShips SWHERES.Class =Kongo AND S.name= O.ship);e)SELECTS.name FROMShips S,Classes CWHERES.Class= C.Class AND numGuns = ALL(SELECT numGuns FROMShips S2,Classes C2WHERES2.Class = C2.Class AND C2.bore= C.bore) ;SELECTS.name FROMShips S,Classes CWHERES.Class= C.Class AND numGuns IN(SELE

34、CT MAX(numGuns) FROMShips S2,Classes C2WHERES2.Class = C2.Class AND C2.bore= C.bore) ;Better answer; SELECTS.name FROMShips S,Classes CWHERES.Class= C.Class AND numGuns = ALL(SELECT numGuns FROMClasses C2WHEREC2.bore = C.bore) ;SELECTS.name FROMShips S,Classes CWHERES.Class= C.Class AND numGuns IN(S

35、ELECT MAX(numGuns) FROMClasses C2WHEREC2.bore = C.bore) ;6.3.3SELECTtitle FROMMovies GROUP BY titleHAVING COUNT(title) 1 ;6.3.4SELECTS.name FROMShips S,Classes CWHERES.Class = C.Class ;Assumption: In R1 join R2, the rows of R2 are unique on the joining columns.SELECTCOLL12,COLL13, COLL14FROMR1WHERECOLL12 IN(SELECT COL22 FROMR2)AND COLL13 IN(SELECT COL33 FROMR3)AND COLL14 IN(SELECT COL44 FROMR4) .6.3.5(a)SELECTS.name,S.addressFROMMovieStar S, MovieExec EWHERES.gender=FAND E.netWorth 10000000 AND S.name= E.nameAND S.address= E.address ;