select  a.student_ID
,       a.student_name
,       a.age
from    student a
join    book b  
on      a.student_ID = b.student_ID 
where   b.subject like '%mathematics%'
        or b.subject like '%computer%'
group by
        a.student_ID
,       a.student_name
,       a.age
having  count(distinct case when b.subject like '%mathematics%' then 1
              when b.subject like '%computer%' then 2 end) = 2

您可以使用表和主题过滤器之间的联接来检查所涉及的行是否包含2个不同的主题

select a.student_ID,a.student_name,a.age
from  student a
inner join book b  on a.student_ID = b.student_ID 
where b.subject in ('mathematics', 'computer')
group by a.student_ID,a.student_name,a.age 
having count(distinct subject) = 2