Answer Posted / gvmahesh

NOCYCLE keyword is default in sequences.

CYCLE :If we want use cycle keyword first we check the table
having primary key or not.If there is no primary key we
absolutely use the CYCLE keyword.

In hierarchical structure,the descendant act as a
ancestor,is called CYCLE.

For eliminating ancestor descendant problem we use NOCYCLE
KEYWORD.

for example we take the EMP table

In EMP table the MGR column has a NULL managerial number.In
that column we insert some of the empno.Then ancestor
descendant problem occur.

select * from emp;

update emp set mgr=7566 where mgr is null;

Then we write a hierarchical query

select empno,ename,sal,mgr from emp
start with ename='KING'
connect by prior empno=mgr;

we execute this query it shows an error

ERROR:
ORA-01436: CONNECT BY loop in user data.

in this situation also we want to see the data through
NOCYCLE keyword.

select empno,ename,sal,mgr from emp
start with ename='KING'
connect by NOCYCLE prior empno=mgr;

What is case function?

988

---

What is the use of count (*) in sql?

980

---

What does truncate mean in sql?

961

---

List and explain the different types of join clauses supported in ansi-standard sql?

1300

---

explain the difference between bool, tinyint and bit. : Sql dba

937

---

Why do we go for stored procedures?

994

---

What does bitemporal mean?

1009

---

What is recursive join in sql?

946

---

What are the types of index in sql?

1015

---

What are commit, rollback, and savepoint?

938

---

What are the syntax and use of the coalesce function?

1103

---

what is a primary key? : Sql dba

982

---

Is there a 64 bit version of ssms?

893

---

What is view? Can we update view

1352

---

Can we debug stored procedure?

957

---