Find the Quiet Students in All Exams - Problem

Database Hard

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_name consists of English letters

Visualization

Tap to expand

Asked in

A Amazon 15 M Microsoft 12 G Google 8

Use window functions with MIN() and MAX() to identify extreme scores per exam, then filter out students who achieved these extremes using NOT EXISTS.

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)

Common Approaches

✓ Window Function with NOT EXISTS

⏱️ Time: O(n log n) Space: O(n)

First identify the minimum and maximum scores for each exam using window functions. Then select students who have taken exams but never achieved these extreme scores.

Window Function with NOT EXISTS — Algorithm Steps

Step 1: Use window functions to mark records with min/max scores per exam
Step 2: Join Student table and exclude students who ever achieved extreme scores
Step 3: Ensure students have taken at least one exam

Visualization

Tap to expand

Step-by-Step Walkthrough

Mark Extremes

Use MIN/MAX window functions per exam

Filter Students

Exclude students with extreme scores

Join Results

Get student names for quiet students

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Window functions require sorting by exam_id

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for window function results

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~20 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Find the Quiet Students in All Exams - Problem

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with NOT EXISTS — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler