《数据库系统基础教程第6章课后习题答案》由会员分享，可在线阅读，更多相关《数据库系统基础教程第6章课后习题答案（54页珍藏版）》请在金锄头文库上搜索。
1、 Solutions Chapter 6 6.1.1 Attributes must be separated by commas. Thus here B is an alias of A. 6.1.2 a) SELECT address AS Studio_Address FROM Studio WHERE NAME = MGM; b) SELECT birthdate AS Star_Birthdate FROM MovieStar WHERE name = Sandra Bullock; c) SELECT starName FROM StarsIn WHERE movieYear =
2、 1980 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 starName FROM StarsIn WHERE movieYear = 1980 OR movieTitle LIKE Love % OR movieTitle LIKE % Love
3、% OR movieTitle LIKE % Love OR movieTitle = Love; d) SELECT name AS Exec_Name FROM MovieExec WHERE netWorth = 10000000; e) SELECT name AS Star_Name FROM movieStar WHERE gender = M OR address LIKE % Malibu %; 6.1.3 a) SELECT model, speed, hd FROM PC WHERE price 1500 ; MODEL RAM SCREEN - - - 2001 2048
4、 20.1 2005 1024 17.0 2006 2048 15.4 2010 2048 15.4 4 record(s) selected. e) SELECT * FROM Printer WHERE color ; MODEL CASE TYPE PRICE - - - - 3001 TRUE ink-jet 99 3003 TRUE laser 999 3004 TRUE ink-jet 120 3006 TRUE ink-jet 100 3007 TRUE laser 200 5 record(s) selected. Note: Implementation of Boolean
5、 type is optional in SQL standard (feature ID T031). PostgreSQL has implementation 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 usi
6、ng CASE. CREATE TABLE Printer ( model CHAR(4) UNIQUE NOT NULL, color SMALLINT , 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 m
7、odel, hd FROM PC WHERE speed = 3.2 AND price = 10 ; CLASS COUNTRY - - Tennessee USA 1 record(s) selected. b) SELECT name AS shipName FROM Ships WHERE launched = 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
8、 NOT NULL TRUE when a=b; else FALSE e) Like in (d), the expression is TRUE when a M2.length AND M2.title =Gone With the Wind ; e) SELECT X1.name AS execName FROM MovieExec X1, MovieExec X2 WHERE XWorth XWorth AND X2.name = Merv Griffin ; 6.2.2 a) SELECT R.maker AS manufacturer, L.speed AS gigahertz
9、FROM Product R, Laptop L WHERE L.hd = 30 AND R.model = L.model ; MANUFACTURER GIGAHERTZ - - A 2.00 A 2.16 A 2.00 B 1.83 E 2.00 E 1.73 E 1.80 F 1.60 F 1.60 G 2.00 10 record(s) selected. b) SELECT R.model, P.price FROM Product R, PC P WHERE R.maker = B AND R.model = P.model UNION SELECT R.model, L.pri
10、ce FROM Product R, Laptop L WHERE R.maker = B AND R.model = L.model UNION SELECT R.model, T.price FROM Product R, Printer T WHERE R.maker = B AND R.model = T.model ; MODEL PRICE - - 1004 649 1005 630 1006 1049 2007 1429 4 record(s) selected. c) SELECT R.maker FROM Product R, Laptop L WHERE R.model =
11、 L.model EXCEPT SELECT R.maker FROM Product R, PC P WHERE R.model = P.model ; MAKER - F G 2 record(s) selected. d) SELECT DISTINCT P1.hd FROM PC P1, PC P2 WHERE P1.hd =P2.hd AND P1.model P2.model ; Alternate Answer: SELECT DISTINCT P.hd FROM PC P GROUP BY P.hd HAVING COUNT(P.model) = 2 ; e) SELECT P
12、1.model, P2.model FROM PC P1, PC P2 WHERE 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 WHERE speed = 3.0 AND L.model=R.model ) M GROUP BY M.maker HAVING COUNT(M.model) = 2 ; MAKER - B 1 record(s) selected. 6.2.3 a
13、) SELECT S.name FROM Ships S, Classes C WHERE S.class = C.class AND C.displacement 35000; NAME - Iowa Missouri Musashi New Jersey North Carolina Washington Wisconsin Yamato 8 record(s) selected. b) SELECT S.name , C.displacement, C.numGuns FROM Ships S , Outcomes O, Classes C WHERE S.name = O.ship AND S.class = C.class AND O.battle = Guadalcanal ; NAME DISPLACEMENT NUMGUNS - - - Kirishima 32000 8 Washington 37000 9 2 record(s) selected. Note:South Dak
