Find the Quiet Students in All Exams - Problem
You are given two tables: Student and Exam.
A quiet student is defined as a student who:
- Has taken at least one exam
- Has never scored the highest score in any exam
- Has never scored the lowest score in any exam
Write a SQL query to find all students who are quiet in ALL exams they have taken. Return the result ordered by student_id.
Table Schema
Student
| Column Name | Type | Description |
|---|---|---|
student_id
PK
|
int | Primary key, unique identifier for each student |
student_name
|
varchar | Name of the student |
Primary Key: student_id
Exam
| Column Name | Type | Description |
|---|---|---|
exam_id
PK
|
int | Identifier for the exam |
student_id
PK
|
int | Foreign key referencing Student table |
score
|
int | Score achieved by the student in the exam |
Primary Key: (exam_id, student_id)
Input & Output
Example 1 — Basic Quiet Student Identification
Input Tables:
Student
| student_id | student_name |
|---|---|
| 1 | Daniel |
| 2 | Jade |
| 3 | Stella |
| 4 | Jonathan |
| 5 | Will |
Exam
| exam_id | student_id | score |
|---|---|---|
| 10 | 1 | 70 |
| 10 | 2 | 80 |
| 10 | 3 | 90 |
| 20 | 1 | 80 |
| 30 | 1 | 70 |
| 30 | 4 | 80 |
Output:
| student_id | student_name |
|---|---|
| 2 | Jade |
💡 Note:
Student 2 (Jade) is quiet because she took exam 10 and scored 80, which was neither the highest (90) nor lowest (70) score in that exam. Students 1, 3, and 4 all scored extremes in at least one exam, so they are not quiet.
Example 2 — No Quiet Students
Input Tables:
Student
| student_id | student_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
Exam
| exam_id | student_id | score |
|---|---|---|
| 10 | 1 | 100 |
| 10 | 2 | 90 |
Output:
| student_id | student_name |
|---|
💡 Note:
No quiet students exist. Alice scored the highest (100) and Bob scored the lowest (90) in exam 10, so both students have extreme scores.
Constraints
-
1 ≤ student_id ≤ 1000 -
1 ≤ exam_id ≤ 1000 -
0 ≤ score ≤ 100 -
student_nameconsists of English letters
Visualization
Tap to expand
Understanding the Visualization
1
Identify Extremes
Find min/max scores per exam
2
Filter Students
Exclude students with extreme scores
3
Join Names
Get student names for results
Key Takeaway
🎯 Key Insight: Use window functions to identify extreme scores per exam, then filter students who never achieved these extremes
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code