AWS Certified Data Engineer Associate · Free Practice Question Medium
Question 37
A company stores its user information in a MySQL database table called user. The name column has the users' names stored in firstname lastname format. Due to legacy reasons, a few users have the names stored in lastname firstname format. A data engineer has been tasked with developing a query that returns all records where the name column has values starting with John or Doe, on a case-insensitive basis.
Which of the following queries represents the correct solution?
-
A
SELECT * FROM user WHERE name ~ * '$(John|Doe)' -
B
SELECT * FROM user WHERE name ~ '$(John|Doe)' -
C
SELECT * FROM user WHERE name ~ '^(John|Doe)' -
D
SELECT * FROM user WHERE name ~ * '^(John|Doe)'
Reveal correct answer
Correct answer: D
Explanation
Correct option:
SELECT * FROM user WHERE name ~ * '^(John|Doe)'
In SQL, ~ is the regular expression operator. You can use ~* to make the query case-insensitive. The ^ operator matches a pattern at the start of a string. For the given use case, you can use the ^ operator to find all records where the name column has values starting with John or Doe on a case-insensitive basis.
Incorrect options:
SELECT * FROM user WHERE name ~ * '$(John|Doe)'
SELECT * FROM user WHERE name ~ '$(John|Doe)'
The $ operator matches a pattern at the end of a string. So, both these options are incorrect.
SELECT * FROM user WHERE name ~ '^(John|Doe)' - Without the ~ * characters combination in the WHERE clause, the query would return the results on a case-sensitive basis, so this option is incorrect.
References:
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
