Students Registered for a Machine Learning Elective — SQL Easy Problem

Problem statement

Machine-learning electives at the college all have codes that begin with ML — ML301, ML410 and so on. Other courses may contain the letters ML inside their code (HTML101 is a web-design course); those are not machine-learning electives.

Return student_id, name and course_codes for every student registered for at least one machine-learning elective, in any order.

Tables

Table: Enrolment

Primary key: student_id.

course_codes lists the codes of the student's electives separated by single spaces, or is NULL if they have not registered yet. A code is capital letters followed by three digits.

Examples

Example 1

Enrolment

Output

How to solve Students Registered for a Machine Learning Elective

A machine-learning elective is a word of the list that starts with ML. The list is words separated by single spaces, so such a word is either at the very start of the text or right after a space — exactly two LIKE patterns:

• course_codes LIKE 'ML%' — the first code is an ML code;
• course_codes LIKE '% ML%' — some later code is (a space, then ML).

The tempting LIKE '%ML%' is wrong: it also matches HTML101 and XML205, where the letters sit in the middle of a word. And LIKE 'ML%' alone misses every student whose ML elective is not listed first.

A neat trick merges the two patterns: put a space in front of the whole list, CONCAT(' ', course_codes), and now every code — the first one included — follows a space, so LIKE '% ML%' alone is enough. A regular expression says the same thing directly, REGEXP '(^| )ML': the start of the text or a space, then ML. LOCATE(' ML', CONCAT(' ', course_codes)) > 0 is the same search written as a position.

Students with a NULL list match no pattern, since NULL LIKE … is unknown, so they drop out without a separate test. Each test scans the string once, so the query is linear in the total length of the lists. (Storing a list in one column is itself a design smell — a separate enrolment row per course would make this an ordinary = filter.)

Reference solution (MySQL)

SELECT student_id, name, course_codes
FROM Enrolment
WHERE course_codes LIKE 'ML%' OR course_codes LIKE '% ML%'

Another way

SELECT student_id, name, course_codes FROM Enrolment WHERE CONCAT(' ', course_codes) LIKE '% ML%'

Another way

SELECT student_id, name, course_codes FROM Enrolment WHERE course_codes REGEXP '(^| )ML'

Another way

SELECT student_id, name, course_codes FROM Enrolment WHERE LOCATE(' ML', CONCAT(' ', course_codes)) > 0