CID, CName and Terms of all those records where Loan Date (L_Date) is after 31st December, 2024.

Q: C_ID, C_Name and Terms of all those records where Loan Date (L_Date) is after 31st December, 2024.

**SQL Query:** ```sql SELECT C_ID, C_Name, Terms FROM CUSTOMERS, LOANS WHERE CUSTOMERS.C_ID = LOANS.C_ID AND L_Date > '2024-12-31'; ``` This joins CUSTOMERS and LOANS on C_ID and returns C_ID, C_Name and Terms only for loans dated after 31 December 2024.

Q: Why is the date written as '2024-12-31' and not '31-12-2024'?

MySQL's standard and default date literal format is YYYY-MM-DD; using DD-MM-YYYY can be misinterpreted or cause errors depending on the server's SQL mode.

Q: Why must C_ID come from a joined query, not just one table?

Because C_ID is the common key linking CUSTOMERS and LOANS — the join condition CUSTOMERS.C_ID = LOANS.C_ID ensures only correctly matched customer-loan pairs appear together in one row.

Structured Query Language (SQL)

SQL Join with Date Comparison Condition

Official Answer

SQL Query:

``sql


SELECT CID, CName, Terms FROM CUSTOMERS, LOANS
WHERE CUSTOMERS.CID = LOANS.CID AND L_Date > '2024-12-31';

This joins CUSTOMERS and LOANS on CID and returns CID, C_Name and Terms only for loans dated after 31 December 2024.

date comparison SQLL_DateYYYY-MM-DD formatjoin conditionSELECT multiple columnsTerms column

Marking Scheme

11 mark for the complete correct query: correct join condition, correct date filter L_Date > '2024-12-31', and correct column list C_ID, C_Name, Terms.

Hint

Compare dates using the 'YYYY-MM-DD' string format; join first, then filter with AND.

Quick Oral Answer

After joining CUSTOMERS and LOANS on C_ID, I filter with L_Date > '2024-12-31' and select C_ID, C_Name and Terms for every loan taken after that date.

Analysis & Explanation

This extends the same join pattern with a date condition instead of a numeric one.

Concept

Dates in MySQL are compared using the standard 'YYYY-MM-DD' string format; L_Date > '2024-12-31' selects every date from 1 January 2025 onward.
CID appears in both tables, so it should be qualified (e.g. CUSTOMERS.CID) in the join condition to avoid ambiguity.

Exam trap

Writing the date in DD-MM-YYYY format (e.g. '31-12-2024') instead of MySQL's default YYYY-MM-DD format can silently give wrong results depending on the SQL mode.

Common Mistakes

1Writing the date literal in the wrong format (e.g. '31-12-2024' instead of '2024-12-31'), which MySQL may misinterpret or reject depending on settings.
2Selecting C_ID without qualifying it (CUSTOMERS.C_ID or LOANS.C_ID) when the column exists in both joined tables, which can cause an 'ambiguous column' error in some RDBMS.

Interesting Facts

MySQL's default date format is always YYYY-MM-DD internally, regardless of how the date is displayed by a client application.

Comparing dates with > and < works exactly like comparing numbers in MySQL, because DATE values are stored in a sortable internal format.

Spotted a mistake or something unclear?

Tell us — we fix reported answers fast.

Frequently Asked Questions

Why is the date written as '2024-12-31' and not '31-12-2024'?

MySQL's standard and default date literal format is YYYY-MM-DD; using DD-MM-YYYY can be misinterpreted or cause errors depending on the server's SQL mode.

Why must C_ID come from a joined query, not just one table?

Because C_ID is the common key linking CUSTOMERS and LOANS — the join condition CUSTOMERS.C_ID = LOANS.C_ID ensures only correctly matched customer-loan pairs appear together in one row.