Easy
Show each student's tests
For each student in the students table, show the tests they have taken and the score received for each test. Order the results by student_id and test_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 |
| ... | ... | ... | ... | ... |
+------------+------------+-----------+-----+------------+
tests
+---------+-----------------+-------+------------+------------+
| test_id | marks_available | score | subject_id | student_id |
+---------+-----------------+-------+------------+------------+
| GEOG001 | 100 | 57 | 1 | 1 |
| GEOG001 | 100 | 12 | 1 | 2 |
| GEOG001 | 100 | 70 | 1 | 3 |
| ... | ... | ... | ... | ... |
+---------+-----------------+-------+------------+------------+
EXPECTED OUTPUT
+------------+------------+-----------+---------+-------+
| student_id | first_name | last_name | test_id | score |
+------------+------------+-----------+---------+-------+
| 1 | Mike | Chaplin | GEOG001 | 57 |
| 1 | Mike | Chaplin | MATH001 | 100 |
| 2 | Emily | Jackson | GEOG001 | 12 |
| ... | ... | ... | ... | ... |
+------------+------------+-----------+---------+-------+
Use a LEFT JOIN to connect the students and tests table.
SELECT
students.student_id,
first_name,
last_name,
test_id,
score
FROM students
LEFT JOIN tests
ON students.student_id = tests.student_id
ORDER BY students.student_id, test_id