您的主要错误：WHERE子句一次只查看一行。表中不能有与两个主题都匹配的行：

where ...
and lower(subject_name) = 'computer engineering'
and lower(subject_name) = 'software engineering'

一定是

where ...
  and 
  (
      lower(subject_name) = 'computer engineering'
    or
      lower(subject_name) = 'software engineering'
  )

或者干脆

where ...
and lower(subject_name) in ('computer engineering', 'software engineering')

那么，你就把事情搞得太复杂了。如果您希望每个SUBJECT_NAME有一个结果行，那么GROUP BY subject_name。

您正在使用的联接语法已被弃用了三十年。不要用它。如果你学过这种语法，那就退出那门课、那本书或那门教程。使用1992年标准SQL中引入的显式ANSI联接。

select
  s.subject_name,
  min(m.value) as min_mark
from subject s
left outer join mark m on m.subject_id = s.subject_id
where lower(s.subject_name) in ('computer engineering', 'software engineering')
group by s.subject_name
order by s.subject_name desc;

我在这里使用了一个外连接，用于处理主题还没有标记，但仍将在输出中显示的情况。如果不需要，请将LEFT OUTER JOIN更改为INNER JOIN。

WITH子句在这里只是为了生成一些示例数据，因此，它不是答案的一部分。样本数据：

WITH
    subject AS
        (
            Select 1 "SUBJ_ID", 'Software Engineering'     "SUBJ_NAME", 'A1' "SUBJ_CODE", 11 "STAFF_ID" From Dual Union All
            Select 2 "SUBJ_ID", 'Computer Programming'     "SUBJ_NAME", 'A2' "SUBJ_CODE", 12 "STAFF_ID" From Dual Union All
            Select 3 "SUBJ_ID", 'Regenerative Cybernetics' "SUBJ_NAME", 'A3' "SUBJ_CODE", 13 "STAFF_ID" From Dual 
        ),
    mark AS
        (
            Select 61 "VALUE", 1 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 65 "VALUE", 2 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union All
            Select 74 "VALUE", 3 "SUBJ_ID", 101 "STUDENT_ID" From Dual Union ALL
            Select 72 "VALUE", 1 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 69 "VALUE", 2 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union All
            Select 66 "VALUE", 3 "SUBJ_ID", 102 "STUDENT_ID" From Dual Union ALL
            Select 91 "VALUE", 1 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 67 "VALUE", 2 "SUBJ_ID", 103 "STUDENT_ID" From Dual Union All
            Select 70 "VALUE", 3 "SUBJ_ID", 103 "STUDENT_ID" From Dual 
        )

如果您只需要主题名称和最小值，则不必使用嵌套查询。此外，在您的问题中，排序应该是主题名称降序，但在您的必填答案中，排序是按最小值降序。
希望这能帮助你完成这项工作：

Select 
    s.SUBJ_NAME "SUBJ_NAME",
    Min(m.VALUE) "MIN_MARK"
From 
    MARK m
Inner Join
    SUBJECT s ON(m.SUBJ_ID = s.SUBJ_ID)
Where
    s.SUBJ_NAME = 'Software Engineering' 
    OR 
    s.SUBJ_NAME = 'Computer Programming'
Group By
    s.SUBJ_NAME
Order By
    s.SUBJ_NAME DESC

..。结果如下：

--  R e s u l t :
--  
--  SUBJ_NAME                  MIN_MARK
--  ------------------------ ----------
--  Software Engineering             61 
--  Computer Programming             65

如果您知道ID，那么最终得到相同结果的更好的过滤方法是：

Where
    s.SUBJ_ID IN(1, 2)

我解决了上面的问题

select subject_name,min(value) as MIN_MARK from mark,subject where
mark.subject_id = subject.subject_id and 
lower(subject_name) in ('computer programming' , 'software engineering')
group by subject_name
order by subject_name desc;

输出：