Oracle Database SQL — Question 130

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six months since hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week.
Which query can be used?

Answer options

• A. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
• B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
• C. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
• D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

Correct answer: D

Explanation

The correct option is D because it correctly calculates the date six months after the HIRE_DATE and then finds the next occurrence of 'MONDAY' after that date. Option A is incorrect as it does not correctly apply NEXT_DAY to the calculated date. Option B is incorrect because it uses a numeric value (1) instead of the day name for NEXT_DAY. Option C is incorrect as it improperly uses MONTHS_BETWEEN, which does not relate to finding the next Monday after the six-month period.