1. WHAT IS DATA OR
INFORMATION?
Ans: The Matter that we feed into the Computer is
called Data or Information.
2. WHAT IS DATABASE?
Ans: The Collection of Interrelated Data is called
Data Base.
3. WHAT IS A DATABASE
MANAGEMENT SYSTEM (DBMS) PACKAGE?
Ans: The Collection of Interrelated Data and some
Programs to access
the Data is Called Data Base Management System
(DBMS).
4. WHEN CAN WE SAY A DBMS
PACKAGE AS RDBMS?
Ans: For a system to Qualify as RELATIONAL DATABASE
MANAGEMENT system,
it must use its RELATIONAL facilities to MANAGE the
DATABASE.
5. WHAT IS ORDBMS?
Ans: Object (oriented) Relational Data Base
Management System is one
that can store data, the relationship of the data,
and the behavior of the data
(i.e., the way it interacts with other data).
6. NAME SOME CODD'S RULES.
Ans: Dr. E.F. Codd presented 12 rules that a
database must obey if it
is to be considered truly relational. Out
those, some are as follows
a) The
rules stem from a single rule- the ‘zero rule’: For a system to Qualify as
RELATIONAL DATABASE MANAGEMENT system, it must use its RELATIONAL facilities
to MANAGE the DATABASE.
b) Information
Rule: Tabular Representation of Information.
c) Guaranteed
Access Rule: Uniqueness of tuples for guaranteed accessibility.
d) Missing
Information Rule: Systematic representation of missing information as NULL Values.
e) Comprehensive
Data Sub-Language Rule: QL to support Data definition,
View definition, Data manipulation, Integrity,
Authorization and Security.
7. WHAT ARE HIERARCHICAL,
NETWORK, AND RELATIONAL DATABASE MODELS?
Ans: a) Hierarchical Model: The Hierarchical
Model was introduced in
the Information Management System (IMS) developed
by IBM in 1968. In this data is organized as a tree structure. Each tree is made
of nodes and branches.
The nodes of the tree represent the record types
and it is a collection
of data attributes entity at that point. The
topmost node in the structure is called the root. Nodes succeeding lower levels
are called children.
b) Network Model: The Network Model, also
called as the CODSYL database
structure, is an improvement over the Hierarchical
mode, in this model concept of parent and child is expanded to have multiple
parent-child relationships, i.e. any child can be subordinate to many different
parents (or nodes). Data is represented by
collection of records, and relationships among data
are represented by
links. A link is an association between precisely
two records. Many-to-many relationships can exists between the parent and child.
c) Relational Model: The Relational Database
Model eliminates the need
for
explicit parent-child relationships. In RDBMS, data is organized in
two-dimensional tables consisting of relational, i.e. no pointers are
maintained between tables.
8. WHAT IS DATA MODELING?
Ans: Data Modeling describes relationship between
the data objects. The relationships
between the collections of data in a system may be graphically represented
using data modeling.
9. DEFINE ENTITY, ATTRIBUTE
AND RELATIONSHIP.
Ans: Entity: An Entity is a thing, which can be
easily identified. An entity is any object, place, person, concept or activity
about which an enterprise records data.
Attribute: An attribute is the property of a given
entity.
Relationship: Relationship is an association among
entities.
10. WHAT IS ER-MODELING?
Ans: The E-R modeling technique is the Top Down
Approach. Entity
relationship is technique for analysis and logical
modeling of a system’s data requirements. It is the most widely used and has
gained acceptance as the ideal database design. It uses three basic units:
entities, their attributes and the relationship that exists between
the
entities. It uses a graphical notation for representing these.
11. WHAT IS NORMALIZATION?
Ans: Normalization is a step-by-step decomposition
of complex records
into simple records.
12. WHAT ARE VARIOUS NORMAL
FORMS OF DATA?
Ans: The First Normal Form 1NF,
The Second Normal Form 2NF,
The Third Normal Form 3NF,
The Boyce and Codd Normal Form BC NF.
13. WHAT IS DENORMALIZATION?
Ans: The intentional introduction of redundancy to
a table to improve
performance is called DENORMALIZATION.
14. WHAT ARE 1-TIER, 2-TIER,
3-TIER OR N-TIER DATABASE ARCHITECTURES?
Ans: 1-Tier Database Architecture is based on
single system, which acts
as both server and client.
2-Tier Architecture is based on one server and
client.
3-Tier Architecture is based on one server and
client out that on client act as a remote system.
N-Tier Architecture is based on N no. Of servers
and N no. Of clients.
15. WHAT ARE A TABLE, COLUMN,
AND RECORD?
Ans: Table:
A Table is a database object that holds your data. It is made up of many
columns. Each of these columns has a data type associated with it.
Column: A column, referred to as an attribute, is
similar to a field in the file system.
Record: A row, usually referred to as tuple, is
similar to record in the file system.
16. WHAT IS DIFFERENCE
BETWEEN A PROCEDURAL LANGUAGE AND A NON-PROCEDURAL LANGUAGE?
Ans:
Procedural Language NON-Procedural
Language
A program in this implements a step-by-step
algorithm to solve the problem. It contains what to do but not how to do .
17.WHAT TYPE OF LANGUAGE
"SQL" IS?
Ans: SQL is a Non-procedural, 4th generation
Language,/ which concerts what to do rather than how to do any process.
18. CLASSIFICATION OF SQL
COMMANDS?
Ans:
DDL (Data Definition Language) DQL [Data Querying Lnaguage ]
DML (Data Manipulating Language) DCL (Data Control Language)
TCL(Data Transaction Language)
Create Alter
Drop Truncate Rename, Select , Insert
Update Delete Merge , Grant Revoke , Rollback Commit savepoint
19. WHAT IS DIFFERENCE
BETWEEN DDL AND DML COMMANDS?
Ans: For DDL commands autocommit is ON implicitly
whereas For DML commands autocommit is to be turned ON explicitly.
20. WHAT IS DIFFERENCE
BETWEEN A TRANSACTION AND A QUERY?
Ans: A Transaction is unit of some commands where
as Query is a single line request for the information from the database.
21. WHAT IS DIFFERENCE
BETWEEN TRUNCATE AND DELETE COMMANDS?
Ans: Truncate Command will delete all the records
where as Delete
Command will delete specified or all the records
depending only on the condition given.
22. WHAT IS DIFFERENCE
BETWEEN UPDATE AND ALTER COMMANDS?
Ans: Alter command is used to modify the database
objects where as the
Update command is used to modify the values of a
data base objects.
23. WHAT ARE COMMANDS OF DCL
CATEGORY?
Ans: Grant and Revoke are the two commands belong
to the DCL Category.
24. WHICH IS AN EFFICIENT
COMMAND - TRUNCATE OR DELETE? WHY?
Ans: Delete is the efficient command because using
this command we can delete only those records that are not really required.
25. WHAT ARE RULES FOR NAMING
A TABLE OR COLUMN?
Ans: 1) Names must be from 1 to 30 bytes long.
2) Names cannot contain quotation marks.
3) Names are not case sensitive.
4) A name must begin with an alphabetic character
from your database character set and the characters $ and #.
But these
characters are discouraged.
5) A name cannot be ORACLE reserved word.
6) A name must be unique across its namespace.
Objects in the name space must have different names.
7) A name can be enclosed in double quotes.
26. HOW MANY COLUMNS CAN A
TABLE HAVE?
Ans: A Table can have 1000 columns.
27. WHAT ARE DIFFERENT
DATATYPES SUPPORTED BY SQL?
Ans: Char (size), Nchar (size), Varchar2 (size),
Nvarchar2 (size) data types for character values,
Number (precision, scale), Number, Number (n),
Float, Float (binary precision) data types for numerical values, Date data type
for date values,
Long, Raw (size), Long Raw, Clob, Blob, Nclob,
Bfile for large objects.
28. WHAT IS DIFFERENCE
BETWEEN LONG AND LOB DATATYPES?
Ans:
LOB LONG
1) The maximum size is 4GB.
2) LOBs (except NCLOB) can be attributes of an
object type.
3) LOBs support random access to data.
4) Multiple LOB columns per table or LOB attributes
in an object type.
1) The maximum
size is 2GB.
2) LONGs
cannot.
3) LONGs support
only sequential access.
4) Only one LONG column was allowed in a table
29. WHAT IS DIFFERENCE
BETWEEN CHAR AND VARCHAR2 DATATYPES?
Ans: Varchar2 is similar to Char but can store
variable no. Of characters and while querying the table varchar2 trims the extra spaces from the column and
fetches the rows that exactly match the criteria.
30. HOW MUCH MEMORY IS ALLOCATED
FOR DATE DATATYPE? WHAT IS DEFAULT DATE FORMAT IN ORACLE?
Ans: For Date data type oracle allocates 7 bytes
Memory.
Default
Date Format is: DD-MON-YY.
31. WHAT IS RANGE FOR EACH
DATATYPE OF SQL?
Ans:
Datatype Range
Char
Varchar2 Number Float
LONG, RAW, LONGRAW Large Objects
(LOB’s) 2000 bytes
4000 bytes
Precision 1 to 38 Scale -84 to 127 Precision 38 decimals Or 122 binary
precision 2
GB 4GB
32. HOW TO RENAME A COLUMN?
Ans: We can’t rename a Column of a table directly.
So we follow the following steps.
To Rename a Column:
a) Alter
the table specifying new column name to be given and data type.
b) Then
copy the values in the column to be renamed into new column.
c) drop
the old column.
33. HOW TO DECREASE SIZE OR
CHANGE DATATYPE OF A COLUMN?
Ans: To Decrease the size of a Data type of a
column
i. Truncate
the table first.
ii. Alter
the table column whose size is to be decreased using the same name and data
type but new size.
34. WHAT IS A CONSTRAINT?
WHAT ARE ITS VARIOUS LEVELS?
Ans: Constraint: Constraints are representators of
the column to
enforce data entity and consistency.There r two
levels 1)Column-level constraints 2)Table-level constraints.
35. LIST OUT ALL THE
CONSTRAINTS SUPPORTED BY SQL.
Ans: Not Null, Unique, Check, Primary Key and
Foreign Key or Referential Integrity.
36. WHAT IS DIFFERENCE
BETWEEN UNIQUE+NOT NULL AND PRIMARY KEY?
Ans: Unique and Not Null is a combination of two
Constraints that can be present any number of times in a table and can’t be a
referential key to any column of an another table where as Primary Key is
single Constraint that can be only once for table and can be a referential key
to a column of another table becoming a referential integrity.
37. WHAT IS A COMPOSITE
PRIMARY KEY?
Ans: A Primary key created on combination of
columns is called Composite Primary Key.
38. WHAT IS A CANDIDATE
COLUMN? HOW MANY CANDIDATE COLUMNS CAN BE
POSSIBLE PER COMPOSITE PRIMARY KEY?
Ans: It is a part of composite primary key. Maximum 32 candidate key can be there in
composite primary key.
39. HOW TO DEFINE A NULL
VALUE?
Ans: A NULL value is something which is
unavailable, it is neither zero nor a space and any mathematical calculation
with NULL is always NULL.
40. WHAT IS NULL? A CONSTRAINT OR
DEFAULT VALUE?
Ans: It is a default value.
41. WHAT IS DEFAULT VALUE FOR EVERY COLUMN OF A TABLE?
Ans: NULL.
42. WHAT IS CREATED IMPLICITLY FOR EVERY UNIQUE AND PRIMARY KEY COLUMNS?
Ans: Index.
43. WHAT ARE LIMITATIONS OF CHECK CONSTRAINT?
Ans: In this we can't specify Pseudo Columns like
sysdate etc.
44. WHAT IS DIFFERENCE BETWEEN REFERENCES AND FOREIGN KEY CONSTRAINT?
Ans: References is used as column level key word
where as foreign key
is used as table level constraint.
45. WHAT IS "ON DELETE CASCADE"?
Ans: when this key word is included in the
definition of a child table
then
whenever the records from the parent table is deleted automatically the
respective values in the child table will be deleted.
46. WHAT IS PARENT-CHILD OR MASTER-DETAIL RELATIONSHIP?
Ans: A table which references a column of another
table(using References)is called as a
child table(detail table) and a table
which is being referred is called
Parent (Master) Table .
47. HOW TO DROP A PARENT TABLE WHEN IT’S CHILD TABLE EXISTS?
Ans: Using "on delete cascade".
48. IS ORACLE CASE SENSITIVE?
Ans: NO
49. HOW ORACLE IDENTIFIES EACH RECORD OF TABLE UNIQUELY?
Ans: By Creating indexes and reference IDs.
50. WHAT IS A PSEUDO-COLUMN? NAME SOME PSEUDO-COLUMNS OF ORACLE?
Ans: Columns that are not created explicitly by the
user and can be
used explicitly in queries are called Pseudo-Columns.
Ex:currval,nextval,sysdate,new,old,sqlcode,sqlerrm,rownum,rowid,level
51. WHAT FOR "ORDER BY" CLAUSE FOR A QUERY?
Ans: To arrange the query result in a specified
Order (ascending,descending) by default it takes
ascending order.
52. WHAT IS "GROUP BY" QUERIES?
Ans: To group the query results based on condition.
53. NAME SOME AGGREGATE FUNCTIONS OF SQL?
Ans: AVG, MAX, SUM, MIN,COUNT.
54. WHAT IS DIFFERENCE BETWEEN COUNT (), COUNT (*) FUNCTIONS?
Ans: Count () will count the specified column
whereas count (*) will
count total no. of rows in a table.
55. WHAT FOR ROLLUP AND CUBE OPERATORS ARE?
Ans: To get subtotals and grand total of values of
a column.
56. WHAT IS A SUB-QUERY?
Ans: A query within a query is called a sub query where the result of
inner query will be used by the outer query.
57. WHAT ARE SQL OPERATORS?
Ans: Value (), Ref () is SQL operator. ( Used with
Objects )
58. EXPLAIN
"ANY","SOME","ALL","EXISTS" OPERATORS?
Ans: Any:
The Any (or it’s synonym SOME) operator computes the lowest
value from the set and compares a value to each
returned by a sub query.
All: ALL compares a
value to every value returned by SQL.
Exists: This operator
produces a BOOLWAN results. If a sub query
produces
any result then it evaluates it to TRUE else it evaluates it to FALSE.
59. WHAT IS A CORRELATED SUB QUERY, HOW IT IS DIFFERENT FROM A NORMAL
SUB QUERY?
Ans: A correlated subquery is a nested subquery,
which is executed once
for each ‘Candidate row’ by the main query, which
on execution uses a value from a column in the outer query. In normal sub query
the result of inner query is dynamically substituted in the condition of the
outer query where as in a correlated subquery, the column
value used in inner query refers to the column
value present in the
outer query forming a correlated subquery.
60. WHAT IS A JOIN - TYPES OF
JOINS?
Ans: A join is used to combine two or more tables
logically to get
query results.
There are four types of Joins namely
EQUI
Join
NON-EQUI
Join
SELF
Join
OUTER
Join.
61. WHAT ARE MINIMUM
REQUIREMENTS FOR AN EQUI-JOIN?
Ans: There shold be atleast one common column
between the joining tables.
62. WHAT IS DIFFERENCE BETWEEN
LEFT, RIGHT OUTER JOIN?
Ans:If there r any values in one table that do not
have corresponding values in the other,in an equi join that row will not be
selected.Such rows can be forcefully selected by using outer join symbol(+) on
either of the sides(left or right) based
on the requirement.
63. WHAT IS DIFFERENCE BETWEEN
EQUI AND SELF JOINS?
Ans: SELF JOIN is made within the table whereas
EQUI
JOIN is made between different tables
having common column.
64. WHAT ARE "SET"
OPERATORS?
Ans: UNION ALL,UNION, INTERSECT ,MINUS are SET
OPERATORS.
65. WHAT IS DIFFERENCE BETWEEN
"UNION" AND "UNION ALL"
OPERATORS?
Ans: UNION will return the values distinctly
whereas UNION ALL will
return even duplicate values.
66. NAME SOME NUMBER, CHARACTER,
DATE, CONVERSION, OTHER
FUNCTIONS.
Ans: Number Functions:
Round (m, [n]), Trunc (m,
[n]), Power (m, n), Sqrt(n),
Abs (m), Ceil (m), Floor (m), Mod
(m, n) ,sign(n)
Character
Functions:
Chr (x), Concat (string1, string2), Lower (string)
Upper (string), Substr
(string, from_str, to_str), ASCII (string)
Length (string), Initcap
(string).
Date
Functions:
Sysdate, Months between (d1, d2),
To_char (d, format)
Last day (d), Next_day (d, day).add_months(d,n), Extract
Conversion Functions: To_char, To_date, To_number
67. WHAT IS DIFFERENCE BETWEEN
MAX () AND GREATEST () FUNCTIONS?
Ans: MAX is an aggregate function which takes only
one column name of a table as parameter whereas Greatest is a general function
which can take any number of values and column names from dual and table
respectively.
68. WHAT FOR NVL () FUNCTION IS?
Ans: NVL Function helps in substituting a value in
place of a NULL.
69. WHAT FOR DECODE () FUNCTION
IS?
Ans: It is substitutes value basis and it actually
does an
'if-then-else' test.
70. WHAT IS DIFFERENCE BETWEEN
TRANSLATE () AND REPLACE () FUNCTIONS?
Ans: Translate()
is a superset of functionality provided by Replace().
71. WHAT IS DIFFERENCE BETWEEN
SUBSTR () AND INSTR () FUNCTIONS?
Ans: Substr() will return the specified part of a
string whereas
Instr()
return the position of the specified part of the string.
72. WHAT IS A JULIAN DAY NUMBER?
Ans: It will return count of the no. Of days
between January 1, 4712 BC
and the given date.
73. HOW TO DISPLAY TIME FROM A
DATE DATA?
Ans: By using time format as 'hh [hh24]: mi: ss' in
to_char() function.
74. HOW TO INSERT DATE AND TIME
INTO A DATE COLUMN?
Ans: By using format 'dd-mon-yy hh [hh24]: mi: ss'
in to_date() function.
75. WHAT IS DIFFERENCE BETWEEN
TO_DATE () AND TO_CHAR () CONVERSION
FUNCTIONS?
Ans: To_date converts character date to date format
whereas
To_char
function converts date or numerical values to characters.
76. WHAT IS A VIEW? HOW IT IS
DIFFERENT FROM A TABLE?
Ans: View is database object, which exists
logically but contains no
physical data and manipulates the base table.
View is saved as a select statement in the database
and contains no
physical data whereas Table exists physically.
77. WHAT IS DIFFERENCE BETWEEN
SIMPLE AND COMPLEX VIEWS?
Ans: Simple views can be modified whereas Complex
views (created based
on more than one table) cannot be modified.
78. WHAT IS AN INLINE VIEW?
Ans: Inline view is basically a subquery with an
alias that u can use
like a view inside a SQL statement. It is not a
schema object like SQL-object.
79. HOW TO UPDATE A COMPLEX
VIEW?
Ans: Using
'INSTEAD OF' TRIGGERS Complex views can be Updated.
80. WHAT FOR "WITH CHECK
OPTION" FOR A VIEW?
Ans: "WITH CHECK OPTION" clause specifies
that inserts and updates r performed through the view r not allowed to create
rows which the view cannot select and
therefore allows integrity constraints and data validation checks to be
enforced on data being inserted or updated.
81. WHAT IS AN INDEX? ADVANTAGE OF AN INDEX
Ans: An Index is a database object used n Oracle to
provide quick
access to rows in a table. An Index increases the
performance of the database.
82. WHAT IS A SEQUENCE?
PSEUDO-COLUMNS ASSOCIATED WITH SEQUENCE?
Ans: Sequence is a Database Object used to generate
unique integers to use as primary keys.
Nextval, Currval are the Pseudo Columns associated with the sequence.
**83. WHAT IS A CLUSTER? WHEN TO USE A CLUSTER? HOW TO DROP A CLUSTER
WHEN CLUSTERED TABLE EXISTS?
Ans: Cluster and Indexes are transparent to the
user. Clustering is a
method of storing tables that are intimately
related and are often joined together into the same area on the disk.
When cluster table exists then to drop cluster we
have to drop the table first then only cluster is to be dropped.
84. WHAT IS A SNAPSHOT OR MATERIALIZED
VIEW?
Ans: Materialized views can be used to replicate
data. Earlier the data
was replicated through CREATE SNAPSHOT command. Now
CREATE MATERIALIZED VIEW can be used as synonym for CREATE SNAPSHOT. Query
performance is improved using the materialized view as these views pre
calculate expensive joins and aggregate operations on the table.
85. WHAT IS A SYNONYM?
Ans: A
Synonym is a database object that allows you to create alternate names for
Oracle tables and views. It is an alias for a table, view, snapshot, sequence,
procedure, function or
package.
86. WHAT IS DIFFERENCE BETWEEN
PRIVATE AND PUBLIC SYNONYM?
Ans: Only the user or table owner can reference
Private synonym whereas
any user can reference the Public synonym.
87. WHAT IS DIFFERENCE BETWEEN
"SQL" AND "SQL*PLUS" COMMANDS?
Ans: SQL
commands are stored in the buffer whereas SQL*PLUS are not.
**88. NAME SOME SQL*PLUS COMMANDS?
Ans: DESC
[CRIBE], START, GET, SAVE, / are SQL*PLUS COMMANDS.
89. WHAT ARE "SQL*PLUS
REPORTING" COMMANDS?
Ans: SPOOL file-name, SPOOL OFF, TTITLE, BTITLE,
BREAK ON, COMPUTE <any
aggregate function> OF <column name>
[break] ON <column name> etc are SQL*PLUS REPORTING COMMANDS.
90. WHAT ARE SYSTEM AND OBJECT
PRIVILEGES?
Ans: Connect and Resource etc are System
Privileges.
Create <object>, Select, Insert, Alter etc
are Object Privileges.
91. WHAT FOR DCL COMMANDS ARE?
Ans: Commit, Rollback are DCL commands.
92. WHAT FOR GRANT COMMAND WITH
"WITH GRANT OPTION"?
Ans: “With Grant Option” with Grant Command gives
privileges to the
user to grant privileges to other user(s)
among the privileges he/she has.
93. HOW TO CHANGE PASSWORD OF A
USER?
Ans: Using Password command or
Using ALTER USER <user name> IDENTIFIED BY
<new password> COMAND.
94. WHAT IS A SCHEMA AND SCHEMA OBJECTS?
Ans: A schema is a collection of logical structures
of data, or schema
objects. A schema is owned by the database user and
has the same name as that of user. Each user owns a single schema. Schema
objects include following
type of objects Clusters, Database Links,
Functions, Indexes, Packages,
Procedures, Sequences, Synonyms, Tables, Database
Triggers, Views.
**95. HOW TO STARTUP AND
SHUTDOWN ORACLE DATABASE?
Ans: Startup and Shutdown Oracle database can be
done by only the
administator. Startup is done by using STARTUP
command and Shutdown is done by SHUTDOWN command
96. WHAT IS A SESSION?
Ans: The period between Login and Logoff on schema.
97. WHAT IS A CLIENT PROCESS?
WHAT IS A SERVER PROCESS?
Ans: ref: 172 Q & A.
98. HOW TO MAKE EVERY DML
OPERATION AS AUTO COMMIT?
Ans: By using SET AUTOCOMMIT ON command.
99. HOW TO DISPLAY DATA PAGE
WISE IN SQL?
Ans: By using SET PAUSE ON command.
100. HOW TO CHANGE LINE SIZE, PAGE SIZE AND SQL PROMPT?
Ans: By using SET LINESIZE <value>, SET
PAGESIZE <value>,
SET
SQLPROMPT <new prompt>.
101. HOW PL/SQL IS DIFFERENT FROM SQL?
Ans: SQL is non-procedural language whereas PL/SQL
is procedural
language that includes features and design of
programming language.
102. WHAT IS ARCHITECTURE OF PL/SQL?
Ans: Give
picture & Explain
103. WHAT IS A PL/SQL BLOCK?
Ans: DECLARE
<declarations>
BEGIN
<Exececutable Statements>
EXCEPTION
<Exception Handler(s)>
END;
104. WHAT ARE DIFFERENT TYPES OF PL/SQL BLOCKS?
Ans: DECLARE BLOCK: In this block all the
declarations of the variable
used in the program is made. If no variables are
used this block will become optional.
BEGIN BLOCK: In this block all the executable statements are
placed. This block is Mandatory.
EXCEPTION BLOCK: In this block all the exceptions are handled.
This block is also very optional.
END: Every begin must be ended with this END; statement.
Anonymous & Named Blocks
105. WHAT ARE COMPOSITE DATA TYPES?
Ans: Records, Tables are two Composite data types.
106. WHAT IS SCOPE OF A VARIABLE IN PL/SQL BLOCK?
Ans: The visuability and accessibility of a
variable within the
block(s) is called scope of a variable.
107. WHAT IS A NESTED BLOCK?
Ans: A block within a block is called Nested Block.
108. WHAT IS A PL/SQL ENGINE?
Ans: The
PL/SQL engine accepts any valid PL/SQL block as input, executes the procedural
part of the statements and sends the SQL statements to the SQL statement
executor in the Oracle server.
109. WHAT IS DEFAULT VALUE FOR A NUMERIC PL/SQL VARIABLE?
Ans: NULL
110. WHAT IS DIFFERENCE BETWEEN SIMPLE LOOP AND A FOR LOOP?
Ans: Simple requires declaration of variables used
in it and exit
condition but For Loop doesn’t require this.
111. WHAT IS A CURSOR? STEPS TO USE A CURSOR?
Ans: Cursor is Private SQL area in PL/SQL.
Declare
the Cursor,
Open the
Cursor,
Fetch
values from SQL into the local Variables,
Close
the Cursor.
112. HOW MANY TYPES OF CURSORS ARE SUPPORTED BY ORACLE?
Ans: There
are two types of cursors namely Implicit Cursor, Explicit Cursor.
113. WHAT IS A CURSOR FOR LOOP?
Ans: Cursor For Loop is shortcut process for
Explicit Cursors because
the Cursor is Open, Rows are fetched once for each
iteration and the cursor is closed automatically when all the rows have been
processed.
114. WHAT ARE CURSOR ATTRIBUTES?
Ans: %Found
%NotFound
%IsOpen
%RowCount are the cursor attributes.
115. WHAT IS USE OF CURSOR WITH "FOR UPDATE OF" CLAUSE?
Ans: This Clause stop accessing of other users on
the particular
columns used by the cursor until the COMMIT is
issued.
116. WHAT IS AN EXCEPTION? HOW IT IS DIFFERENT FROM ERROR?
Ans: Whenever an error occurs Exception raises.
Error is a bug whereas the Exception is a warning or error condition.
117. NAME SOME BUILT-IN EXCEPTIONS.
Ans: Too_Many_Rows,
No_Data_Found, Zero_Divide, Not_Logged_On
Storage_Error, Value_Error etc.
118. HOW TO CREATE A USER-DEFINED EXCEPTION?
Ans: User-Defined Exception is created as follows:
DECLARE
<exception
name> EXCEPTION;
-
- - - - - - - - ;
- - - - - - - - -;
BEGIN
- - - - - - - - -;
- - - - - - - - -;
RAISE
<exception name>;
EXCEPTION
WHEN
<exception name> THEN
- - - - - - - - -;
- - - - - - - - -;
END;
119. WHAT IS "OTHERS" EXCEPTION?
Ans: It is used to along with one or more exception
handlers.
This
will handle all the errors not already handled in the block.
120. WHAT IS SCOPE OF EXCEPTION HANDLING IN NESTED BLOCKS?
Ans: Exception scope will be with in that block in
which exception
handler is written.
121. WHAT IS A SUB-PROGRAM?
Ans: A SUBPROGRAM IS A PL/SQL BLOCK, WHICH WILL BE
INVOKED BY TAKING
PARAMATERS.
122. WHAT ARE DIFFERENT TYPES OF SUB-PROGRAMS?
Ans: THEY R TWO TYPES: 1) PROCEDURE 2) FUNCION.
123. HOW A PROCEDURE IS DIFFERENT FROM A FUNCTION?
Ans: Function has return key word and returns a
value whereas a
Procedure doesn’t return any value.
124. WHAT ARE TYPES OF PARAMETERS THAT CAN BE
PASSED TO FUNCTION OR
PROCEDURE?
Ans: IN, IN OUT, OUT.
125. WHAT IS "IN OUT" PARAMETER?
Ans: A parameter, which gets value into the
Procedure or Function and
takes the value out of the Procedure or
Function area, is called IN OUT parameter.
126. DOES ORACLE SUPPORTS PROCEDURE OVERLOADING?
Ans: NO.
127. WHAT IS A PACKAGE AND PACKAGE BODY?
Ans: Package is declarative part of the functions
and procedures stored
in that package and package body is
the definition part of the functions and procedures
of that package.
128. WHAT IS ADVANTAGE OF PACKAGE OVER PROCEDURE OR
FUNCTION?
Ans: Packages provides Functions or Procedures
Overloading facility and security to
those Functions or Procedures.
129. IS IT POSSIBLE TO HAVE A PROCEDURE AND A
FUNCTION WITH THE SAME
NAME?
Ans: NO if it is out side a Package, YES if it is
within a Package.
130. DOES ORACLE SUPPORTS RECURSIVE FUNCTION CALLS?
Ans: YES.
131. WHAT IS A TRIGGER? HOW IT IS DIFFERENT FROM A
PROCEDURE?
Ans: Trigger:
A Trigger is a stored PL/SQL program unit associated
with a specific database table.
Procedure: A Procedure is to be explicitly called by the user
whereas Triggers are automatically called
implicitly
by Oracle
itself whenever event Occurs.
132. WHAT IS DIFFERENCE BETWEEN A TRIGGER AND A
CONSTRAINT?
Ans: Constraints are always TRUE whereas Triggers
are NOT always TRUE
and Constraints has some limitations whereas
Trigger has no limitations.
133. WHAT ARE DIFFERENT EVENTS FOR A TRIGGER AND
THEIR SCOPES?
Ans: Insert, Update or Delete.
134. WHAT IS DIFFERENCE BETWEEN TABLE LEVEL AND ROW
LEVEL TRIGGERS?
Ans: Table level Triggers execute once for each
table based transaction
whereas Row level Triggers will execute once FOR
EACH ROW.
** 135. WHAT ARE AUTONOMOUS TRIGGERS?
Ans: Supports to provide Commit statement in
Triggers. Triggers a declared as independent
Transactions.
136. WHAT IS AN "INSTEAD OF" TRIGGER?
Ans: These Triggers are used with the Complex Views
only to make
possible of Insert, Update and Delete on those
Views.
** 137. HOW MANY TRIGGERS CAN BE CONFIGURED ON A
TABLE AND VIEW?
Ans: 18 Triggers
138. WHAT IS "TABLE MUTATING" ERROR? HOW
TO SOLVE IT?
Ans: ORA-04091: Table
name is mutating, trigger/function may not see it
Cause : A trigger or a user-defined PL/SQL function
that is referenced
in the statement attempted to query or modify a
table that was in the middle of being modified by the statement that fired the
trigger.
Action : Rewrite the trigger or function so it does
not read the table.
139. WHEN TO USE ":NEW" AND
":OLD" SPECIFIERS?
Ans: The
prefix :old is used to refer to values already present in the
table. The prefix :new is a correlation name that
refers to the new value that is inserted
/ updated.
** 141. HOW TO CREATE A USER-DEFINED VARIABLE IN PL/SQL?
Ans: Define
variable in declaration section
142. HOW TO CREATE AN ARRAY VARIABLE IN PL/SQL?
Ans: Using CREATE [OR REPLACE] TYPE <type
name>
AS VARRAY (size) OF ELEMENT_TYPE (NOT NULL)
Command;
**143. HOW TO MAKE A USER-DEFINED DATA TYPE GLOBAL IN
PL/SQL?
Ans: Declare the variable in a Package
144. HOW TO CREATE AN OBJECT IN ORACLE?
Ans: Using CREATE [OR REPLACE] TYPE <type
name> AS OBJECT (ATTRIBUTE
NAME DATA TYPE,..) Command
145. WHAT IS A TRANSIENT AND PERSISTENT OBJECT?
Ans: The Object created in a table is called
Persistent Object.
Object
created on execution of PL/SQL block is called Transient Object.
**146. WHAT IS A COLUMN OBJECT AND TABLE OBJECT?
Ans: A Column Object is only a Column of a table.
147. HOW TO GRANT PERMISSION ON AN OBJECT TO OTHER
USER?
Ans: GRANT <permission> ON <object
name> TO <user name>.
148. WHAT IS A COLLECTION OF ORACLE?
Ans: Varray, Nested Table is a collection of
Oracle.
149. WHAT IS DIFFERENCE BETWEEN VARRAY AND NESTED
TABLE?
Ans: Varray has a fixed size.
Nested
tables can carry any number of values.
150. HOW TO MODIFY CONTENTS OF A VARRAY IN ORACLE?
Ans: To modify a stored VARRAY it has to selected
into a
PL/SQL
variable and then inserted back into the table.
151. WHAT IS USE OF "THE" OPERATOR FOR
NESTED TABLE?
Ans: THE operator allows nested tables to be
manipulated using DML when
it is stored in a Table.
152. WHICH PACKAGE IS USED FOR FILE INPUT/OUTPUT IN
ORACLE?
Ans: UTL_FILE Package is used for File input/output
in Oracle.
153. NAME SOME METHODS AND PROCEDURES OF FILE I/O
PACKAGE?
Ans: FOPEN, FCLOSE,
FFLUSH, IS_OPEN, GET_LINE, PUT_LINE, PUTF, NEW_LINE
**154. WHAT IS SQLJ? HOW IT IS DIFFERENT FROM JDBC
CONNECTIVITY?
Ans: SQLJ is basically a Java program containing
embedded static SQL
statements that are compatible with Java design
philosophy.
155. WHAT IS AN ITERATOR? Name some TYPES OF
ITERATORS?
Ans: SQLJ Iterators are basically record groups
generated during
transaction, which requires manipulation of more
than one records from one or more tables. There are two types Iterators namely
Named Iterator and Positional Iterator.
** 156. WHAT ARE DIFFERENT STEPS TO WRITE A DYNAMIC
SQL PROGRAM?
Ans: Eg:
char c_sqlstring[]={“DELETE FROM sailors WHERE rating>5”};
EXEC SQL PREPARE readytogo FROM :c_sqlstring;
EXEC SQL EXECUTE readytogo;
157. WHAT IS TABLE PARTITIONING AND INDEX
PARTITIONING?
Ans: Oracle8 allows tables and Indexes to be
partitioned or broken up into smaller parts based on range of key values.
Partitioning is a “divide and conquer” strategy that improves administration
and performance in data warehouse and OLTP systems.
159. WHAT IS PHYSICAL MEMORY STRUCTURE OF ORACLE?
Ans: The basic oracle memory structure associated
with Oracle includes:
Software Code Areas The System Global Area (SGA)
,The Database Buffer Cache
The shared Pool, The Program Global Areas (PGA),
Stack Areas ,Data Areas, Sort Areas
160. WHAT IS LOGICAL MEMORY STRUCTURE OF ORACLE?
Ans: Database, Tablespace , DB Object, Segment, Extents
161. WHAT IS SGA?
Ans: A System Global Area is a group of shared
memory allocated by
Oracle that contains data and control information
for one Oracle database instance. IF the multiple users are concurrently
connected to the same instance, the data in the instance’s SGA is “shared”
among the users.
Consequently, the SGA is often referred to as
either the “system Global Area” or the “Shared
Global Area”.
162. WHAT IS PGA?
Ans: The Program Global Area is a memory buffer
that contains data and
control information for a server process. A PGA is
created by Oracle when a server process is started. The information in a PGA
depends on the configuration of Oracle.
163. WHAT IS AN ORACLE INSTANCE?
Ans: Every time a database is started, an SGA is
allocated and Oracle
background processes are started. The combination
of these processes and memory buffers is called an Oracle instance.
164. WHAT ARE DIFFERENT ORACLE PROCESSES?
Ans: A
process is a “thread of control” or a mechanism in an operating
system that can be execute a series of steps. Some
operating systems use terms jobs or
task. A process normally has its own private memory
area in which it runs. An Oracle database system has general types of process:
User Processes and Oracle Processes.
**165. WHAT IS DIFFERENCE BETWEEN PMON AND SMON?
Ans: SMON (System Monitor) performs instance
recovery at instance of
startup. In a multiple instance system (one that
uses the parallel server), SMON of one instance can also perform instance
recovery other instance that have failed whereas The PMON (Process Monitor)
performs process recovery when a user process fails.
**166. WHAT IS DIFFERENCE BETWEEN DATABASE AND
TABLESPACE?
Ans:
Database is a physical Component
Tablespace is a Logical component
167. WHAT IS JOB OF DATABASE WRITER (DBWR) PROCESS?
Ans: The Data Base Writer writes modified blocks
from the database
buffer cache to the data files.
168. WHAT IS JOB OF LOG WRITER (LGWR) PROC*SS?
Ans: The Log Writer writes redo log files to disk.
Redo log data is
generated in the redo log buffer of the SGA. As
transactions commit and log buffer fills, LGWR writes redo entries into an
online redo log file.
169. WHAT IS RECOVERER?
Ans: The Recover (RECO) is used to resolve
distributed transactions that are pending due to network or system failure in a
distributed database. At timed intervals, the local RECO attempts to concept to
remote database and automatically complete the commit or rollback of the local
portion of any pending distributed transactions.
170. WHAT IS ARCHIVER?
Ans: The
Archiver (ARCH) copies the online redo log files to archival storage when they
are full. ,ARCH is active only when a database’s redo log is used ARCHILOG
mode.
** 171. WHAT IS A STORED QUERY?
Ans: VIEW
172. WHAT IS USER PROCESS AND SERVER PROCESS?
Ans: A User process is created and maintained to
execute the software
code of an application program (such as PRO *
Program) or an ORACLE tool (such as SQL * DBA). The User process also manages
the communication
with server processes. User processes communication
with the server
Processes through the program interface.
Other
processes call ORACLE processes. In a dedicated server
configuration, a server Process handles requests
for a single user process. A multithread
server configuration allows many user processes to
share a small number of server processes, minimizing the utilization of
available system resources.
**173. WHAT IS A SELF REFERENTIAL INTEGRITY?
Ans: Table related to itself .Foreign key of the
table links to primary key of the same
table.
174. WHAT IS A "RAISE" STATEMENT?
Ans: It is used to Raise Exceptions.
175. WHAT IS ROWID? HOW IT IS DIFFERENT FROM ROWNUM?
Ans: Rowid is the address of the row at where it is
stored in the
database. Rownum is count of records whereas Rowid
is identification of the each row.
No comments:
Post a Comment