SQL Statements and Data for OracleWord格式文档下载.docx
- 文档编号:18235180
- 上传时间:2022-12-14
- 格式:DOCX
- 页数:51
- 大小:22.46KB
SQL Statements and Data for OracleWord格式文档下载.docx
《SQL Statements and Data for OracleWord格式文档下载.docx》由会员分享,可在线阅读,更多相关《SQL Statements and Data for OracleWord格式文档下载.docx(51页珍藏版)》请在冰豆网上搜索。
Thesetupscriptsinchapters2,6,17,and18havebeenseparatedintomultipleparts.Thisoccursbecausethosechaptershavesituationswheredataismodifiedinsomeway,requiringadditionalsetupscriptsforsubsequentSQLstatmentstoworkcorrectly.
Allofthestatementsinasetupscriptcanbeexecutedallatonce.However,ifyoushouldencounteranyproblemsinexecutingasetupscript,tryexecutingthestatementsoneatatime.Inmostcases,thatwillsolveanyproblemsyouencounter.
TherearenoscriptsorSQLstatementsforchapters1,16,19or20.
Thefollowingisalistofchapters.ClickonanyoftheselinkstogoimmediatelytotheSQLforthatchapter.
Chapter2
Chapter3
Chapter4
Chapter5
Chapter6
Chapter7
Chapter8
Chapter9
Chapter10
Chapter11
Chapter12
Chapter13
Chapter14
Chapter15
Chapter16
Chapter17
Chapter18
SetupScript-Part1:
DROPTABLECustomers;
CREATETABLECustomers
(CustomerIDINTNOTNULL,
FirstNameVARCHAR2(45)NULL,
LastNameVARCHAR2(45)NULL);
INSERTINTOCustomers(CustomerID,FirstName,LastName)
VALUES(1,'
William'
'
Smith'
);
VALUES(2,'
Natalie'
Lopez'
VALUES(3,'
Brenda'
Harper'
StatementsfromBook:
SELECT*FROMCustomers;
SELECT*
FROMCustomers;
SELECTLastName
SELECT
FirstName,
LastName
SetupScript-Part2:
("
LastName"
VARCHAR2(45)NULL);
INSERTINTOCustomers("
)
VALUES('
"
SetupScript:
DROPTABLEOrders;
CREATETABLEOrders
(OrderIDINTNOTNULL,
LastNameVARCHAR2(45)NULL,
QuantityPurchasedINTNULL,
PricePerItemFLOATNULL);
INSERTINTOOrders(OrderID,FirstName,LastName,QuantityPurchased,PricePerItem)
4,2.5);
10,1.25);
5,4);
DROPTABLEOrders123;
CREATETABLEOrders123
(LastNameVARCHAR2(45)NULL);
INSERTINTOOrders123(LastName)
SELECT
'
FirstName:
'
FirstName
FROMOrders;
5,
OrderID,
QuantityPurchased,
PricePerItem,
QuantityPurchased*PricePerItem
OrderID,
FirstName,
LastName,
FirstName||'
||LastName
||LastNameAS"
Name"
FROMOrders123Orders;
Orders.LastName
DROPTABLEtable1;
CREATETABLEtable1
(PresidentVARCHAR2(20)NULL);
INSERTINTOtable1(President)
GeorgeWashington'
INSERTINTOOrders(OrderID,FirstName,LastName,QuantityPurchased,PricePerItem)
DROPTABLEProducts;
CREATETABLEProducts
(ProductIDINTNOTNULL,
DescriptionVARCHAR2(45)NULL,
ColorVARCHAR2(45)NULL);
INSERTINTOProducts(ProductID,Description,Color)
ChairA'
Red'
INSERTINTOProducts(ProductID,Description)
ChairB'
LampC'
Green'
NOTE:
StatementsinvolvingtheLEFTandRIGHTfunctionsarenotavailableinOracle.
SUBSTR('
thewhitegoat'
4,5)AS"
TheAnswer"
FROMDUAL;
LTRIM('
theapple'
)AS"
CONCAT(CONCAT(FirstName,'
),LastName)AS"
UPPER('
AbrahamLincoln'
ConverttoUppercase"
LOWER('
ABRAHAMLINCOLN'
ConverttoLowercase"
SUBSTR(RTRIM(President),-10,10)AS"
FROMtable1;
GeorgeWashington'
-10,10)
SELECTCURRENT_DATE
SELECTROUND(712.863,3)
SELECTROUND(712.863,2)
SELECTROUND(712.863,1)
SELECTROUND(712.863,0)
SELECTROUND(712.863,-1)
SELECTROUND(712.863,-2)
ThestatementsinvolvingtheRANDandPIfunctionsarenotavailableinOracle.
2009-04-11'
AS"
OriginalDate"
CAST('
11-APR-2009'
ASDATE)AS"
ConvertedDate"
Description,
Color
FROMProducts;
Description,
NVL(Color,'
Unknown'
Color"
INSERTINTOCustomers(CustomerID,FirstName,LastName)VALUES(1,'
INSERTINTOCustomers(CustomerID,FirstName,LastName)VALUES(2,'
Janet'
INSERTINTOCustomers(CustomerID,FirstName,LastName)VALUES(3,'
INSERTINTOCustomers(CustomerID,FirstName,LastName)VALUES(4,'
(TableIDINTNOTNULL,
CharacterDataVARCHAR2(45)NULL,
NumericDataINTNULL);
INSERTINTOtable1(TableID,CharacterData,NumericData)VALUES(1,'
23'
23);
INSERTINTOtable1(TableID,CharacterData,NumericData)VALUES(2,'
5'
5);
INSERTINTOtable1(TableID,CharacterData)VALUES(3,'
Dog'
INSERTINTOtable1(TableID,NumericData)VALUES(4,-6);
FROMCustomers
ORDERBYLastName;
ORDERBYFirstName;
ORDERBYFirstNameASC;
ORDERBYFirstNameDESC;
ORDERBYLastName,FirstName;
CONCAT(CONCAT(LastName,'
),FirstName)AS"
ORDERBY"
;
ORDERBYCONCAT(LastName,FirstName);
SELECTNumericData
FROMtable1
ORDERBYNumericDataNULLSFIRST;
NVL(NumericData,0)
ORDERBYNVL(NumericData,0);
CharacterData
ORDERBYCharacterDataNULLSFIRST;
CategoryCodeVARCHAR2(45)NULL,
ProductDescriptionVARCHAR2(45)NULL);
INSERTINTOProducts(ProductID,CategoryCode,ProductDescription)VALUES(1,'
F'
Apple'
INSERTINTOProducts(ProductID,CategoryCode,ProductDescription)VALUES(2,'
Orange'
INSERTINTOProducts(ProductID,CategoryCode,ProductDescription)VALUES(3,'
S'
Mustard'
INSERTINTOProducts(ProductID,CategoryCode,ProductDescription)VALUES(4,'
V'
Carrot'
CASECategoryCode
WHEN'
THEN'
Fruit'
Vegetable'
ELSE'
Other'
ENDAS"
Category"
ProductDescriptionAS"
Description"
CASE
WHENCategoryCode='
FruitVARCHAR2(45)NULL,
VegetableVARCHAR2(45)NULL,
SpiceVARCHAR2(45)NULL,
INSERTINTOProducts(ProductID,Fruit,Vegetable,Spi
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Statements and Data for Oracle