Answer Posted / venu

types of joins:
• Equijoin
• Non-equijoin
• Outer join
• Self join

Equijoins:
-------------
To determine an employee’s department name, you compare the
value in the DEPARTMENT_ID
column in the EMPLOYEES table with the DEPARTMENT_ID values
in the DEPARTMENTS table.
The relationship between the EMPLOYEES and DEPARTMENTS
tables is an equijoin—that is, values
in the DEPARTMENT_ID column on both tables must be equal.
Frequently, this type of join involvesprimary and foreign
key complements.

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Non-Equijoins:
----------------
A non-equijoin is a join condition containing something
other than an equality operator.
The relationship between the EMPLOYEES table and the
JOB_GRADES table has an
example of a non-equijoin. A relationship between the two
tables is that the SALARY
column in the EMPLOYEES table must be between the values in
the LOWEST_SALARY
and HIGHEST_SALARY columns of the JOB_GRADES table. The
relationship is
obtained using an operator other than equals (=).

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins:
---------------
The missing rows can be returned if an outer join operator
is used in the join condition. The operator
is a plus sign enclosed in parentheses (+), and it is
placed on the “side” of the join that is deficient in
information. This operator has the effect of creating one
or more null rows, to which one or more rows
from the nondeficient table can be joined.
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;

Self Joins:
-------------
Joining a Table to Itself
Sometimes you need to join a table to itself. To find the
name of each employee’s manager, you need
to join the EMPLOYEES table to itself, or perform a self
join.

SELECT worker.last_name || ’ works for ’
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

How to pass a parameter to a cursor in oracle?

1231

---

What is translate in oracle?

1145

---

How to delete a user account in oracle?

1022

---

What is the data pump import utility?

1074

---

How to define a procedure inside another procedure?

1068

---

I have a parent program and a child program. I want to write a statement in Exception Block of the parent program so that when the statement in the exception block is executed, the control goes to the next statement in the parent block bypassing the child block.How do i do that?

2505

---

what is dynamic SGA and static SGA

4491

---

What is Data Dictionary Cache in Oracle?

1244

---

How to drop an index in oracle?

1121

---

How to create an oracle testing table?

1128

---

Please send me Informatica 8.1 certification dumps, my mail id mona85gupta@gmail.com

1924

---

what is the use of triggers in Java program? I mean where do we use triggers in Java programming?

2234

---

How to create additional tablespaces for an new database?

1079

---

Are truncate and delete commands same? If so why?

1204

---

you are a universe designer and report developer in BO, what type of information you gather from client?Briefly explain plz

2002

---