数据库第六章答案Word下载.docx
《数据库第六章答案Word下载.docx》由会员分享，可在线阅读，更多相关《数据库第六章答案Word下载.docx（71页珍藏版）》请在冰点文库上搜索。
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.
SELECTmodel,
speedASgigahertz,
hdASgigabytes
MODELGIGAHERTZGIGABYTES
------------------------
SELECTmaker
FROMProduct
WHERETYPE='
printer'
;
MAKER
-----
D
E
H
7record（s）selected.
ram,
screen
FROMLaptop
WHEREprice>
1500;
MODELRAMSCREEN
------------------
2001204820.1
2005102417.0
2006204815.4
2010204815.4
4record（s）selected.
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））
）;
CASEcolor
WHEN1
THEN'
TRUE'
WHEN0
FALSE'
ELSE'
ERROR'
ENDCASE,
type,
price
FROMPrinter
WHEREcolor=1;
f）
WHEREspeed=3.2
ANDprice<
2000;
MODELHD
-----------
1005250
1006320
2record（s）selected.
6.1.4
SELECTclass,
country
FROMClasses
WHEREnumGuns>
=10;
CLASSCOUNTRY
------------------------------
TennesseeUSA
1record（s）selected.
SELECTnameASshipName
FROMShips
WHERElaunched<
1918;
SHIPNAME
------------------
Haruna
Hiei
Kirishima
Kongo
Ramillies
Renown
Repulse
Resolution
Revenge
RoyalOak
RoyalSovereign
11record（s）selected.
SELECTshipASshipName,
battle
FROMOutcomes
WHEREresult='
sunk'
SHIPNAMEBATTLE
------------------------------------
ArizonaPearlHarbor
BismarkDenmarkStrait
FusoSurigaoStrait
HoodDenmarkStrait
KirishimaGuadalcanal
ScharnhorstNorthCape
YamashiroSurigaoStrait
WHEREname=class;
Iowa
NorthCarolina
Yamato
6record（s）selected.
WHEREnameLIKE'
R%'
Asmentionedinexercise2.4.3,therearesomedanglingpointersandtoretrieveallshipsaUNIONofShipsandOutcomesisrequired.
Belowqueryreturns8rowsincludingshipnamedRodney.
UNION
SELECTshipASshipName
WHEREshipLIKE'
f）Onlyusingafilterlike'
%%%'
willincorrectlymatchnamesuchas'
ab'
since%canmatchanysequenceof0ormorecharacters.
_%_%_%'
0record（s）selected.
Asin（e）,UNIONwithresultsfromOutcomes.
DukeofYork
KingGeorgeV
PrinceofWales
3record（s）selected.
6.1.5
Theresultingexpressionisfalsewhenneitherof（a=10）or（b=20）isTRUE.
a=10b=20a=10ORb=20
NULLTRUETRUE
TRUENULLTRUE
FALSETRUETRUE
TRUEFALSETRUE
TRUETRUETRUE
TheresultingexpressionisonlyTRUEwhenboth（a=10）and（b=20）areTRUE.
a=10b=20a=10ANDb=20
TheexpressionisalwaysTRUEunlessaisNULL.
a<
10a>
=10a=10ANDb=20
FALSETRUETRUE
TheexpressionisTRUEwhena=bexceptwhenthevaluesareNULL.
aba=b
NOTNULLNOTNULLTRUEwhena=b;
elseFALSE
Likein（d）,theexpressionisTRUEwhena<
=bexceptwhenthevaluesareNULL.
aba<
=b
NOTNULLNOTNULLTRUEwhena<
=b;
6.1.6
FROMMovies
WHERELENGTHISNOTNULL;
6.2.1
SELECTM.nameASstarName
FROMMovieStarM,
StarsInS
WHEREM.name=S.starName
ANDS.movieTitle='
Titanic'
ANDM.gender='
SELECTS.starName
FROMMoviesM,
StarsInS,
StudiosT
WHERET.name='
ANDM.year=1995
ANDM.title=S.movieTitle
ANDM.studioName=T.name;
SELECTX.nameASpresidentName
FROMMovieExecX,
StudioT
WHEREX.cert#=T.presC#
ANDT.name='
SELECTM1.title
FROMMoviesM1,
MoviesM2
WHEREM1.length>
M2.length
ANDM2.title='
GoneWiththeWind'
SELECTX1.nameASexecName
FROMMovieExecX1,
MovieExecX2
WHEREXWorth>
XWorth
ANDX2.name='
MervGriffin'
6.2.2
SELECTR.makerASmanufacturer,
L.speedASgigahertz
FROMProductR,
LaptopL
WHEREL.hd>
=30
ANDR.model=L.model;
MANUFACTURERGIGAHERTZ
----------------------
A2.00
A2.16
B1.83
E2.00
E1.73
E1.80
F1.60
G2.00
10record（s）selected.
SELECTR.model,
P.price
PCP
WHERER.maker='
B'
ANDR.model=P.model
L.price
ANDR.model=L.model
T.price
PrinterT
ANDR.model=T.model;
MODELPRICE
1004649
1005630
10061049
20071429
SELECTR.maker
WHERER.model=L.model
EXCEPT
WHERER.model=P.model;
F
G
SELECTDISTINCTP1.hd
FROMPCP1,
PCP2
WHEREP1.hd=P2.hd
ANDP1.model>
P2.model;
AlternateAnswer:
SELECTDISTINCTP.hd
FROMPCP
GROUPBYP.hd
HAVINGCOUNT（P.model）>
=2;
SELECTP1.model,
P2.model
WHEREP1.speed=P2.speed
ANDP1.ram=P2.ram
ANDP1.model<
MODELMODEL
----------
10041012
SELECTM.maker
FROM
（SELECTmaker,
R.model
FROMPCP,
ProductR
WHERESPEED>
=3.0
ANDP.model=R.model
UNION
SELECTmaker,
FROMLaptopL,
WHEREspeed>
ANDL.model=R.model
）M
GROUPBYM.maker
HAVINGCOUNT（M.model）>
B
6.2.3
SELECTS.name
FROMShipsS,
ClassesC
WHERES.class=C.class
ANDC.displacement>
35000;
NAME
Missouri
Musashi
NewJersey
Washington
Wisconsin
8record（s）selected.
SELECTS.name,
C.displacement,
C.numGuns
FROMShipsS,
OutcomesO,
WHERES.name=O.ship
ANDS.class=C.class
ANDO.battle='
Guadalcanal'
NAMEDISPLACEMENTNUMGUNS
-------------------------------------
Kirishima320008
Washington370009
SouthDakotawasalsoengagedinbattleofGuadalcanalbutnotchosensinceitisnotinShipstable（Hence,noinformationregardingit'
sClassisavailable）.
SELECTnameshipName
SELECTshipshipName
FROMOutcomes;
Arizona
Bismark
California
Fuso
Hood
Rodney
Scharnhorst
SouthDakota
Tennesee
Tennessee
WestVirginia
Yamashiro
34record（s）selected.
SELECTC1.country
FROMClassesC1,
ClassesC2
WHEREC1.country=C2.country
ANDC1.type='
bb'
ANDC2.type='
bc'
COUNTRY
------------
Gt.Britain
Japan
SELECTO1.ship
FROMOutcomesO1,
BattlesB1
WHEREO1.battle=B1.name
ANDO1.result='
damaged'
ANDEXISTS
（SELECTB2.date
FROMOutcomesO2,
BattlesB2
WHEREO2.battle=B2.name
ANDO1.ship=O2.ship
ANDB1.date<
B2.date
）;
SHIP
SELECTO.battle
FROMOutcomesO,
ShipsS,
WHEREO.ship=S.name
GROUPBYC.country,
O.battle
HAVINGCOUNT（O.ship）>
3;
ClassesC,
OutcomesO
WHEREC.Cla
升级会员 