Easy
Filter students by name III
Select all students from the students
table whose first names begin with the character 'm' or 'M'. Order the results by student_id
in ascending order.
SCHEMA
students
+------------+------------+-----------+-----+------------+
| student_id | first_name | last_name | age | class_code |
+------------+------------+-----------+-----+------------+
| 1 | Mike | Chaplin | 12 | 7A |
| 2 | Emily | Jackson | 12 | 7A |
| 3 | Robert | Jackson | 12 | 7A |
| ... | ... | ... | ... | ... |
+------------+------------+-----------+-----+------------+
EXPECTED OUTPUT
+------------+------------+------------+-----+------------+
| student_id | first_name | last_name | age | class_code |
+------------+------------+------------+-----+------------+
| 1 | Mike | Chaplin | 12 | 7A |
| 5 | Mike | Tydaughter | 11 | 7A |
| 6 | Matt | Jones | 12 | 7A |
| ... | ... | ... | ... | ... |
+------------+------------+------------+-----+------------+
Use the WHERE
and 'LIKE' keywords to apply filters to string columns. For example, running a query like:
SELECT *
FROM students
WHERE last_name LIKE 'Matt'
would return all rows in the students
table where the first_name
is equal to 'Matt'.
In SQL, the percentage sign % is used to represent zero, one, or multiple characters. For example, running a query like:
SELECT *
FROM students
WHERE last_name LIKE 'M%'
would return all rows in the students
table where the last_name
starts with an 'M' and is followed by zero, one or more characters.
Final tip: Remember to account for the possibility that names may be upper- or lower-case!
SELECT *
FROM students
WHERE LOWER(first_name) LIKE 'm%'
ORDER BY student_id ASC