106. Examine the data inthe LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:
LIST_PRICE MIN_PRICE
10000 8000
20000
30000 30000
Which two expressionsgive 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)
Answer: BD
解析：
引用官方文档：
NULLIF compares expr1 and expr2. If theyare equal, then the function returns null.
If they are not equal, then the functionreturns expr1. You cannot specify the literal
NULL for expr1
NVL lets you replace null (returned as ablank) with a string in the results of a query. If
expr1 is null, then NVL returns expr2. Ifexpr1 is not null, then NVL returns expr1.
COALESCE returns the first non-null expr inthe expression list. You must specify at
least two expressions. If all occurrencesof expr eva luate to null, then the function
returns null.
NVL2 lets you determine the value returned bya query based on whether a specified
expression is null or not null. If expr1 isnot null, then NVL2 returns expr2. If expr1
is null, then NVL2 returns expr3.
107. View the Exhibitand examine the structure and data in the INVOICE table.
Which two SQL statementswould execute successfully (Choose two.)
A. SELECT AVG(inv_date )
FROM invoice;
B. SELECTMAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECTMAX(AVG(SYSDATE - inv_date))
FROM invoice;
D. SELECT AVG( inv_date- SYSDATE), AVG(inv_amt)
FROM invoice;
Answer: BD
解析：
引用官方文档：
This function takes as an argument anynumeric data type or any nonnumeric data
type that can be implicitly converted to anumeric data type. The function returns the
same data type as the numeric data type ofthe argument.
所以A错
A later date is considered greater than anearlier one. For example, the date equivalent
of '29-MAR-2005' is less than that of'05-JAN-2006' and '05-JAN-2006 1:35pm' is greater
than '05-JAN-2005 10:09am'.
Oracle compares VARCHAR2 values usingnonpadded
comparison semantics.
所以B正确
C选项中avg得到单一结果，无法使用max函数
所以C错误
D选项中inv_date-sysdate得到数字，所以能使用avg
所以D正确
108. Which twostatements are true regarding the COUNT function (Choose two.)
A. The COUNT functioncan be used only for CHAR, VARCHAR2, and NUMBER data types.
B. COUNT(*) returns thenumber of rows including duplicate rows and rows containing NULL value in
any of the columns.
C. COUNT(cust_id)returns the number of rows including rows with duplicate customer IDs and NULL
value in the CUST_IDcolumn.
D. COUNT(DISTINCTinv_amt)returns the number of rows excluding rows containing duplicates and
NULL values in theINV_AMT column.
E. A SELECT statementusing the COUNT function with a DISTINCT keyword cannot have a
WHERE clause.
Answer: BD
解析：
A选项，count()还可以用在date等数据类型的行上
B选项，如果count不指定distinct的话，就会返回所有的重复行
C选项，count不会计算列为空的
D选项，明确指定了distinct，就不会返回重复行，当然也不会返回空行
E选项，当然可以使用where，测试：
scott@ORCL>select count(distinct comm)from emp where sal>1000;
COUNT(DISTINCTCOMM)
-------------------
4
109. Examine thestructure of the MARKS table:
name Null Type
STUDENT_ID NOT NULLVARCHAR2(4)
STUDENT_NAMEVARCHAR2(25)
SUBJECT1 NUMBER(3)
SUBJECT2 NUMBER(3)
SUBJECT3 NUMBER(3)
Which two statementswould execute successfully (Choose two.)
A. SELECT student_name,subject1
FROM marks
WHERE subject1 >AVG(subject1);
B. SELECTstudent_name,SUM(subject1)
FROM marks
WHE