1、数据库系统基础概论第三版答案SolutionsChapter 66.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 = 19
2、80 OR 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. SELECT starNameFROM StarsInWHERE movieYear = 1980 OR movieTitle LIKE Love % OR movieTitle LIKE % Love % OR m
3、ovieTitle LIKE % Love 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.8
4、0 2501005 3.20 2501007 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) s
5、elected. e)SELECT *FROM 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 implem
6、entation similar to above 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 SMALLI
7、NT , type VARCHAR(8) , 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 M2.length AND M2.title =Gone With t
8、he 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 AND R.model = L.model ;MANUFACTURER GIGAHERTZ- -A 2.00A 2.16A 2.00B 1.83E 2.00E 1.73
9、E 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.modelUNIONSELECT R.model, T.priceFROM Product R, Printer TWHERE R.maker = B AND R.
10、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 ;MAKER-FG 2 record(s) selected. d)SELECT DISTINCT P1.hdFROM PC P1, PC P2WHERE P1.hd
11、=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.model = 3.0 AND P.model=R.model UNION SELECT maker, R.model FROM Laptop L, Product R W
12、HERE speed = 3.0 AND L.model=R.model ) MGROUP BY M.makerHAVING COUNT(M.model) = 2 ;MAKER-B 1 record(s) selected. 6.3.1a)SELECT DISTINCT makerFROM ProductWHERE model IN (SELECT model FROM PC WHERE speed = 3.0 );SELECT DISTINCT R.makerFROM Product RWHERE EXISTS (SELECT P.model FROM PC P WHERE P.speed
13、= 3.0 AND P.model =R.model );b)SELECT P1.modelFROM Printer P1WHERE P1.price = ALL (SELECT P2.price FROM Printer P2 ) ;SELECT P1.modelFROM Printer P1WHERE P1.price IN (SELECT MAX(P2.price) FROM Printer P2 ) ;c)SELECT L.modelFROM Laptop LWHERE L.speed = L.speed ) ;d)SELECT modelFROM (SELECT model, pri
14、ce FROM PC UNION SELECT model, price FROM Laptop UNION SELECT model, price FROM Printer ) M1WHERE M1.price = ALL (SELECT price FROM PC UNION SELECT price FROM Laptop UNION SELECT price FROM Printer ) ;(d) contd -SELECT modelFROM (SELECT model, price FROM PC UNION SELECT model, price FROM Laptop UNIO
15、N SELECT model, price FROM Printer ) M1WHERE M1.price IN (SELECT MAX(price) FROM (SELECT price FROM PC UNION SELECT price FROM Laptop UNION SELECT price FROM Printer ) M2 ) ; e)SELECT R.makerFROM Product R, Printer TWHERE R.model =T.model AND T.price = ALL (SELECT P1.speed FROM Product R1, PC P1 WHE
16、RE R1.model=P1.model AND P1.ram IN (SELECT MIN(ram) FROM PC ) );SELECT R1.makerFROM Product R1, PC P1WHERE R1.model=P1.model AND P1.ram = (SELECT MIN(ram) FROM PC ) AND P1.speed IN (SELECT MAX(P1.speed) FROM Product R1, PC P1 WHERE R1.model=P1.model AND P1.ram IN (SELECT MIN(ram) FROM PC ) );6.3.2a)
17、SELECT C.countryFROM Classes CWHERE numGuns IN (SELECT MAX(numGuns) FROM Classes );SELECT C.countryFROM Classes CWHERE numGuns = ALL (SELECT numGuns FROM Classes );b)SELECT DISTINCT C.classFROM Classes C, Ships SWHERE C.class = S.class AND EXISTS (SELECT ship FROM Outcomes O WHERE O.result=sunk AND
18、O.ship = S.name ) ;SELECT DISTINCT C.classFROM Classes C, Ships SWHERE C.class = S.class AND S.name IN (SELECT ship FROM Outcomes O WHERE O.result=sunk ) ;c)SELECT S.nameFROM Ships SWHERE S.class IN (SELECT class FROM Classes C WHERE bore=16 ) ;SELECT S.nameFROM Ships SWHERE EXISTS (SELECT class FRO
19、M Classes C WHERE bore =16 AND C.class = S.class );d)SELECT O.battleFROM Outcomes OWHERE O.ship IN (SELECT name FROM Ships S WHERE S.Class =Kongo );SELECT O.battleFROM Outcomes OWHERE EXISTS (SELECT name FROM Ships S WHERE S.Class =Kongo AND S.name = O.ship );e)SELECT S.nameFROM Ships S, Classes CWH
20、ERE S.Class = C.Class AND numGuns = ALL (SELECT numGuns FROM Ships S2, Classes C2 WHERE S2.Class = C2.Class AND C2.bore = C.bore ) ;SELECT S.nameFROM Ships S, Classes CWHERE S.Class = C.Class AND numGuns IN (SELECT MAX(numGuns) FROM Ships S2, Classes C2 WHERE S2.Class = C2.Class AND C2.bore = C.bore
21、 ) ;Better answer;SELECT S.nameFROM Ships S, Classes CWHERE S.Class = C.Class AND numGuns = ALL (SELECT numGuns FROM Classes C2 WHERE C2.bore = C.bore ) ;SELECT S.nameFROM Ships S, Classes CWHERE S.Class = C.Class AND numGuns IN (SELECT MAX(numGuns) FROM Classes C2 WHERE C2.bore = C.bore ) ;6.4.6(a)
22、SELECT AVG(speed) AS Avg_SpeedFROM PC ;AVG_SPEED- 2.4846153846153846153846153 1 record(s) selected. (b)SELECT AVG(speed) AS Avg_SpeedFROM LaptopWHERE price 1000 ;AVG_SPEED- 1.9983333333333333333333333 1 record(s) selected. (c)SELECT AVG(P.price) AS Avg_PriceFROM Product R, PC PWHERE R.model=P.model AND R.maker=A ;AVG_PRICE- 1195 1 record(s) selected.
