CREATE TABLE catedet (
citd number(5) primary key,cdesc varchar2(120),srate number(10,3),ctype varchar2(3));

insert into catedet 
values
(1,'ENGLISH NEW SERIES-A',450,'FVU');

insert into catedet 
values
(2,'URDU SERIES-A',450,'FVU');

insert into catedet 
values
(3,'MATH SERIES-A',450,'FVU');

insert into catedet 
values
(5,'ART STUDIO',250,'FVU');

insert into catedet 
values
(6,'G.K SERIA-A',340,'FVU');

insert into catedet 
values
(7,'ALQURAN NAZARA',130,'FVU');
--New Type
insert into catedet 
values
(8,'ENGLISH GOLEN SERIES SNC',490,'TWE');

insert into catedet 
values
(9,'URDU GOLEN SERIES SNC',490,'TWE');

insert into catedet 
values
(10,'MATH GOLEN SERIES SNC',490,'TWE');

insert into catedet 
values
(11,'ART STUDIO SNC',390,'TWE');

insert into catedet 
values
(12,'GRAMMAR SERIES SNC',455,'TWE');

insert into catedet 
values
(13,'ALQURAN IBTADIA SNC',225,'TWE');

insert into catedet 
values
(14,'ISAMAIAE SERIES SNC',435,'TWE');
--New Type
insert into catedet 
values
(15,'ENGLISH GOLEN SERIES SNC',390,'THE');

insert into catedet 
values
(16,'URDU SERIES SNC',590,'THE');

insert into catedet 
values
(17,'MATH OXFORD SNC',490,'THE');

insert into catedet 
values
(18,'ART STUDIO SNC',390,'THE');

insert into catedet 
values
(19,'GRAMMAR SERIES SNC',565,'THE');

insert into catedet 
values
(20,'SCIENCE SNC',490,'THE');
--New Type
insert into catedet 
values
(21,'ENGLISH GOLEN SERIES SNC',390,'FRE');

insert into catedet 
values
(22,'URDU SERIES SNC',590,'FRE');

insert into catedet 
values
(23,'MATH OXFORD SNC',490,'FRE');

insert into catedet 
values
(24,'ART STUDIO SNC',390,'FRE');

insert into catedet 
values
(25,'SCIENCE SERIES SNC',565,'FRE');

insert into catedet 
values
(26,'GRAMMAER SERIES SNC',490,'FRE');

insert into catedet 
values
(27,'SCIENCE SERIES SNC',525,'FRE');


---2nd table:

--bOOKS qUANTITY bY tYPE
create table bkkdet (
    sno number(6) primary key,ctyp varchar2(3),bamt number(7),b1 number(2),b2 number(2),b3 number(2),b4 number(2),b5 number(2),b6 number(2),
    b7 number(2),b8 number(2),b9 number(2),btot number(3));

insert into bkkdet
values
(1,'FVU',2700,1,1,1,1,1,1,1,0,0,7);

insert into bkkdet
values
(2,'TWE',2300,1,1,1,1,1,1,0,0,0,6);

insert into bkkdet
values
(3,'THE',2600,1,1,1,1,1,1,0,0,0,6);

insert into bkkdet
values
(4,'FRE',2800,1,1,1,1,1,1,1,0,0,7);

-- third table 
create table bredet (
    stuid number(4),btyp varcar2(3),b1 number(2),b2 number(2),b3 number(2),b4 number(2),b5 number(2),b6 number(2),
    b7 number(2),b8 number(2),b9 number(2));

-------for b1

Cursor c1 is select citd, srate,rouwnum 
from (
select citd,srate from catedet
where cytpe=:btyp
--and cdesc like 'ENG%' 
order by citd)
where rownum=1;   --Works Fine

--for b2
Cursor c1 is select citd, srate,rouwnum 
from (
select citd,srate from catedet
where cytpe=:btyp 
-- and cdesc like 'URDU%'
order by citd)
where rownum=2;   --Work Fine

--for b5,b7
Cursor c1 is select citd, srate,rouwnum 
from (
select citd,srate from catedet
where cytpe=:btyp 
and cdesc like 'GRAM%' or cdesc like 'SCI%'
order by citd);   --not fetch the exact srate ????

where rownum=2;   --Work Fine

SELECT citd, srate, rnum
FROM (
    SELECT citd, srate, ROWNUM AS rnum
    FROM (
        SELECT citd, srate
        FROM catedet
        WHERE cytpe = :btyp
          AND (
              (cytpe = '1' AND cdesc LIKE 'G.K%') OR
              (cytpe = '2' AND (cdesc LIKE 'GRAM%' OR cdesc LIKE 'SCI%')) OR
              (cytpe = '3' AND (cdesc LIKE 'GRAM%' OR cdesc LIKE 'SCI%'))
          )
        ORDER BY citd
    )
    WHERE ROWNUM <= 2
)
WHERE rnum = 2;