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

ZHAODONG

53. View the Exhibit and examine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

A. SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
B. SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
C. SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
D. SELECT promo_name, promo_cost, promo_begin_date
FROM promotions
WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

53.检查PROMOTIONS表的数据
PROMO_BEGIN_DATE列使用默认日期格式dd-mon-rr存储.
你需要获得一个报表，显示所有2000年1月1日之前，促销种类为POST的promos的名称，成本和开始日期，你应该使用哪个SQL语句？

答案：D
A和B不正确因为promo_begin_date条件不对
C不正确因为promo_category 条件不正确，但是promo_begin_date < '1-JANUARY-00'条件是正确的，因为这里使用JANUARY或JAN都可以。

ZHAODONG

54. View the Exhibit and examine the structure of the CUSTOMERS table.
Evaluate the query statement:
SQL> SELECT cust_last_name, cust_city, cust_credit_limit
FROM customers
WHERE cust_last_name BETWEEN 'A' AND 'C' AND cust_credit_limit BETWEEN
1000 AND 3000;
What would be the outcome of the above statement?

A. It executes successfully.
B. It produces an error because the condition on CUST_LAST_NAME is invalid.
C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null values.
D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clause.

54. 查看CUSTOMERS表结构，评估下面的查询语句，查询语句的结果是什么？
A. 执行成功
B. 报错因为CUST_LAST_NAME的条件是无效的
C. 只要CUST_CREDIT_LIMIT列没有包含任何值就可以执行成功
D. 报错因为AND操作符不能联合多个BETWEEN子句
答案：A

ZHAODONG

55. Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit = 3000;
Which statement is true regarding the above two queries?
A. Performance would improve in query 2.
B. Performance would degrade in query 2.
C. There would be no change in performance.
D. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.

55.评估下面的两个查询：
关于上面的两条语句哪一个是正确的？
A. 查询2的性能比较高
B. 查询2的性能比较低
C. 性能没有改变
D. 只要CUST_CREDIT_LIMIT列中有空值，则查询2的性能比较高
答案：C

ZHAODONG

56. View the Exhibit and examine the structure of the PROMOTIONS table.
查看PROMOTIONS表的结构。
Using the PROMOTIONS table, you need to find out the names and cost of all the promos done on 'TV' and 'internet' that ended in the time interval 15th March '00 to 15th October '00.（使用PROMOTIONS表，你想找出所有在15th March 00到15th October 00这一段时间内结束促销，促销类型为'TV'和'internet'的promos的名称和成本）
Which two queries would give the required result? (Choose two.)
哪两个查询给出所需的结果？（选择两个）

A. SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category IN ('TV', 'internet') AND
promo_end_date BETWEEN '15-MAR-00' AND '15-OCT-00';
B. SELECT promo_name, promo_cost
FROM promotions
WHERE promo_category = 'TV' OR promo_category ='internet' AND
promo_end_date >='15-MAR-00' OR promo_end_date <='15-OCT-00';
C. SELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category BETWEEN 'TV' AND 'internet') AND
(promo_end_date IN ('15-MAR-00','15-OCT-00'));
D. SELECT promo_name, promo_cost
FROM promotions
WHERE (promo_category = 'TV' OR promo_category ='internet') AND
(promo_end_date >='15-MAR-00' AND promo_end_date <='15-OCT-00');

答案：AD
B不正确因为AND比OR的优先级要高所有会导致条件不正确
C promo_end_date IN ('15-MAR-00','15-OCT-00')不满足条件

ZHAODONG

57. The CUSTOMERS table has the following structure:（CUSTOMERS表结构如下）
name                     Null               Type
CUST_ID                 NOT NULL         NUMBER
CUST_FIRST_NAME      NOT NULL         VARCHAR2(20)
CUST_LAST_NAME       NOT NULL         VARCHAR2(30)
CUST_INCOME_LEVEL                       VARCHAR2(30)
CUST_CREDIT_LIMIT                         NUMBER
You need to write a query that does the following tasks:
（你需要写一个查询完成下面的任务）
1. Display the first name and tax amount of the customers. Tax is 5% of their credit limit.
（显示customers的first name和tax amount，Tax是credit limit的5%）
2. Only those customers whose income level has a value should be considered.
（只考虑income level有值的customers）
3. Customers whose tax amount is null should not be considered.
（不考虑tax amount为空的Customers）
Which statement accomplishes all the required tasks?（哪个子句能完成所需任务）
A. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
tax_amount IS NOT NULL;
B. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL AND
cust_credit_limit IS NOT NULL;
C. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level <> NULL AND
tax_amount <> NULL;
D. SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE (cust_income_level,tax_amount) IS NOT NULL;

答案：B
A不正确因为列别名不能用于WHERE子句
C不光是有别名，而如果使用<>不等于操作符与NULL运算条件永远为假
D的语法不正确

ZHAODONG

58. The PART_CODE column in the SPARES table contains the following list of values:
（SPARES表的PART_CODE列包含下面的值：）
PART_CODE
A%_WQ123
A%BWQ123
AB_WQ123
Evaluate the following query:（评估下面的这个查询）
SQL> SELECT part_code
FROM spares
WHERE part_code LIKE '%\%_WQ12%' ESCAPE '\';
Which statement is true regarding the outcome of the above query?
（关于上面查询的结果哪句话是正确的？）
A. It produces an error.
B. It displays all values.
C. It displays only the values A%_WQ123 and AB_WQ123 .
D. It displays only the values A%_WQ123 and A%BWQ123.
E. It displays only the values A%BWQ123 and AB_WQ123.

答案：D，因为LIKE '%\%_WQ12%' ESCAPE '\'里的\为转译字符，把第二个%转译，而_下划线没有转译，代表一个字符，所有答案为D

ZHAODONG

59. View the Exhibit and examine the data in the PRODUCTS table.
You need to display product names from the PRODUCTS table that belong to the 'Software/Other ' category with minimum prices as either $2000 or $4000 and no unit of measure.
You issue the following query:
SQL>SELECT prod_name, prod_category, prod_min_price
FROM products
WHERE prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR
prod_min_price = 4000) AND prod_unit_of_measure <> '';
Which statement is true regarding the above query?

A. It executes successfully but returns no result.
B. It executes successfully and returns the required result.
C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid

59. 检查PRODUCTS表的数据
你需要显示PRODUCTS表中产品种类为Software/Other，最低价格为$2000或$4000，并且没有度量单位的产品名称。你执行下面的查询，关于查询哪句话正确？
A 执行成功但不返回结果
B 执行成功并返回所需结果
C 报错因为PROD_UNIT_OF_MEASURE条件是无效的
D 报错因为PROD_CATEGORY条件是无效的

答案：A，因为prod_unit_of_measure <> ''该条件为假，’’相当于NULL，所以会导致该WHERE子句里所有AND连接的值为假，虽然执行成功但不会返回结果，正确的条件应该是prod_unit_of_measure is not null

ZHAODONG

60. View the Exhibit and examine the structure of CUSTOMERS table.
（查看CUSTOMERS表结构）
Evaluate the following query:（评估下面的查询语句）
SQL>SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE 'A_%g_%' AND
cust_credit_limit BETWEEN 5000 AND 15000 AND
cust_credit_limit NOT IN (7000, 11000) AND
cust_city NOT BETWEEN 'A' AND 'B';
Which statement is true regarding the above query?
（关于上面的查询哪句话是正确的？）

A. It executes successfully.（执行成功）
B. It produces an error because the condition on the CUST_CITY column is not valid.
（报错，因为CUST_CITY列的条件无效）
C. It produces an error because the condition on the CUST_FIRST_NAME column is not valid.（报错，因为CUST_FIRST_NAME的条件无效）
D. It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.（报错，因为CUST_CREDIT_LIMIT的条件无效）
答案：A

ZHAODONG

61. View the Exhibit and examine the structure of the PROMOTIONS table.
（查看PROMOTIONS表结构）
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
（你需要从PROMOTIONS表获取所有promos的报表，基于如下的条件：）
1. The promo name should not begin with 'T' or 'N'.
（promo name不是以'T’或'N'开头）
2. The promo should cost more than $20000.
（promo的成本大于$20000）
3. The promo should have ended after 1st January 2001.
（在2001年1月1日之后结束的promo）
Which WHERE clause would give the required result?
（哪个WHERE子句能给出所需结果？）

A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
答案：C

ZHAODONG

62. View the Exhibit and examine the structure of the CUSTOMERS table.
（查看CUSTOMERS表的结构）
You want to generate a report showing the last names and credit limits of all customers whose last names start with A, B, or C, and credit limit is below 10, 000.
（你想获取一个报表，显示所有customers的last names和credit limits，客户的last names以A, B, 或C开头，并且credit limit小于10，000）
Evaluate the following two queries:（评估下面的两个查询）
SQL> SELECT cust_last_name, cust_credit_limit FROM customers
WHERE (UPPER(cust_last_name) LIKE 'A%' OR
UPPER(cust_last_name) LIKE 'B%' OR UPPER(cust_last_name) LIKE 'C%')
AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers
WHERE UPPER(cust_last_name) BETWEEN 'A' AND 'C'
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
（关于上面的查询哪个句子是正确的？）

A. Only the first query gives the correct result.（只有第一个查询给出正确结果）
B. Only the second query gives the correct result（只有第二个查询给出正确结果）
C. Both execute successfully and give the same result.
（两个都能执行成功，并给出正确结果）
D. Both execute successfully but do not give the required result.
（两个都能执行成功，但不能给出所需结果）
答案：A，因为第二条查询语句里的BETWEEN 'A' AND 'C'，到达'C'这个字母后，只能包含到'C'一个字母,而例如’CBA’、’CAB’这样的不能包含进来。

ZHAODONG

63. View the Exhibit and examine the structure of the PRODUCTS table.（查看表结构）
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition.
（你只想显示产品价格是最低价格两倍的产品的名称.报表应该以最高价格的产品名称开头）
Evaluate the following SQL statement:（评估下面的SQL语句）
SQL>SELECT prod_name,prod_list_price
FROM products
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?（哪一个ORDER BY子句加到上面的SQL语句后能得到正确的结果？）
(Choose all that apply.)

A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY (2*prod_min_price)DESC, prod_name;
C. ORDER BY prod_name, (2*prod_min_price)DESC;
D. ORDER BY prod_name DESC, prod_list_price DESC;
E. ORDER BY prod_list_price DESC, prod_name DESC;
答案：AE
B是按2*prod_min_price排序，不满足条件
C和D是先按prod_name排序，也不满足条件

ZHAODONG

64. View the Exhibit and examine the data in the PROMO_CATEGORY and PROMO_COST columns of the PROMOTIONS table.
（查看PROMOTIONS表的PROMO_CATEGORY 和PROMO_COST列的数据）
Evaluate the following two queries:（评估下面两个查询）
SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"
FROM promotions ORDER BY code;
SQL>SELECT DISTINCT promo_category promo_cost "code"
FROM promotions ORDER BY 1;
Which statement is true regarding the execution of the above queries?
（关于执行上面的查询哪句话是正确的？）

A. Only the first query executes successfully.（只有第一个执行成功）
B. Only the second query executes successfully.（只有第二个执行成功）
C. Both queries execute successfully but give different results.
（两个都执行成功，但是结果不同）
D. Both queries execute successfully and give the same result.
（两个都执行成功，并且结果相同）
答案：B，因为ORDER BY使用列别名时要完全匹配，如果别名加了双引号，必须也要加双引号，1代表使用第一列进行排序

ZHAODONG

65. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city.（你需要在CUSTOMERS表上获得一个报表，显示员工信息，按city降序排列，并且每个city中员工的income level也降序排列显示）
Which query would accomplish this task?（哪一个查询能完成这个任务？）

A. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city desc, cust_income_level DESC ;
B. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_income_level desc, cust_city DESC;
C. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY (cust_city, cust_income_level) DESC;
D. SELECT cust_city, cust_income_level, cust_last_name
FROM customers
ORDER BY cust_city, cust_income_level DESC;
答案：A
B选项顺序错误，C语法错误，D条件不符合

ZHAODONG

66. View the Exhibit and examine the data in the COSTS table.（查看表数据）
You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost.
（需要一个报表，显示COSTS表中所有产品ID，产品的unit price至少要比unit cost多25%，按unit cost的25%降序显示产品信息）
You issue the following query:（执行下面的查询）
SQL>SELECT prod_id
FROM costs
WHERE unit_price >= unit_cost * 1.25
ORDER BY unit_cost * 0.25 DESC;
Which statement is true regarding the above query?（关于上面的查询哪句话是正确的？）

A. It executes and produces the required result.（获取所需的结果）
B. It produces an error because an expression cannot be used in the ORDER BY clause.
（报错，因为表达式不能用在ORDER BY子句中）
C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause.（报错，因为DESC不能用在ORDER BY的表达式中）
D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause.（报错，因为ORDER BY子句中的表达式也应该在SELECT子句中）
答案：A

ZHAODONG

67. Which two statements are true regarding the ORDER BY clause? (Choose two.)
A. It is executed first in the query execution.
B. It must be the last clause in the SELECT statement.
C. It cannot be used in a SELECT statement containing a HAVING clause.
D. You cannot specify a column name followed by an expression in this clause.
E. You can specify a combination of numeric positions and column names in this clause.

67. 关于ORDER BY子句哪两句话是正确的？（选择两个）
A. 查询语句首先执行它
B. 必须放在SELECT语句的最后
C. 它不能用在包含HAVING子句的SELECT语句中
D. 你不能在该子句中指定一个带有表达式的列
E. 你能在该子句中指定一个数字位置和列名的组合
答案：BE

ZHAODONG

68. Which statement is true regarding the default behavior of the ORDER BY clause?
A. In a character sort, the values are case- sensitive.
B. NULL values are not considered at all by the sort operation.
C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.

68. 关于ORDER BY子句的默认行为，哪句话是正确的？
A. 对一个字符排序，值是大小写敏感的
B. NULL值不考虑在排序操作里
C. 只有在SELECT语句中列出的列才能用于ORDER BY子句中
D. 如果有小数位，数字值是从大到小显示的
答案：A

ZHAODONG

69. You need to generate a list of all customer last names with their credit limits from the CUSTOMERS table. Those customers who do not have a credit limit should appear last in the list.
（你需要从CUSTOMERS表中获取一个列表，显示客户的last name和他们的credit limits，v那些没有credit limit的客户显示到列表的后面）
Which two queries would achieve the required result? (Choose two.)
（哪两个查询能获取所需结果，选择两个）
A. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit DESC ;
B. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit;
C. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_credit_limit NULLS LAST;
D. SELECT cust_last_name, cust_credit_limit
FROM customers
ORDER BY cust_last_name, cust_credit_limit NULLS LAST;

答案：BC
A不正确因为cust_credit_limit DESC降序排列后，空值会排列到前面
B默认升续排列，空值会排列到后面
C NULLS LAST关键字可以把空值排列到后面
D不正确因为排序条件不正确

ZHAODONG

70. View the E xhibit and examine the structure of the PRODUCTS table.（查看表结构）
You want to display only those product names with their list prices where the list price is at least double the minimum price. The report should start with the product name having the maximum list price satisfying this condition.
（你想只显示产品名称和产品价格，产品价格至少是最小价格的两倍。报表应该从具有最高价格的产品开始显示。）
Evaluate the following SQL statement:（评估下面的SQL语句）
SQL>SELECT prod_name,prod_list_price     
FROM products     
WHERE prod_list_price >= 2 * prod_min_price
Which ORDER BY clauses can be added to the above SQL statement to get the correct output?
(Choose all that apply.)哪一个ORDER BY子句加入到SQL语句中能得到正确结果？

A. ORDER BY prod_list_price DESC, prod_name;
B. ORDER BY (2*prod_min_price)DESC, prod_name;
C. ORDER BY prod_name, (2*prod_min_price)DESC;
D. ORDER BY prod_name DESC, prod_list_price DESC;
E. ORDER BY prod_list_price DESC, prod_name DESC;
答案：AE
71. Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database ? (Choose three .)
A. addition
B. subtraction
C. raising to a power
D. finding the quotient
E. finding the lowest value

ZHAODONG

72. Which tasks can be performed using SQL functions built into Oracle Database ? (Choose three.)
A. displaying a date in a nondefault format
B. finding the number of characters in an expression
C. substituting a character string in a text expression with a specified string
D. combining more than two columns or expressions into a single column in the output

72.哪一个任务能使用ORACLE内置函数来完成？（选择三个）
A. 显示一个非默认格式的日期
B. 在一个表达式中查找字符的数量
C .使用指定的字符串来替换文本表达式中的字符串
D. 联合超过两个列或表达式输出为一个列
答案：ABC
A例如：to_char()转换日期输出，B例如：regexp_count()，C例如：replace()
D不对的原因是因为没有能联合超过两个列的函数，CONCAT()只能联合两个列

ZHAODONG

73. Which tasks can be performed using SQL functions that are built into Oracle database ? (Choose three .)
A. finding the remainder of a division
B. adding a number to a date for a resultant date value
C. comparing two expressions to check whether they are equal
D. checking whether a specified character exists in a given string
E. removing trailing, leading, and embedded characters from a character string

73. 哪一个任务能使用内置函数完成？（选择三个）
A. 取余
B. 给日期添加一个数字合成一个日期值
C. 比较两个表达式查看是否相等
D. 检查指定的字符串是否存在另一个字符串中
E. 从一个字符串中移除尾部，前部，内含的字符
的答案是：ACD
A例如：MOD()，C例如NULLIF，D例如：INSTR()或regexp_count()
B和E虽然给的答案是不正确的，但是我觉得B可以使用ADD_MONTHS()来完成，而E可以使用replace()函数完成，因为trim()只能去除前后的字符，不明白是不是理解错误

ZHAODONG

74. Which statements are true regarding single row functions? (Choose all that apply.)
A. MOD : returns the quotient of a division
B. TRUNC : can be used with NUMBER and DATE values
C. CONCAT : can be used to combine any number of values
D. SYSDATE : returns the database server current date and time
E. INSTR : can be used to find only the first occurrence of a character in a string
F. TRIM : can be used to remove all the occurrences of a character from a string

74. 关于单行函数哪句话是正确的？（选择所有合适的）
A. MOD返回一个商
B. TRUNC能用于NUMBER和DATE值
C. CONCAT能用于联合任意数量的值
D. SYSDATE返回数据库服务器当前的日期和时间
E. INSTR只能用于查找字符串中第一次出现的字符
F. TRIM能用于移除所有字符串中出现的字符
答案：BD
Instr函数：格式：instr(源字符串,目标字符串,起始位置,匹配序号)
例如：instr(‘CORPORATE FLOOR’,’OR’,3,2)中，源字符串为‘CORPORATE FLORR’，目标字符串为’OR’，起始位置为3，取第2个匹配的位置。默认查找顺序为从左到右，当起始位置为负数的时候，从右边开始查找。上面返回的值为14

ZHAODONG

75. The following data exists in the PRODUCTS table:（表中的数据）
PROD_ID   PROD_LIST_PRICE
123456     152525.99
You issue the following query:（执行下面的查询）
SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*')
FROM products
WHERE prod_id = 123456;
What would be the outcome?（结果是什么？）
A. 152526 ****
B. **152525.99
C. 152525** **
D. an error message
答案：A
ROUND四舍五入，所以ROUND(prod_list_price)＝152526，RPAD右填充函数，所以
RPAD(( ROUND(prod_list_price)), 10,'*')的结果为152526****

ZHAODONG

76. You need to display the first names of all customers from the CUSTOMERS table that contain the character 'e' and have the character 'a' in the second last position.
（你需要从CUSTOMERS表中显示所有customers的first names，名字中要包含e，并且倒数第二个字符要包含a）
Which query would give the required output?（哪个查询能给出所需的结果？）
A. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, -2, 1)='a';
B. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>'' AND
SUBSTR(cust_first_name, -2, 1)='a';
C. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')IS NOT NULL AND
SUBSTR(cust_first_name, 1,-2)='a';
D. SELECT cust_first_name
FROM customers
WHERE INSTR(cust_first_name, 'e')<>0 AND
SUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a';

答案：A
INSTR如果不包含字符则返回0，SUBSTR可以从倒数第二个字符开始截取一个
77. In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:
SQL> SELECT INITCAP(cust_first_name ' '
UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2)))
FROM customers
WHERE cust_first_name = 'ABIGAIL';
What would be the outcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. an error messag

ZHAODONG

77. CUSTOMERS表中，CUST_CITY值为Paris对应CUST_FIRST_NAME值为ABIGAIL
评估下面的语句，结果是什么？
答案：B
首先SUBSTR截取的结果是Pa，UPPER转换后为PA，INITCAP后为Abigail Pa

78. Evaluate the following query:（评估下面的查询）
SQL> SELECT TRUNC(ROUND(156.00,-1),-1)
FROM DUAL;
What would be the outcome?（结果是什么？）
A. 16
B. 100
C. 160
D. 200
E. 150
答案：C，ROUND四舍五入为160，TRUNC截断后也是160

ZHAODONG

79. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'.（表中CUST_LAST_NAME列包含'Anderson' and 'Ausson'值）
You issue the following query:（执行下面的查询）
SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O'))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE 'a%n';
What would be the outcome?（结果是什么？）

A. 'Oder' and 'Aus'
B. an error because the TRIM function specified is not valid
C. an error because the LOWER function specified is not valid
D. an error because the REPLACE function specified is not valid
答案：B，因为TRIM('son' FROM cust_last_name)第一个’son’不能是多个字符，只能是一个，replace函数可以替换字符串里全部出现的查找串。

ZHAODONG

80. Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format.
B. The default internal storage of dates is in the character format.
C. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century.
D. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required.
80. 关于处理日期型数据，哪两句话是正确的？
A. 默认内部存储的日期是使用数字格式
B. 默认内部存储的日期是使用字符格式
C. RR日期格式自动从SYSDATE函数中计算出世纪，不允许用户输入世纪
D. RR日期格式自动从SYSDATE函数中计算出世纪，但是如果需要还允许用户输入世纪

答案：AD
A参照Database Concepts的DATE Data Type部分：
The database stores dates internally as numbers. Dates are stored in fixed-length fields of 7 bytes each, corresponding to century, year, month, day, hour, minute, and second

ZHAODONG

81. You are currently located in Singapore and have connected to a remote database in Chicago.
You issue the following command:
SQL> SELECT ROUND(SYSDATE-promo_begin_date,0)
FROM promotions
WHERE (SYSDATE-promo_begin_date)/365 > 2;
PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.
What is the outcome?
A. an error because the ROUND function specified is invalid
B. an error because the WHERE condition specified is invalid
C. number of days since the promo started based on the current Chicago date and time
D. number of days since the promo started based on the current Singapore date and time

81.你正在Singapore（新加坡），你连接到位于Chicago的远程数据库，PROMOTIONS是一个public synonym，通过public database link连接到PROMOTIONS表。
你执行下面的命令，结果是什么？
A. 报错因为ROUND函数无效
B. 报错因为WHERE条件无效
C. 基于当前Chicago的日期和时间promo开始的天数
C. 基于当前Singapore的日期和时间promo开始的天数
答案为C，一般都是使用的远程服务器时间

ZHAODONG

82. Examine the data in the CUST_NAME column of the CUSTOMERS table.（查看表数据）
CUST_NAME
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikkilineni
Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
（你需要显示第二个名字以"Mc" or "MC"开头的员工第二个名字）
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
答案：B

ZHAODONG

83. Examine the data in the CUST_NAME column of the CUSTOMERS table.（查看表数据）
CUST_NAME
Lex De Haan
Renske Ladwig
Jose Manuel Urman
Jason Mallin
You want to extract only those customer names that have three names and display the * symbol in place of the first name as follows:
（你只想提取那些有三个名字的客户，第一个名字用*号替换）
CUST NAME
*** De Haan
**** Manuel Urman
Which two queries give the required output? (Choose two.)
A. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',1,2)<>0;
B. SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',-1,2)<>0;
C.SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)-
INSTR(cust_name,' '),'*')  "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',-1,-2)<>0;
D.SELECT LPAD(SUBSTR(cust_name,INSTR(cust_name,' ')),LENGTH(cust_name)- INSTR(cust_name,' '),'*') "CUST NAME"
FROM customers
WHERE INSTR(cust_name, ' ',1,2)<>0;
答案：AB  
instr如果第3个参数为负数，则从右边开始查找，但是返回的值是从左边开始计算的
例如：select instr(‘ababab’,’a’,-1,3) from dual;返回的值是1，即从右边第1个字符开始查找，第3次出现a的位置是从左边数第1个

ZHAODONG

84. View the Exhibit and examine the structure of the EMPLOYEES table.（查看表结构）
Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:（表数据如下）
ENAME      HIREDATE
SMITH       17-DEC-80
ALLEN       20-FEB-81
WARD       22-FEB-81
You want to generate a list of user IDs as follows:（你想获取如下的一个列表）
USERID
Smi17DEC80
All20FEB81
War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-')) "USERID"
FROM employees;
What is the outcome?

A. It executes successfully and gives the correct output.（成功给出结果）
B. It executes successfully but does not give the correct output.（成功结果不正确）
C. It generates an error because the REPLACE function is not valid.（报错因为REPLACE无效）
D. It generates an error because the SUBSTR function cannot be nested in the CONCAT function.
（报错，因为SUBSTR函数不能被嵌套在CONCAT函数中）
答案：A

ZHAODONG

85. View the Exhibit and examine the structure and data in the INVOICE table.（查看表结构）
Which statements are true regarding data type conversion in expressions used in queries?  (Choose all that apply.)
关于查询中的表达式中的数据类型转换，哪句话是正确的？（选择所有合适的）

A. inv_amt ='0255982'  : requires explicit conversion
B. inv_date > '01-02-2008'  : uses implicit conversion
C. CONCAT(inv_amt,inv_date)  : requires explicit conversion
D. inv_date = '15-february-2008'    :  uses implicit conversion
E. inv_no BETWEEN '101' AND '110'  :  uses implicit conversion
答案：DE
A把0255982隐式转换，B给出的时间串需要使用显式转换，因为与默认日期格式不一样
C使用隐式转换，转换成字符型

ZHAODONG

86. Examine the structure and data of the CUST_TRANS table:（查看表结构和数据）
CUST_TRANS
Name         Null           Type
CUSTNO       NOT NULL      CHAR(2)
TRANSDATE                   DATE
TRANSAMT                   NUMBER(6,2)
CUSTNO  TRANSDATE      TRANSAMT
11       01-JAN-07       1000
22       01-FEB-07       2000
33       01-MAR-07      3000
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table.
（日期使用的默认dd-mon-rr格式存储）
Which SQL statements would execute successfully? (Choose three .)
（哪一个SQL语句能执行成功，选择三个）
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate  =  '01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno > '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

答案：ACD
A使用隐式转换把‘10’转换成数值型，B条件中需要使用显式转换，E中的’A’不能转换成数值所以会报错

ZHAODONG

87. You want to display the date for the first Monday of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;
What is the outcome?
A. It executes successfully and returns the correct result.
B. It executes successfully but does not return the correct result.
C. It generates an error because TO_CHAR should be replaced with TO_DATE.
D. It generates an error because rrrr should be replaced by rr in the format string.
E. It generates an error because fm and double quotation marks should not be used in the format string.
87. 你想显示下个月的第一个星期一的日期，执行下面的命令，结果是什么？
A.执行成功,给出正确结果
B.执行成功，但不返回结果
C.报错因为TO_CHAR应该被TO_DATE替换
D.报错因为rrrr应该被rr替换
E.报错因为fm和双引号不能用于格式字符串

答案：A
LAST_DAY显示本月的最后一天的日期，NEXT_DAT函数返回输入日期开始，紧随其后的指定星期对应的日期

ZHAODONG

88. You need to calculate the number of days from 1st January 2007 till date.
（你需要计算从2007.1.1到现在的天数）
Dates are stored in the default format of dd-mon-rr.
Which SQL statements would give the required output? (Choose two .)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;
D. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;
E. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;

答案：BC

ZHAODONG

89. You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'.需要把11-oct-2007显示为Eleventh of October, Two Thousand Seven
Which SQL statement would give the required result?
A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year')
FROM DUAL;
B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year')
FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year')
FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year'))
FROM DUAL;

答案：C
AD的语法不正确，B如果不属于转换日期格式标识符需要使用双引号引起，如”of”


如果是字符格式化，指定fm参数后将仅返回指定属性实际所占长度(不再以空格填充)。
如果是数值格式化，fm参数会自动舍弃被格式化元素的前置0

ZHAODONG

90. Examine the structure and data in the PRICE_LIST table:（查看表结构和数据）
name            Null           Type
PROD_ID        NOT NULL     NUMBER(3)
PROD_PRICE                   VARCHAR2(10)
PROD_ID    PROD_PRICE
100         $234.55
101         $6,509.75
102         $1,234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
（你计划给product price打折25%，并使用与PROD_PRICE相同格式显示打折后的值）
Which SQL statement would give the required result?
A. SELECT TO_CHAR(prod_price* .25,'$99,999.99')
FROM PRICE_LIST;
B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00')
FROM PRICE_LIST;
C. SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')
FROM PRICE_LIST;
D. SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')
FROM PRICE_LIST;
答案：C，因为PROD_PRICE里带有$，所有需要先TO_NUMBER转换成数据型后才能进行计算，然后再TO_CHAR转换成字符型显示

ZHAODONG

91. View the Exhibit and examine the structure of the PROMOTIONS table.（查看表结构）
Which two SQL statements would execute successfully? (Choose two.)
哪两个SQL语句能执行成功？（选择两个）

A. UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000';
B. SELECT promo_begin_date
FROM promotions
WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
C. UPDATE promotions
SET promo_cost = promo_cost+ 100
WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8));
D. SELECT TO_CHAR(promo_begin_date,'dd/month')
FROM promotions
WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98'));
答案：AB
C的SUBSTR的结果是’2000’,TO_DATE需要使用YYYY格式符才能转换
D里的TO_DATE转换的日期如果与默认格式不一样，也需要使用格式符转换

ZHAODONG

92. View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.（查看表的数据与PROMO_END_DATE输出的所需格式）
Which two queries give the correct result? (Choose two.)
哪两个查询能给出正确结果？（选择两个）

A. SELECT promo_name, TO_CHAR(promo_end_date,'Day') ', '
TO_CHAR(promo_end_date,'Month') ' '
TO_CHAR(promo_end_date,'DD, YYYY') AS last_day
FROM promotions;
B. SELECT promo_name,TO_CHAR (promo_end_date,'fxDay') ', '
TO_CHAR(promo_end_date,'fxMonth') ' '
TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day
FROM promotions;
C. SELECT promo_name, TRIM(TO_CHAR(promo_end_date,'Day')) ', '
TRIM(TO_CHAR(promo_end_date,'Month')) ' '
TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
FROM promotions;
D. SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')','
TO_CHAR(promo_end_date,'fmMonth') ' '
TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
FROM promotions;
答案：CD
A不正确因为都是默认的显示长度一样，即显示结果会对齐显示
B fx加到此处没什么作用,fx为精确匹配格式，
例如：'2012/12-12','fxyyyy/mm-dd'（匹配），如果是'2012-12-12','fxyyyy/mm/dd'（不匹配）

ZHAODONG

93. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Using the CUSTOMERS table,you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.
（使用CUSTOMERS表，你想获取一个报表，显示所有客户增长15%的credit limit.如果客户没有credit limit则显示Not Available）
Which SQL statement would produce the required result?（哪个SQL语句给出所需结果）

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT"
FROM customers;
B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT"
FROM customers;
C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT"
FROM customers;
D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT"
FROM customers;
答案：D
NVL介绍：如果参数1为空，则返回参数2的值，如果参数1不为空，则返回参数1的值，当两个参数类型不同时，oracle会将两个参数进行隐式转换，如果不能隐式转换刚会报错，转换规则如下：
1.如果参数1为字符型，则把参数2转换为参数1的类型，返回值为VARCHAR2
2.如果参数1为数值型，则判断两个参数的最高数值优先级（如双精实数比单精实数优先级高），然后转换成高优先级的数值，返回该类型的值.
A的使用NVL函数后再*0.15,如果是Not Available会报错
BC在转换Not Available为数值类型时会报错

ZHAODONG

94. Examine the structure of the PROGRAMS table:（查看表结构）
Name            Null          Type
PROG_ID        NOT NULL     NUMBER(3)
PROG_COST                   NUMBER(8,2)
START_DATE    NOT NULL     DATE
END_DATE                     DATE
Which two SQL statements would execute successfully? (Choose two.)
哪两个SQL语句执行成功？（选择两个）
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)
FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')
FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
FROM programs;

答案：AD
B虽然nvl函数可以进行隐式转换（sysdate可以隐式转换为一个天数Julian Days），但是如果to_date转换一个数值时会报错。C是NVL第二个参数在进行隐式转换的时候报错。Months_between函数：如果第一个参数的日期大于第二个参数参数的日期，则返回正数，否则返回负数

ZHAODONG

95. The PRODUCTS table has the following structure:（查看表结构）
name                    Null                 Type
PROD_ID                NOT NULL           NUMBER(4)
PROD_NAME                                 VARCHAR2(25)
PROD_EXPIRY_DATE                         DATE
Evaluate the following two SQL statements:（评估下面两个SQL语句）
SQL>SELECT prod_id, NVL2(prod_expiry_date, prod_expiry_date + 15,'')
FROM products;
SQL>SELECT prod_id, NVL(prod_expiry_date, prod_expiry_date + 15)
FROM products;
Which statement is true regarding the outcome?（关于结果哪个语句是正确的？）
A. Both the statements execute and give different results.
（两个都执行成功，但给出不同的结果）
B. Both the statements execute and give the same result.
（两个都执行成功，并给出相同的结果）
C. Only the first SQL statement executes successfully.（第一个执行成功）
D. Only the second SQL statement executes successfully（第二个执行成功）

答案：A(第一条语句当为null时输出第二个参数’’也是空,不为空时输出和一个prod_expiry_date + 15，而第二条语句当为null时输出第二个参数，而第二个参数有空值运算，所以也为null，而不为空时输出prod_expiry_date)
NVL2：如果参数1不为空，返回参数2的值，如果为空，返回参数3的值，如果参数2与参数3的数据类型不一样则需要隐式转换，转换规则与93题的NVL一样，’’相当于null，null可以转换成任何值。

ZHAODONG

96. Examine the structure of the INVOICE table.（查看表结构）
name                Null           Type
INV_NO             NOT NULL     NUMBER(3)
INV_DATE                          DATE
INV_AMT                           NUMBER(10,2)
Which two SQL statements would execute successfully? (Choose two.)
A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')
FROM invoice;
B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')
FROM invoice;
C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)
FROM invoice;
D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')
FROM invoice;

答案：AC
BD进行隐式转换时报错，C可以把sysdate转换成一个天数（Julian Days）

ZHAODONG

97. View the Exhibit and evaluate the structure and data in the CUST_STATUS table.
You issue the following SQL statement:(查看表结构和数据，执行下面的语句)
SQL> SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000)"BONUS"
FROM cust_status;
Which statement is true regarding the execution of the above query?
（关于上面的查询哪句话是正确的？）

A. It produces an error because the AMT_SPENT column contains a null value.
（报错，因为AMT_SPENT包含空值）
B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.（显示所有AMT_SPENT小于CREDIT_LIMIT的员工有1000元奖金）
C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .
（显示所有AMT_SPENT等于CREDIT_LIMIT或AMT_SPENT为空的员工有1000元奖金）
D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.
（报错，因为在使用NVL2函数之前，需要使用TO_NUMBER函数把NULLIF函数的结果进行转换）

答案：C
NULLIF：如果参数1和参数2相等，则返回空，如果不相等，则返回参数1，参数1不能直接指定为NULL（literal NULL），但是如果列中有空是可以的，如果两个参数不是数值类型的参数，则数据类型必须一致，否则报错

ZHAODONG

98. Which statement is true regarding the COALESCE function?
A. It can have a maximum of five expressions in a list.
B. It returns the highest NOT NULL value in the list for all rows.
C. It requires that all expressions in the list must be of the same data type.
D. It requires that at least one of the expressions in the list must have a NOT NULL value
98.关于COALESCE函数哪一句话是正确的？
A. 最大只能包含5个表达式列表
B. 返回列表中最高的一个非空值
C. 列表中所有表达式的数据类型必须一致
D. 列表中至少要有一个表达式为非空

答案：C
COALESCE(expr1,expr2[,…])：返回表达式列表中第一个非空的表达式，必须指定至少两个表达式，表达式数据类型必须一致.

ZHAODONG

99. View the Exhibit and examine the structure of the PROMOTIONS table.（查看表结构）
Using the PROMOTIONS table, you need to find out the average cost for all promos in the ranges $0-2000 and $2000-5000 in category A
（使用PROMOTIONS表，你需要找出category A中在$0-2000范围和$2000-5000范围内的所有promos的average cost）
You issue the following SQL statement:（执行下面的SQL语句）
SQL>SELECT AVG(CASE
WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A'
then promo_cost
ELSE null END) "CAT_2000A",
AVG(CASE
WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A'
THEN promo_cost
ELSE null END) "CAT_5000A"
FROM promotions;
What would be the outcome?（结果是什么？）

A. It executes successfully and gives the required result.（执行成功给出所需结果）
B. It generates an error because NULL cannot be specified as a return value.
（报错，因为NULL不能作为一个返回值被指定）
C. It generates an error because CASE cannot be used with group functions.
（报错，因为CASE不能用在组函数中）
D. It generates an error because multiple conditions cannot be specified for the WHEN clause.（报错，因为WHEN子句不能指定多个条件）
答案：A，返回的NULL值在计算平均值时会不计算，例如返回4行，有1行为空，则平均值就是总数除以3

ZHAODONG

100. View the Exhibit and examine the structure of the PROMOTIONS table.(查看表结构)
Which SQL statements are valid? (Choose all that apply.)（选择所有有效的SQL语句）

A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,promo_cost * 0.25, 100) "Discount"
FROM promotions;
B. SELECT promo_id, DECODE(promo_cost, 10000,DECODE(promo_category, 'G1', promo_cost *.25, NULL),NULL) "Catcost"
FROM promotions;
C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),NULL, promo_cost*.25, 'N/A') "Catcost"
FROM promotions;
D. SELECT promo_id, DECODE(promo_cost, >10000, 'High',<10000, 'Low') "Range"
FROM promotions;
答案：AB
C不正确，因为把’N/A’隐式转换成数值时不成功。D语法不正确。
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

101. Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE（查看PROMOTIONS表中PROMO_BEGIN_DATE列的数据）
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
（你想显示在1999 和2000年开始的promotions的数量）
Which query gives the correct output?（哪一个查询给出正确结果？）
A. SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",
SUM(DECODE(SUBSTR(promo_begin_date,8),'99',1,0)) "1999"
FROM promotions;
B. SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1
ELSE 0 END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1
ELSE 0 END) "2000"
FROM promotions;
C. SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1
ELSE 0 END) "1999",COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1
ELSE 0 END) "2000"
FROM promotions;
D. SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 0)) "1999",
COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,
0)) "2000"
FROM promotions;

答案：A
BC比对的条件不正确，D截取不正确

ZHAODONG

102. Examine the structure of the TRANSACTIONS table:（查看表结构）
name            Null            Type
TRANS_ID        NOT NULL        NUMBER(3)
CUST_NAME                      VARCHAR2(30)
TRANS_DATE                      TIMESTAMP
TRANS_AMT  NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon. The value zero should be displayed for transactions where the transaction amount has not been entered.（你想显示在中午12点之前完成的transactions的日期、时间和transaction数量。如果transaction数量没有值则显示0。）
Which query gives the required result?（哪一个查询给出所需结果？）
A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
TO_CHAR(trans_amt,'$99999999D99')
FROM transactions
WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND
COALESCE(trans_amt,NULL)<>NULL;
B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL(TO_CHAR(trans_amt,'$99999999D99'),0)
FROM transactions
WHERE TO_CHAR(trans_date,'hh24') < 12;
C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),
COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0)
FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;
D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'),
NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0)
FROM transactions
WHERE TO_DATE(trans_date,'hh24') < 12;

答案：B
A的WHERE条件不正确，并且显示不满足条件，CD的TO_NUMBER函数转换会出错

ZHAODONG

103. Examine the structure of the TRANSACTIONS table:（查看表结构）
name          Null          Type
TRANS_ID      NOT NULL     NUMBER(3)
CUST_NAME                  VARCHAR2(30)
TRANS_DATE                 DATE
TRANS_AMT                  NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:（你想显示transaction date，并指定该日期是工作日还是周末）
SQL>SELECT TRANS_DATE,CASE
WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'
ELSE 'weekday'
END "Day Type"
FROM transactions;
SQL>SELECT TRANS_DATE, CASE
WHEN TO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'
ELSE  'weekend'
END "Day Type"FROM transactions;
Which statement is true regarding the above queries?（关于上面的查询哪句话是正确的？）
A. Both give wrong results.（两个给出错误结果）
B. Both give the correct  result.（两个给出正确结果）
C. Only the first query gives the correct result.（第一个给出正确结果）
D. Only the  second query gives the correct result.（第二个给出正确结果）

答案：C
因为第二条语句中的BETWEEN 'MONDAY' AND 'FRIDAY'，该语句这样用只能判断字符串的大小，而不是包含周一到周五，具有迷惑性。注意当使用TO_CHAR(trans_date,'DAY')转换出的日期是固定长度的，会有空格填充不够的长度，C答案感觉也不对，应该是A，因为如果TRANS_DATE有空值的话，会导致显示weekday，所以结果也不对。

ZHAODONG

104. Examine the structure of the PROMOS table:（查看表结构）
name                      Null              Type
PROMO_ID                NOT NULL        NUMBER(3)
PROMO_NAME                              VARCHAR2(30)
PROMO_START_DATE     NOT NULL        DATE
PROMO_END_DATE                         DATE
You want to generate a report showing promo names and their duration (number of days). If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed.（显示promo names和their duration，如果PROMO_END_DATE没有值，则显示'ONGOING'）
Which queries give the correct output? (Choose all that apply.)选择所有合适的
A.SELECT promo_name,TO_CHAR(NVL(promo_end_date-promo_start_date,'ONGOING'))
FROM promos;
B. SELECT
promo_name,COALESCE(TO_CHAR(promo_end_date-promo_start_date),'ONGOING')
FROM promos;
C.SELECT promo_name,NVL(TO_CHAR(promo_end_date-promo_start_date),'ONGOING')
FROM promos;
D. SELECT
promo_name,DECODE(promo_end_date-promo_start_date,NULL,'ONGOING',promo_end_date-promo_start_date)
FROM promos;
E. SELECT
promo_name,decode(coalesce(promo_end_date,promo_start_date),null,'ONGOING',
promo_end_date - promo_start_date)
FROM promos;

答案：BCD
A不正确，因为隐式转换'ONGOING'为数值类型的会报错。
E不正确，虽然语法没有错误，但是条件不满足，coalesce返回第一个不为空的值，因为promo_start_date肯定不为空，所以即使promo_end_date为空，coalesce也会返回promo_start_dat，所以都会显示promo_end_date - promo_start_date值，即当promo_end_date为空时，还是会显示空（注意，如果你直接指定NULL-sysdate会报转换数据类型错，而在字段里就可以减，并返回空）