数据库第三章所有例题参考答案.docx
- 文档编号:12824564
- 上传时间:2023-06-08
- 格式:DOCX
- 页数:12
- 大小:18.01KB
数据库第三章所有例题参考答案.docx
《数据库第三章所有例题参考答案.docx》由会员分享,可在线阅读,更多相关《数据库第三章所有例题参考答案.docx(12页珍藏版)》请在冰点文库上搜索。
数据库第三章所有例题参考答案
11级信管,保密,图档上机考试题目与参考答案
3.3SimpleSelectStatements
1.EXAMPLE3.3.1
findtheaidvaluesandnamesofagentsthatarebasedinNewYork.
selectaid,anamefromagentswherecity=’NewYork’;
2.EXAMPLE3.3.3
Retrieveallpidvaluesofpartsforwhichordersareplaced.
selectdistinctpidfromorders;
3.EXAMPLE3.3.4
retrieveallcustomer-agentnamepairs,(cname,aname),wherethecustomerplacesanorderthroughtheagent.
selectdistinctame,agents.aname
fromcustomers,orders,agents
wherecustomers.cid=orders.cidandorders.aid=agents.aid;
4.EXAMPLE3.3.6
allpairsofcustomersbasedinthesamecity.
selectc1.cid,c2.cid
fromcustomersc1,customersc2
wherec1.city=c2.cityandc1.cid 5.EXAMPLE3.3.7 findpidvaluesofproductsthathavebeenorderedbyatleasttwocustomers. selectdistinctx1.pid fromordersx1,ordersx2 wherex1.pid=x2.pidandx1.cid 6.EXAMPLE3.3.8 Getcidvaluesofcustomerswhoorderaproductforwhichanorderisalsoplacedbyagenta06. selectdistincty.cid fromordersx,ordersy wherey.pid=x,pidandx.aid=‘a06’; 3.4Subqueries 7.EXAMPLE3.4.1 GetcidvaluesofcustomerswhoplaceorderswithagentsinDuluthorDallas. selectdistinctcidfromorders whereaidin(selectaidfromagents wherecity=‘Duluth’orcity=‘Dallas’) 8.EXAMPLE3.4.2 toretrieveallinformationconcerningagentsbasedinDuluthorDallas(veryclosetotheSubqueryinthepreviousexample). select*fromagents wherecityin(‘Duluth’,‘Dallas’); orselect*fromagents wherecity=‘Duluth’orcity=‘Dallas’; 9.EXAMPLE3.4.3 todeterminethenamesanddiscountsofallcustomerswhoplaceordersthroughagentsinDuluthorDallas. selectdistinctcname,discntfromcustomers wherecidin(selectcidfromorderswhereaidin (selectaidfromagentswherecityin(‘Duluth’,‘Dallas’))); 10.EXAMPLE3.4.4 tofindthenamesofcustomerswhoorderproductp05. selectdistinctcnamefromcustomers,orders wherecustomers.cid=orders.cidandorders.pid=‘p05’ orselectdistinctcnamefromcustomerswhere‘p05’in (selectpidfromorderswherecid=customers.cid); 11.EXAMPLE3.4.5 Getthenamesofcustomerswhoorderproductp07fromagenta03. selectdistinctcnamefromcustomers wherecidin(selectcidfromorderswherepid=‘p07’andaid=‘a03’) 12.EXAMPLE3.4.6 toretrieveordnovaluesforallordersplacedbycustomersinDuluththroughagentsinNewYork. selectordnofromordersxwhereexists (select*fromcustomersc,agentsa wherec.cid=x.cidanda.aid=x.aidandc.city=‘Duluth’anda.city=‘NewYork’); 13.EXAMPLE3.4.7 findaidvaluesofagentswithaminimumpercentcommission. selectaidfromagentswherepercent=(selectmin(percent)fromagents); 14.EXAMPLE3.4.8 findallcustomerswhohavethesamediscountasthatofanyofthecustomersinDallasorBoston. selectcid,cnamefromcustomers wherediscnt=some(selectdiscntfromcustomers wherecity=‘Dallas’orcity=‘Boston’); 15.EXAMPLE3.4.9 GetcidvaluesofcustomerswithdiscntsmallerthanthoseofanycustomerswholiveinDuluth. selectcidfromcustomers wherediscnt wherecity=‘Duluth’); 16.EXAMPLE3.4.10 Retrieveallcustomernameswherethecustomerplacesanorderthroughagenta05. selectdistinctamefromcustomersc whereexists(select*fromordersx wherec.cid=x.cidandx.aid=‘a05’); orselectdistinctamefromcustomersc,ordersx wherec.cid=x.cidandx.aid=‘a05’; 17.EXAMPLE3.4.11 Getcidvaluesofcustomerswhoorderbothproductsp01andp07. selectdistinctcidfromordersx wherepid=‘p01’andexsits(select*fromorders wherecid=x.cidandpid=‘p07’); or selectdistinctx.cidfromordersx,ordersy wherex.pid=‘p01’andx.cid=y.cidandy.pid=‘p07’; 18.EXAMPLE3.4.12 Retrieveallcustomernameswherethecustomerdoesnotplaceanorderthroughagenta05. selectdistinctamefromcustomersc wherenotexists(select*fromordersx wherec.cid=x.cidandx.aid=‘a05’); 19.EXAMPLE3.4.13 retrievingallcustomernameswherethecustomerdoesnotplaceanorderthroughagenta05,butusingthetwoequivalentNOTINand<>ALLpredicatesinplaceofNOTEXISTS. selectdistinctamefromcustomersc wherec.cidnotin(selectcidfromorderswhereaid=‘a05’); orselectamefromcustomersc wherec.cid<>all(selectcidfromorderswhereaid=‘a05’); 20.EXAMPLE3.4.14 Findcidvaluesofcustomerswhodonotplaceanyorderthroughagenta03. selectdistinctcidfromordersx wherenotexists(select*fromorders wherecid=x.cidandaid=‘a03’); or selectcidfromcustomersc wherenotexists(select*fromorders wherecid=c.cidandaid=‘a03’); 21.EXAMPLE3.4.15 Retrievethecitynamescontainingcustomerswhoorderproductp01. selectdistinctcityfromcustomerswherecidin (selectcidfromorderswherepid=‘p01’); orselectdistinctcityfromcustomerswherecid=some (selectcidfromorderswherepid=‘p01’); orselectdistinctcityfromcustomerscwhereexsits (select*fromorderswherecid=c.cidandpid=‘p01’); orselectdistinctcityfromcustomersc,ordersx wherex.cid=c.cidandx.pid=‘p01’; orselectdistinctcityfromcustomerscwhere‘p01’in (selectpidfromorderswherecid=c.cid); 3.5UNIONOperatorsandFORALLConditions 22.EXAMPLE3.5.1 tocreatealistofcitieswhereeitheracustomeroranagent,orboth,isbased. selectcityfromcustomers unionselectcityfromagents; 23.EXAMPLE3.5.2 GetthecidvaluesofcustomerswhoplaceorderswithallagentsbasedinNewYork. selectc.cidfromcustomersc wherenotexsits (select*fromagentsa wherea.city=‘NewYork’andnotexsits (select*fromordersx wherex.cid=c.cidandx.aid=a.aid)); 24.EXAMPLE3.5.3 GettheaidvaluesofagentsinNewYorkorDuluthwhoplaceordersforallproductscostingmorethanadollar. selectaidfromagentsa where(a.city=‘NewYork’ora.city=‘Duluth’) andnotexsits (selectp.pidfromproductsp wherep.price>1.00andnotexsits (select*fromordersx wherex.pid=p.pidandx.aid=a.aid)); 25.EXAMPLE3.5.4 Findaidvaluesofagentswhoplaceordersforproductp01aswellasforallproductscostingmorethanadollar. selecta.aidfromagentsawherea.aidin (selectaidfromorderswherepid=‘p01’) andnotexsits(selectp.pidfromproductsp wherep.price>1.00andnotexsits(select*fromordersx wherex.pid=p.pidandx.aid=a.aid)); orselectdistincty.aidfromordersy wherey.pid=‘p01’andnotexsits (selectp.pidfromproductsp wherep.price>1.00andnotexsits (select*fromordersx wherex.pid=p.pidandx.aid=y.aid)); 26.EXAMPLE3.5.6 FindpidvaluesofproductssuppliedtoallcustomersinDuluth. selectpidfromproductsp wherenotexsits (selectc.cidfromcustomersc wherec.city=‘Duluth’andnotexists (select*fromordersx wherex.pid=p.pidandx.cid=c.cid)); 3.7SetFunctionsinSQL 27.EXAMPLE3.7.1 determinethetotaldollaramountofallorders. selectsum(dollars)astotaldollarsfromorders 28.EXAMPLE3.7.2 Todeterminethetotalquantityofproductp03thathasbeenordered. selectsum(qty)asTOTALfromorderswherepid=’p03’ 29.EXAMPLE3.7.4 Getthenumberofcitieswherecustomersarebased. selectcount(distinctcity)fromcustomers 30.EXAMPLE3.7.5 Listthecidvaluesofaltcustomerswhohaveadiscountlessthanthemaximumdiscount. selectcidfromcustomers wherediscnt<(selectmax(discnt)fromcustomers) 31.EXAMPLE3.7.6 Findproductsorderedbyatleasttwocustomers. selectp.pidfromproductsp where2<= (selectcount(distinctcid)fromorderswherepid=p.pid) 图档的学生的上机考查的考题到此为止 ______________________________________________________________________________________________________________________ 信管,保密的学生上机考查还包括下面的题目 32.EXAMPLE3.7.7 Addarowwithspecifiedvaluesforcolumnscid,cname,andcity(c007,Windix,Dallas,null)tothecustomerstable. insertintocustomers(cid,cname,city) values(‘c007’,‘Windix’,‘Dallas’) 33.EXAMPLE3.7.9 Afterinsertingtherow(c007,Windix,Dallas,null)tothecustomerstableinExample3.7.7,assumethatwewishtofindtheaveragediscountofallcustomers. selectavg(discnt)fromcustomers 3.8GroupsofRowsinSQL 34.EXAMPLE3.8.1 tocalculatethetotalproductquantityorderedofeachindividualproductbyeachindividualagent. selectpid,aid,sum(qty)asTOTALfromorders groupbypid,aid 35.EXAMPLE3.8.2 Printouttheagentnameandagentidentificationnumber,andtheproductnameandproductidentificationnumber,togetherwiththetotalquantityeachagentsuppliesofthatproducttocustomersc002andc003. selectaname,a.aid,pname,p.pid,sum(qty) fromordersx,productsp,agentsa wherex.pid=p.pidandx.aid=a.aidandx.cidin(‘c002’,‘c003’) groupbya.aid,a.aname,p.pid,p.pname 36.EXAMPLE3.8.3 PrintoutallproductandagentIDsandthetotalquantityorderedoftheproductbytheagent,whenthisquantityexceeds1000. selectpid,aid,sum(qty)asTOTALfromorders groupbypid,aid havingsum(qty)>1000 37.EXAMPLE3.8.4 Providepidvaluesofallproductspurchasedbyatleasttwocustomers. selectdistinctpidfromorders groupbypid havingcount(distinctcid)>=2 3.9ACompleteDescriptionofSQLSelect 38.EXAMPLE3.9.1 Listallcustomers,agents,andthedollarsalesforpairsofcustomersandagents,andordertheresultfromlargesttosmallestsalestotals.Retainonlythosepairsforwhichthedollaramountisatleastequalto900.00. selectame,c.cid,a.aname,a.aid,sum(dollars)ascasales
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 第三 所有 例题 参考答案