ch6.docx
- 文档编号:3193585
- 上传时间:2022-11-20
- 格式:DOCX
- 页数:68
- 大小:28.48KB
ch6.docx
《ch6.docx》由会员分享,可在线阅读,更多相关《ch6.docx(68页珍藏版)》请在冰豆网上搜索。
ch6
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.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
NULLTRUETR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ch6