oracleOCP-051答案解析.doc

Oracle OCP 11G 051答案解析1. View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMEStables.The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table.Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing theCUSTOMERS and TIMES tables, respectively.Evaluate the following CREATE TABLE command:CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)ASSELECT prod_id, cust_id, time_idFROM sales;Which statement is true regarding the above command?A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in thecolumn definition.B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specifiedcolumns would be passed to the new table.C. The NEW_SALES table would not get created because the column names in the CREATE TABLEcommand and the SELECT clause do not match.D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on thespecified columns would be passed to the new table.Answer: B答案解析：A答案：默认sysdate可以在列出被指定。shTEST0924 CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE) AS 2 SELECT prod_id, cust_id, time_id FROM sales;Table created.B答案：NULL约束会传递。由以下可以看出，not null是可以传递的。B正确第一种方法，查看表的结构，可见not null约束是可以传递的。shTEST0924 desc SALESName Null? Type- - -PROD_ID NOT NULL NUMBERCUST_ID NOT NULL NUMBERTIME_ID NOT NULL DATECHANNEL_IDNOT NULL NUMBERPROMO_ID NOT NULL NUMBERQUANTITY_SOLD NOT NULL NUMBER(10,2)AMOUNT_SOLD NOT NULL NUMBER(10,2)shTEST0924 desc new_salesName Null? Type- - -PROD_ID NOT NULL NUMBERCUST_ID NOT NULL NUMBERORDER_DATENOT NULL DATE第二种方法：由下面的查询看出，not null也是传递的。B正确shTEST0924 select a.owner,a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION 2 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b 3 where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and A.table_name=SALES;OWNER TABLE_NAME COLUMN_NAME CONSTRAINT_NAME CONSTRAINT SEARCH_CONDITION- - - - - -SH SALES CUST_ID SALES_CUSTOMER_FK RSH SALES PROD_ID SALES_PRODUCT_FK RSH SALES TIME_IDSALES_TIME_FK RSH SALES CHANNEL_ID SALES_CHANNEL_FK RSH SALES PROD_ID SYS_C0011009 C PROD_ID IS NOT NULLSH SALES CUST_IDSYS_C0011010 C CUST_ID IS NOT NULLSH SALES TIME_IDSYS_C0011011 C TIME_ID IS NOT NULLSH SALES CHANNEL_IDSYS_C0011012 C CHANNEL_ID IS NOT NULLSH SALES PROMO_ID SYS_C0011013 C PROMO_ID IS NOT NULLSH SALES QUANTITY_SOLD SYS_C0011014 C QUANTITY_SOLD IS NOT NULLSH SALES AMOUNT_SOLD SYS_C0011015 C AMOUNT_SOLD IS NOT NULLSH SALES PROMO_ID SALES_PROMO_FK R12 rows selected.shTEST0924 l 1 select a.owner,a.TABLE_NAME,b.COLUMN_NAME,a.CONSTRAINT_NAME,a.CONSTRAINT_TYPE,a.SEARCH_CONDITION 2 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b 3* where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and A.table_name=NEW_SALESshTEST0924 /OWNER TABLE_NAME COLUMN_NAME CONSTRAINT_NAME CONSTRAINT SEARCH_CONDITION- - - - - -SH NEW_SALES PROD_ID SYS_C0011428 C PROD_ID IS NOT NULLSH NEW_SALES CUST_ID SYS_C0011429 C CUST_ID IS NOT NULLSH NEW_SALES ORDER_DATE SYS_C0011430 C ORDER_DATE IS NOT NULLC答案：由A中所见，此表是可以创建的，虽然字段名不一样，但数据库类型一样，就可以创建表。C错误。D答案：由B第二种方法看出，仅仅只是传递了not null约束，而SALES_PRODUCT_FK，SALES_CUSTOMER_FK，SALES_TIME_FK这三个外键约束是没有被传递的。2. View the Exhibit to examine the description for the SALES table.Which views can have all DML operations performed on it? (Choose all that apply.)A. CREATE VIEW v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id = SYSDATE - 2*365WITH CHECK OPTION;C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id = SYSDATE - 2*365WITH CHECK OPTION;D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time_id create view v4 2 as select prod_id,cust_id,sum(quantity_sold) from sales 3 where time_id create view v4 2 as select prod_id,cust_id,sum(quantity_sold) sumqty from sales 3 where time_id SELECT po_id, CASEWHEN MONTHS_BETWEEN (shipment_date,po_date)1 THENTO_CHAR(shipment_date - po_date) * 20) ELSE No Penalty END PENALTYFROM shipments;SQLSELECT po_id, DECODE(MONTHS_BETWEEN (po_date,shipment_date)1,TO_CHAR(shipment_date - po_date) * 20), No Penalty) PENALTY FROM shipments;Which statement is true regarding the above commands?A. Both execute successfully and give correct results.B. Only the first query executes successfully but gives a wrong result.C. Only the first query executes successfully and gives thecorrect result.D. Only the second query executes successfully but gives a wrong result.E. Only the second query executes successfully and gives the correct result.Answer: C答案解析：题意要求： 显示PO_ID和支付的罚款总金额，SHIPMENT_DATE与PO_DATE进行比较，SHIPMENT_DATE如果比PO_DATE晚一个月，则每天罚款$20。MONTHS_BETWEEN(date1, date2): 返回date1和date2两个日期之间间隔几个月，结果可以为正或为负。如果date1晚于date2则结果为正;如果date1早于date2则结果为负;结果的非整数部分也代表月间隔的一部分。DECODE(expr,search1,result1,search2,result2,default)：比较expr与search，如果等于search1则返回result1，如果等于search2则返回result2，依次类推，如果都不等于，如果有default则返回default，否则返回NULL.ORACLE在比较之前，会自动把expr和每一个search隐式转换成第一个search（search1）的数据类型。自动把返回值转换成第一个result（result1）的数据类型。如果第一个result的数据类型为CHAR或者值是null，则Oracle转换返回值为VARCHAR2.在DECODE函数中,NULL是相等的，如果expr为空，则Oracle将会返回第一个为NULL的search所对应的result。DECODE列表中的最大表达式个数为255个。第一个SQL的表达是正确，可以正确执行的。其实我觉得这道题的答案有点出入，MONTHS_BETWEEN返回的是月数，每天罚款$20，相乘起来，因为一个是天的单位，一个是月的单位，如果题中改成每月罚款20，则才是正确的。DECODE的表达是错误的。shTEST0924 SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)1, 2 TO_CHAR(shipment_date - po_date) * 20), No Penalty) PENALTY 3 FROM shipments;SELECT po_id, DECODE (MONTHS_BETWEEN (po_date,shipment_date)1, *ERROR at line 1:ORA-00907: missing right parenthesis7. Which twostatements are true regarding the USING and ON clauses in table joins? (Choose two.)A. Both USING and ON clauses can be used for equijoins and nonequijoins.B. A maximum of one pair of columns can be joined between two tables using the ON clause.C. TheON clause can be used to join tables on columns that have different names but compatible datatypes.D. TheWHERE clause can be used to apply additional conditions in SELECT statements containing theON or theUSING clause.Answer: CD答案解析：A, USING和ON子句可以用于等值连接和非等值连接,USING不能用于非等值连接，ON可以。B, 使用ON子句最大只能使用两个列连接两个表.错误，可以连接多个列C,ON子句用于连接表的列可以是不同的名字，但是数据类型要兼容，正确。ON子句还可以用于联接同一表内或者不同表中具有不同名称的列。D,在包含ON或USING子句的SELECT命令中，WHERE子句可以做为附加的条件，正确。8. View the Exhibit and examine the structure of the CUSTOMERS table.Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)A. listing of customers who do not have a credit limit and were born before 1980B. finding the number of customers, in each city, whose marital status is marriedC. finding the average credit limit of male customers residing in Tokyo or SydneyD. listing of those customers whose credit limit is the same as the credit limit of customers residing in thecity TokyoE. finding the number of customers, in each city, whose credit limit is more than the average credit limit ofall the customersAnswer: DE答案解析：ABC不用子查询就可以查询出，题意选择两个，排除ABC,选择DEA. 列出没有信贷限额并且1980年以前出生的客户。SELECT 客户 from 表名 where 信贷限额 is null and 出生日期1980;B. 查找每个城市的已婚客户的数量。SELECT 城市名，COUNT(*) FROM 表名 where 客户婚否=结婚 group by 城市名;C. 查找属于Tokyo或者 Sydney两个城市的男性客户的平均信贷限额。SELECT 城市名， AVG(信贷限额) from 表名 where 性别=男 and 城市 in（Tokyo，Sydney） group by 城市名D 列出与Tokyo城市的客户的信贷限额相等的客户E. 查找每个城市的客户数量，这些客户的信贷限额大于所有客户的平均信贷限额。9. Which statement is true regarding the INTERSECT operator?A. It ignores NULL values.B. Reversing the order of the intersected tables alters the result.C. The names of columns in all SELECT statements must be identical.D. The number of columns and datatypes must be identical for all SELECT statements in the query.Answer: D答案解析：参考：/rlhua/article/details/12883007A. 它忽略空值，错误，不会忽略空值B. 交换交集表的前后顺序可以改变交集结果，错误，不会改变结果C. 所有SELECT查询语句中的列的名字必须相同。错误，列名可以不必相同D. 对于所有SELECT查询语句，列的数量和数据类型必须相同。使用INTERSECT运算符可以返回多个查询的所有共同行。准则 在查询中使用的所有SELECT语句中，由查询中的SELECT语句选定的列数和列的数据类型必须相同。不过，列名不必相同。 使相交的表按反方向排序不会更改结果。 INTERSECT不会忽略NULL值。10. View the Exhibit; e xamine the structure of the PROMOTIONS table.Each promotion has a duration of at least seven days .Your manager has asked you to generate a report,which provides the weekly cost for each promotiondone to l date.Which query would achieve the required result?A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7FROM promotions;B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7FROM promotions;C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7)FROM promotions;D. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date)/7)FROM promotions;Answer: D答案解析：提要要求:报表提供促销期间每周的成本D,正确，符合题意。AB C语法错误shTEST0924 SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions *ERROR at line 1:ORA-00932: inconsistent datatypes: expected NUMBER got DATEshTEST0924 SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions *ERROR at line 1:ORA-00932: inconsistent datatypes: expected NUMBER got DATEshTEST0924 SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions *ERROR at line 1:ORA-00932: inconsistent datatypes: expected NUMBER got DATE11. View the Exhibit and examine the structure of the PRODUCTS table.All products have a list price.You issue the following command to display the total price of each product after a discount of 25% and atax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.SQLSELECT prod_name, prod_list_price -(prod_list_price*(25/100)+(prod_list_price -(prod_list_price*(25/100)*(15/100)+100AS TOTAL PRICEFROM products;What would be the outcome if all the parenthese s are removed from the above statement?A. It produces a syntax error.B. The result remains unchanged.C. The total price value would be lower than the correct value.D. The total price value would be higher than the correct value.htmlview plaincopyprint?1. shTESTDBSELECTprod_name,prod_list_price-(prod_list_price*(25/100)2. 2+(prod_list_price-(prod_list_price*(25/100)*(15/100)+1003. 3ASTOTALPRICE4. 4FROMproductswhererownum65. 5orderbyTOTALPRICE;6. 7. PROD_NAMETOTALPRICE8. -9. YBox613.73287510. 5MPTelephotoDigitalCamera1641.2328811. 17LCDw/built-inHDTVTuner1812.4828812. Envoy256MB-40GB1812.4828813. MiniDVCamcorderwith3.5SwivelLCD1983.73288去除括号后：sqlview plaincopyprint?1. shTESTDBSELECTprod_name,prod_list_price-prod_list_price*25/1002. 2+prod_list_price-prod_list_price*25/100*15/100+1003. 3ASTOTALPRICE4. 4FROMproductswhererownum65. 5orderbyTOTALPRICE;6. 7. PROD_NAMETOTALPRICE8. -9. YBox613.73287510. 5MPTelephotoDigitalCamera1641.2328811. 17LCDw/built-inHDTVTuner1812.4828812. Envoy256MB-40GB1812.4828813. Mi