Examine these statement which execute successfully: Now, examine this desired output: Which two queries will produce the out? A. B. C. D. E.

Question

Exhibit

Options

• ASELECT * FROM band WHERE song RLIKE 'the' COLLATE utf8mb4_0900_ai_ci AND song RLIKE 'the' COLLATE utf8mb4_0900_ai_ci;
• BSELECT * FROM band WHERE song RLIKE '^The' COLLATE utf8mb4_0900_ai_ci AND song RLIKE 'Hill$' COLLATE utf8mb4_0900_ai_ci;
• CSELECT * FROM band WHERE song RLIKE '^The' COLLATE utf8mb4_0900_as_cs AND SUBSTRING(song, 4) RLIKE "the" COLLATE utf8mb4_0900_as_cs;
• DSELECT * FROM band WHERE song LIKE 'The%' COLLATE utf8mb4_0900_ai_ci AND song LIKE '%Hill' COLLATE utf8mb4_0900_ai_ci;
• ESELECT * FROM band WHERE song RLIKE 'the' COLLATE latin1_general_cs AND song RLIKE '^the' COLLATE latin1_general_ci;

• A
  5% (2)
• B
  83% (33)
• D
  10% (4)
• E
  3% (1)

Explanation

B and C both correctly identify songs that start with "The" and end with "Hill" by using proper regex anchors: ^ pins the match to the beginning of the string and $ pins it to the end. Option B uses utf8mb4_0900_ai_ci (case-insensitive), so ^The and Hill$ together precisely bracket the desired rows. Option C uses the case-sensitive utf8mb4_0900_as_cs collation with ^The to enforce capitalization, then applies SUBSTRING(song, 4) to extract the text after "The" and checks it for "the" (lowercase), which correctly identifies the same qualifying rows in a case-sensitive manner.

A fails because RLIKE 'the' has no anchors - it matches "the" anywhere in the string - and repeating the identical unanchored pattern twice is redundant; it never enforces a start-with or end-with constraint. D uses LIKE with % wildcards, which appears similar but behaves differently than regex under collation rules; LIKE 'The%' can over-match (e.g., "Theory", "Therapy") and collation application differs between LIKE and RLIKE in MySQL 8. E mixes latin1_general_cs and latin1_general_ci collations across two conditions and neither pattern uses anchors correctly to match the full required structure, and applying a latin1 collation to a utf8mb4 column causes a collation mismatch.

Memory tip: Think of regex anchors as bookends - ^ is the start bookend, $ is the end bookend. If an answer lacks both bookends for a "starts with / ends with" requirement, it's almost certainly wrong. Also remember: ai_ci = case-Insensitive, as_cs = case-Sensitive.

No community discussion yet for this question.