21.
Consider the following two SQL statements, and choose the best option:
1. SELECT TO_DATE('30-SEP-07','DD-MM-YYYY') from dual;
2. SELECT TO_DATE('30-SEP-07','DD-MON-RRRR') from dual;
- [A]Statement 1 will error; 2 will produce result.
- [B]The resulting date value from the two statements will be the same.
- [C]The resulting date value from the two statements will be different.
- [D]Both statements will generate an error.
<
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Statement 1 will result in 30-SEP-0007, and statement 2 will result in 30-SEP-2007. The RR and RRRR formats derive the century based on the current date if the century is not specified. The YY format will use the current century, and the YYYY format expects the century in the input. |
22.
What will be the result of executing the following SQL, if today's date is February 28, 2009?
SELECT ADD_MONTHS('28-FEB-09', -12) from dual;
- [A]28-FEB-10
- [B]28-FEB-08
- [C]29-FEB-08
- [D]28-JAN-08
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
|
The ADD_MONTHS function returns the date d plus i months. If |
23.
Using the SALESPERSON_REVENUE table described here, which statements will properly display
the TOTAL_REVENUE (CAR_SALES + WARRANTY_SALES) of each salesperson?
Column Name salesperson_id car_sales warranty_sales Key Type pk NULLs/Unique NN NN FK Table Datatype NUMBER NUMBER NUMBER Length 10 11,2 11,2
- [A]SELECT salesperson_id, car_sales, warranty_sales, car_sales + warranty_ sales total_sales FROM salesperson_revenue;
- [B]SELECT salesperson_id, car_sales, warranty_sales, car_sales + NVL2(warranty_sales,0) total_sales FROM salesperson_revenue;
- [C]SELECT salesperson_id, car_sales, warranty_sales, NVL2(warranty_sales, car_sales + warranty_sales, car_sales) total_sales FROM salesperson_revenue;
- [D]SELECT salesperson_id, car_sales, warranty_sales, car_sales + COALESCE(car_sales, warranty_sales, car_sales + warranty_sales) total_sales FROM salesperson_revenue;
- Answer & Explanation
- Report
Answer : [C]
Explanation :
Explanation :
| Option A will result in NULL TOTAL_SALES for rows where there are NULL WARRANTY_ SALES. Option B is not the correct syntax for NVL2, because it requires three arguments. With option C, if WARRANTY_SALES is NULL, then CAR_SALES is returned; otherwise, CAR_ SALES+WARRANTY_SALES is returned. The COALESCE function returns the first non-NULL argument and could be used to obtain the desired results, but the first argument here is CAR_SALES, which is not NULL, and therefore COALESCE will always return CAR_SALES. |
24.
Which date components does the CURRENT_TIMESTAMP function display?
- [A]Session date, session time, and session time zone offset
- [B]Session date and session time
- [C]Session date and session time zone offset
- [D]Session time zone offset
- Answer & Explanation
- Report
Answer : [A]
Explanation :
Explanation :
| The CURRENT_TIMESTAMP function returns the session date, session time, and session time zone offset. The return datatype is TIMESTAMP WITH TIME ZONE. |
25.
Which two SQL statements will replace the last two characters of last_name with 'XX' in
the employees table when executed? (Choose two.)
- [A]SELECT RTRIM(last_name, SUBSTR(last_name, LENGTH(last_name)-1)) || 'XX' new_col FROM employees;
- [B]SELECT REPLACE(last_name, SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;
- [C]SELECT REPLACE(SUBSTR(last_name, LENGTH(last_name)-1), 'XX') new_col FROM employees;
- [D]SELECT CONCAT(SUBSTR(last_name, 1,LENGTH(last_name)-2), 'XX') new_col FROM employees;
- Answer & Explanation
- Report
Answer : [A, D]
Explanation :
Explanation :
| The SUBSTR function in option A would return the last two characters of the last name. These two characters are right-trimmed using the RTRIM function. The result would be the first portion of the last name and is concatenated to 'XX'. Option B also would do the same as A, but would replace all the occurrences of the last two characters (Paululul will be PaXXXXXX instead of PaululXX). Option C would return only the last two characters of the last name. The SUBSTR function in option D would return the first character through the last –2 characters. 'XX' is concatenated to the result. |
|
|
||||||||||||||||||||||||||||
