Oracle Database Sql Certified Associate · Free Practice Question Medium
Question 38
View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement.
Exhibit: 1
- SELECT first_name, employee_id, NEXT_DAY(ADD_Months(hire_date, 6), 1) "Review")
- FROM employees;
Given: The review date is the first Monday after the completion of six months of the hiring. The NLS_TERRITORY parameter is set to AMERICA in the session.
Required:
Retrieve the FIRST_NAME, EMPLOYEE_ID, and review date for employees.
Avoid the review dates where the given Monday is a holiday.
Identify the result of the query.
-
A
The query would execute to give the desired output.
-
B
The query would not execute because the NEXT_DAY function accepts a string as argument.
-
C
The query would execute but the output would give review dates that are on weekends.
-
D
The query would not execute because date functions cannot be nested.
Reveal correct answer
Correct answer: C
Explanation
The query would execute successfully and return a Monday after 6 months, but there is no built-in provision to check whether that Monday is a recognized business day (e.g., not a public holiday).
The query:
- SELECT first_name, employee_id, NEXT_DAY(ADD_Months(hire_date, 6), 1) "Review")
- FROM employees;
will execute without errors.
NEXT_DAY(..., 1)correctly finds the next Monday after 6 months fromhire_date(with1meaning Monday underNLS_TERRITORY='AMERICA').However, the query does not account for holidays or company-specific non-business days — it only avoids weekends implicitly, by choosing Monday.
A. The query is valid and will execute successfully to retrieve the desired output of FIRST_NAME, EMPLOYEE_ID, and review date for employees.
B. The NEXT_DAY function in Oracle SQL does not require a string as an argument, so the query will execute without any issues related to the argument type for the function.
C. The query will execute successfully, but it may result in review dates that fall on weekends due to the nature of the calculation based on the first Monday after six months of hiring.
D. Date functions can be nested in SQL queries, so the query will execute without any issues related to nesting date functions.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
