- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
21.
Which single-row function could you use to return a specific portion of a character string?
- A.INSTR
- B.SUBSTR
- C.LPAD
- D.LEAST
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| SUBSTR returns part of the string. INSTR returns a number. LPAD adds to a character string. LEAST does not change an input string. |
22.
The data in the PRODUCT table is as described here. The bonus amount is calculated as
the lesser of 5 percent of the base price or 20 percent of the surcharge.
Which of the following statements will achieve the desired results?
- A.SELECT sku, name, LEAST(base_price * 1.05, surcharge * 1.2)FROM products;
- B.SELECT sku, name, LEAST(NVL(base_price,0) * 1.05, surcharge * 1.2)FROM products;
- C.SELECT sku, name, COALESCE(LEAST(base_price*1.05, surcharge * 1.2), base_price * 1.05)FROM products;
- D.A, B, and C will all achieve the desired results.
- D.None of these statements will achieve the desired results.
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Options A and B do not account for NULL surcharges correctly and will set the bonus to NULL where the surcharge is NULL. In option B, the NVL function is applied to the base_price column instead of the surcharge column. In option C, the LEAST function will return a NULL if surcharge is NULL, in which case BASE_PRICE * 1.05 would be returned from the COALESCE function. |
23.
Which function(s) accept arguments of any datatype? (Choose all that apply.)
- A.SUBSTR
- B.NVL
- C.ROUND
- D.DECODE
- E.SIGN
- Answer & Explanation
- Report
Answer : [B, D]
Explanation :
Explanation :
| ROUND does not accept character arguments. SUBSTR accepts only character arguments. SIGN accepts only numeric arguments. |
24.
What will be returned by SIGN(ABS(NVL(-32,0)))?
- A.1
- B.32
- C.-1
- D.0
- E.Null
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
|
The functions are evaluated from the innermost to outermost, as follows: SIGN(ABS(NVL(-32,0))) = SIGN(ABS(-32)) = SIGN(32) = 1 |
25.
How will the results of the following two statements differ?
Statement 1:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city;
Statement 2:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city
GROUP BY state;
Statement 1:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city;
Statement 2:
SELECT MAX(longitude), MAX(latitude)
FROM zip_state_city
GROUP BY state;
- A.Statement 1 will fail because it is missing a GROUP BY clause.
- B.Statement 2 will return one row, and statement 1 may return more than one row.
- C.Statement 2 will fail because it does not have the columns used in the GROUP BY clause in the SELECT clause.
- D.Statement 1 will display one row, and statement 2 will display two columns for each state.
- Answer & Explanation
- Report
Answer : [D]
Explanation :
Explanation :
| Even though you do not have a state column in the SELECT clause, having it in the GROUP BY clause will group the results by state, so you end up getting two values (two columns) for each state. |
|
|
||||||||||||||||||||||||||||
