SQL/PLSQL INTERVIEW QUESTION

AND ANSWER
1. SQL:- Structured query language is a specialized language
for Updating, Deleting, and Requesting information from
Database. Developed by IBM in1970s.
2. PL/SQL:- PL/SQL is a procedural language that has both
interactive SQL and procedural programming language
constructs such as interaction, conditional branching.
3. ORACLE:- The Oracle Database is an object relational Data
base management system produced and marked by Oracle
Corporation.
4. META-DATA:- Meta-data is data about data. Meta data is
structured data which describes the characteristics of a
resource.
5. Query:- To retrieve data from the data base table is called
query.
6. Attribute:- It is a particular property, which describes the
entity.
7. Field:- Every table is broken up into smaller entities called
field.
Field in the customers table consists of ID,
Name, Address, Age and Salary.
8. Table:- Table is a container that holds information about
like items.
For-Exam: - An Employee table would contain the same
basic details on each employee Name, Title, Department and
So On.
9. Table-Space:- T.S is a logical Storage structure at the
highest level that stores all the data in data files.

10. Data-Base:- A D.B is a logical Collection of relevant

data, reflecting some inherent meaning, representing some
aspect of real world.

11. DBMS:- It is a collection of programs that enables user to

create and maintain a Database.

12. RDBMS:- RDBMS stands for Relational Database

Management System. It Organizes data into related rows and
columns.

13. Sequence:- Sequence is a user created Database object

that can be shared
integers.

by multiple user to generate unique

14. Entity:- It is a thing in the real world with an

independent existence.

15. Relationship:- It is an association among two or more

entities.

16. Column:- A column is a vertical entity in a table that

contains all information associated with a specific field in a table.

17. Normalization:- Normalization is a process with which

we can reduce the redundancy . OR
Normalization means the process in which row data is converted
into filtered data.
#. 1NF:- Table that contains one or more repeating groups
identifies and remove repeating group.
#. 2NF:- Describe a table i.e. fully functionally dependent.

18. What is View, Types of view?

# View: - It is a logical table based on one or more table or

view.
It is a logically represents
subset of data from one or more tables.
Ex:- We have one table name employee it have field Name;
Emp-Id; Emp name; Emp addr; Select emp _ id from emp;
Syntax:- Create View View name as (Query or Sql Stmt).
Ex: - Create View View _ EMP as select empid from EMP;
Query: - Select*from view _ EMP where empid between 500 and
1000;

Two types of View:1. Simple - View:- Refer the Data from only single table.
- A simple view selects from one table.
- A simple view does not contain functions.
2. Complex - View:- Refer the Data from more than one table.
- A Complex view selects from one or more table.
- A Complex view contains function.

19. What is Index, Types of Index?.

#Index: - It is a Schema object which contains entry for each
value that appears in the index column of the table. OR
- Index is a data structure that improves the speed of operations
in a table.
Syntax:- Create index index name on table name (column
name).
Ex:- Create index ab_name on student (Stud_id);

Types of Index:(1). Clustered Index or Normal Index:- Clustered Index is a special type of index that records the way
records in the table are physically stored.
- The leaf nodes of a clustered index contain the data page.
- Clustered Index is physically stored a table can have 1 clustered
Index.
(2). Non Clustered - Index or Bitmap Index:- Non-Clustered Index is a special type of index in which the
logical order of the index does not match the physical stored
order of the rows on disk.
- The leaf nodes of a non-clustered index do not consist of the
data page.
- Non-Clustered Index is logically stored a table can have 249
Non-Clustered Index.
(3). Functional based - Index:-They are based on expressions.
-Enable query to evaluate value returned by an expression.
(4). Domain - Index:-They are Index which are instances of an application specific
Index of type Index.

20. Difference between Table & View?

# Table:
A Table is a list of all records.
A Table is an actual entity which contains rows of a data.

# View: A view is a subset of a table based on the parameters of the

view.
A view is the result of a query creating a subset of rows and
columns from the present table.

21. Difference between Paging & Fragmentation?

Paging occurs in memory whereas.
Fragmentation occurs on disk.

22. Difference between DBMS & RDBMS?

In DBMS there is no relationship in all tables.
In RDBMS there is different relationship b/w different table.

23. Difference between SQL & ORACLE?

SQL server 2000 is the product of Microsoft but

Oracle is the product of Oracle Corporation.
In SQL only DML, DDL etc.
In PL/SQL Procedure, trigger, function etc.

24. Difference between Function & Procedure?

#Function:

Functions return a single value through return statement.

Function use through select, Update or Delete Statement.
Function cant support DML, DDL.
Function accepts only in parameter.
Function returns a value.

#Procedure:-

 Procedure can return one or more than one values through

parameter.
Procedure cannot.
Procedure support DML, DDL.
Procedures take in, out, in out parameter.
Procedure does not return a value.

25. Difference between Stored Procedure &

Function?
#Stored Procedure:

Stored Procedure is precompiled execution plan.

Stored Procedure return the value or not.
It is precompiled code, it is stored in database.
S.P returns always integer value by default zero.

#Function: Whereas Functions are not.

Function can return a value.
It need compile whenever it will run.
Function return type could be scalar or table or table value.

26. Difference between Trigger & Procedure?

#Trigger:

Defined with create trigger.

Implicitly invoked.
Commit, save point and rollback are not allowed.
Data dictionary contains source code in user triggers.
TCL commands are not allowed.

#Procedure: Defined with create procedure.

Explicitly invoked.

 Commit, save point and rollback are allowed.

Data dictionary contains source code in user source.
TCL commands are allowed.

27. Difference between Stored Procedure &

Trigger?
# Stored Procedure:

Stored Procedure can give one O/P parameter.

Stored Procedure may return a value.
In Stored Procedure you can pass parameter.
You can write a Stored Procedure in a trigger.
Stored Procedure is written for a data- base.

# Trigger:

Trigger cannot give any O/P parameter.

But Trigger not.
But in Trigger you cannot.
But in Trigger You cannot write a Stored Procedure.
Trigger is written an individual table or view.

28. Difference between Unique key & Primary key?

# Unique key:

Allow null values and doesnt allow duplicate values.

Not accept null values.
Allow null value.
Unique key create non-cluster index.

# Primary key: Does not allow both null and duplicate values.
Accept only null value.
Doesnt allow null values.
Primary key create cluster index.

29. Difference between Synonym & Alias?

# Synonym: It is a private object.
Only the user who created can access it.
If the base table is dropped Synonym is dropped
automatically.
# Alias: It is a global object.
Any user can access it.
If the base table is dropped Alias will not drop automatically.

30. Difference between DELETE, TRUNCATE &

DROP?
# DELETE:

Delete is a DML Command.

Delete can use where condition.
It deletes specified data.
Rollback is possible.
Trigger will be fired.

# TRUNCATE:

Truncate is a DDL Command.

Truncate cannot use where condition.
It removes all the data.
Rollback is not possible.
No Trigger will be fired.

# DROP:-

Drop is the DDL Statement.

Drop commands remove the table from data dictionary.
Rollback is not possible.
No Triggers will be fired.

31. Difference between Commit, Rollback & Save

Point?
#Commit: It ends the current transaction by making all pending data
changes permanent.
To commit the SQL operations done so far.
Commit the changes from the transactions.
Commit is used to save the changes permanently on the
server.
# Rollback: It ends the current transaction by discarding all pending data
changes.
To revoke the SQL operations done so far.
Any changes from the transactions.
Rollback is used to undo the changes and restore pervious
state.
# Save Point: It marks the save point with the current transaction.
To perform line by line transaction we are using save point.

32. Difference between Grant Command & Revoke

Command?
# Grant Command:-

 It is used when we want the database to be shared with

other user.
Grant will give permission to the user on database.
G.C is used to provide access or privileges on the database
objects to the user.
Syntax:- SQL>Grant<Privilege name1>,<Privilege name2>,
on <Object name> To <User name>
Ex: - SQL> Grant select on E-mp to End users.
# Revoke Command: It is used when we want one database to stop sharing the
information with other user.
Revoke we can remove the permission.
R.C removes user access rights or privileges to the database
objects.
Syntax:- SQL>Grant<Privilege name1>,<Privilege name2>,
on <Object name> from <User name>
Ex: - SQL> Revoke insert, delete on Emp from operators.
#Privilege: - Ability to compile the function/ procedure.
Ability to execute the function/ procedure directly.

33. Difference between DECODE & CASE?

#DECODE:

Decode
Decode
Decode
Decode

can work with only scalar Values.

is used only in SQL Statement.
does not expects data type consistency.
is a function.

#CASE: Case can work with logical operators, predicates & search
able sub queries.
Case can work as a PL/SQL construct.
Case expects data type consistency.

 Case is a statement.

34. Difference between NVL & NVL2?

#NVL: It accepts 2 parameters if the 1st parameter is null then it
returns the 2nd.
NVL function substitutes a value when a null value is
encountered.
If expr1 is null, then NVL return exp2. If expr1 is not null,
then NVL returns expr1
# NVL2: It accepts 3 parameters if the 1st parameter is null then it
returns the 3rd else the 2nd.
NVL2 substitute a value when a null value is encountered as
well as when a non-null value is encountered.
If expr1 is null, then NVL2 returns expr3. If expr1 is not null,
then NVL2 returns expr2.

35. Difference between %Type & % Row Type?

# %Type: Type is declared for the column level.
Type record means we can fetch record from more than one
table.
% Type is used to change the size of any data type but only
for one record.
# % Row Type: Row Type is declared at the record level.
Row Type is used to hold the total row values.
%Row Type is used to declare a record that represents a
particular row of a table.

36. Difference between Replace & Translate?

# Replace: Replace entire string at a time.
Returns string if no match found.
Replace provides functionality related to that provided by the
Translate function.
Replace function replaces single character with multiple
characters.
# Translate: Translate replace character one to - one basis.
Returns null if no match found.
Translate provides single character one - to one
substitution.
Translate function replaces single character with single
character only.

37. Difference between Exists & In?

#Exists: EXISTS is much faster than IN (when the subquery results is very
large)
Returns true if a subquery contains any rows.
The Outer query will ran first and then only sub query.

#In: IN is faster than EXISTS (when the subquery results is very small)
Returns true if a specified value matches any value in a
subquery or a list.
The sub query will run first and then only outer query.

38. What is Sub query? Types of Sub query?

# Sub query: - Sub query can be defined as a select query that
returns single or multiple values.
Types of Sub query:(1)
Single row Sub query: - These query return only one
row from the inner select statement is called Single row sub
query.
Ex: - = : <>; <; >; >=; <=.
# Select * from emp where salary = (select salary from
emp where emp id= (10));
(2)
Multi row Sub query: - These query return more than
one row from inner select statement.
Ex:- IN; ANY; ALL.
# Select. e name, sal, Dept no from emp where sal in
(select min (sal) from emp group by dept no);
Syntax: - SQL> Select Select List from Table Name where
Column Name operator (select select List from Table Name);

39. What is Cursor? Types of Cursor?.

- Cursor is a mechanism by which you can assign a name to a
Select Stmt and manipulate the information within the SQL
Statement.
- Cursor is a private SQL are used to execute SQL Statement.
Two Types of Cursor:(1). Implicit - Cursor: - It is automatically declared by Oracle
server. It is private memory area allocated by O.S.
(2). Explicit Cursor: - It is declared by programmer or user. It is
easy to trap error.

Four Steps to perform explicit

cursor:-

(1)Cursor Declaration: - In the Declaration section.

(2)Cursor Opening: - In the Execution section.
(3)Cursor Fetching: - Fetch the data from cursor into PL/SQL
variables or records in Execution Section.
(4)Cursor Closing: - Close the cursor in the Execution section
before you end the PL/SQL Block.

# Cursor Attribute four Types:(1).

(2).
(3).
(4).

SQL
SQL
SQL
SQL

%
%
%
%

Row Count: - Number Return.

Is Open: - T/F Return.
Found: - T/F Return.
Not Found: - T/F Return.

#Cursor Advantages:- Using Cursor to getting multiple values.

- One Cursor has multiple Select Statement.

#Cursor Disadvantages:- It is returned by only one row at a time.

- It gives less performance.
- Each time we fetch a row from the Cursor, where as a
normal cursor select statement query makes only
one trip.
#Cursor Example:// Declaring a cursor //
Cursor EMP _Cursor is
Select EMP no, E name, Sal from EMP;
Begin
// Opening a cursor //
Open EMP _ Cursor;
Loop

// Fetching records from a cursor //

Fetch Emp _ Cursor into Var _ Emp no, Var _ E Name, Var _ Sal;
// Testing exit Conditions //
Exit when Emp _ Cursor % Not Found If (Var _ Sal >1000) then
DBMS_Out put. put_Line (Var _Emp No!! !! Var _E Name!! !!
Var_Sal);
ELSE
DBMS_Out put. put_Line (Var _E Name!! Sal is less then 1000);
End if;
End Loop;
// Closing the Cursor //
Close EMP _ Cursor;
DBMS_Output. put_line (Done);
End;

40. What is Trigger? Level of Trigger? Types of

Trigger?
# Trigger: - A Trigger is a Pl/SQL block structure which is fired
when a DML statement like, Insert, Delete, Update is executed on
a data base table. A trigger is triggered automatically when an
associated DML statement is executed.
OR
- Trigger is like as procedure. This procedure will call at some
events and occurring. Like Insert, Update, Delete.
- Ex:- ATM Machine .

- Ex:- Create table product(Product_id number (5), Product_

name Varchar2(32), Supplier _ Name Varchar2(32), Unit price
number(7,2))
# Level of Trigger:(1). Row level of Trigger: - An event is triggered for each row
updated, inserted or deleted.
Ex: - Create or Replace Trigger Deptupdate after update on
Dept For each row.
(2). Statement level of Trigger:- - An event is triggered for
each SQL Statement executed.
Ex: - Create or Replace Trigger Dept Delete after Delete on
Dept.
# Three Types of Trigger:(1). Before - Trigger:- These trigger fire before any
transaction are implemented like Insert, Update and Delete.
(2). After - rigger:- These trigger fire after any transaction is
implemented like Insert, Update and Delete,
(3). Instead of Trigger: - These are special bind of trigger that
is defined on data base view.

41. What is Join? Types of Join?

* Join: - Join is a query that combines rows from two or more
tables, views or materialized views.
- A Join is performed whenever multiple tables appear in the
queries from clause.
Ex:- Join queries are used to select data for multiple tables and
display that data in a single O/P.
Syntax:- Where Table1 . Column 1 = Table 2. Column 2.
Ex:- SQL> Select Emp no, E name, D name, Loc from Emp, Dept;

Or: - SQL> Select Emp no, E name, Sal, Grade from Emp, sal
Grade;

#Four Types of Join:(1). Equi or Simple or Inner Join:- Returns the rows from two
or more tables joined together based upon a equality conditions
in the where clause.
Ex:- Select * from emp, dept where emp dep no = dept . dept no;
(2). Non - Equi Join:- Returns the rows from two or more tables
based upon a relationship other than the equality conditions in
the where clause.
Ex:- Select E. E name, E. Sal, S Grade From Emp E, Sal Grade S
where E. Sal Between S. Losal AND S.Hisal;
(3). Self - Join:- Self Join joins a table to itself. Basically it is for
the Hierarchical relationship.
Ex:- Select E1. E name, E2. E name from Emp E1, Emp E2 where
E1 . Mgr = E2. EMP no;
Syntax:- SQL> Select Columns From Table1 T1, Table1 T2 where
T1. Column 1 = T2. Column 2.
(4). Outer - Join:- Outer Join combines two or more tables
returning those rows from one table that have no direct match in
the other table.
Ex:- Select E. E name , D. Dept Name from Emp E, Dept D where
e. dept no = D. Dept no(+);
Syntax:- Select Table1. Column, Table2. Column from Table1,
Table2 where Table1. Column = Table2. Column (+);

42. What is Procedure? Types of Procedure?

* Procedure:- Procedure is a type of sub program that performs an

actions.
- Procedure may return one or more values through parameters or
may not return at all.
#Three Types of Procedure:(1). In Mode:- It is read only value from calling environment.
(2). Out Mode:- It is write only value from calling environment.
(1). In /Out Mode:- It is read and write only value from calling
environment.
# Stored Procedure:- Stored Procedure is a sequence of
statement that perform specific function.

43. What is Function? Types of Function?

* Function: - Function is always returns a value using the return
statement.
# Two Types of Function:(1)Single Row Function: - These functions return a single result
for every row of a queried table or view.
- Single row function are used to manipulate data Items.
(2)Multi Row Function:- These functions manipulate group of
rows and return one result per group of rows.
- Multi rows function or aggregate functions are SUM; MAX;
MIN; AVG; COUNT.

44. What is ASCII - Function?

It returns the ASCII representation in the character Database
set of First Characters of the CHAR.
Syntax: - SQL> SELRCT ASCII (A), ASCII (APPLE) FROM DUAL;

45. How Many types of SQL in Oracle?

There are five types of SQL in Oracle.
(1). Data Retrieval statement:- Select Statement.
(2). Data Manipulation Language (DML) statement:- It is used to retrieve, store, modify, delete, insert and update
data in database.
- Insert; Update; Delete Statement.

(3). Data Definition language (DDL) statement:- It is used to create and modify the structure of database objects
in database.
- Create; Alter; Drop; Rename; Truncate Statement.
(4). Transaction Control Language (TCL) Statement:- It is used to manage different transaction occurring within a
database.
- Commit; Rollback; Save point Statement.
(5). Data Control Language (DCL) statement:- It is used to create roles, permissions and referential integrity as
well it is used to control access to database by searching it.
- Grant; Revoke Statements.

46. What is Select, Insert, Delete & Update

Statement?
* Select Statement:- It is used to select data from a data base.

- The select statement is used to retrieve Data from One or More,

Tables; Object tables; Views; Object Views; Materialized views.
Syntax:- select column _ name from table _ name
And: - Select * from table _ name
Ex:- select first _ name from student _ details;
Or Select name, age, salary from employee where age > 50;
* Insert Statement:- It is used to add new rows of data to a
table.
Syntax:- Insert into table _ name (Column1, Column2, Column3)
Values (Value1, Value2, Value3);
Ex:- Insert into employee (Id, Name, Dept, Age, Salary location)
Values (105, Chandan, Developer, 26, 35000);
* Delete Statement:- It is used to delete rows from a table.
Syntax:- Delete From table name [ where condition]
* Update Statement:- It is used to update existing records in a
table.
Syntax:- Update table _ name set column 1= Value, Column 2 =
Value 2, Where some _ column = some _ value.
Ex:- Update persons set Address = Anandpuri 7, City= Patna
where last Name = Kmar and First Name = Rajnish .

47. What is Alias? Types of Alias?

- Aliases are defined for columns and table.
- Alias is an Alternate Name given for any Oracle Object.
- Alias in Oracle are two types:-

* Column Alias: - Column Alias renames a column heading in a

query.
- Alias heading appears in upper casing by Default.
* Table Alias: - Table Alias Renames the Original name of the table
in a SQL Statement.
- Table Alias are very important when working with self joins.
- The Table Alias is applied for the current SQL Statement only.
Syntax:- Select Column _ Name (s) from table _ name as alias _
name.
Ex:- Select first _ name As name from Student _ details;
O/P:- Rahul Sharma, Rajnish kr, Priya Chandra, Ashutosh mishara.

48. What are Group by, Order By, & having by?
*Group By:- The Group by clause is used along with the group functions to
retrieve data grouped according to one or more columns.
For Ex: - If U want to know the total amount of salary spent on
each department, the query would be:Syntax: - Select column1, sum (Column 2) from list of table
Group By Column list;
Ex:- Select dept, sum (Salary) from employee group by dept;

*Order By:- The order by clause is used in a select statement to

sort results either in ascending or descending order. Oracle sorts
query results in ascending order by default.
For Ex:- If U want to sort the employee table by salary of the
Employee, the SQL query would be.

Ex: - Select name, salary from employee order by salary.

*Having By: - Having clause is used to filter data based on the

group functions. This is similar to where condition but is used with
group functions. Group functions cannot be used in where clause
but can be used in having clause.
Ex: - Select dept, Sum (Salary) from employee group by dept
Having Sum (Salary) > 25000.

49. What is where clause?

- Where clause is used when you want to retrieve specific
information from a table excluding other irrelevant data.
Ex:- The where clause is used to extract only those records that
fulfill a specified criterion.
Syntax: - Select column _ name from table _ name where column
_ name operator value.
Ex:- Select * from persons where city = PATNA

50. What is Operator? Types of Operator?

(1). And Operator: - For a row to be selected all the specified
condition must be true.
Ex: - Select Ename, Sal, dept no, Job from EMP where dept no= 20
And Job = MANAGER;
(2). Or Operator: - For a row to be selected at least one of the
conditions must be true.
Ex: - Select Enema, Sal, dept no, Job from EMP where dept no= 20
Or Job = MANAGER;
(3). Not Operator: - For a row to be selected the specified
condition must be false.

Ex: - Select Ename, sal, Job from EMP where Not Job =
MANAGER;

51. what is In Operator, Between and Operator & Is

Null Operator?
* In Operator: - This operator can be used upon any data type.
- This Operator is used to test for values in a specified list.
Ex:- Select Ename, Sal, job from Emp where Ename IN
(FORD,ALLEN);
* Between And Operator: - This Operator is used to display
rows based on a range of value.
- The Lower Limit should be declared first.
Ex:- Select Ename, Sal, Job from Emp Where sal Between 1000
AND 1500;
* Is Null Operator:- This Operator test for Null Values.
- It is the only operator that can be used to test for Nulls.
- The Negation is Is Not Null.
Ex:- Select Ename, Dept no, Comm From Emp Where Comm is
Null.

52. What is Like Operator?

- The like Operator is used to search for a matching character
patterns.
- Like Operator is used to list all rows in a table whose column
value match a specified pattern (%).
- The Character patterns matching operation is referred as a wild
card search.

Ex:- Select Ename, Job, From Emp where Ename Like S% ;

53. What is Sysdate?

- It is a date function that returns current date and time.
- Sysdate is generally selected upon a dummy table.
Ex:- SQL> Select Sysdate from Dual;

54. What is Set Operator?

Set Operators are used to combine information of similar data
type for one or more than one table.
Difference types of set operator.
1.
2.
3.
4.

Union.
Union all.
Interacted.
Minus.

55. What is Pseudo Column?

- Pseudo Column is an act as a data base table column, but its
not a actual column in a table. We can fetch values from a pseudo
column.
- Pseudo Column behaves like a table column, but is not actually
stored in a table.
Ex:- User; User Id; Row num; Row Id; Level; Next Val; Curval;
Sysdate

56. What is Exception? Types of exception?

- Exception is an identifier in PL/SQL raised during the execution
of block.
- Run time error is called exception.
Three types of Exception.

1. Predefined:- It can be only handle.

2. User Defined:- It can be declare, raise handle.
3. Non Predefined:- Define and Handle.

57. What is Package? Types of Package?

- Package is a method to handle all the required function and
procedure together.
- Package cannot be executed.
- Package cannot be nested.
It consists of two parts:1). Package Specification: - P.S contains information about the
content of package and global & public variable.
2). Package Body: - P.B contains the actual executable code for
the object describe in the package specification.

58. What is Literals? Types of Literals?

Literals and Constants value are synonyms to one another and
refer to a fixed data value.
Types of Literals recognized by Oracle are:1). Text Literals.
2). Integer Literals.
3). Number Literals.
4). Interval Literals.
Ex:- Employee Information Manager s specification N Nchar
Literal.

59. What is Synonyms? Types of Synonyms?

Synonyms is an alternative name for objects such as Tables, View,

Sequence, Stored procedures and other data base objects.
Syntax: - Create [Public] synonym <Synonym Name> for <
Schema Name>. <Object Name>;
Ex: - SQL> Create Synonyms EMP Info for Scott. EMP;
Types of Synonyms:1). Private Synonym.
2). Public Synonym.

60. What is Merge Statement?

The Mere Statement is used to select rows from one or more
sources for update or Insert into one or more table.
Ex:- SQL> Create Table My Bonus (Emp no Number, Bonus
Number Default 100);

61. What is Materialized View?

- A M.V is a data base object that contains the result of a query.
- M.V are used in data ware houses and data Marts.
Ex: - SQL> Grant Query Rewrite To SCOTT;
SQL> Grant Create Materialized view To SCOTT;

62. What is Object Tables?

- Object Tables are created by using the user defined data types.
- In Object table each row or record is treated as an object.
Syntax: - Create or replace Type < Type Name> as object
(Column Name 1 Data Type (Size), Column Name 2 Data Type
(Size), Column Name N Data Type (Size)),

63. What is Object View & Inline View?

* Object View: - O.V is a view of user defined type, where each
row contains objects and each object with an object Identifier.
Object View Support:- Object
- Object type.
- Nested Tables.
* Inline View: - The Inline view is a named sub query where the
inputs for the query depend on the values provided by the outer
query.
The Values will be provided in every run of the query.

64. Difference between Synonym & View?

* Synonym: - Synonym is an alternate name for a base
table or view. Each user can assign his or her own
synonyms for any table or view that was created by some
other fully qualified name.
* View: - View is a predefined selection of data in base
tables. It is a virtual table that does not physically exist
but is processed as a table. It is derived from one or more
base tables or views or combinations of views and tables.
65. What are constraints? Types of Constraints?
Constraints can be specified when a table is created (With the
create table statement) or after the table is created (With the
after table statement).
There are Six types of Constraints:1). Not Null Constraints.

2). Unique Constraints.

3). Primary Key Constraints.
4). Foreign Key Constraints.
5). Check Constraints.
6). Default / Ref Constraints.

66. What are Not Null, Unique, Primary Key,

Foreign Key, Check Constraints and Default
option?
* Not Null:- A Not Null Constraints prohibits a column from
containing Null Values.
- Not Null should be defined only at column level.
- The default constraints is not specified is null constraints.
Ex:- SQL> Create table students ( Stud No Number(6), stud Name
Varchar2 (25), Course Name Varchar2 (25), Join Date Date Not
Null);
* Unique:- Same as primary key but can accept one null key only.
Ex:- SQL> Create table Promotions( Promo _ Id Number(6), Promo
Name Varchar2 (20), Promo category Varchar2 (15), Promo cost
Number (10,2) Promo Beg Date Date, Promo End Date Date);
* Primary Key:- Uniquely identify each entity in the table and does
not contains null & duplicate values.
Ex:- SQL> Create table Locations (Location Id Number (4), St
Address Varchar2 (40) Not Null, Postal Code Varchar2 (6), City
Varchar2 (30));

* Foreign Key:- A F.K is one or more column whose value must exit
in the primary key of another table
* Check Constraints:- It defines a condition that each row Must
satisfy.
- To satisfy the constraint, each row in the table must make the
condition either true or unknown.
* Default Option:- The Default option is given to maintain a
default value in a column.
- The Default value can be literal, an expression or a SQL function.
- The Default expression must match the data type of the column.
* Composite key:- The Combination of two or more than two keys
which identifies entities uniquely.
* Super Key: - All possible key which can identify entities in the
table uniquely.
* Alternate Key:- Rest of the condition key which is not the
primary key.

67. What is Group function?

Group function is built in SQL functions that operate on groups of
rows and return one value for the entire group.
These functions are: - Count; Max; Min ; Avg ; Sum; Distinct.

68. What is Count & Distinct?

*Count:- This function returns the number of rows in the table
that satisfies the condition specified in the where condition.
Ex: - If U want the No of employees in a particular department,
the query would be: - Select Count (*) from employee where dept
= Electronics;

*Distinct:- This function is used to select the distinct rows.

Ex:- If U want to select all distinct department names from
employee table, the query would be:- Select Distinct dept From
employee;
- To get the count of employees with unique name, the query
would be:Select count (DISTINCT Name) from employee;

69. What are Min, Max, Average and Sum Function?

* Minimum (MIN) Function:- This function is used to get the
minimum value from a column. It ignores Null values.
Ex:- To get the minimum salary drawn by an employee the query
would be:- Select MIN (Salary) from employee.
* Maximum (MAX) Function:- This function is used to get the
maximum value from a column. It ignores Null values.
Ex:- To get the maximum salary drawn by an employee the query
would be:- Select MAX (Salary) from employee.
* Average (Avg) Function:- This function is used to get the average
value of a numeric column. - It ignores Null values.
Ex:- To get the average salary the query would be :- Select Avg (Salary) from employee.
* Sum Function:- This function is used to get the sum of a numeric
column.
- It ignores Null values.

Ex:- To get the total salary given out to the employee the query
would be :- Select Sum (Salary) from employee.

70. What is Conversion Function? Types of

Conversion Function?
The Conversion function converts a value from one data type to
another.
Two types of Conversion function:1). Implicit Data Type Conversion:- Implicit Data Type Conversion
work according to the conversion specified by oracle.
- CHAR to DATE conversions succeed only if the character strings
represent the default format of date. DD MON - YY.
- VARCHAR2 / CHAR NUMBER.
-VARCHAR2 / CHAR DATE.
2). Explicit Data Type Conversion:- SQL Provides three functions to
convert a value from one data type to another.
- The explicit conversion functions are:. To CHAR To Character Conversion.
. To DATE To DATE Conversion.
. To NUMBER To Number Conversion.

71. What is Aggregate or Group function?

- These functions return a single row based on group of rows.
- These functions can appear in select lists and having
clauses only.

72. What is Standard deviation; Count Function?

* Standard deviation:-

- It returns the standard deviation of the column.

- It ignores Null Values.
EX:- Select STD DEV (Sal), STD DEV (DISTINCT Sal) from Emp;

* Count Function:- It returns the number of rows in the query.

- If * is used returns all rows, Including duplicated and NULLS.
Ex:- Select Count (*) From Emp;
- Select Count (Sal), Count (Comm) from Emp;

73. What is Date Function?

* Add Month Function:- It returns the Date D plus or minus n Months.
- The argument n can be any positive or Negative Integer.
Ex:- SQL> Select sysdate , Add- Months (Sysdate,2) from Dual;
* Month between Function:- It returns number of months b/w dates d1 and d2
- If d1 is later than d2, the result is + Ve else Ve.
Ex:- Select E name , Hire date, sysdate , Months _ Between
(Sysdate, Hire date) from Emp;
* Next Day Function:- It returns the date of the 1st week day Named by Char, That is
latter than the Dated.
- The char must be a day of the week in the sessions date
language.
Ex:- SQL> Select sysdate , Next _ Day (Sysdate, WED) from
Dual;
* Last Day Function:- It returns the date of the last day of the month that constants
D.

- Mostly used to determine how many days are left in the

current Month.
Ex: - SQl> Select Sysdate, Last _ Day (Sysdate) Last day from
dual;

74. What is Sub String; In String Function?

* Sub String:- Returns specified characters from character value,
Starting from a specified position m to n characters long.
Ex:- Select ABCDEFGH string , SUBSTR (ABCDEFGH,3,4) sub
string from DUAL;
* In Sting Function:- It returns the Numeric position of a named
character.
Ex:- Select STRING String InSTR (STRING, R) In string from
Dual;

75. What is LPAD; RPAD Function?

* LPAD:- Pads the character value right justified to a total width
of n character positions.
- The default padding character is space.
Syntax:- LPAD (Char1, n, char2)
Ex:- Select page1 string LPAD (Page1, 15, *) L Padded from
Dual;
* RPAD:- Pads the character value left justified to a total width of
n character positions.
- The default padding character is space.
Syntax:- RPAD (Char1, n, char2)
Ex:- Select page1 string RPAD (Page1, 15, *) R Padded from
Dual;

76. What is LTRIM; RTRIM Function?

* L TRIMS Function:- It enables to TRIM Heading characters from a

character string.
- All the leftmost characters that appear in the set are removed.
Syntax:- L TRIM (Char, SET)
Ex:- Select Job, L TRIM (Job, MAN) L Trimmed From Emp Where
Job Like MANAGER
* R TRIMS Function:- :- It enables the trimming of trailing
characters from a character string.
- All the Right most characters that appear in the set are
removed.
Syntax:- R TRIM (Char, SET)
Ex:- Select R TRIM (Job, ER), Job From Emp Where L TRIM (JOB,
MAN) LIKE GER;

77. What is TRIM; REPLACE; Translate and CHR

Function?
* TRIM Function:- It enables to trim Heading or trailing characters
or both from a character string.
- If Leading is specified concentrates on leading characters.
- If Trailing is specified concentrates on Trailing characters.
Syntax:- Trim (Leading / Trailing/ Both, Trim char From Trim
Source)
Ex:- Select MITHSS String TRIM (S From MITHSS ) Trimmed
From Dual;

* REPLACE Function:- It returns the every occurrence of search

string replaced by the replacement string.

- It substitutes one string for another as well as removes

character strings.
Syntax:- Replace (Char, Search _ Str, Replace _ Str)
Ex:- Select E name, Replace (JOB, MAN, DAM) Replaced from
Emp where Job = MANAGER ;
* Translate Function: - Used to translate character by character in
a string.
Syntax: - Translate (Char, From, To)
Ex: - Select Job, TRANSLATE (Job, P, ) From Emp where Job =
PRESIDENT;
* CHAR Function: - It returns a character having the ASCII
Equivalent to n.
- It returns the Equivalent for n in data base character set or
National Character set.
Syntax: - CHR (n USING NCHAR _ CS)
Ex: - Select CHR (67)!! CHR (65)!! CHR (84) Sample From Dual;
78. What is OLAP; ROLLUP?
* OLAP:- Some features for query processing in ORACLE include
the use of ONLINE ANALYTICAL PROCESSING (OLAP) Upon the
Data base.
- OLAP features are useful for Data warehousing and data mart
applications.
- OLAP operations are performance enhancements.
* TOP _ N QUERIES.
* GROUP By.
* CUBE.

* ROLLUP:- It is a group by operation and is used to produce

subtotals at any level of the aggregation.
- The generated sub Totals Rolled Up to produce Grant total.
Syntax:- Group by rollup (Column 1, Column 2)
Ex:- Select Dept no, SUM (SAL) from Emp group by Rollup (Dept
no);
79. What are Union; Union All; Intersect and Menu?
* Union:- Returns the combined rows from two queries, sorting
then and removing duplicates.
Ex:- Select Job From EMP where Dept No = 20; Union
- Select region _ name from regions Union select region _ name
from regions;
* Union All:- Returns the combined rows from two queries, without
sorting or removing duplicates.
Ex:- Select Job From EMP where Dept No = 30; Union All
- Select region _ name from regions Union all select region _
name from regions;
* Intersect:- Returns only the rows that occur in both queries,
result sets, sorting then and removing duplicates.
Ex:- Select Job From EMP where Dept No = 30; Intersect
- Select region _ name from regions Intersect select region _
name from regions;
* Menu:- Returns only the rows in the first result set that do not
appear in the second result set , sorting then and removing
duplicates.
Ex:- Select Job From EMP where Dept No = 30; Menu

- Select region _ name from regions Menu select region _ name

from regions;

80. What is Data Types? Types of Data Types?

- The Data Type associates a fixed set of properties with that
value stored.
- The Data type defines the domain of values that each column
can contain.
- The Values of One Data Type are different from another Data
type.
- Built in Data Types:* Character Data type.
* Number Data type.
* Long & Raw Data type
* Date time Data type.
* Large Object Data type.
* Row Id Data type.
1). Character Data type:- They store character Data, which can
be alphanumeric data collection.
- The data is stored in strings with Byte collections as values.
- Oracle supports both single & multi byte character sets.
(1). CHAR Data -Type:- The Size should be specified.
- It specifies fixed length character strings.
- The Default length is 1 Byte and the maximum is 2000 Bytes.
- The size of a character can range from 1 Byte to 4 Bytes
depending on the database character set.

(2). N CHAR Data -Type:- Maximum length is determined by the

National character set definition.
- Minimum size is 2000Bytes and size has to be specified.
- First time defined in ORACLE 9i and contains Unicode data only.
(3). VAR CHAR2 - Data Type:- Specifies the variable length
character string.
- Minimum size is 1 Byte and the maximum size is 4000 Bytes.
- It occupies only that space for which the data is supplied.
(4). N VAR CHAR2 - Data Type:- First time defined in ORACLE 9i.
- It is defined for Unicode data only.
- The minimum size is 1 Byte and maximum size is 4000 Bytes.
(5). Numeric - Data Type:2). Number Data type:- It stores zero, positive and negative
fixed and floating point number.
- The precision can range from 1 to 38 the scale can range from
84 to 127.
- The range of magnitude is:- 1.0 * 10 - 130 to 9.9 9 * 10 + 125.
# ). Float Data Type:- It facilitates to have a decimal points
anywhere from the first to the last digit or can have no decimal
point at all.
[Floating point number with precision of 126 binary (or 38
decimal).]
3). Long & Raw Data type:- This data type stores variable
length character strings.
- It is used to store very length Text strings.

- The length of Long Values may be limited by the memory

available on the computer.
4). Date & Time Data type:- It is used to store Date and Time
Information .
- The Information revealed by date is.
- Century, Year, Month, Date, Hour, Minute, Second.
- This is either length zero, if the column is empty or 7 bytes.
#). Time stamp Data Type:- It is an extension of the DATE Data
Type.
- It stores the data in the form of
- Century, Year, Month, Date, Hour, Minute, Second.
- [This is length zero if the column is empty or up to 11
bytes.]
#). Raw & Long Raw Data Type:- Raw & Long Raw Data Types
are intended for storage of binary data or byte strings.
- Row and Long raw are variable length data types.
- The Oracle converts the Raw & Long raw data into
Hexadecimal form.
- [ Variable length binary data from 1 byte to 4 KB]
5). Large Object Data type:- The Built in LOB data types
are:* BLOB * CLOB * NCLOB
- These data types store the Data Internally.
-The LOB Data types can store large and unstructured data like
Text, Image, Video, and Spatial data.
- [The Maximum storage size is up to 4 GB]
* BLOB Data Type:- It stores unstructured binary large objects.

* CLOB Data Type:- It Stores single byte and multi byte

character data.
* NCLOB Data Type:- It Stores Unicode data using the National
character set.
6). Row Id Data Type:- Each row in the data base has an
address.
- The rows address can be queried using the pseudo column ROW
ID.
- [A value coded in base 64 that is the pointer to the location of a
row in a table.]

82. What is Correlated subquery?

Ans:- A correlated subquery (also known as a synchronized subquery) is a
subquery (a query nested inside another query) that uses values from outer
query. The subquery is evaluated once for each row processed by the outer
query.
OR
A query is called correlated subquery when both the inner
query and the outer query are interdependent. For every row
processed by the inner query, the outer query is processed as
well. The inner query depends on the outer query before it can
be processed.
SELECT
WHERE

p.product_name
p.product_id

FROM

(SELECT

product

o.product_id

p
FROM

order_items
WHERE o.product_id = p.product_id);

83. What is Guidelines to Use G.F?