Easy
Select students who took a test
Write a query to display the details and test scores of each student who took a test.
The result should include the student's ID, first name, last name, test ID, and the score they received on that test. Make sure to order the results first by the student_id and then by test_id, both 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 |
| ... | ... | ... | ... | ... |
+------------+------------+-----------+---------+-------+
Since this question asks for only the details of students who took a test, to solve this question you should use an INNER JOIN clause to combine the students and tests tables based on the student_id. Then, select the required columns and order the results by student_id and test_id.
SELECT
s.student_id,
s.first_name,
s.last_name,
t.test_id,
t.score
FROM students s
INNER JOIN tests t ON s.student_id = t.student_id
ORDER BY s.student_id, t.test_id