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

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

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

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

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

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

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

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

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

113. Examine the structure of the ORDERS table:（查看表结构）
Name                Null                Type
ORDER_ID           NOT NULL          NUMBER(12)
ORDER_DATE        NOT NULL          TIMESTAMP(6)
CUSTOMER_ID       NOT NULL           NUMBER(6)
ORDER_STATUS                          NUMBER(2)
ORDER_TOTAL                           NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following command:（你想查找每年所有orders的total value，你执行下面的命令：）
SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,'yyyy');
Which statement is true regarding the outcome?（关于结果正确的是？）
A. It executes successfully and gives the correct output.（执行成功给出正确结果）
B. It gives an error because the TO_CHAR function is not valid.（报错因为TO_CHAR函数无效）
C. It executes successfully but does not give the correct output.（执行成功但是不能给出正确结果）
D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.（报错，因为SELECT列表中的数据类型转换与GROUP BY子句中的数据类型转换不匹配）
答案：D

ZHAODONG

114. View the Exhibit and examine the structure of the SALES table.（查看表结构）
The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
（下面的查询用于检索那些售出了超过55000台,并且已被定购超过10次的所有产品ID）
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?（关于查询正确的是？）

A. It executes successfully and generates the required result.（执行成功并给出正确结果）
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.（报错，因为COUNT(*)也应该指定到SELECT子句中）
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.（报错，因为COUNT(*)只能用在HAVING子句中，不能在WHERE子句中）
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).（执行成功，但是没有返回结果，因为COUNT(prod_id)应该用于替代COUNT(*)）

答案：C

ZHAODONG

115. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Evaluate the following SQL statement:（评估下面的语句）
SQL> SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?（关于结果哪句话正确）

A. It executes successfully.（执行成功）
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.（报错，因为BETWEEN操作符不能用在HAVING子句中）
C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.（报错，因为WHERE and HAVING子句不能同时用于SELECT子句中）
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.（报错，因为WHERE and HAVING子句不能使用同一列作为条件）
答案：A

ZHAODONG

116. Examine the data in the ORD_ITEMS table:（查看表数据）
ORD_NO     ITEM_NO     QTY
1            111           10
1            222           20
1            333           30
2            333           30
2           444           40
3           111           40
You want to find out if there is any item in the table for which the average maximum quantity is more than 50.（你想找到表中任意item的最大值的平均值是否大于50）
You issue the following query:（执行下面的查询）
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?（关于查询结果正确的是）
A. It executes successfully and gives the correct output.（执行成功并给出正确结果）
B. It gives an error because the HAVING clause is not valid.（报错，因为HAVING子句是无效的）
C. It executes successfully but does not give the correct output.（执行成功，但是不能给出正确结果）
D. It gives an error because the GROUP BY expression is not valid.（报错，因为GROUP BY表达式无效）

答案：B
HAVING子句后面不能有好几个组函数嵌套，只能使用一个组函数