Students who are passed in all subjects and having total marks greater than 170

SQL> select * from marks;

 STUDENTID  SUBJECTID    MARK
---------- ---------- ----------
     1          1         **21**
     1          2         75
     1          3         87
     2          1         82
     2          2         64
     2          3         77
     3          1         82
     3          2         **23**
     3          3         67

9 rows selected.

Need to calculate who are passed in all subjects(>30 in each subject) and having total mark >170 Here output like :

 STUDENTID  Status         Total_MARK

 ---------- ----------      ----------

  2           Passed              223

Answer

You can use grouping with having clause as below

 select student_id, 'Passed' as status, sum(mark) as total_mark 
   from marks
  group by student_id
 having sum(mark)>170 and min(mark)>30;

Demo

Edit ( due to your last comment ) : Using

with marks( student_id, mark ) as
(
 select 1, 21 from dual union all
 select 1, 75 from dual union all
 select 1, 87 from dual union all
 select 2, 82 from dual union all
 select 2, 64 from dual union all
 select 2, 77 from dual union all
 select 3, 82 from dual union all
 select 3, 23 from dual union all
 select 3, 67 from dual
)
 select student_id as "Student ID", 
        case when sum(mark)>170 and min(mark)>30 then 
                  sum(mark)||' (Passed)' 
        else
                  sum(mark)||' (Failed)'         
        end as "Total Mark (Status)"
   from marks
  group by student_id;

Student ID  Total Mark (Status)
----------  -------------------
1           183 (Failed)
2           223 (Passed)
3           172 (Failed)

might be a smart way by combining Total Mark and Status. If you really want to sperate those columns, then need to write seperate case when statemants as in the similar manner.