Your question is Top N Students by Subject. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
How would you retrieve the top N students for a given subject?
Write a PostgreSQL query against the students, subjects, and student_scores tables. For this exercise, use Mathematics as the subject and return the top 3 students. Scores that are NULL must be ignored.
student_id, student_name, subject_name, score, and position.position ascending. Break equal scores by lower student_id.| Column | Type | Description |
|---|---|---|
| student_idPK | INTEGER | Unique student identifier |
| student_name | VARCHAR(100) | Student full name |
| Column | Type | Description |
|---|---|---|
| subject_idPK | INTEGER | Unique subject identifier |
| subject_name | VARCHAR(100) | Subject name |
| Column | Type | Description |
|---|---|---|
| score_idPK | INTEGER | Unique score record identifier |
| student_id | INTEGER | Referenced student identifier |
| subject_id | INTEGER | Referenced subject identifier |
| score | NUMERIC(5,2) | Student score for the subject |