数据库系统基础教程第6章课后习题答案.docx
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 LIKE '%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 movieTitle = 'Love'd)SELECTname AS Exec_Name FROMMovieExecWHEREnetWorth >= 10000000;e)SELECTname AS Star_Name FROMmovieStarWHEREgender = 'M'OR address LIKE '% Malibu %'6.1.3a)SELECTmodel,speed, hdFROMPCWHEREprice < 1000 ;MODELSPEEDHD10022.1025010031.428010042.8025010053.2025010072.2020010082.2025010092.0025010102.8030010111.8616010122.8016010133.068011 record(s) selected.b)SELECTmodel,FROMspeed hdPCAS gigahertz, AS gigabytesWHEREprice< 1000 ;MODEL GIGAHERTZGIGABYTES10022.1025010031.428010042.8025010053.2025010072.2020010082.2025010092.0025010102.8030010111.8616010122.8016010133.068011 record(s) selected.c)SELECTmaker FROMProductWHERETYPE = 'printer' ; MAKERD D E E E H H7 record(s) selected.d)SELECTmodel,ram, screenFROMLaptop2001204820.12005102417.02006204815.42010204815.4WHEREprice > 1500 ; MODEL RAMSCREEN4 record(s) selected.e)SELECT*FROMPrinterWHEREcolor ;MODEL CASETYPEPRICE3001TRUEink-jet993003TRUElaser9993004TRUEink-jet1203006TRUEink-jet1003007TRUElaser2005 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 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 'ERROR'END CASE,type, priceFROMPrinter WHEREcolor = 1;f)SELECTmodel,hdFROMPCWHEREspeed = 3.2 AND price < 2000;MODEL HD 100525010063202 record(s) selected.6.1.4a) SELECTclass, countryFROMClassesWHEREnumGuns>=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) selected.c)SELECTship AS shipName,battleFROMOutcomesWHEREresult = 'sunk' ; SHIPNAMEBATTLEArizonaPearl HarborBismarkDenmark StraitFusoSurigao StraitHoodDenmark StraitKirishimaGuadalcanalScharnhorstNorth CapeYamashiroSurigao Strait7 record(s) selected.d)SELECTname AS shipName FROMShipsWHEREname = class ; 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 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 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 '_% _%_%' ;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=10) and (b=20) are TRUE. a = 10b = 20a = 10 AND b = 20TRUETRUETRUEc)The expression is always TRUE unless a is NULL. a < 10a >= 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 expression is TRUE when a<=b except when the values are NULL.aba <= bNOT NULLNOT NULLTRUE whena<=b;elseFALSE6.1.6SELECT*FROMMoviesWHERELENGTH IS NOT NULL;6.2.1a)SELECTM.name AS starName FROMMovieStar M,StarsIn SWHEREM.name= S.starName AND S.movieTitle = 'Titanic' AND M.gender= 'M'b)SELECTS.starName FROMMovies M , StarsIn S,Studios TWHERET.name='MGM'AND M.year= 1995AND M.title= S.movieTitle AND M.studioName = T.name;c)SELECTX.name ASpresidentNameFROMMovieExecX,WHEREStudio TX.cert# =T.presC#ANDT.name='MGM'd)SELECTM1.title FROMMovies M1,Movies M2WHEREM1.length > 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 gigahertzFROMProduct 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= 'B'ANDR.model= P.modelUNIONSELECTFROMR.model, L.price ProductR,Laptop L WHERER.maker = 'B'AND R.model = L.model UNIONSELECTR.model,T.priceFROMProduct R, Printer TWHERER.maker = 'B'100464910056301006104920071429AND 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 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 < P2.model ;MODEL MODEL 100410121 record(s) selected.f)SELECTM.maker FROM(SELECT maker,R.modelFROMPC P,Product R WHERESPEED >= 3.0AND P.model=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.displacement>35000;NAMEIowa Missouri Musashi New JerseyNorth Carolina 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 engaged in battle of Guadalcanal but not chosen since it is not in Ships table(Hence, no information regarding it's Class is available).c)SELECTname shipName FROMShipsUNIONSELECTship shipName FROMOutcomes ;SHIPNAMEArizona Bismark California Duke of York FusoHaruna Hiei Hood IowaKing George V Kirishima 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 C2WHEREC1.country = C2.country AND C1.type= 'bb'AND 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< B2.date) ;SHIP0 record(s) selected.f)SELECTO.battle FROMOutcomes O,Ships S, Classes CWHEREO.ship= S.name AND S.class = C.classGROUP BY C.country,O.battleHAVING COUNT(O.ship) > 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 qualified 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 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 list 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 PrinterP1.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< ANYP.speed PC PFROMLaptop LWHEREEXISTS(SELECTP.speedFROMPC PWHERE) ;P.speed >= L.speedd)SELECTmodel FROM(SELECT model,priceFROMPC UNIONSELECTmodel,priceFROMLaptop UNIONSELECTmodel,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 FROMPCUNIONSELECTprice FROMLaptopUNIONSELECTprice FROMPrinter) M2) ;e)SELECTR.maker FROMProduct R,Printer TWHERER.model=T.model AND T.price <= ALL(SELECT MIN(price)FROMPrinter);SELECTR.maker FROMProduct R,Printer T1WHERER.model=T1.model AND T1.price IN(SELECT MIN(T2.price)FROMPrinter T2);f)SELECTR1.maker FROMProduct R1,PC P1WHERER1.model=P1.model AND P1.ram IN(SELECT MIN(ram) FROMPC)AND P1.speed >= 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 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 numGuns FROMClasses);b)SELECTDISTINCTC.classFROMClassesC,Ships SWHEREC.class= S.classAND EXISTS(SELECT shipFROMOutcomes O WHEREO.result='sunk'AND 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 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(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(SELECT 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(SELECT 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='F'AND E.netWorth > 10000000 AND S.name= E.nameAND S.address= E.address ;