- Home
- Database
- Oracle 12 C
- OCA Oracle 12 C SQl Fundamentals(1Z0-061)
66.
Using the SALESPERSON_REVENUE table described here, which statements will properly
display the TOTAL_REVENUE ( CAR_SALES + WARRANTY_SALES ) of each salesperson?
- A.SELECT salesperson_id, car_sales, warranty_sales, car_sales + warranty_ sales total_salesFROM salesperson_revenue;
- B.SELECT salesperson_id, car_sales, warranty_sales, car_sales + NVL2(warranty_sales,0) total_salesFROM salesperson_revenue;
- C.SELECT salesperson_id, car_sales, warranty_sales, NVL2(warranty_sales, car_sales + warranty_sales, car_sales) total_salesFROM salesperson_ revenue;
- D.SELECT salesperson_id, car_sales, warranty_sales, car_sales + COALESCE(car_sales, warranty_sales, car_sales + warranty_sales) total_ salesFROM 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. Therefore, COALESCE will always return CAR_SALES . |
67.
What would be the result of executing the following SQL, if today’s date were
February 28, 2009?
SELECT ADD_MONTHS('28-FEB-09', -12) from dual;
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 |
68.
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;
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 results.
- 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. |
69.
What will the following SQL statement return?
SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;
SELECT COALESCE(NULL,'Oracle ','Certified') FROM dual;
- A.NULL
- B.Oracle
- C.Certified
- D.Oracle Certified
- Answer & Explanation
- Report
Answer : [B]
Explanation :
Explanation :
| The COALESCE function returns the first non- NULL parameter, which is the character string 'Oracle' . |
70.
Which expression will always return the date one year later than the current date?
- A.SYSDATE + 365li>
- B.SYSDATE + TO_YMINTERVAL('01-00')
- C.CURRENT_DATE + 1
- D.NEW_TIME(CURRENT_DATE,1,'YEAR')
- E.None of the above
- Answer & Explanation
- Report
Answer : [E]
Explanation :
Explanation :
| Option A will not work if there is a February 29 (leap year) in the next 365 days. Option B will always add one year to the present date, except if the current date is Feb- ruary 29 (leap year). Option C will return the date one day later. NEW_TIME is used to return the date/time in a different time zone. ADD_MONTHS (SYSDATE,12) can be used to achieve the desired result. |
|
|
||||||||||||||||||||||||||||
