Before Facing PL/SQL Interview

# SQL : The language used to access a relational database is called SQL

# DRL- (I)Select

# DML-

(I) Insert

(II) Update

(III) Delete

# DDL-

(I) Create

(II) Alter

(III) Drop

(IV) Rename

(V) Truncate

# TCL-

(I) Commit

(II) Savepoint

(III) Rollback


# DCL-

(I)Grant

(II) Revoke


# NULL: In database terms, nothing is called null.

# DISTINCT: To obtain unique value or a column contains duplicate value.

# PRIMARY KEY: Is an column used for identifying the uniqueness of each row in table.

# FORGIN KEY: Then common column appering in the chilled table is referenced as forgin key .

# JOIN : Is a query that combaine row from two are more table,view,M.view,

# CARTATION PRODUCT: Is a join query without join condition.

# EQUI/SIMPAL/INNER JOIN : Is a join query with a join condition .Containg an equality (=) oprator.

# TABLE ALIAS : Can be used table of names.

# SELF JOIN : It’s a join of table to itself.

# NON EQUI JOIN: When join condition that are executed when such as no column in one table cooesponding directly to a column in other tables.

# OUTER JOIN(which table want null use + sign) : An outer join extends the result of
Simpal join.

# ANSI/ISO JOIN SYNTAX : join table name on join condition From Emp join dept
on Emp.deptno=Dept.deptno;

# How many comparision do you need : - If you join N table then N-1 join condition here.

# NATURAL JOIN: Is a join between two table where oracles join the table according to the columns in the two table sharing in the same name.

# CROSS JOIN: ISO syantax producing creationproduct through use of cross join.

# GROUP FUNCTON : The data from main query can be displayed by using a group function is a sub query.

# SUB QUERY : Query within a query OR select statement nested within select statement.

# TYPES OF SUB QUERY:

1.Single Row Sub Query :Main query expect return only one value.
2.Multipel Row Sub Query : Return more the one row.
3.Inline(Inline view) Row Sub Query :Is a sub query use in from clause.
4.Multipel Column Sub Query : Return more then one column of return data in condition.

# Constrints : Are used to define an intigrity constraints, as rule that restrict the value in Database.

# Data Intigrity: It’s a state in which all the data value stored in database collect.

1.Entity Intigrity : It define a row as unique Enitity for particular table.
2.Domain Intigrity :Validates the entries for a given column.
3.Refrential Intigrity :Define relationship between tables where record are entered are deleted.
4.User define Intigrity : allow to difine specfic business rule that to not full into any one of the other Intigrity catgories.

# UNIQUE KEY : Enforce uniquness on value in constraints columns.

# CHECK : Enforce to value add to constraint column must be present static list.

# NOT NULL : Column such that the column may not be null for any row.

# SEQUENCE : Database object that genrate integer according to rules specified at time of
sequence is creation.

# INDEXES : Are schema objects that provide a mapping of all the values in the table column.

1.B-TREE INDEX: Nornaml Index
2.BETMAP INDEX : Store rowed associated with key value Bitmap.
3.PARTIONED INDEX : Table Data can pe partion is posible
4.FUNCTION BASED INDEX :They are based on expression
5.DOMAIN INDEX :Instance of application specific index of type index type.
6.REVECE KEY INDEX :

# Synonmys: Schema objects which act as an alternate name for an existing objects.

1.Private Synonmys
2.Public Synonmys

# EXISTS OPRATER : Test to existence of row in sequence.

# ROWNUM: Is a result set row postion show after transaction.

# SCHEMA: Is a logical grouping of database objects based on userwho own the objects.

# PL/SQL : Stands for procedurial language Extention to SQL.

1.Anonymoys Block :
2.Named Block:
3.Sub Programed Block:

# CURSSOR: Used to process multipal row retrival from database(with select statement).
OR
Is a Handler or Pointer to the context area.
1.Implicity Cursor :
2.Ecplicit Cursor
3.Refrence Cursor

# CURSOR ATTRIBUTE :

1.NOT FOUND
2.FOUND
3.ISOPEN
4.ROWCOUNT

# EXCEPTION: To handle unpredictable situation ,plsql provide mechnisum is known as Exception.

1.EXCEPTION HANDLING :
2.EXCEPTION TRAPING :
3.EXCEPTION PROPOGATION :

Genral Categories:
1) System define Exception
2)User Define Exception

# PROCEDURE : Is a named program unit that can be called on directly buy its name.
( Not pass littreal)

# FUNCTION : Is a named program unit that return atlest singal value.

# PACKAGE : Is simpaly collection of related type procedure,functions.

1.SPECFFICATION
2.BODY

# TRIGEER: Is a named pl/sql block stored in database and executed implicit when trigeer event is occurs.

# COLLECTION : Manupulate many variable at once as one unit DATA TYPE LIKE.

1.INDEX BY TABLE : Non scaler Type
2.NEXTED TABLE : created sequential key& like key can not be negative.
3.VARRY: Is data type ,simlar ina rray.

# Query : Its an opration that retrive data from one are more table or view.