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

ZHAODONG

112. View the Exhibit and examine the structure of the PROMOTIONS table.（查看表结构）
Evaluate the following SQL statement:（评估下面的SQL语句）
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST')
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above query generates an error on execution.（上面的查询会报错）
Which clause in the above SQL statement causes the error?（哪一个子句报的错）

A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY
答案：C
GROUP BY后面不能使用列别名
ORDER BY可以使用列别名，但要完全匹配列别名，例如有双引号的别名要带双引号，并且ORDER BY子句必须要放在SELECT语句的最后。

ZHAODONG

111. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
（哪条语句显示每个城市中每个收入水平的最高信用额度）

A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit )
FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;
B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ;
D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

答案：B
AC不正确，因为GROUP BY子句不满足条件
D报错，因为GROUP BY子句中不能使用MAX

ZHAODONG

110. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK.
（使用CUSTOMERS表，你需要获取一个报表，显示居住在WASHINGTON和NEW YORK的客户的平均credit limit）
Which SQL statement would produce the required result?（哪条SQL语句给出所需结果？）

A. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;
B. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D. SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');
答案：C
AB不正确，因为GROUP BY里的分组不满足条件
D不正确，因为需要使用GROUP BY子句

ZHAODONG

109. Examine the structure of the MARKS table:（查看表结构）
name                Null             Type
STUDENT_ID         NOT NULL       VARCHAR2(4)
STUDENT_NAME                      VARCHAR2(25)
SUBJECT1                            NUMBER(3)
SUBJECT2                            NUMBER(3)
SUBJECT3                            NUMBER(3)
Which two statements would execute successfully? (Choose two.)
哪两个语句可以执行成功？（选择两个）
A. SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE 'R%';
C. SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;

答案：CD
A组函数不能用于WHERE子句中
B需要使用group by子句

ZHAODONG

108. Which two statements are true regarding the COUNT function? (Choose two.)
（关于COUNT函数哪两个句子是正确的？选择两个）
A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.（COUNT函数只能用于CHAR,VARCHAR2,NUMBER数据类型）
B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.（count(*)返回包括重复行和NULL的行）
C. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
（COUNT(cust_id)返回包括重复行和NULL的行）
D. COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
（COUNT(DISTINCT inv_amt)返回不包括重复值和NULL的行）
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.（使用带有DISTINCT关键字的COUNT函数的SELECT语句不能含有WHERE子句）

答案：BD
A不正确，因为还可以为DATE型
C不正确，因为不包括NULL行

ZHAODONG

107. View the Exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
查看表结构和数据，哪两条语句执行成功？

A. SELECT AVG(inv_date )
FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date))
FROM invoice;
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt)
FROM invoice;
答案：BD
A不正确，因为AVG的参数为数值型或能隐式转换成数据值的非数据值参数，在隐式转换inv_date时会报错。
C不正确，嵌套组函数需要使用group by子句

ZHAODONG

106. Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:（查看表数据）
LIST_PRICE    MIN_PRICE
10000          8000
20000
30000          30000
Which two expressions give the same output? (Choose two.)哪两个表达式结果相同
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0)
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0)

答案：BD
A显示：10000   20000   0
B显示：10000   20000   30000
C显示：8000            30000
D显示：10000   20000   30000

ZHAODONG

105. 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       NOT NULL        DATE
You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.（你想显示promos names，如果开始和结束为同一天的promos使用'Same Day'显示）
Which query gives the correct output?（哪个给出正确输出？）
A. SELECT promo_name,NVL(NULLIF(promo_start_date,promo_end_date),'Same Day')
FROM promos;
B. SELECT promo_name,NVL(TRUNC(promo_end_date-promo_start_date),'SameDay')
FROM promos;
C. SELECT promo_name,NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)),NULL,'SameDay')
FROM promos;
D. SELECT promo_name,DECODE((NULLIF(promo_start_date,promo_end_date)),NULL,'Same day')
FROM promos;

答案：D
A不正确，因为'Same Day'隐式转换成日期时报错。
B不正确，不仅不满足条件，而且'Same Day'隐式转换成数值时报错。
C不正确，不满足条件

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会报转换数据类型错，而在字段里就可以减，并返回空）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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