oracle OCP-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中所見，此表是可以創(chuàng)建的，雖然字段名不一樣，但數據庫類型一樣，就可以創(chuàng)建表。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的表達是正確，可以正確執(zhí)行的。其實我覺得這道題的答案有點出入，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