My solution passed the first test case, but got the wrong answer after final submission. I am thankful for anyone willing to point out my mistakes. Thanks!
The question is as below:
Table: Student
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| student_id | int |
| student_name | varchar |
+---------------------+---------+
student_id is the primary key for this table. student_name is the name of the student.
Table: Exam
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| exam_id | int |
| student_id | int |
| score | int |
+---------------+---------+
(exam_id, student_id) is the primary key for this table. Student with student_id got score points in exam with id exam_id.
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 |
+-------------+---------------+
Explanation in the question:
For exam 1: Student 1 and 3 hold the lowest and high score respectively. For exam 2: Student 1 hold both highest and lowest score. For exam 3 and 4: Studnet 1 and 4 hold the lowest and high score respectively. Student 2 and 5 have never got the highest or lowest in any of the exam. Since student 5 is not taking any exam, he is excluded from the result. So, we only return the information of Student 2.
My answer was to create two tables, the one is to list eligible student, with at least one exam. The other is to find the max(score) and the min(score) of the exam table. And use <> to locate the quiet students' id and then join with Student table to find this student_id's name, as below:
-- get eligible student
with eligible_student as
(
select distinct student_id as eligible_id from Exam
group by 1
order by 1
),
-- get the high and low score
high_low as
(select student_id, max(score) as high_score, min(score) as low_score
from Exam),
result as
(select eligible_student.eligible_id as student_id
from eligible_student inner join
high_low
on eligible_student.eligible_id <> high_low.student_id
-- left join Student
-- on eligible_student.eligible_id = Student.student_id
group by student_id
order by student_id
)
select result.student_id, s.student_name as student_name
from result left join Student s
on result.student_id = s.student_id
order by student_id;
This part is wrong:
with high_low as
(select student_id, max(score) as high_score, min(score) as low_score
from Exam)
because it outputs:
+------------+------------+-----------+
| student_id | high_score | low_score |
+------------+------------+-----------+
| 1 | 90 | 60 |
+------------+------------+-----------+
and student_id=1 has no relation to the found high_score or low_score.
After this the found (but incorrect) student_id is used in the selection for the cte result.
A solution:
with high_low as
(select max(score) as high_score, min(score) as low_score
from Exam)
select student.student_id, student_name
from (
select exam.student_id
from exam
group by exam.student_id
having max(score) <> (select high_score from high_low)
and min(score) <> (select low_score from high_low)) x
inner join student on student.student_id=x.student_id;
or:
select
student.*
from exam
inner join student on student.student_id=exam.student_id
group by student_id
having max(score) not in (select max(score) from exam)
and min(score) not in (select min(score) from exam);