数据库系统基础教程第九章答案.docx
- 文档编号:2354285
- 上传时间:2023-05-03
- 格式:DOCX
- 页数:62
- 大小:63.58KB
数据库系统基础教程第九章答案.docx
《数据库系统基础教程第九章答案.docx》由会员分享,可在线阅读,更多相关《数据库系统基础教程第九章答案.docx(62页珍藏版)》请在冰点文库上搜索。
数据库系统基础教程第九章答案
9.3.1
a)
Inthefollowing,weusemacroNOT_FOUNDasdefinedinthesection.
voidclosestMatchPC(){
EXECSQLBEGINDECLARESECTION;
charmanf,SQLSTATE[6];
inttargetPrice,/*holdspricegivenbyuser*/floattempSpeed,speedOfClosest;chartempModel[4],modelOfClosest[4];inttempPrice,priceOfClosest;
/*fortuplejustreadfromPC&closestpricefoundsofar*/
EXECSQLENDDECLARESECTION;
EXECSQLDECLAREpcCursorCURSORFORSELECTmodel,price,speedFROMPC;
EXECSQLOPENpcCursor;
/*askuserfortargetpriceandreadtheanswerintovariabletargetPrice*/
/*Initially,thefirstPCistheclosesttothetargetprice.
IfPCisempty,wecannotanswerthequestion,andsoabort.*/
EXECSQLFETCHFROMpcCursorINTO:
modelOfClosest,:
priceOfClosest,:
speedOfClosest;
if(NOT_FOUND)/*printmessageandexit*/;
while
(1){
EXECSQLFETCHpcCursorINTO:
tempModel,:
tempPrice,:
tempSpeed;
if(NOT_FOUND)break;if(/*tempPriceclosertotargetPricethanispriceOfClosest*/){
modelOfClosest=tempModel;priceOfClosest=tempPrice;
speedOfClosest=tempSpeed;
}
}
/*Now,modelOfClosestisthemodelwhosepriceisclosesttotarget.Wemustgetitsmanufacturerwithasingle-rowselect*/
EXECSQLSELECTmaker
INTO:
manfFROMProductWHEREmodel=:
modelOfClosest;
printf("manf=%s,model=%d,speed=%d\n",manf,modelOfClosest,speedOfClosest);
EXECSQLCLOSECURSORpcCursor;
b)
voidacceptableLaptop(){
EXECSQLBEGINDECLARESECTION;
intminRam,minHd,minScreen;/*givenbyuser*/
floatminSpeed;
charmodel[4],maker,
floatspeed;intram,hd,screen,price;
EXECSQLENDDECLARESECTION;
EXECSQLPREPAREquery1FROM
‘SELECTmodel,speed,ram,hd,screen,price,maker
FROMLaptopl,Productp
WHEREspeed>=?
ANDram>=?
ANDhd>=?
ANDscreen>=?
AND
l.model=p.model'
EXECSQLDECLAREcursor1CURSORFORquery1;
/*askuserforminimumspeed,ram,hdsize,andscreensize*/
EXECSQLOPENcursor1USING:
minSpeed,:
minRam,:
minHd,:
minScreen;while(!
NOT_FOUND){
EXECSQLFETCHcursor1INTO
:
model,:
speed,:
ram,:
hd,:
screen,:
price,:
maker;
if(FOUND)
{printf("maker:
%s,model:
%d,\n
speed:
%.2f,ram:
%d,hd:
%d,screen:
%d,price:
%d\n",maker,model,speed,ram,hd,screen,price);
}
}
EXECSQLCLOSECURSORcursor1;
}
c)
voidproductsByMaker(){
EXECSQLBEGINDECLARESECTION;
charmaker,model[4],type[10],color[6];floatspeed;
intram,hd,screen,price;
EXECSQLENDDECLARESECTION;
EXECSQLPREPAREquery1FROM
‘SELECT*FROMPCWHEREmodelIN(SELECTmodelFROMProductWHEREmaker=?
AND
type=‘pc');
EXECSQLPREPAREquery2FROM‘SELECT*FROMLaptop
WHEREmodelIN(SELECTmodelFROMProductWHEREmaker=?
AND
type=‘laptop');
EXECSQLPREPAREquery3FROM
‘SELECT*FROMPrinterWHEREmodelIN(SELECTmodelFROMProductWHEREmaker=?
AND
type=‘printer');
EXECSQLDECLAREcursor1CURSORFORquery1;
EXECSQLDECLAREcursor2CURSORFORquery2;
EXECSQLDECLAREcursor3CURSORFORquery3;
/*askuserformanufacturer*/
Printf(“maker:
%sn”,maker);
/*getPCsmadebythemanufacturer*/
EXECSQLOPENcursor1USING:
maker;
Printf(“producttype:
PCn”);
while(!
NOT_FOUND){
EXECSQLFETCHcursor1INTO:
model,:
speed,:
ram,:
hd,:
price;
if(FOUND)
{
n”model,speed,ram,hd,price);
}
}
/*getLaptopsmadebythemanufacturer*/EXECSQLOPENcursor2USING:
maker;
Printf(“producttype:
Laptopn”);
while(!
NOT_FOUND){
EXECSQLFETCHcursor2INTO
:
model,:
speed,:
ram,:
hd,:
screen,:
price;
if(FOUND)
{printf("model:
%d,speed:
%.2f,ram:
%d,hd:
%d,screen:
%d,price:
%d\n",model,speed,ram,hd,screen,price);
}
/*getPrintersmadebythemanufacturer*/
EXECSQLOPENcursor3USING:
maker;
Printf(“producttype:
Printern”);
while(!
NOT_FOUND){
EXECSQLFETCHcursor3INTO
:
model,:
color,:
type,:
price;
if(FOUND)
{
printf("model:
%d,color:
%s,type:
%s,price:
%d\n",model,color,type,price);
}
}
EXECSQLCLOSECURSORcursor1;
EXECSQLCLOSECURSORcursor2;
EXECSQLCLOSECURSORcursor3;
}
d)
voidwithinBudget(){
EXECSQLBEGINDECLARESECTION;
inttotal_budget,rest_budget,pc_price,printer_price;charpc_model[4],printer_model[4],color[6];floatmin_speed;
EXECSQLENDDECLARESECTION;
EXECSQLPREPAREquery1FROM
‘SELECTmodel,priceFROMPC
WHEREspeed>=?
ANDprice<=?
ORDERBYprice';
EXECSQLPREPAREquery2FROM
‘SELECTmodel,priceFROMPrinter
WHEREprice<=?
ANDcolor=?
ORDERBYprice';
EXECSQLDECLAREcursor1CURSORFORquery1;
EXECSQLDECLAREcursor2CURSORFORquery2;
/*askuserforbudget&theminimumspeedofpc*/
/*getthecheapestPCoftheminimumspeed*/
EXECSQLOPENcursor1USING:
min_speed,:
total_budget;
EXECSQLFETCHcursor1INTO:
pc_model,:
pc_price;
if(NOT_FOUND)
Printf(“nopcfn”);
else
{
Printf(“pcmodel:
%sn”,pc_model);
}
/*getPrinterwithinthebudget*/
rest_budget=total_budget-pc_price;
color=“true”;
EXECSQLOPENcursor2USING:
rest_budget,:
color;
EXECSQLFETCHcursor2INTO:
printer_model;
if(NOT_FOUND){
EXECSQLCLOSECURSORcursor2;
color=“false”;
EXECSQLOPENcursor2USING:
rest_budget,:
color;
if(NOT_FOUND)
);
printf(“noprinterfoundwithinthebudgetelse{
printf(“printermodel:
%s
}
EXECSQLFETCHcursor2INTO:
printer_model;
n”,printer_model);
}
else{
printf(“printermodel:
%s
n”
printer_model);
EXECSQLCLOSECURSORcursor1;
EXECSQLCLOSECURSORcursor2;}
e)voidnewPCproduct(){
EXECSQLBEGINDECLARESECTION;
charpmaker,pmodel[4],ptype[6];
floatpspeed;
intpram,phd,pscreen,pprice;
intpcount;
EXECSQLENDDECLARESECTION;
EXECSQLPREPAREstmt1FROM
‘SELECTCOUNT(*)INTO:
countFROMPC
WHEREMODEL=?
;
EXECSQLPREPAREstmt2FROM
‘INSERTINTOProductVALUES(?
?
?
)
EXECSQLPREPAREstmt3FROM
‘INSERTINTOPCVALUES(?
?
?
?
?
)';
/*askuserformanufacturer,model,speed,RAM,hard-disk,
&priceofanewPC*/
EXECSQLEXECUTEstmt1USING:
pmodel;
IF(count>0)
Printf(“Warnning:
ThePCmodelalreadyexistsn”);
ELSE
{
EXECSQLEXECUTEstmt2USING:
pmaker,:
pmodel,:
ptype;
EXECSQLEXECUTEstmt3UINGNG:
pmodel,:
pspeed,:
pram,
:
phd,:
pprice
}
}
9.3.2
a)
voidlargestFirepower(){
EXECSQLBEGINDECLARESECTION;
charcclass[20],maxFirepowerClass[20];
intcnumGuns,cbore;
floatfirepower,maxFirepower;
EXECSQLENDDECLARESECTION;
EXECSQLDECLAREcursor1CURSORFOR
SELECTclass,numGuns,boreFROMClasses;
EXECSQLOPENcursor1;
EXECSQLFETCHFROMcursor1INTO:
cclass,:
cnumGuns,:
cbore;
if(NOT_FOUND)/*printmessageandexit*/;
maxFirepower=cnumGuns*(power(cbore,3));strcpy(maxFirepowerClass,cclass);
while
(1){
EXECSQLFETCHcursor1INTO:
cclass,:
cnumGuns,:
cbore;
if(NOT_FOUND)break;
firepower=cnumGuns*(power(cbore,3));
if(firepower>maxFirepower)
{maxFirepower=firepower;
strcpy(maxFirepowerClass,cclass);
}
}
printf("Classofmaximumfirepower:
%s\n",maxFirepowerClass);
EXECSQLCLOSECURSORcursor1;
}
b)
voidgetCountry(){
EXECSQLBEGINDECLARESECTION;
charibattle[20],iresult[10],ocountry[20];
charstmt1[200],stmt2[200];
EXECSQLENDDECLARESECTION;
strcpy(stmt1,“SELECTCOUNTRYFROMClassesC
WHEREC.classIN(
SELECTS.classFROMShipsS
WHERES.nameIN(
SELECTshipFROMOutcomes
WHEREbattle=?
))”);
Strcpy(stm2,“SELECTcountryFROMClasses
WHEREclass=(SELECTMAX(COUNT(class))FROMShipss,Outcomeso
?
')”);
WHEREo.name=s.shipAND
s.result=
EXECSQLPREPAREquery1FROMstmt1;
EXECSQLPREPAREquery2FROMstmt2;
EXECSQLDECLAREcursor1CURSORFORquery1;
EXECSQLDECLAREcursor2CURSORFORquery2;
/*askuserforbattle*/
/*getcountriesoftheshipsinvolvedinthebattle*/
EXECSQLOPENcursor1USING:
ibattle;
while(!
NOT_FOUND){
EXECSQLFETCHcursor1INTO:
ocountry;
if(FOUND)
n”,ocoutry);
}
EXECSQLCLOSECURSORcursor1;
/*getthecountrywiththemostshipssunk*/strcpy(iresult,“sunk”);
EXECSQLOPENcursor2USING:
iresult;
/*loopforthecasethere'sthesamemax#ofshipssunk*/
While(!
NOT_FOUND){
EXECSQLFETCHcursor2INTO:
ocountry;
If(FOUND)
Printf(“countrywiththemostshipssunk:
%s,ocountry);
}
/*getthecountrywiththemostshipsdamaged*/strcpy(iresult,“damaged”);
EXECSQLOPENcursor2USING:
iresult;
/*loopforthecasethere'sthesamemax#ofshipsdamaged*/
While(!
NOT_FOUND){
EXECSQLFETCHcursor2INTO:
ocountry;
If(FOUND)Printf(“countrywiththemostshipsdamaged:
%s,ocountry);
}
}
c)
voidaddShips(){
EXECSQLBEGINDECLARESECTION;
chariclass[20],itype[3],icontry[20],iship[20];
intinumGuns,ibore,idisplacement,ilaunched;
charstmt1[100],stmt2[100];
EXECSQLENDDECLARESECTION;
strcpy(stmt1,“INSERTINTOClassesVALUES(?
?
?
?
?
?
)”);
strcpy(stmt2,“INSERTINTOShipsVALUES(?
?
?
)”);
/*askuserforaclassandotherinfoforClassestable*/
EXECSQLEXECUTEIMMEDATE:
stmt1USING:
iclass,:
itype,:
icontry,
:
inumGuns,:
ibore,:
idisplacement;
/*askuserforashipandlaunched*/
WHILE(there_is_input)
{
EXECSQLEXECUTEIMMEDATE:
stmt2USING:
iship,:
iclass,ilaunched;
/*askuserforashipandlaunched*/
}
}
d)
voidfindError(){
EXECSQLBEGINDECLARESECTION;
charbname[20],bdate[8],newbdate[8];
charsname[20],lyear[4],newlyear[4];
charstmt1[100],stmt2[100];
EXECSQLENDDECLARESECTION;
strcpy(stmt1,“UPDATEBattlesSETdate=?
WHEREname=?
”);
strcpy(stmt2,“UPDATEShipsSETlaunched=?
WHEREname=?
”);
EXECSQLDECLAREC1CURSORFOR
Selectb.name,b.date,s.name,s.launched
FROMBattlesb,Outcomeso,Shipss
WHEREb.name=o.battleAND
o.ship=s.nameANDYEAR(b.date) EXECSQLOPENC1; while(! NOT_FOUND){ EXECSQLFETCHC1INTO: bname,: bdate,: sname,: lyear; /*promptuserandaskifachangeisneeded*/
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 基础教程 第九 答案