Identify if at least one row with given condition exists

Commonly, you'd express this as either

SELECT COUNT(*)  FROM employee WHERE name like 'kaushik%'   AND rownum = 1

where the rownum = 1 predicate allows Oracle to stop looking as soon as it finds the first matching row or

SELECT 1  FROM dual WHERE EXISTS( SELECT 1                 FROM employee                WHERE name like 'kaushik%' )

where the EXISTS clause allows Oracle to stop looking as soon as it finds the first matching row.

The first approach is a bit more compact but, to my eye, the second approach is a bit more clear since you really are looking to determine whether a particular row exists rather than trying to count something. But the first approach is pretty easy to understand as well.

How about:

select max(case when name like 'kraushik%' then 1 else 0 end)from employee

Or, what might be more efficient since like can use indexes:

select count(x)from (select 1 as x      from employee      where name like 'kraushik%'     ) twhere rownum = 1

since you require that the sql query should return 1 or 0, then you can try the following query :-

select count(1) from dual where exists(SELECT 1              FROM employee             WHERE name like 'kaushik%')

Since the above query uses Exists, then it will scan the employee table and as soon as it encounters the first record where name matches "kaushik", it will return 1 (without scanning the rest of the table). If none of the records match, then it will return 0.