Question { CSC, 76479 }

What are joins..how many types of joins are there?

Answer

In Oracle we can catogrise joins in following ways -
(1) Equi Join (Query having equal sign for condition)

select empno, ename, dname
from emp join dept
on emp.deptno = dept.deptno;

(2) Natural Join : both joining table must have the same
column name on which we are joining both tables.and no need
to specify the column name

select empno, ename, dname
from emp natural join dept;

(3) Non Equi Join: in both table column value is not
directly equal. so, for joining both table can't use equal
sign. in plce of it use BETWEEN,<=, >= operators.

select ename, sal
from emp join salgrade
on sal between losal and hisal;

(4)Outer Join: to fetch matched or unmatched data from
single or both tables.
- left outer join
- right outer join
- full outer join
(4) self Join or Theta Join : joining one table with itself
as another table.

select e1.empno, e1.ename employee, e2.empno mgr_number,
e2.ename manager
from emp e1 right outer join emp e2
on e1.mgr = e2.empno

(5) Cross Join : when we avoid to specify the joiing
condition means WHERE clause then it's become CROSS JOIN.

select ename, dname
from emp join dept;



always try to avoid CROSS JOIN.

Question { 9229 }

Can we create more than one constraint to column ?

Answer

yes, we can
ex.

create table t1
(empno number(4) not null,
ename varchar2(30),
constraint u_k_t1 unique key (empno));

Question { 4281 }

can u call user defined functions in select statements

Answer

yes, we can call user defined function in SELECT statement.

ex:
select function_name from dual;

Question { 8574 }

What different of iner joint & outer joint with example

Answer

INNER JOIN: inner join fetch only matched data from 2 or
more tables.
ex: select empno, ename, e1.deptno, d1.deptno, dname
from emp e1, dept d1
where e1.deptno = d1.deptno;

output will the all records whose deptno is exists in both
table (emp and dept)

OUTER JOIN: is used to fetched matched and unmatched data
from 2 or more tables.there are 3 type of outer join:
Right outer join , Left outer join, Full outer join.
Right outer join will display the matched and unmatched
records of right side located table.and Left outer join does
just opposite of it.and Full outer join will display matched
and unmatched both type of records from both tables.
ex: select empno, ename, e1.deptno, d1.deptno, dname
from emp e1 right outer join dept d1
on e1.deptno =d1.deptno;

Question { TCS, 7807 }

What is cursor

Answer

In order to process a sql statements oracle will allocate an
area of memory known as the context area. The context area
contains information necessary to complete the processing,
including the number of rows processed by the select
statement, a pointer to the parsed representation of the
statement, and in the case of a query, the active set,
which is the set of rows returned by the query.
A cursor is a handle or pointer to the context area.
Sql cursor attributes are:
1. SQL%ROWCOUNT: number of rows affected by the most recent
sql statement (an integer value).
2. SQL%FOUND: Boolean attribute that evaluates to TRUE if
the most recent SQL statement affects one or more rows.
3. SQL%NOTFOUND: Boolean attribute that evaluates to true if
the most recent SQL statement does not affects any rows.
4. SQL%ISOPEN: always evaluates to false because pl/sql
closes implicit cursors immediately after they are executed.

Cursor Types:
1. Implicit cursor
2. Explicit Cursor
3. REF cursor
4. Parametrized cursor
5. FOR LOOP Cursor

Implicit cursor:

a.Are opened implicitly by oracle whenever a DML or select
statement is executed.
b.Opened, fetched, closes internally.
c.Un-named cursors
d.Attributes: sql%isopen, sql%found, sql%notfound, sql%rowcount.

Explicit cursor

•Are declared and opened explicitly by developers to
manipulate multiple rows returned by queries one by one.
•Manually we have to declare, open, fetch, and close it.
•Name given to a context area.
•Attributes: cur_name%isopen, cur_name%found,
ur_name%notfound, cur_name%rowcount.

REF CURSOR EXAMPLE

declare
type t1 is ref cursor;
v1 t1;
begin
open v1 for select * from inv;
open v1 for select * from inv2;
end;

Parameterized cursor:

We can pass parameters for cursor as like procedures and
functions.
Syntax: cursor cursor_name[parameter_name datatype] is
select statement;
the advantage of parameterized cursor is, a single cursor
can be opened and closed several times in a block, returning
different active set in each occasion.
Note: formal parameters should not be mentioned with data type

Thanks,
Tripti