FAQ's in SQL/PLSQL-3

What is Instead Of Trigger?

This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.

What is the Sequence of Firing Database Triggers?

a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger

What is the Difference between PL/SQL Table & Nested Table?

PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.

What is the Difference between Nested Table & Varray?

Nested Table
a) This are Sparse
b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

Varray
a) This are Dense
b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces

What are the various SQL Statements?

a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

What is Rowid?

It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.

What is Partitioning?

It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

What is a Cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.

What is the Difference between Nested Subquery & Correlated Subquery?

Nested Subquery
a) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query
b) Outer query is driver by Inner Query

Correlated Subquery
a) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query
b) Inner Query is Driven by Outer Query

What is the Difference between Translate & Replace?

Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.