Oracle interview questions /Answer

Explain the difference between trigger and stored procedure.

Trigger in act which is performed automatically before or after a event occur

Stored procedure is a set of functionality which is executed when it is explicitly invoked.

Explain Row level and statement level trigger.

Row-level: - They get fired once for each row in a table affected by the statements.

Statement: - They get fired once for each triggering statement.

Advantage of a stored procedure over a database trigger

Firing of a stored procedure can be controlled whereas on the other hand trigger will get fired whenever any modification takes place on the table.

What are cascading triggers?

A Trigger that contains statements which cause invoking of other Triggers are known as cascading triggers. Here’s the order of execution of statements in case of cascading triggers:

·         Execute all BEFORE statement triggers that apply to the current statement.

·         Loop for each row affected statement.

·         Execute all BEFORE row triggers that apply to the current statement in the loop.

·         Lock and change row, perform integrity constraints check; release lock.

·         Execute all AFTER row triggers that apply to the current statement.

·         Execute all AFTER statement triggers that apply to the current statement.

What is a JOIN? Explain types of JOIN in oracle.

A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

Types of JOINS:

JOIN Type	Example	Description
Simple JOIN	SELECT p.last_name, t.deptName FROM person p, dept t WHERE p.id = t.id;	Find name and department name of students who have been allotted a department
Inner/Equi/Natural JOIN	SELECT * from Emp INNER JOIN Dept WHERE Emp.empid=Dept.empid	Extracts data that meets the JOIN conditions only. A JOIN is by default INNER unless OUTER keyword is specified for an OUTER JOIN.
Outer Join	SELECT distinct * from Emp LEFT OUTER JOIN Dept Where Emp.empid=Dept.empid	It includes non matching rows also unlike Inner Join.
Self JOIN	SELECT a.name,b.name from emp a, emp b WHERE a.id=b.rollNumber	Joining a Table to itself.