Answer Posted / tripti gour

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.

How to end the current transaction in oracle?

1020

---

What are the limitations of check constraint?

1022

---

Explain what does a control file contain?

1094

---

What are the differences between char and varchar2 in oracle?

1162

---

In which language oracle has been developed?

1069

---

What is a named program unit?

1068

---

what is reindexing?

1665

---

How to write a query with a right outer join in oracle?

1160

---

How many file formats are supported to export data?

1211

---

what is difference between sql plus and sql*plus? (not sql and sql plus).

3889

---

In what script is snap$ created? In what script is the scott/tiger schema created?

2708

---

Is there a combination of "like" and "in" in sql?

1051

---

How do you increase the OS limitation for open files (LINUX and/or Solaris)?

1928

---

What is index in Oracle?

1068

---

For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame ?

2017

---