ch6Word下载.docx
《ch6Word下载.docx》由会员分享，可在线阅读，更多相关《ch6Word下载.docx（68页珍藏版）》请在冰豆网上搜索。
WHEREmovieYear=1980
ORmovieTitleLIKE'
%Love%'
However,abovequerywillalsoreturnwordsthathavethesubstringLovee.g.Lover.BelowquerywillonlyreturnmoviesthathavetitlecontainingthewordLove.
Love%'
%Love%'
%Love'
ORmovieTitle='
Love'
d）
SELECTnameASExec_Name
FROMMovieExec
WHEREnetWorth>
=10000000;
e）
SELECTnameASStar_Name
FROMmovieStar
WHEREgender='
M'
ORaddressLIKE'
%Malibu%'
6.1.3
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
NULLTRUETR
升级会员 