數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程第6章課后習(xí)題答案

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 RAMSCREENrecord(s) selected.e)SELECT*FROMPrinterWHEREcolor ;MODEL CASETYPEPRICE3001TRUEink-jet993003TRUElaser9993004TRUEink-jet1203006TRUEink-jet1003007TR

5、UElaser200record(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 be

6、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, priceFROM

7、Printer 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) select

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

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

10、eve 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 incorrec

11、tly 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 both (a=1

13、0) 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), the ex

14、pression 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 gigahertzFROMPr

15、oduct 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 R.model

16、= 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 G2 reco

17、rd(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.model=R.mod

18、el 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 Carolina Wash

19、ington 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 engaged i

20、n 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 Kongo M

21、issouri 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 C2WHEREC1

22、.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 B1.date 3

23、;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 qualified with

24、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 R1A1, R2

25、A2,.,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 list of att

26、ributes 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 PrinterP1.

27、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 UNIONSELECTmodel,

28、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 FROMPCU

29、NIONSELECTprice 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.model AND P

30、1.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 numGuns FR

31、OMClasses);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.name FROMS

32、hips 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(SELECT

33、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(SELECT MAX(n

34、umGuns) 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(SELECT MA

35、X(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

36、(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 ;Note: As mentioned previously in the book, the names of stars are un

37、ique. However no such restriction exists for executives. Thus, both name and address are required as join columns.Alternate solution:SELECTname,addressFROMMovieStarWHEREgender= FAND (name, address) IN (SELECT name,addressFROMMovieExecWHEREnetWorth 10000000) ;(b)SELECTname,addressFROMMovieStarWHERE (

38、name,address) NOT IN (SELECT name address FROMMovieExec) ;6.3.6By replacing the column in subquery with a constant and using IN subquery for the constant, statement equivalent to EXISTS can be found.i.e. replace WHERE EXISTS (SELECT C1 FROM R1.) by WHERE 1 IN (SELECT 1 FROM R1.)Example:SELECT DISTIN

39、CT R.makerFROMProduct RWHEREEXISTS(SELECT P.model FROMPC PWHEREP.speed = 3.0AND P.model=R.model) ;Above statement can be transformed to below statement. SELECT DISTINCT R.makerFROMProduct RWHERE1 IN(SELECT 1 FROMPC PWHEREP.speed = 3.0AND P.model=R.model) ;6.3.7(a)n*m tuples are returned where there

40、are n studios and m executives. Each studio will appear m times; once for every exec.(b)There are no common attributes between StarsIn and MovieStar; hence no tuples are returned.(c)There will be at least one tuple corresponding to each star in MovieStar. The unemployed stars will appear once with n

41、ull values for StarsIn. All employed stars will appear as many times as the number of movies they are working in. In other words, for each tuple in StarsIn(starName), the correspoding tuple from MovieStar(name) is joined and returned. For tuples in MovieStar that do not have a corresponding entry in

42、 StarsIn, the MovieStar tuple is returned with null values for StarsIn columns.6.3.8Since model numbers are unique, a full natural outer join of PC, Laptop and Printer will return one row for each model. We want all information about PCs, Laptops and Printers even if the model does not appear in Pro

43、duct but vice versa is not true. Thus a left natural outer join between Product and result above is required. The type attribute from Product must be renamed since Printer has a type attribute as well and the two attributes are different.(SELECT maker,model,type AS productTypeFROMProduct) RIGHT NATU

44、RAL OUTER JOIN (PC FULL NATURAL OUTER JOIN Laptop) FULL NATURALOUTER JOIN Printer);attributes that do not existinone relation,a constant such as NAor0.0canbe used. Below is an exampleofthis approachusing PC and Laptop.Alternately, the Product relation can be joined individually with each of PC,Lapto

45、p and Printer and the three results can be Unioned together. ForSELECTR.MAKER,R.MODEL,R.TYPE,P.SPEED,P.RAM,P.HD,0.0 AS SCREEN,P.PRICEFROMPRODUCTR,WHEREPC P R.MODEL= P.MODELUNIONSELECTR.MAKER,R.MODEL,R.TYPE,L.SPEED,L.RAM,L.HD,L.SCREEN,L.PRICEFROMPRODUCT R, LAPTOP LWHERER.MODEL = L.MODEL;6.3.9SELECT*F

46、ROMClasses RIGHT NATURAL OUTER JOIN Ships ;6.3.10SELECT*FROMClasses RIGHT NATURAL OUTER JOIN ShipsUNION(SELECT C2.class,C2.type,C2.country,C2.numguns,C2.bore,C2.displacement, C2.class NAME , 0FROMClasses C2, Ships S2WHEREC2.Class NOT IN(SELECT Class FROMShips) ;6.3.11(a)SELECT*FROMR, S ;(b)Let Attr

47、consist ofAttrR = attributes unique to R AttrS = attributes unique to SAttrU = attributes common to R and SThus in Attr, attributes common to R and S are not repeated.SELECTAttr FROMR,SWHERER.AttrU1 = S.AttrU1 AND R.AttrU2 = S.AttrU2 .AND R.AttrUi = S.AttrUi ;(c) SELECT*FROMR, SWHEREC ;6.4.1(a)DISTI

48、NCT keyword is not required here since each model only occurs once in PC relation.SELECTmodel FROMPCWHEREspeed = 3.0 ;(b)SELECTDISTINCTR.makerFROMProductR,Laptop LWHERER.model= L.modelANDL.hd 100 ;(c) SELECTR.model,FROMP.priceProductR,WHEREPC PR.model= P.modelANDR.maker= BUNIONSELECTFROMR.model, L.p

49、riceProductR,Laptop LWHERER.model= L.modelANDR.maker= BUNIONSELECTFROMR.model, T.priceProductR,PrinterTWHERER.model= T.modelANDR.maker= B ;(d)SELECTmodel FROMPrinterWHEREcolor=TRUEAND type =laser ;(e)SELECT DISTINCT R.makerFROMProduct R, Laptop LWHERER.model= L.model AND R.maker NOT IN(SELECT R1.mak

50、er FROMProduct R1,PC PWHERER1.model = P.model) ;better:SELECT DISTINCT R.makerFROMProduct RWHERER.type= laptopAND R.maker NOT IN (SELECT R.maker FROMProduct RWHERER.type = pc) ;(f)With GROUP BY hd, DISTINCT keyword is not required.SELECThdFROMPC GROUP BY hdHAVING COUNT(hd) 1 ;(g)SELECTP1.model,P2.mo

51、delFROMPC P1,PC P2WHEREP1.speed =P2.speedANDP1.ram=P2.ramANDP1.model = 2.8)OR R.model IN (SELECT L.model FROMLaptop LWHEREL.speed = 2.8)GROUP BY R.makerHAVING COUNT(R.model) 1 ;(i)After finding the maximum speed, an IN subquery can provide the manufacturer name.SELECTMAX(M.speed) FROM(SELECT speed F

52、ROMPCUNIONSELECTspeed FROMLaptop) M ;SELECTR.maker FROMProduct R,PC PWHERER.model= P.model AND P.speed IN(SELECT MAX(M.speed) FROM)UNION(SELECT speed FROMPCUNIONSELECTspeed FROMLaptop) MSELECTR2.maker FROMProduct R2,Laptop LWHERER2.model = L.model AND L.speed IN(SELECT MAX(N.speed)FROM) ;Alternately

53、,(SELECT speed FROMPCUNIONSELECTspeed FROMLaptop) NSELECTCOALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROMPC P2FULL OUTER JOIN Laptop L2ONP2.speed = L2.speed ; SELECTR.makerFROMProduct R, PC PWHERER.model= P.model AND P.speed IN(SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROMPC P2FULL OUT

54、ER JOIN Laptop L2ONP2.speed = L2.speed)UNIONSELECTR2.maker FROMProduct R2,Laptop LWHERER2.model = L.model AND L.speed IN(SELECT COALESCE(MAX(P2.speed),MAX(L2.speed),0) SPEED FROMPC P2FULL OUTER JOIN Laptop L2ONP2.speed = L2.speed)(j)SELECTR.maker FROMProduct R,PC PWHERER.model = P.model GROUP BY R.m

55、akerHAVING COUNT(DISTINCT speed) = 3 ;(k)SELECTR.maker FROMProduct R,PC PWHERER.model = P.model GROUP BY R.makerHAVING COUNT(R.model) = 3 ;better;SELECTR.maker FROMProduct R WHERER.type=pc GROUP BY R.makerHAVING COUNT(R.model) = 3 ;6.4.2(a)We can assume that class is unique in Classes and DISTINCT k

56、eyword is not required.SELECTclass,countryFROMClasses WHEREbore = 16 ;(b)Ship names are not unique (In absence of hull codes, year of launch can help distinguish ships).SELECT DISTINCT name AS Ship_Name FROMShipsWHERElaunched 35000 ;(e)SELECT DISTINCT O.ship AS Ship_Name, C.displacement, C.numGunsFR

57、OM Classes C , Outcomes O, Ships SWHERE C.class = S.class AND S.name = O.shipAND O.battle = Guadalcanal ;SHIP_NAMEDISPLACEMENTNUMGUNSKirishima320008Washington3700092 record(s)selected.Note: South Dakota was also in Guadalcanal but its class information is not available. Below query will return name

58、of all ships that were in Guadalcanal even if no other information is available (shown as NULL). The above query is modified from INNER joins to LEFT OUTER joins.SELECT DISTINCT O.ship AS Ship_Name, C.displacement, C.numGunsFROMOutcomes OLEFT JOIN Ships SONS.name = O.ship LEFT JOIN Classes CONC.clas

59、s = S.classWHEREO.battle= Guadalcanal ;SHIP_NAMEDISPLACEMENTNUMGUNSKirishima South Dakota Washington32000- 370008- 93 record(s)selected.(f)The Set opearator UNION guarantees unique results. SELECTship AS Ship_NameFROMOutcomes UNIONSELECTname AS Ship_Name FROMShips ;(g)SELECTC.class FROMClasses C,Shi

60、ps SWHEREC.class = S.class GROUP BY C.classHAVING COUNT(S.name) = 1 ;better:SELECTS.class FROMShips S GROUP BY S.classHAVING COUNT(S.name) = 1 ;(h)The Set opearator INTERSECT guarantees unique results. SELECTC.countryFROMClasses C WHEREC.type=bbINTERSECTSELECTC2.country FROMClasses C2 WHEREC2.type=b