-- A "quite" student is the one who took at least one exam and didn't score neither the high score nor the low score.

-- Write an SQL query to report the students (student_id, student_name) being "quiet" in ALL exams.

-- Don't return the student who has never taken any exam. Return the result table ordered by student_id.

-- The query result format is in the following example.

Student table:

--  ------------- --------------- 
-- | student_id  | student_name  |
--  ------------- --------------- 
-- | 1           | Daniel        |
-- | 2           | Jade          |
-- | 3           | Stella        |
-- | 4           | Jonathan      |
-- | 5           | Will          |
--  ------------- --------------- 

Exam table:

--  ------------ -------------- ----------- 
-- | exam_id    | student_id   | score     |
--  ------------ -------------- ----------- 
-- | 10         |     1        |    70     |
-- | 10         |     2        |    80     |
-- | 10         |     3        |    90     |
-- | 20         |     1        |    80     |
-- | 30         |     1        |    70     |
-- | 30         |     3        |    80     |
-- | 30         |     4        |    90     |
-- | 40         |     1        |    60     |
-- | 40         |     2        |    70     |
-- | 40         |     4        |    80     |
--  ------------ -------------- ----------- 

Result table:

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

Is my solution correct?

--My Solution
Select Student_id, Student_name
From (
Select 
    B.Student_id, 
    A.Student_name,
    Score, 
    Max(Score) Over (Partition by Exam_id) score_max, 
    Max(Score) Over (Partition by Exam_id) score_min 
From
    Student A, Exam B 
Where 
    A.Student_ID = B.Student_ID
) T
Where   
    Score != Max_score or Score != Min_Score
Group by 
    student_id, student_name 
Having 
    Count(*) = (Select distinct count(exam_id) from exam)
Order by
    A.student_id
    

CodePudding user response：

Your result is correct but you need two changes in your query.

1. You have to change Max by Min in your score_min.

// ...
min(score) over (partition by exam_id) score_min,
max(score) over (partition by exam_id) score_max    
// ...

2. Having it should be like this:

having count(1) = 
(select count(distinct exam_id) from exam t2 
where t1.student_id = t2.student_id)

CodePudding user response：

The Solution is correct. Any alternate more shorter solutions are welcomed.