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