数据库系统基础概论第三版答案.docx
《数据库系统基础概论第三版答案.docx》由会员分享,可在线阅读,更多相关《数据库系统基础概论第三版答案.docx(24页珍藏版)》请在冰点文库上搜索。
数据库系统基础概论第三版答案
Solutions
Chapter6
6.1.1
Attributesmustbeseparatedbycommas.ThushereBisanaliasofA.
6.1.2
a)
SELECTaddressASStudio_Address
FROMStudio
WHERENAME='MGM';
b)
SELECTbirthdateASStar_Birthdate
FROMMovieStar
WHEREname='SandraBullock';
c)
SELECTstarName
FROMStarsIn
WHEREmovieYear=1980
ORmovieTitleLIKE'%Love%';
However,abovequerywillalsoreturnwordsthathavethesubstringLovee.g.Lover.BelowquerywillonlyreturnmoviesthathavetitlecontainingthewordLove.
SELECTstarName
FROMStarsIn
WHEREmovieYear=1980
ORmovieTitleLIKE'Love%'
ORmovieTitleLIKE'%Love%'
ORmovieTitleLIKE'%Love'
ORmovieTitle='Love';
d)
SELECTnameASExec_Name
FROMMovieExec
WHEREnetWorth>=10000000;
e)
SELECTnameASStar_Name
FROMmovieStar
WHEREgender='M'
ORaddressLIKE'%Malibu%';
6.1.3
a)
SELECTmodel,
speed,
hd
FROMPC
WHEREprice<1000;
MODELSPEEDHD
---------------------
10022.10250
10031.4280
10042.80250
10053.20250
10072.20200
10082.20250
10092.00250
10102.80300
10111.86160
10122.80160
10133.0680
11record(s)selected.
b)
SELECTmodel,
speedASgigahertz,
hdASgigabytes
FROMPC
WHEREprice<1000;
MODELGIGAHERTZGIGABYTES
------------------------
10022.10250
10031.4280
10042.80250
10053.20250
10072.20200
10082.20250
10092.00250
10102.80300
10111.86160
10122.80160
10133.0680
11record(s)selected.
c)
SELECTmaker
FROMProduct
WHERETYPE='printer';
MAKER
-----
D
D
E
E
E
H
H
7record(s)selected.
d)
SELECTmodel,
ram,
screen
FROMLaptop
WHEREprice>1500;
MODELRAMSCREEN
------------------
2001204820.1
2005102417.0
2006204815.4
2010204815.4
4record(s)selected.
e)
SELECT*
FROMPrinter
WHEREcolor;
MODELCASETYPEPRICE
------------------------
3001TRUEink-jet99
3003TRUElaser999
3004TRUEink-jet120
3006TRUEink-jet100
3007TRUElaser200
5record(s)selected.
Note:
ImplementationofBooleantypeisoptionalinSQLstandard(featureIDT031).PostgreSQLhasimplementationsimilartoaboveexample.OtherDBMSprovideequivalentsupport.E.g.InDB2thecolumntypecanbedeclareasSMALLINTwithCONSTRAINTthatthevaluecanbe0or1.TheresultcanbereturnedasBooleantypeCHARusingCASE.
CREATETABLEPrinter
(
modelCHAR(4)UNIQUENOTNULL,
colorSMALLINT,
typeVARCHAR(8),
priceSMALLINT,
CONSTRAINTPrinter_ISCOLORCHECK(colorIN(0,1))
);
SELECTmodel,
CASEcolor
WHEN1
THEN'TRUE'
WHEN0
THEN'FALSE'
ELSE'ERROR'
ENDCASE,
type,
price
FROMPrinter
WHEREcolor=1;
f)
SELECTmodel,
hd
FROMPC
WHEREspeed=3.2
ANDprice<2000;
MODELHD
-----------
1005250
1006320
2record(s)selected.
6.2.1
a)
SELECTM.nameASstarName
FROMMovieStarM,
StarsInS
WHEREM.name=S.starName
ANDS.movieTitle='Titanic'
ANDM.gender='M';
b)
SELECTS.starName
FROMMoviesM,
StarsInS,
StudiosT
WHERET.name='MGM'
ANDM.year=1995
ANDM.title=S.movieTitle
ANDM.studioName=T.name;
c)
SELECTX.nameASpresidentName
FROMMovieExecX,
StudioT
WHEREX.cert#=T.presC#
ANDT.name='MGM';
d)
SELECTM1.title
FROMMoviesM1,
MoviesM2
WHEREM1.length>M2.length
ANDM2.title='GoneWiththeWind';
e)
SELECTX1.nameASexecName
FROMMovieExecX1,
MovieExecX2
WHEREXWorth>XWorth
ANDX2.name='MervGriffin';
6.2.2
a)
SELECTR.makerASmanufacturer,
L.speedASgigahertz
FROMProductR,
LaptopL
WHEREL.hd>=30
ANDR.model=L.model;
MANUFACTURERGIGAHERTZ
----------------------
A2.00
A2.16
A2.00
B1.83
E2.00
E1.73
E1.80
F1.60
F1.60
G2.00
10record(s)selected.
b)
SELECTR.model,
P.price
FROMProductR,
PCP
WHERER.maker='B'
ANDR.model=P.model
UNION
SELECTR.model,
L.price
FROMProductR,
LaptopL
WHERER.maker='B'
ANDR.model=L.model
UNION
SELECTR.model,
T.price
FROMProductR,
PrinterT
WHERER.maker='B'
ANDR.model=T.model;
MODELPRICE
-----------
1004649
1005630
10061049
20071429
4record(s)selected.
c)
SELECTR.maker
FROMProductR,
LaptopL
WHERER.model=L.model
EXCEPT
SELECTR.maker
FROMProductR,
PCP
WHERER.model=P.model;
MAKER
-----
F
G
2record(s)selected.
d)
SELECTDISTINCTP1.hd
FROMPCP1,
PCP2
WHEREP1.hd=P2.hd
ANDP1.model>P2.model;
AlternateAnswer:
SELECTDISTINCTP.hd
FROMPCP
GROUPBYP.hd
HAVINGCOUNT(P.model)>=2;
e)
SELECTP1.model,
P2.model
FROMPCP1,
PCP2
WHEREP1.speed=P2.speed
ANDP1.ram=P2.ram
ANDP1.modelMODELMODEL
----------
10041012
1record(s)selected.
f)
SELECTM.maker
FROM
(SELECTmaker,
R.model
FROMPCP,
ProductR
WHERESPEED>=3.0
ANDP.model=R.model
UNION
SELECTmaker,
R.model
FROMLaptopL,
ProductR
WHEREspeed>=3.0
ANDL.model=R.model
)M
GROUPBYM.maker
HAVINGCOUNT(M.model)>=2;
MAKER
-----
B
1record(s)selected.
6.3.1
a)
SELECTDISTINCTmaker
FROMProduct
WHEREmodelIN
(SELECTmodel
FROMPC
WHEREspeed>=3.0
);
SELECTDISTINCTR.maker
FROMProductR
WHEREEXISTS
(SELECTP.model
FROMPCP
WHEREP.speed>=3.0
ANDP.model=R.model
);
b)
SELECTP1.model
FROMPrinterP1
WHEREP1.price>=ALL
(SELECTP2.price
FROMPrinterP2
);
SELECTP1.model
FROMPrinterP1
WHEREP1.priceIN
(SELECTMAX(P2.price)
FROMPrinterP2
);
c)
SELECTL.model
FROMLaptopL
WHEREL.speed(SELECTP.speed
FROMPCP
);
SELECTL.model
FROMLaptopL
WHEREEXISTS
(SELECTP.speed
FROMPCP
WHEREP.speed>=L.speed
);
d)
SELECTmodel
FROM
(SELECTmodel,
price
FROMPC
UNION
SELECTmodel,
price
FROMLaptop
UNION
SELECTmodel,
price
FROMPrinter
)M1
WHEREM1.price>=ALL
(SELECTprice
FROMPC
UNION
SELECTprice
FROMLaptop
UNION
SELECTprice
FROMPrinter
);
(d)–contd--
SELECTmodel
FROM
(SELECTmodel,
price
FROMPC
UNION
SELECTmodel,
price
FROMLaptop
UNION
SELECTmodel,
price
FROMPrinter
)M1
WHEREM1.priceIN
(SELECTMAX(price)
FROM
(SELECTprice
FROMPC
UNION
SELECTprice
FROMLaptop
UNION
SELECTprice
FROMPrinter
)M2
);
e)
SELECTR.maker
FROMProductR,
PrinterT
WHERER.model=T.model
ANDT.price<=ALL
(SELECTMIN(price)
FROMPrinter
);
SELECTR.maker
FROMProductR,
PrinterT1
WHERER.model=T1.model
ANDT1.priceIN
(SELECTMIN(T2.price)
FROMPrinterT2
);
f)
SELECTR1.maker
FROMProductR1,
PCP1
WHERER1.model=P1.model
ANDP1.ramIN
(SELECTMIN(ram)
FROMPC
)
ANDP1.speed>=ALL
(SELECTP1.speed
FROMProductR1,
PCP1
WHERER1.model=P1.model
ANDP1.ramIN
(SELECTMIN(ram)
FROMPC
)
);
SELECTR1.maker
FROMProductR1,
PCP1
WHERER1.model=P1.model
ANDP1.ram=
(SELECTMIN(ram)
FROMPC
)
ANDP1.speedIN
(SELECTMAX(P1.speed)
FROMProductR1,
PCP1
WHERER1.model=P1.model
ANDP1.ramIN
(SELECTMIN(ram)
FROMPC
)
);
6.3.2
a)
SELECTC.country
FROMClassesC
WHEREnumGunsIN
(SELECTMAX(numGuns)
FROMClasses
);
SELECTC.country
FROMClassesC
WHEREnumGuns>=ALL
(SELECTnumGuns
FROMClasses
);
b)
SELECTDISTINCTC.class
FROMClassesC,
ShipsS
WHEREC.class=S.class
ANDEXISTS
(SELECTship
FROMOutcomesO
WHEREO.result='sunk'
ANDO.ship=S.name
);
SELECTDISTINCTC.class
FROMClassesC,
ShipsS
WHEREC.class=S.class
ANDS.nameIN
(SELECTship
FROMOutcomesO
WHEREO.result='sunk'
);
c)
SELECTS.name
FROMShipsS
WHERES.classIN
(SELECTclass
FROMClassesC
WHEREbore=16
);
SELECTS.name
FROMShipsS
WHEREEXISTS
(SELECTclass
FROMClassesC
WHEREbore=16
ANDC.class=S.class
);
d)
SELECTO.battle
FROMOutcomesO
WHEREO.shipIN
(SELECTname
FROMShipsS
WHERES.Class='Kongo'
);
SELECTO.battle
FROMOutcomesO
WHEREEXISTS
(SELECTname
FROMShipsS
WHERES.Class='Kongo'
ANDS.name=O.ship
);
e)
SELECTS.name
FROMShipsS,
ClassesC
WHERES.Class=C.Class
ANDnumGuns>=ALL
(SELECTnumGuns
FROMShipsS2,
ClassesC2
WHERES2.Class=C2.Class
ANDC2.bore=C.bore
);
SELECTS.name
FROMShipsS,
ClassesC
WHERES.Class=C.Class
ANDnumGunsIN
(SELECTMAX(numGuns)
FROMShipsS2,
ClassesC2
WHERES2.Class=C2.Class
ANDC2.bore=C.bore
);
Betteranswer;
SELECTS.name
FROMShipsS,
ClassesC
WHERES.Class=C.Class
ANDnumGuns>=ALL
(SELECTnumGuns
FROMClassesC2
WHEREC2.bore=C.bore
);
SELECTS.name
FROMShipsS,
ClassesC
WHERES.Class=C.Class
ANDnumGunsIN
(SELECTMAX(numGuns)
FROMClassesC2
WHEREC2.bore=C.bore
);
6.4.6
(a)
SELECTAVG(speed)ASAvg_Speed
FROMPC;
AVG_SPEED
---------------------------------
2.4846153846153846153846153
1record(s)selected.
(b)
SELECTAVG(speed)ASAvg_Speed
FROMLaptop
WHEREprice>1000;
AVG_SPEED
---------------------------------
1.9983333333333333333333333
1record(s)selected.
(c)
SELECTAVG(P.price)ASAvg_Price
FROMProductR,
PCP
WHERER.model=P.model
ANDR.maker='A';
AVG_PRICE
-----------
1195
1record(s)selected.