数据库第六章答案.docx
《数据库第六章答案.docx》由会员分享，可在线阅读，更多相关《数据库第六章答案.docx（71页珍藏版）》请在冰豆网上搜索。
数据库第六章答案
第六章
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.1.4
a）
SELECTclass,
country
FROMClasses
WHEREnumGuns>=10;
CLASSCOUNTRY
------------------------------
TennesseeUSA
1record（s）selected.
b）
SELECTnameASshipName
FROMShips
WHERElaunched<1918;
SHIPNAME
------------------
Haruna
Hiei
Kirishima
Kongo
Ramillies
Renown
Repulse
Resolution
Revenge
RoyalOak
RoyalSovereign
11record（s）selected.
c）
SELECTshipASshipName,
battle
FROMOutcomes
WHEREresult='sunk';
SHIPNAMEBATTLE
------------------------------------
ArizonaPearlHarbor
BismarkDenmarkStrait
FusoSurigaoStrait
HoodDenmarkStrait
KirishimaGuadalcanal
ScharnhorstNorthCape
YamashiroSurigaoStrait
7record（s）selected.
d）
SELECTnameASshipName
FROMShips
WHEREname=class;
SHIPNAME
------------------
Iowa
Kongo
NorthCarolina
Renown
Revenge
Yamato
6record（s）selected.
e）
SELECTnameASshipName
FROMShips
WHEREnameLIKE'R%';
SHIPNAME
------------------
Ramillies
Renown
Repulse
Resolution
Revenge
RoyalOak
RoyalSovereign
7record（s）selected.
Note:
Asmentionedinexercise2.4.3,therearesomedanglingpointersandtoretrieveallshipsaUNIONofShipsandOutcomesisrequired.
Belowqueryreturns8rowsincludingshipnamedRodney.
SELECTnameASshipName
FROMShips
WHEREnameLIKE'R%'
UNION
SELECTshipASshipName
FROMOutcomes
WHEREshipLIKE'R%';
f）Onlyusingafilterlike'%%%'willincorrectlymatchnamesuchas'ab'
since%canmatchanysequenceof0ormorecharacters.
SELECTnameASshipName
FROMShips
WHEREnameLIKE'_%_%_%';
SHIPNAME
------------------
0record（s）selected.
Note:
Asin（e）,UNIONwithresultsfromOutcomes.
SELECTnameASshipName
FROMShips
WHEREnameLIKE'_%_%_%'
UNION
SELECTshipASshipName
FROMOutcomes
WHEREshipLIKE'_%_%_%';
SHIPNAME
------------------
DukeofYork
KingGeorgeV
PrinceofWales
3record（s）selected.
6.1.5
a）
Theresultingexpressionisfalsewhenneitherof（a=10）or（b=20）isTRUE.
a=10b=20a=10ORb=20
NULLTRUETRUE
TRUENULLTRUE
FALSETRUETRUE
TRUEFALSETRUE
TRUETRUETRUE
b）
TheresultingexpressionisonlyTRUEwhenboth（a=10）and（b=20）areTRUE.
a=10b=20a=10ANDb=20
TRUETRUETRUE
c）
TheexpressionisalwaysTRUEunlessaisNULL.
a<10a>=10a=10ANDb=20
TRUEFALSETRUE
FALSETRUETRUE
d）
TheexpressionisTRUEwhena=bexceptwhenthevaluesareNULL.
aba=b
NOTNULLNOTNULLTRUEwhena=b;elseFALSE
e）
Likein（d）,theexpressionisTRUEwhena<=bexceptwhenthevaluesareNULL.
aba<=b
NOTNULLNOTNULLTRUEwhena<=b;elseFALSE
6.1.6
SELECT*
FROMMovies
WHERELENGTHISNOTNULL;
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.2.3
a）
SELECTS.name
FROMShipsS,
ClassesC
WHERES.class=C.class
ANDC.displacement>35000;
NAME
------------------
Iowa
Missouri
Musashi
NewJersey
NorthCarolina
Washington
Wisconsin
Yamato
8record（s）selected.
b）
SELECTS.name,
C.displacement,
C.numGuns
FROMShipsS,
OutcomesO,
ClassesC
WHERES.name=O.ship
ANDS.class=C.class
ANDO.battle='Guadalcanal';
NAMEDISPLACEMENTNUMGUNS
-------------------------------------
Kirishima320008
Washington370009
2record（s）selected.
Note:
SouthDakotawasalsoengagedinbattleofGuadalcanalbutnotchosensinceitisnotinShipstable（Hence,noinformationregardingit'sClassisavailable）.
c）
SELECTnameshipName
FROMShips
UNION
SELECTshipshipName
FROMOutcomes;
SHIPNAME
------------------
Arizona
Bismark
California
DukeofYork
Fuso
Haruna
Hiei
Hood
Iowa
KingGeorgeV
Kirishima
Kongo
Missouri
Musashi
NewJersey
NorthCarolina
PrinceofWales
Ramillies
Renown
Repulse
Resolution
Revenge
Rodney
RoyalOak
RoyalSovereign
Scharnhorst
SouthDakota
Tennesee
Tennessee
Washington
WestVirginia
Wisconsin
Yamashiro
Yamato
34record（s）selected.
d）
SELECTC1.country
FROMClassesC1,
ClassesC2
WHEREC1.country=C2.country
ANDC1.type='bb'
ANDC2.type='bc';
COUNTRY
------------
Gt.Britain
Japan
2record（s）selected.
e）
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）;
SHIP
------------------
0record（s）selected.
f）
SELECTO.battle
FROMOutcomesO,
ShipsS,
ClassesC
WHEREO.ship=S.name
ANDS.class=C.class
GROUPBYC.country,
O.battle
HAVINGCOUNT（O.ship）>3;
SELECTO.battle
FROMShipsS,
ClassesC,
OutcomesO
WHEREC.Cla
升级会员 