数据库系统基础概论第三版答案.docx
- 文档编号:28328688
- 上传时间:2023-07-10
- 格式:DOCX
- 页数:24
- 大小:16.96KB
数据库系统基础概论第三版答案.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.model MODELMODEL ---------- 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.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础 概论 第三 答案