Oracle Interview Question

What is joining?

Ans: When we require to retrieve data from more than one table in a database then a join condition is used .Its called joining.

What is left outer join and right outer join? Example with query?

      Ans: Left outer join- This join is used to retrieves all rows from the left table (employee) even if there is no match in the right table (dept).

      SELECT e.name, d.dept_id, d.dept_name FROM employee e
LEFT OUTER JOIN department d
ON (e.dept_id=d.dept_id)
This query retrieves all rows in the employee table, which is the left table even if there is no match in the department table.                

      Right outer join- This join is used to retrieves all rows from the right table (dept) even if there is no match in the right table (employee).

SELECT e.name, d.dept_id, d.dept_name FROM employee e
RIGHT OUTER JOIN department d
ON (e.dept_id=d.dept_id)
This query retrieves all rows in the department table, which is the right table even if there is no match in the employee table.                

 

What is equi join?

      Ans:
Employee table

Emp_id	Dept_id   (fk)
100	2
101	1

                 Department table

Dept_id	Dept_name  (pk)
1	Development
2	Hr

Suppose there are two tables employees table and department table. We want to compare the value in the dept_id column in the employee table with the dept_id values in the department table. The relationship between the employee and department table is an equijoin. The values in the dept_id column on both tables must be equal.

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name FROM employee e, department d WHERE e.dept_id=d.dept_id;

What is self-join?

Some times we need to join a table to itself. To find the name of each employee's manager, we need self-join. Suppose there is an employee table, which has three columns emp_id, name, and manager_id. Emp-id is primary key in that table and Manager_id is foreign key column for emp_id in the same employee table. Manager_id refer to the emp-id column of employee table.

SELECT worker.name manager.name FROM employee worker, employee manager WHERE worker.manager_id=manager.employee_id;

What is trigger? Example?

 Ans: A database trigger is a PL/SQL block that is used to automatically execute any insert, update, and delete statements against a table.

 

 

 

Next>>