To display the names of all students who have secured more than 50 in all subjects that they have appeared in

Question

Write a query to display the names of all students who have secured more than 50 in all subjects that they have appeared in, ordered by student name in ascending order.enter image description here

I have used this below query but didn't getting desired result as it is not comparing the result by grouping the student_id as whole. Can anyone suggest any changes in query please.

select distinct student_name from student s join mark m on
s.student_id = m.student_id join subject sb
on m.subject_id = sb.subject_id
where m.value IN 
(select value from mark m1 join student s1
on m1.student_id = s1.student_id join subject sb1
on m1.subject_id = sb1.subject_id
where value > 50 group by s1.student_id,m1.value, sb1.subject_id)
order by 1;

Answer 1

I don't think that you need such a complex subquery. All you need to do is just find out the minimum marks for a student (INNER JOIN ensures that only those subjects are considered, which have been attempted by the student).

Do a simple JOIN between student and marks table on student_id. We do a GROUP BY on the student_id and get minimum_marks for each student. If the minimum_marks > 50, it means that the student has > 50 marks in all the subjects. Try the following, this will work in MySQL (as per the Original tag by OP):

SELECT s.student_name, MIN(m.value) AS minimum_marks 
FROM student s 
JOIN mark m ON s.student_id = m.student_id 
GROUP BY s1.student_id 
HAVING minimum_marks > 50 
ORDER BY s.student_name ASC

Edit As per Oracle (since OP edited later), aliased fields/expressions are not allowed in the HAVING clause. Revised query would look like:

SELECT s.student_name, MIN(m.value) AS minimum_marks 
FROM student s 
JOIN mark m ON s.student_id = m.student_id 
GROUP BY s1.student_id 
HAVING MIN(m.value) > 50 
ORDER BY s.student_name ASC

Answer 2

select student_name
from student join mark using (student_id)
group by student_name
having min(value) > 50
order by student_name;

A sub-query should be avoided if possible. So by having check if the minimum marks scored by a student is greater than 50 , we can say that he has scored more than 50 in all his subjects.

Answer 3

select student_name from student where student_id in
(select student_id 
    from (select student_id ,min(value) from mark group by student_id having min(value)>50)
)
order by student_name;

As it is simple by making subquery as shown above.

1.Extract min value that is greater than 50 and create a table and then compare it from the student table.

2.without using any Join operation

Answer 4

select distinct student_name from student,mark
where student.student_id = mark.student_id
having min(value) > 50
group by student_name
order by student_name;

Answer 5

select student_name
from student s inner join mark m
on s.student_id= m.student_id
having min(m.value)>50
group by student_name
order by student_name asc;

Answer 6

select s.student _name from student s join mark m 
on s.student_id = m.student_id
group by s.student_name
having min(m.value) > 50
order by s.student_name;

Answer 7

MySQL> select studentid, name,mark from students where mark>20;

Answer 8

select student_name from student
where student_id in (select student_id from mark group by student_id having min(value) > 50)
order by student_name;