Based on table CLUB, which SQL statement will display the total FEE collected…
2017
Based on table CLUB, which SQL statement will display the total FEE collected from MONTHLY members till date? ----------------------------------------
- A.
SELECT SUM(MONTHS_BETWEEN(SYSDATE, DOJ) * FEE) FROM CLUB WHERE TYPE = 'MONTHLY';
- B.
SELECT SUM(MONTH_BETWEEN(DOJ, SYSDATE) * FEE) FROM CLUB WHERE TYPE = 'MONTHLY';
- C.
SELECT SUM(MONTH_BETWEEN(DOJ, SYSDATE) * FEE) FROM CLUB;
- D.
SELECT MONTHS_BETWEEN(SYSDATE, DOJ) * FEE FROM CLUB WHERE TYPE = 'MONTHLY';
Attempted by 1414 students.
Show answer & explanation
Correct answer: A
Answer: SELECT SUM(MONTHS_BETWEEN(SYSDATE, DOJ) * FEE) FROM CLUB WHERE TYPE = 'MONTHLY';
Why this works:
MONTHS_BETWEEN(SYSDATE, DOJ) gives the number of months from the member's joining date (DOJ) up to today (SYSDATE).
Multiplying that value by FEE yields the total amount collected from each monthly member.
SUM(...) aggregates the totals for all members, and the WHERE clause restricts the calculation to only monthly members.
Note: MONTHS_BETWEEN can return fractional months. If you want to count only full months, apply TRUNC or FLOOR to MONTHS_BETWEEN, for example: SUM(TRUNC(MONTHS_BETWEEN(SYSDATE, DOJ)) * FEE).
Why the other choices are incorrect:
The option that uses MONTH_BETWEEN is invalid because the correct Oracle function name is MONTHS_BETWEEN, and using reversed arguments (DOJ, SYSDATE) gives a negative value.
The option without WHERE TYPE = 'MONTHLY' would include non-monthly members and so does not return the requested total for monthly members only.
The option that omits SUM returns row-level values (one result per member) instead of a single total; aggregation with SUM is required.