047 全中文解析 1270题.docx
《047 全中文解析 1270题.docx》由会员分享,可在线阅读,更多相关《047 全中文解析 1270题.docx(139页珍藏版)》请在冰点文库上搜索。
047全中文解析1270题
1Z0-0471-50
1.YouneedtoloadinformationaboutnewcustomersfromtheNEW_CUSTtableintothetablesCUSTandCUST_SPECIAL.Ifanewcustomerhasacreditlimitgreaterthan10,000,thenthedetailshavetobeinsertedintoCUST_SPECIAL.AllnewcustomerdetailshavetobeinsertedintotheCUSTtable.Whichtechniqueshouldbeusedtoloadthedatamostefficiently?
A.externaltable
B.theMERGEcommand
C.themultitableINSERTcommand
D.INSERTusingWITHCHECKOPTION
答案:
C
分析:
本题考点是multitableINSERT
题目要求将NEW_CUST表上所有的记录插入CUST表,creditlimit大于10000的记录要同时插入CUST_SPECIAL表,应该使用有条件的insert命令,sql如下:
insertall
whencredit_limit>=10000intoCUST_SPECIAL
when1=1intoCUST
A.外部表是只读的,不能进行插入操作
B.merge命令只能针对一张表进行插入或者修改操作,不能针对多张表进行插入操作
D.insert命令没有withcheckoption选项,withcheckoption选项是createview时使用的
2.ViewtheExhibitandexaminethedescriptionoftheCUSTOMERStable.YouwanttoaddaconstraintontheCUST_FIRST_NAMEcolumnoftheCUSTOMERStablesothatthevalueinsertedinthecolumndoesnothavenumbers.WhichSQLstatementwouldyouusetoaccomplishthetask?
A.ALTERTABLECUSTOMERSADDCONSTRAINTcust_f_nameCHECK(REGEXP_LIKE(cust_first_name,'^A-Z'))NOVALIDATE;
B.ALTERTABLECUSTOMERSADDCONSTRAINTcust_f_nameCHECK(REGEXP_LIKE(cust_first_name,'^[0-9]'))NOVALIDATE;
C.ALTERTABLECUSTOMERSADDCONSTRAINTcust_f_nameCHECK(REGEXP_LIKE(cust_first_name,'[[:
alpha:
]]'))NOVALIDATE;
D.ALTERTABLECUSTOMERSADDCONSTRAINTcust_f_nameCHECK(REGEXP_LIKE(cust_first_name,'[[:
digit:
]]'))NOVALIDATE;
答案:
C
分析:
本题考点是正则表达式/REGEXP
要在表CUSTOMERS的字段CUST_FIRST_NAME建个约束,使这个字段不能包含数字
A.匹配开头是AZ的字符串。
B.匹配开头是0或者是9的字符串。
C.匹配包含字母的字符串。
D.匹配包含数字的字符串。
3.WhichthreetaskscanbeperformedusingregularexpressionsupportinOracleDatabase10g?
(Choosethree.)
A.Itcanbeusedtoconcatenatetwostrings.
B.Itcanbeusedtofindoutthetotallengthofthestring.
C.Itcanbeusedforstringmanipulationandsearchingoperations.
D.Itcanbeusedtoformattheoutputforacolumnorexpressionhavingstringdata.
E.Itcanbeusedtofindandreplaceoperationsforacolumnorexpressionhavingstringdata.
答案:
CDE
分析:
本题考点是正则表达式/REGEXP
Oracle10g支持正则表达式的函数主要有REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE,用来寻找或替换匹配的字符串
A.连接两个字符串用符号||,正则表达式没有这个功能。
B.字符串的长度是用length()函数,正则表达式没有这个功能。
4.ViewtheExhibitandexaminethestructureoftheEMPtablewhichisnotpartitionedandnotanindexorganizedtable.EvaluatethefollowingSQL statement:
ALTERTABLEempDROPCOLUMNfirst_name;
Whichtwostatementsaretrueregardingtheabovecommand?
(Choosetwo.)
A.TheFIRST_NAMEcolumnwouldbedroppedprovideditdoesnotcontainanydata.
B.TheFIRST_NAMEcolumnwouldbedroppedprovidedatleastoneormorecolumnsremaininthetable.
C.TheFIRST_NAMEcolumncanberolledbackprovidedtheSETUNUSEDoptionisaddedtotheaboveSQLstatement.
D.TheFIRST_NAMEcolumncanbedroppedevenifitispartofacompositePRIMARYKEYprovidedtheCASCADEoptionisused.
答案:
BD
分析:
本题考点是setunused
EMP表不是分区表也不是索引组织表,dropcolumns是物理删除,setunused是逻辑删除,两者都不可以通过rollback恢复,所有引用到该列的对象都会失败。
A.不管列上有没有数据,删除列的命令都可以正常执行。
C.setunused的列无法访问,无法使用rollback撤销unused设置
5.EvaluatetheCREATETABLEstatement:
CREATETABLEproducts(product_idNUMBER(6)CONSTRAINTprod_id_pkPRIMARYKEY,product_nameVARCHAR2(15));
WhichstatementistrueregardingthePROD_ID_PKconstraint?
A.Itwouldbecreatedonlyifauniqueindexismanuallycreatedfirst.
B.Itwouldbecreatedandwoulduseanautomaticallycreateduniqueindex.
C.Itwouldbecreatedandwoulduseanautomaticallycreatednonuniqueindex.
D.Itwouldbecreatedandremainsinadisabledstatebecausenoindexisspecifiedinthecommand.
答案:
B
分析:
本题考点是主键约束和唯一性约束自动建立索引
Oracle在创建主键约束或唯一性约束时,会自动检测该列是否创建过唯一性索引,如果有则不创建而直接使用,如果没有则自动隐式创建唯一性索引。
A.创建主键约束不需要首先手动创建唯一性索引,Oracle会自动隐式创建唯一性索引
C.创建主键约束时,Oracle会自动隐式创建的索引是唯一性索引
D.创建主键约束时没有指定索引,Oracle会自动寻找唯一性索引,找不到则会自动创建唯一性索引。
所有约束创建时默认都是自动生效的
6.Whichtwostatementsaretrue?
(Choosetwo.)
A.TheUSER_SYNONYMSviewcanprovideinformationaboutprivatesynonyms.
B.TheuserSYSTEMownsallthebasetablesanduseraccessibleviewsofthedatadictionary.
C.AllthedynamicperformanceviewsprefixedwithV$areaccessibletoallthedatabaseusers.
D.TheUSER_OBJECTSviewcanprovideinformationaboutthetablesandviewscreatedbytheuser.
E.DICTIONARYisaviewthatcontainsthenamesofallthedatadictionaryviewsthattheusercanaccess.
答案:
AE
分析:
本题考点是数据字典/datadictionary
B.system用户是操作系统管理员,sys才是数据库管理员,数据字典的所有基表和视图都属于sys用户。
C.v$为前缀的动态性能视图要有DBA权限才能访问。
D.USER_OBJECTS视图不仅包含用户的表、视图信息,还包括了其他对象如触发器、索引、过程等等。
7.ViewtheExhibitandexaminethedescriptionoftheORDERStable.WhichtwoWHEREclauseconditionsdemonstratethecorrectusageofconversionfunctions?
(Choosetwo.)
A.WHEREorder_date>TO_DATE('JUL102006','MONDDYYYY')
B.WHERETO_CHAR(order_date,'MONDDYYYY')='JAN202003'
C.WHEREorder_date>TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY')
D.WHEREorder_dateIN(TO_DATE('Oct212003','MonDDYYYY'),TO_CHAR('NOV212003','MonDDYYYY'))65
答案:
AB
分析:
本题考点是日期格式/datetype
C.order_date是timestampwithlocaltimezone数据类型,TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY')返回的是字符串,如果不符合系统时间格式则Oracle无法隐式转换,两者比较时会出错。
D.in是集合操作符,集合中TO_DATE('Oct212003','MonDDYYYY')返回date格式,TO_CHAR('NOV212003','MonDDYYYY')返回字符串,数据类型不一致。
8.ViewtheExhibitandexaminethedescriptionoftheEMPLOYEEStable.Yourcompanydecidedtogiveamonthlybonusof$50toalltheemployeeswhohavecompletedfiveyearsinthecompany.ThefollowingstatementiswrittentodisplaytheLAST_NAME,DEPARTMENT_ID,andthetotalannualsalary:
SELECTlast_name,department_id,salary+50*12"AnnualCompensation"FROMemployeesWHEREMONTHS_BETWEEN(SYSDATE,hire_date)/12>=5;
Whenyouexecutethestatement,the"AnnualCompensation"isnotcomputedcorrectly.Whatchangeswouldyoumaketothequerytocalculatetheannualcompensationcorrectly?
A.ChangetheSELECTclausetoSELECTlast_name,department_id,salary*12+50"AnnualCompensation".
B.ChangetheSELECTclausetoSELECTlast_name,department_id,salary+(50*12)"AnnualCompensation".
C.ChangetheSELECTclausetoSELECTlast_name,department_id,(salary+50)*12"AnnualCompensation".
D.ChangetheSELECTclausetoSELECTlast_name,department_id,(salary*12)+50"AnnualCompensation".
答案:
C
分析:
本题要求为每个工作满5年的员工的月工资增加$50,返回他们的LAST_NAME、DEPARTMENT_ID和年薪,年薪=(salary+50)*12。
9.EvaluatethefollowingCREATESEQUENCEstatement:
CREATESEQUENCEseq1STARTWITH100INCREMENTBY10MAXVALUE200CYCLENOCACHE;
ThesequenceSEQ1hasgeneratednumbersuptothemaximumlimitof200.YouissuethefollowingSQLstatement:
SELECTseq1.nextvalFROMdual;
WhatisdisplayedbytheSELECTstatement?
A.1
B.10
C.100
D.anerror
答案:
A
分析:
本题考点是序列/SEQUENCE
STARTWITH100指定序列第一次使用是从100开始,INCREMENTBY10指定序列每次使用增加10,MAXVALUE200指定序列最大值为200,CYCLE指定序列可以循环使用,NOCACHE指定序列不使用缓存。
序列没有设置minvalue参数,所以这个序列第一次使用从100开始,每用1次增加10,达到最大值200后循环到minvalue,由于没有设置minvalue,所以使用默认值1。
10.ViewtheExhibitandexaminethedescriptionoftheEMPLOYEEStable.YouwanttodisplaytheEMPLOYEE_ID,FIRST_NAME,andDEPARTMENT_IDforalltheemployeeswhoworkinthesamedepartmentandhavethesamemanagerasthatoftheemployeehavingEMPLOYEE_ID104.Toaccomplishthetask,youexecutethefollowingSQLstatement:
SELECTemployee_id,first_name,department_idFROMemployeesWHERE(manager_id,department_id)=(SELECTdepartment_id,manager_idFROMemployeesWHEREemployee_id=104)ANDemployee_id<>104
Whenyouexecutethestatementitdoesnotproducethedesiredoutput.Whatisthereasonforthis?
A.TheWHEREclauseconditioninthemainqueryisusingthe=comparisonoperator,insteadofEXISTS.
B.TheWHEREclauseconditioninthemainqueryisusingthe=comparisonoperator,insteadoftheINoperator.
C.TheWHEREclauseconditioninthemainqueryisusingthe=comparisonoperator,insteadofthe=ANYoperator.
D.ThecolumnsintheWHEREclauseconditionofthemainqueryandthecolumnsselectedinthesubqueryshouldbeinthesameorder.
答案:
D
分析:
本题考点是组合列
where子句的(manager_id,department_id)作为组合列,要求子查询返回的也是一个结构相同的组合列,而不是(department_id,manager_id)
11.ViewtheExhibitandexaminethedescriptionsofORDER_ITEMSandORDERStables.YouwanttodisplaytheCUSTOMER_ID,PRODUCT_ID,andtotal(UNIT_PRICEmultipliedbyQUANTITY)fortheorderplaced.YoualsowanttodisplaythesubtotalsforaCUSTOMER_IDaswellasforaPRODUCT_IDforthelastsixmonths.
WhichSQLstatementwouldyouexecutetogetthedesiredoutput?
A.SELECTo.customer_id,oi.product_id,SUM(oi.unit_price*oi.quantity)"Total"FROMorder_itemsoiJOINordersoONoi.order_id=o.order_idGROUPBYROLLUP(o.customer_id,oi.product_id)WHEREMONTHS_BETWEEN(order_date,SYSDATE)<=6
B.SELECTo.customer_id,oi.product_id,SUM(oi.unit_price*oi.quantity)"Total"FROMorder_itemsoiJOINordersoONoi.order_id=o.order_idGROUPBYROLLUP(o.customer_id,oi.product_id)HAVINGMONTHS_BETWEEN(order_date,SYSDATE)<=6
C.SELECTo.customer_id,oi.product_id,SUM(oi.unit_price*oi.quantity)"Total"FROMorder_itemsoiJOINordersoONoi.order_id=o.order_idGROUPBYROLLUP(o.customer_id,oi.product_id)WHEREMONTHS_BETWEEN(order_date,SYSDATE)>=6
D.SELECTo.customer_id,oi.product_id,SUM(oi.unit_price*oi.quantity)"Total"FROMorder_itemsoiJOINordersoONoi.order_id=o.order_idWHEREMONTHS_BETWEEN(order_date,SYSDATE)<=6GROUPBYROLLUP(o.customer_id,oi.product_id)
答案:
D
分析:
本题考点是WHERE与HAVING的区别
A.where要写在groupby之前
B.having子句中的列要在select子句中出现
C.where要写在groupby之前
12.ViewtheExhibitandexaminethestructureoftheEMPLOYEEStable.Youwanttoretrieve(检索)hierarchical(分层)dataoftheemployee