【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解051

ZHAODONG

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 1980
B. finding the number of customers, in each city, whose marital status is 'married'
C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo’.
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers.
8.查看CUSTOMERS表结构。
哪两个任务需要在一条语句中执行子查询或表连接？（选择两个）
A. 列出没有信贷限额并且1980年以前出生的客户。
B. 查找每个城市的已婚客户的数量。
C. 查找属于'Tokyo'或者 'Sydney'两个城市的男性客户的平均信贷限额。
D 列出与'Tokyo'城市的客户的信贷限额相等的客户
E. 查找每个城市的客户数量，这些客户的信贷限额大于所有客户的平均信贷限额。
答案为DE

ZHAODONG

7. Which two statements 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. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.

7.关于表连接的USING和ON子句，哪两句话是正确的？（选择两个）
A. USING和ON子句可以用于等值连接和非等值连接。
B. 使用ON子句最大只能使用两个列连接两个表.
C. ON子句用于连接表的列可以是不同的名字，但是数据类型要兼容。
D. 在包含ON或USING子句的SELECT命令中，WHERE子句可以做为附加的条件。

答案为CD
A不对USING用于等值连接ON用于非等值连接，B不对ON子句可以连接多个列。

ZHAODONG

6. Examine the structure of the SHIPMENTS table:
name               Null             Type
PO_ID              NOT NULL       NUMBER(3)
PO_DATE           NOT NULL       DATE
SHIPMENT_DATE    NOT NULL       DATE
SHIPMENT_MODE                    VARCHAR2(30)
SHIPMENT_COST                     NUMBER(8,2)
You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day.
Evaluate the following two queries:
SQL> SELECT po_id, CASE
WHEN MONTHS_BETWEEN (shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' END PENALTY
FROM shipments;
SQL>SELECT 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 the correct 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.

6.检查SHIPMENTS表结构：
你想获取一个报表，报表内容显示PO_ID和支付的罚款总金额，SHIPMENT_DATE与PO_DATE进行比较，SHIPMENT_DATE如果比PO_DATE晚一个月，则每天罚款$20。
评估下面两条查询语句：
关于上面的命令哪句话是正确的？

答案为C

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个。

ZHAODONG

5. Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three)
A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

5.哪一个SQL语句可以把1890.55显示成$1,890.55 （选择三个）
A. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL

答案为：ADE

ZHAODONG

4. Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
C. Arguments can only be column values or constants.
D. They always return a single result row for every row of a queried table.
E. They can return a data type value different from the one that is referenced.

4.关于单行函数，哪两句话是正确的？（选择两个）
A. 只接受一个参数
B. 只可以被嵌套两级
C. 参数只能是列值或者常量.
D. 对于查询表的每一行总是返回一个结果行.
E. 返回的数据类型值可以与之前引用的数据类型不同

答案为DE

这里提一下Multiple-Row Functions（多行函数）：多个行返回一个结果行。
A不对，单行函数可以接受一个或多个参数，B不对可以被嵌套多级，C不对参数还可以是变量和表达式。
D:他们总是返回一个查询表的每行一个结果行
他们可以返回一个不同的从一个参考数据类型值

ZHAODONG

3. You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'.
Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'

3. 你需要从SALES表中提取出PROD_ID列包含‘_D123’字符串的产品明细。
下面哪一个WHERE条件子句可以获得所需的输出结果？
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'

答案为B

答案解析：ESCAPE子句为指定转译字符，因为‘_’下滑线在LIKE子句中指的是任意一个字符，所以需要把_’下滑线进行转译。

ZHAODONG

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 v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;

2. 查看SALES表的描述。
可以在哪一个视图上面执行所有的DML操作？（选择所有可以的）

答案为AB
WITH CHECK OPTION表示：Oracle数据库禁止更改不包含在子查询条件里的行。C不正确的原因为有其它列不能为空，D不正确的原因为一个复杂视图，并且SUM(quantity_sold)需要一个别名。