Answer Posted / rakesh

Total Joins of 8 types::
table1: emp[ename,city]
table2: emp_sal[ename,salary]

1]self join
select e2.ename from emp e1, emp e2 where e1.city=e2.city
and e1.ename="Ram"

2]Natural Join
select ename,city,salary from emp natural join emp_sal

3]cross join
select ename,city,salary from emp cross join emp_sal

4]left outer join : all rows from left table
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename=e2.ename(+))

5]right outer join : all rows from right table
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename(+)=e2.ename)

6]full outer join : all rows from [left table+right table]
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename=e2.ename(+))
UNION
select e1.ename,city,salary
from emp e1,emp_sal e2
where (e1.ename(+)=e2.ename)
OR CAN SAY:
select ename,city,salary
from emp e1 full outer join emp_sal e2
where e1.ename=e2.ename

7]Equi Join/simple join/inner join
select e1.ename,e2.salary
from emp e1, emp_sal e2
where e1.ename=e2.ename

8]Non Equi Join
select e1.ename,e2.salary
from emp e1, emp_sal e2
where e2.salary BETWEEN 1000 and 2000

How to create a new oracle data file?

1033

---

How many categories of data types?

997

---

interview questions with answer for cts

2587

---

What is difference between cartesian join and cross join?

1092

---

What is oracle rowcount?

1089

---

How to save query output to a local file?

1028

---

How to use "while" statements in oracle?

1328

---

State and explain the different types of data models?

944

---

Why does for update in oracle 8 cause an ora-01002 error?

1003

---

What is an index associated with a constraint?

1040

---

List the types of joins used in writing subqueries?

998

---

How to create an oracle database?

1049

---

How to update values on multiple rows in oracle?

1035

---

Can the query output be sorted by multiple columns in oracle?

993

---

How to create id with auto_increment on oracle?

990

---