Answer / ravi kumar singh

DECLARE
RETVAL NUMBER:=0;/*A_CHK IS TABLE AND A IS COLOUMN HAVING NOS*/
I NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
SELECT COUNT(*) INTO RETVAL FROM A_CHK WHERE A=I;
IF (RETVAL=0) THEN
DBMS_OUTPUT.PUT_LINE(I);
ELSE NULL;
END IF;
END LOOP;
END;

Answer / subrat ray

SQL> desc numtab;
Name Null? Type
------ ------- ------
X NUMBER(4)


declare
v_num number(2);
cursor c1 is select rownum num from all_tables where rownum<=100
minus
select x num from numtab;
begin
open c1;
loop
fetch c1 into v_num;
exit when c1%notfound;
dbms_output.put_line(v_num);
end loop;
close c1;
end;

Answer / neha sinha

create or replace procedure pro1(cur_diff OUT SYS_REFCURSOR) is
BEGIN
OPEN cur_diff FOR

select diff
from (select /*+ no_merge */
rownum as diff
from (select 1 from dual group by cube(1, 1,
1, 1, 1)) a,
(select 1 from dual group by cube(1, 1,
1, 1, 1, 1)) b)
where diff >= (select min(lst) from list1)
and diff <= (select max(lst) from list1)
minus
select lst from list1;
end pro1;
-- where list1 is table name and lst is column in which
--some no is missing

Answer / kavitha n

BEGIN

FOR DATA IN (SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= 100
MINUS
SELECT num
FROM missingsquence)
LOOP
exit when sql%notfound;

DBMS_OUTPUT.put_line (DATA.num);
END LOOP;
END;
/

Answer / sumit

declare i number :=1;

begin
a number;
while (i<=100) loop
select num from table into a where num=i;
If a is null then dbms_output.put_line(i);
i:=i+1;
end loop;
end;

Answer / sureshramsing@gmail.com

select level from dual connect by level<=100 minus select <column name> from <table name>;

Answer / mani

DECLARE

l_id NUMBER;/* Temp IS TABLE AND ID IS COLOUMN HAVING
Numbers*/

BEGIN

FOR I IN 1..100 LOOP
SELECT COUNT(*) INTO l_id FROM temp WHERE id=I;
IF (l_id = 0) THEN
DBMS_OUTPUT.PUT_LINE(I);
ELSE
NULL;
END IF;
END LOOP;

END;

Answer / vishnu

create table test100 (id number);

insert into test100
select level from dual connect by level <=100;

select id from (SELECT rownum id from test100) where id
not in (select ID from(select rownum,id from test100) where id!=decode(id,rownum,rownum+1,rownum));

Answer / dipanshu saini

select level from dual connect by level<=(select max(seq_no) from test_tab)
minus
select seq_no from test_tab;

what is the difference between mysql_fetch_array and mysql_fetch_object? : Sql dba

0 Answers  

What is the syntax and use of the coalesce function?

0 Answers  

What is primary key and foreign key with example?

0 Answers  

What are analytical functions in sql?

0 Answers  

What are types of joins?

0 Answers  

How to set up sql*plus output format in oracle?

0 Answers  

What is sqlite format?

0 Answers  

How do you create a unique index?

0 Answers  

What are the operators used in select statements?

0 Answers  

What is the difference between jpql and sql?

0 Answers  

What is the use of time stamp in select statement?

1 Answers   TCS,

Display the total debit counts, total credit counts, sum of total debits, sum of total credits from an employee's transaction table (a single table) containing the following columns. Transaction_number Transaction_amount Transaction_date Transaction_type --> tells if the amount is a credit or a debit. As the query alone is important, the table is left with specifying just the field's name. Pls help me with this query.

3 Answers   iNautix, Oracle,