Very Easy
Filter with a list I
Consider the following table tests, which contains a record of the test scores of students in a local high school:
+---------+-----------------+-------+------------+------------+
| test_id | marks_available | score | subject_id | student_id |
+---------+-----------------+-------+------------+------------+
| GEOG001 | 100 | 57 | 1 | 1 |
| GEOG001 | 100 | 12 | 1 | 2 |
| GEOG001 | 100 | 70 | 1 | 3 |
| ... | ... | ... | ... | ... |
+---------+-----------------+-------+------------+------------+
Using the IN operator, write a query which retrieves all rows where the score is either 10, 20, 30, 40, 50, 60, 70, 80, 90 or 100.
EXPECTED OUTPUT
+---------+-----------------+-------+------------+------------+
| test_id | marks_available | score | subject_id | student_id |
+---------+-----------------+-------+------------+------------+
| MATH001 | 100 | 100 | 1 | 2 |
| MATH001 | 100 | 90 | 3 | 2 |
| MATH001 | 100 | 10 | 5 | 2 |
| ... | ... | ... | ... | ... |
+---------+-----------------+-------+------------+------------+
The IN clause can be used within the WHERE clause to filter using a list of values. For example, the following code would retrieve all students in the students table whose class was either ‘7A’, ‘7B’ or ‘8A’:
SELECT *
FROM students
WHERE class IN ('7A', '7B', '8A')
SELECT *
FROM tests
WHERE score IN (10, 20, 30, 40, 50, 60, 70, 80, 90, 100)