problem in select query

[tamilselvan]

Nope.
See the result:
SQL> L
1 select student_id from (
2 select distinct student_id,
3 count(*) over (partition by student_id) courses
4 from my_table
5 where course in ('java','c'))
6* where courses = 2
SQL> /

STUDENT_ID
----------
13
15


Tamil

[Cookies]

here is my rewrite but I think Tamil's did the same thing:

select student_id from (
  select student_id,
   max(decode(course, 'java', student_id)) java,
   max(decode(course, 'c', student_id)) c,
   max(case when course not in ('java', 'c') then 'x' end) x
  from my_table
  group by student_id)
where java is not null and c is not null and x is null 

[akerber00]

select
a.student_id
from my_table a,
my_table b
where upper(a.course)='java' and
a.student_id=b.student_id and
upper(b.course)='c'

[tamilselvan]

3rd method:

SQL> 
  1  select student_id from my_table a
  2  where a.course = 'c'
  3  and   exists ( select null
  4                  from my_table b
  5                where b.student_id = a.student_id
  6                  and b.course = 'java')
  7  and not exists
  8      ( select null
  9        from  my_table c
 10         where c.student_id = a.student_id
 11*         and  c.course not in ( 'c', 'java')) ; 

Tamil