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

如果可能，应避免使用子查询。因此，如果检查一个学生的最低分数是否大于50，我们可以说他在所有科目中都取得了50分以上。

我不认为你需要这么复杂的子查询。您所需要做的就是找出学生的最低分数（INNER JOIN确保只考虑学生尝试过的科目）。
在student和student_id上的marks表之间执行简单的JOIN操作。我们在student_id上执行GROUP BY，并为每个学生获得minimum_marks。如果minimum_marks> 50，则意味着学生在所有科目中得分> 50。试试下面的方法，在MySQL中也可以（根据OP的Original标签）：

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

编辑根据Oracle（因为OP是后来编辑的），HAVING子句中的aliased fields/expressions are not allowed。修改后的查询如下所示：

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

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;

因为它是简单的通过制作子查询如上所示。

1.提取大于50的最小值并创建一个表，然后从student表中比较。
2.不使用任何Join操作

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;

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;

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;

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

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;

SELECT s.student_name FROM student s WHERE s.student_id NOT IN（SELECT m.student_id FROM mark m where m.student_id = s.student_id and m.value <= 50）ORDER BY s.student_name ASC;