Solution

To identify quiet students—those who have taken at least one exam and never scored the highest or the lowest in any of their exams—we can follow a structured approach using SQL's window functions and subqueries. This involves ranking students within each exam, filtering out those who achieved top or bottom ranks, and finally selecting the remaining students who meet the quiet student criteria.

Approach Overview

1. Rank Students' Scores Within Each Exam:
   • Assign ranks to students based on their scores in each exam to identify the highest and lowest scorers.
2. Identify Students with Highest or Lowest Scores:
   • Extract students who have ever ranked first (highest score) or last (lowest score) in any exam.
3. Determine Quiet Students:
   • Exclude students identified in the previous step from the list of all students who have taken at least one exam. The remaining students are the quiet students.
4. Retrieve Quiet Students' Details:
   • Fetch the student_id and student_name of the quiet students from the Student table.
5. Finalize the Results:
   • Select distinct candidate details and present them in the desired format, ordered by student_id.

SQL Query

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)

SELECT DISTINCT e.student_id, s.student_name
FROM Exam e 
LEFT JOIN Student s ON s.student_id = e.student_id
WHERE e.student_id NOT IN (
    SELECT student_id 
    FROM cte 
    WHERE high_score = 1 OR low_score = 1
)
ORDER BY e.student_id;

Step-by-Step Approach

Step 1: Rank Students' Scores Within Each Exam (cte)

Assign ranks to students within each exam to determine who scored the highest and the lowest.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)
SELECT * FROM cte;

Explanation:

• RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score:
  • Assigns a rank to each student in descending order of their scores within each exam_id. The student(s) with the highest score receive a rank of 1.
• RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score:
  • Assigns a rank to each student in ascending order of their scores within each exam_id. The student(s) with the lowest score receive a rank of 1.

Intermediate Output After Step 1 (cte):

+---------+------------+-----------+----------+
| exam_id | student_id | high_score| low_score|
+---------+------------+-----------+----------+
| 10      | 1          | 3         | 1        |
| 10      | 2          | 2         | 2        |
| 10      | 3          | 1         | 3        |
| 20      | 1          | 1         | 1        |
| 30      | 1          | 3         | 1        |
| 30      | 3          | 2         | 2        |
| 30      | 4          | 1         | 3        |
| 40      | 1          | 3         | 1        |
| 40      | 2          | 2         | 2        |
| 40      | 4          | 1         | 3        |
+---------+------------+-----------+----------+

Step 2: Identify Students with Highest or Lowest Scores

Find all students who have ever ranked first (highest score) or last (lowest score) in any exam.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)
SELECT DISTINCT student_id
FROM cte 
WHERE high_score = 1 OR low_score = 1;

Explanation:

• WHERE high_score = 1 OR low_score = 1:
  • Filters the cte to include only those students who have either the highest (high_score = 1) or the lowest (low_score = 1) score in any exam.

Intermediate Output After Step 2:

+------------+
| student_id |
+------------+
| 1          |
| 3          |
| 4          |
+------------+

Step 3: Determine Quiet Students

Exclude students identified in Step 2 from those who have taken at least one exam. The remaining students are the quiet students.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
),
excluded AS (
    SELECT DISTINCT student_id
    FROM cte 
    WHERE high_score = 1 OR low_score = 1
)

SELECT DISTINCT e.student_id, s.student_name
FROM Exam e 
JOIN Student s ON s.student_id = e.student_id
WHERE e.student_id NOT IN (SELECT student_id FROM excluded)
ORDER BY e.student_id;

Explanation:

• JOIN Student s ON s.student_id = e.student_id:
  • Associates each exam record with the corresponding student's details.
• WHERE e.student_id NOT IN (SELECT student_id FROM excluded):
  • Filters out students who have ever scored the highest or the lowest in any exam.
• SELECT DISTINCT e.student_id, s.student_name:
  • Ensures that each quiet student appears only once in the final output, even if they have taken multiple exams.

Intermediate Output After Step 3:

+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 2          | Jade         |
+------------+--------------+

Explanation of Output:

• Student 2 (Jade):
  • Exam 10: Scored 80 (middle score)
  • Exam 40: Scored 70 (middle score)
  • Never scored the highest or the lowest in any exam.

Note:

• Students 1, 3, and 4 are excluded as they have scored the highest or lowest in at least one exam.