1、数据库第六章答案第六章6.1.1Attributes must be separated by commas. Thus here B is an alias of A.6.1.2a)SELECT address AS Studio_AddressFROM StudioWHERE NAME = MGM;b)SELECT birthdate AS Star_BirthdateFROM MovieStarWHERE name = Sandra Bullock;c)SELECT starNameFROM StarsInWHERE movieYear = 1980 OR movieTitle LIKE
2、 %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. SELECT starNameFROM StarsInWHERE movieYear = 1980 OR movieTitle LIKE Love % OR movieTitle LIKE % Love % OR movieTitle LIKE % Love
3、 OR movieTitle = Love;d)SELECT name AS Exec_NameFROM MovieExecWHERE netWorth = 10000000;e)SELECT name AS Star_NameFROM movieStarWHERE gender = M OR address LIKE % Malibu %;6.1.3a)SELECT model, speed, hdFROM PCWHERE price 1000 ;MODEL SPEED HD- - -1002 2.10 2501003 1.42 801004 2.80 2501005 3.20 250100
4、7 2.20 2001008 2.20 2501009 2.00 2501010 2.80 3001011 1.86 1601012 2.80 1601013 3.06 80 11 record(s) selected. b)SELECT model , speed AS gigahertz, hd AS gigabytesFROM PCWHERE price 1500 ;MODEL RAM SCREEN- - -2001 2048 20.12005 1024 17.02006 2048 15.42010 2048 15.4 4 record(s) selected. e)SELECT *FR
5、OM PrinterWHERE color ;MODEL CASE TYPE PRICE- - - -3001 TRUE ink-jet 993003 TRUE laser 9993004 TRUE ink-jet 1203006 TRUE ink-jet 1003007 TRUE laser 200 5 record(s) selected. Note: Implementation of Boolean type is optional in SQL standard (feature ID T031). PostgreSQL has implementation similar to a
6、bove example. Other DBMS provide equivalent support. E.g. In DB2 the 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 ( model CHAR(4) UNIQUE NOT NULL, color SMALLINT , type VARCHAR(8)
7、, price SMALLINT , CONSTRAINT Printer_ISCOLOR CHECK(color IN(0,1) );SELECT model, CASE color WHEN 1 THEN TRUE WHEN 0 THEN FALSE ELSE ERROR END CASE , type, price FROM Printer WHERE color = 1;f)SELECT model, hdFROM PCWHERE speed = 3.2 AND price = 10 ;CLASS COUNTRY- -Tennessee USA 1 record(s) selected
8、. b)SELECT name AS shipNameFROM ShipsWHERE launched 1918 ;SHIPNAME-HarunaHieiKirishimaKongoRamilliesRenownRepulseResolutionRevengeRoyal OakRoyal Sovereign 11 record(s) selected. c)SELECT ship AS shipName, battleFROM OutcomesWHERE result = sunk ;SHIPNAME BATTLE- -Arizona Pearl HarborBismark Denmark S
9、traitFuso Surigao StraitHood Denmark StraitKirishima GuadalcanalScharnhorst North CapeYamashiro Surigao Strait 7 record(s) selected. d)SELECT name AS shipNameFROM ShipsWHERE name = class ; SHIPNAME-IowaKongoNorth CarolinaRenownRevengeYamato 6 record(s) selected. e)SELECT name AS shipNameFROM ShipsWH
10、ERE name LIKE R%;SHIPNAME-RamilliesRenownRepulseResolutionRevengeRoyal OakRoyal Sovereign 7 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 R
11、odney.SELECT name AS shipNameFROM ShipsWHERE name LIKE R%UNIONSELECT ship AS shipNameFROM OutcomesWHERE ship LIKE R%; f) Only using a filter like % % % will incorrectly match name such as a b since % can match any sequence of 0 or more characters.SELECT name AS shipNameFROM ShipsWHERE name LIKE _% _
12、% _% ;SHIPNAME- 0 record(s) selected. Note: As in (e), UNION with results from Outcomes.SELECT name AS shipNameFROM ShipsWHERE name LIKE _% _% _%UNIONSELECT ship AS shipNameFROM OutcomesWHERE ship LIKE _% _% _% ;SHIPNAME-Duke of YorkKing George VPrince of Wales 3 record(s) selected. 6.1.5a)The resul
13、ting expression is false when neither of (a=10) or (b=20) is TRUE. a = 10 b = 20 a = 10 OR b = 20 NULL TRUE TRUE TRUE NULL TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE TRUE TRUE b)The resulting expression is only TRUE when both (a=10) and (b=20) are TRUE. a = 10 b = 20 a = 10 AND b = 20 TRUE TRUE TRUEc
14、)The expression is always TRUE unless a is NULL. a = 10 a = 10 AND b = 20 TRUE FALSE TRUE FALSE TRUE TRUE d)The expression is TRUE when a=b except when the values are NULL. a b a = b NOT NULL NOT NULL TRUE when a=b; else FALSE e)Like in (d), the expression is TRUE when a=b except when the values are
15、 NULL. a b a = b NOT NULL NOT NULL TRUE when a M2.length AND M2.title =Gone With the Wind ; e)SELECT X1.name AS execNameFROM MovieExec X1, MovieExec X2WHERE XWorth XWorth AND X2.name = Merv Griffin ;6.2.2a)SELECT R.maker AS manufacturer, L.speed AS gigahertzFROM Product R, Laptop LWHERE L.hd = 30 AN
16、D R.model = L.model ;MANUFACTURER GIGAHERTZ- -A 2.00A 2.16A 2.00B 1.83E 2.00E 1.73E 1.80F 1.60F 1.60G 2.00 10 record(s) selected. b)SELECT R.model, P.priceFROM Product R, PC PWHERE R.maker = B AND R.model = P.modelUNIONSELECT R.model, L.priceFROM Product R, Laptop LWHERE R.maker = B AND R.model = L.
17、modelUNIONSELECT R.model, T.priceFROM Product R, Printer TWHERE R.maker = B AND R.model = T.model ; MODEL PRICE- -1004 6491005 6301006 10492007 1429 4 record(s) selected. c)SELECT R.makerFROM Product R, Laptop LWHERE R.model = L.modelEXCEPTSELECT R.makerFROM Product R, PC PWHERE R.model = P.model ;M
18、AKER-FG 2 record(s) selected. d)SELECT DISTINCT P1.hdFROM PC P1, PC P2WHERE P1.hd =P2.hd AND P1.model P2.model ;Alternate Answer:SELECT DISTINCT P.hdFROM PC PGROUP BY P.hdHAVING COUNT(P.model) = 2 ; e)SELECT P1.model, P2.modelFROM PC P1, PC P2WHERE P1.speed = P2.speed AND P1.ram = P2.ram AND P1.mode
19、l = 3.0 AND P.model=R.model UNION SELECT maker, R.model FROM Laptop L, Product R WHERE speed = 3.0 AND L.model=R.model ) MGROUP BY M.makerHAVING COUNT(M.model) = 2 ;MAKER-B 1 record(s) selected. 6.2.3a)SELECT S.nameFROM Ships S, Classes CWHERE S.class = C.class AND C.displacement 35000;NAME-IowaMiss
20、ouriMusashiNew JerseyNorth CarolinaWashingtonWisconsinYamato 8 record(s) selected. b)SELECT S.name , C.displacement, C.numGunsFROM Ships S , Outcomes O, Classes CWHERE S.name = O.ship AND S.class = C.class AND O.battle = Guadalcanal ; NAME DISPLACEMENT NUMGUNS- - -Kirishima 32000 8Washington 37000 9
21、 2 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 its Class is available).c)SELECT name shipNameFROM ShipsUNIONSELECT ship shipNameFROM Outcomes ;SHIPNAME-ArizonaBismarkCaliforniaDuke of Yo
22、rkFusoHarunaHieiHoodIowaKing George VKirishimaKongoMissouriMusashiNew JerseyNorth CarolinaPrince of WalesRamilliesRenownRepulseResolutionRevengeRodneyRoyal OakRoyal SovereignScharnhorstSouth DakotaTenneseeTennesseeWashingtonWest VirginiaWisconsinYamashiroYamato 34 record(s) selected.d)SELECT C1.coun
23、tryFROM Classes C1, Classes C2WHERE C1.country = C2.country AND C1.type = bb AND C2.type = bc ;COUNTRY-Gt. BritainJapan 2 record(s) selected. e)SELECT O1.shipFROM Outcomes O1, Battles B1WHERE O1.battle = B1.name AND O1.result = damaged AND EXISTS (SELECT B2.date FROM Outcomes O2, Battles B2 WHERE O2.battle=B2.name AND O1.ship = O2.ship AND B1.date 3;SELECT O.battleFROM Ships S , Classes C, Outcomes OWHERE C.Cla
